How to calculate metrics for one particular column from other columns in Pandas?
<p>I have a dataset where I need to calculate metrics related to each person on a dataset. For example, I have a dataframe with data that looks like this</p>

<pre><code>id   name    age   task_date    task_venue   money_earned
1    John    25    2016-05-01      A             100
2    Jane    28    2016-05-12      A             120
1    John    25    2016-05-03      B             150
3    Suse    21    2016-05-30      B             200
...        
</code></pre>

<p>So, what I need to do is calculate metrics for each person <code>John, Jane, Suse</code> etc., such as <code>Time Since Last Task</code>, <code>Average Earnings</code>, <code>Total Earnings</code>, <code>Total Tasks Done</code> and so on. </p>

<p>The real dataset is large (around 1M rows and has a lot more columns ~50 and ~75K persons), but this explains essentially what I need to do. </p>

<p>What would be the most efficient way to take this dataframe and do those calculations? I'd prefer do use pandas functions but pure python would also work as long as I can save the result in another dataframe?</p>

<p>You need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.groupby.html" rel="nofollow"><code>groupby</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.aggregate.html" rel="nofollow"><code>aggregate</code></a> metrics like for column <code>money_earned</code> <code>mean</code> and <code>sum</code>, for column <code>task_venue</code> <code>size</code> and so on:</p>

<pre><code>print (df.groupby('name').agg({'money_earned':['mean', sum], 'task_venue': len}))
     task_venue money_earned     
            len         mean  sum
name                             
Jane          1          120  120
John          2          125  250
Suse          1          200  200
</code></pre>

<p><a href="http://pandas.pydata.org/pandas-docs/stable/groupby.html#aggregation" rel="nofollow">Aggregation in pandas documentation</a>.</p>

<hr>

<p>After aggregation you get <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.html" rel="nofollow"><code>MultiIndex</code></a> in columns, you can flat it by <code>list comprehension</code>:</p>

<pre><code>df1 = df.groupby('name').agg({'money_earned':['mean', sum], 'task_venue': len})
df1.columns = ['_'.join(col) for col in df1.columns]
print (df1)
      task_venue_len  money_earned_mean  money_earned_sum
name                                                     
Jane               1                120               120
John               2                125               250
Suse               1                200               200
</code></pre>

<hr>

<p>If need aggregate custom function use:</p>

<pre><code>df.task_date = pd.to_datetime(df.task_date)

def f(x):
    #print (x)
    return x.min() + pd.Timedelta('3d')

df1 = df.groupby('name').agg({'money_earned':['mean',sum], 'task_venue': len, 'task_date':f})
df1.columns = ['_'.join(col) for col in df1.columns]
print (df1)
      task_venue_len task_date_f  money_earned_mean  money_earned_sum
name                                                                 
Jane               1  2016-05-15                120               120
John               2  2016-05-04                125               250
Suse               1  2016-06-02                200               200
</code></pre>

<hr>

<p>If aggregation is slow, because big <code>DataFrame</code>, I suggest <a href="http://dask.pydata.org/en/latest/dataframe-api.html#dask.dataframe.DataFrame.reduction" rel="nofollow"><code>dask.dataframe.DataFrame.reduction</code></a>.</p>

python pandas optimization data-analysis data-science