Nested groupby in DataFrame and aggregate multiple columns
<p>I am trying to do nested groupby as follows:</p>

<pre><code>&gt;&gt;&gt; df1 = pd.DataFrame({'Date': {0: '2016-10-11', 1: '2016-10-11', 2: '2016-10-11', 3: '2016-10-11', 4: '2016-10-11',5: '2016-10-12'}, 'Stock': {0: 'ABC', 1: 'ABC', 2: 'ABC', 3: 'ABC', 4: 'ABC', 5: 'XYZ'}, 'Quantity': {0: 60,1: 50, 2: 40, 3: 30, 4: 20, 5: 10}, 'UiD':{0:1,1:1,2:1,3:2,4:2,5:3}, 'StartTime': {0: '08:00:00.241', 1: '08:00:00.243', 2: '12:34:23.563', 3: '08:14.05.908', 4: '18:54:50.100', 5: '10:08:36.657'}, 'Sign':{0:1,1:1,2:0,3:-1,4:0,5:-1}, 'leg1':{0:2,1:2,2:4,3:5,4:7,5:8}})
&gt;&gt;&gt; df1
         Date  Quantity  Sign     StartTime Stock  UiD  leg1
0  2016-10-11        60     1  08:00:00.241   ABC    1     2
1  2016-10-11        50     1  08:00:00.243   ABC    1     2
2  2016-10-11        40     0  12:34:23.563   ABC    1     4
3  2016-10-11        30    -1  08:14.05.908   ABC    2     5
4  2016-10-11        20     0  18:54:50.100   ABC    2     7
5  2016-10-12        10    -1  10:08:36.657   XYZ    3     8
&gt;&gt;&gt; dfg1=df1.groupby(['Date','Stock'])
&gt;&gt;&gt; dfg1.apply(lambda x:x.groupby('UiD').first()).groupby(['Date','Stock']).apply(lambda x:np.sum(x['Quantity']))
Date        Stock
2016-10-11  ABC      90
2016-10-12  XYZ      10
dtype: int64
&gt;&gt;&gt;
&gt;&gt;&gt; dfg1['leg1'].sum()
Date        Stock
2016-10-11  ABC      20
2016-10-12  XYZ       8
Name: leg1, dtype: int64
</code></pre>

<p>So far so good. Now I am trying to concatenate the two results into a new DataFrame <code>df2</code> as follows:</p>

<pre><code>&gt;&gt;&gt; df2 = pd.concat([dfg1['leg1'].sum(), dfg1.apply(lambda x:x.groupby('UiD').first()).groupby(['Date','Stock']).apply(lambda x:np.sum(x['Quantity']))],axis=1)
                   0   1
Date       Stock        
2016-10-11 ABC    20  90
2016-10-12 XYZ     8  10
&gt;&gt;&gt;
</code></pre>

<p>I am wondering if there is a better way to re-write following line in order to avoid repetition of <code>groupby(['Date','Stock'])</code></p>

<pre><code>dfg1.apply(lambda x:x.groupby('UiD').first()).groupby(['Date','Stock']).apply(lambda x:np.sum(x['Quantity']))
</code></pre>

<p>Also this fails if <code>['Date','Stock']</code> contains <code>'UiD'</code> as one of the keys or if <code>['Date','Stock']</code> is replaced by just <code>['UiD']</code>.</p>