pandas: how to do multiple groupby-apply operations
<p>I have more experience with Râs <code>data.table</code>, but am trying to learn <code>pandas</code>. In <code>data.table</code>, I can do something like this:</p>

<pre><code>&gt; head(dt_m)
   event_id           device_id longitude latitude               time_ category
1:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
2:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
3:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
4:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
5:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
6:  1004583 -100015673884079572        NA       NA 1970-01-01 06:34:52   1 free
                 app_id is_active
1: -5305696816021977482         0
2: -7164737313972860089         0
3: -8504475857937456387         0
4: -8807740666788515175         0
5:  5302560163370202064         0
6:  5521284031585796822         0


dt_m_summary &lt;- dt_m[,
                     .(
                       mean_active = mean(is_active, na.rm = TRUE)
                       , median_lat = median(latitude, na.rm = TRUE)
                       , median_lon = median(longitude, na.rm = TRUE)
                       , mean_time = mean(time_)
                       , new_col = your_function(latitude, longitude, time_)
                     )
                     , by = list(device_id, category)
                     ]
</code></pre>

<p>The new columns (<code>mean_active</code> through <code>new_col</code>), as well as <code>device_id</code> and <code>category</code>, will appear in <code>dt_m_summary</code>. I could also do a similar <code>by</code> transformation in the original table if I want a new column that has the results of the groupby-apply:</p>

<p><code>dt_m[, mean_active := mean(is_active, na.rm = TRUE), by = list(device_id, category)]</code></p>

<p>(in case I wanted, e.g., to select rows where <code>mean_active</code> is greater than some threshold, or do something else).</p>

<p>I know there is <code>groupby</code> in <code>pandas</code>, but I havenât found a way of doing the sort of easy transformations as above. The best I could think of was doing a series of groupby-applyâs and then merging the results into one <code>dataframe</code>, but that seems very clunky. Is there a better way of doing that?</p>

<p>IIUC, use <code>groupby</code> and <code>agg</code>.  See <a href="http://pandas.pydata.org/pandas-docs/stable/groupby.html#applying-multiple-functions-at-once" rel="nofollow">docs</a> for more information.</p>

<pre><code>df = pd.DataFrame(np.random.rand(10, 2),
                  pd.MultiIndex.from_product([list('XY'), range(5)]),
                  list('AB'))

df
</code></pre>

<p><a href="http://i.stack.imgur.com/sqwqv.png" rel="nofollow"><img src="http://i.stack.imgur.com/sqwqv.png" alt="enter image description here"></a></p>

<pre><code>df.groupby(level=0).agg(['sum', 'count', 'std'])
</code></pre>

<p><a href="http://i.stack.imgur.com/NGOGJ.png" rel="nofollow"><img src="http://i.stack.imgur.com/NGOGJ.png" alt="enter image description here"></a></p>

<hr>

<p>A more tailored example would be</p>

<pre><code># level=0 means group by the first level in the index
# if there is a specific column you want to group by
# use groupby('specific column name')
df.groupby(level=0).agg({'A': ['sum', 'std'],
                         'B': {'my_function': lambda x: x.sum() ** 2}})
</code></pre>

<p><a href="http://i.stack.imgur.com/HPtvm.png" rel="nofollow"><img src="http://i.stack.imgur.com/HPtvm.png" alt="enter image description here"></a></p>

<p><strong><em>Note</em></strong> the <code>dict</code> passed to the <code>agg</code> method has keys <code>'A'</code> and <code>'B'</code>.  This means, run the functions <code>['sum', 'std']</code> for <code>'A'</code> and <code>lambda x: x.sum() ** 2</code> for <code>'B'</code> (and label it <code>'my_function'</code>)</p>

<p><strong><em>Note 2</em></strong> pertaining to your <code>new_column</code>.  <code>agg</code> requires that the passed functions reduce columns to scalars.  You're better off adding the new column ahead of the <code>groupby</code>/<code>agg</code></p>

python pandas dataframe group-by