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>@piRSquared has a great answer but in your particular case I think you might be interested in using pandas very flexible <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.GroupBy.apply.html" rel="nofollow" title="apply function">apply function</a>.  Because it can be applied to each group one at a time you can operate on multiple columns within the grouped DataFrame simultaneously.</p>

<pre><code>def your_function(sub_df):
    return np.mean(np.cos(sub_df['latitude']) + np.sin(sub_df['longitude']) - np.tan(sub_df['time_']))

def group_function(g):
    return pd.Series([g['is_active'].mean(), g['latitude'].median(), g['longitude'].median(), g['time_'].mean(), your_function(g)], 
                     index=['mean_active', 'median_lat', 'median_lon', 'mean_time', 'new_col'])

dt_m.groupby(['device_id', 'category']).apply(group_function)
</code></pre>

<p>However, I definitely agree with @piRSquared that it would be very helpful to see a full example including expected output.</p>

python pandas dataframe group-by