pandas: create single size & sum columns after group by multiple columns
<p>I have a dataframe where I am doing groupby on 3 columns and aggregating the sum and size of the numerical columns. After running the code </p>

<pre><code>df = pd.DataFrame.groupby(['year','cntry', 'state']).agg(['size','sum'])
</code></pre>

<p>I am getting something like below:</p>

<p><a href="http://i.stack.imgur.com/mZvvz.png" rel="nofollow"><img src="http://i.stack.imgur.com/mZvvz.png" alt="Image of datafram"></a></p>

<p>Now I want to split my size sub columns from main columns and create only single size column but want to keep the sum columns under main column headings. I have tried different approaches but not successful.
These are the methods I have tried but unable to get things working for me:</p>

<p><a href="http://stackoverflow.com/questions/19384532/how-to-count-number-of-rows-in-a-group-in-pandas-group-by-object">How to count number of rows in a group in pandas group by object?</a></p>

<p><a href="http://stackoverflow.com/questions/10373660/converting-a-pandas-groupby-object-to-dataframe">Converting a Pandas GroupBy object to DataFrame</a></p>

<p>Will be grateful to if anyone can help me with this one.</p>

<p>Regards,</p>

<p>piRSquared beat me to it but if you must do it this way and want to keep the alignment with columns and sum or size underneath you could reindex the columns to remove the size value and then add in a new column to contain the size value.</p>

<p>For example:</p>

<pre><code>group = df.groupby(['year', 'cntry','state']).agg(['sum','size'])
mi = pd.MultiIndex.from_product([['Col1','Col2','Col3'],['sum']])
group = group.reindex_axis(mi,axis=1)
sizes = df.groupby('state').size().values
group['Tot'] = 0
group.columns = group.columns.set_levels(['sum','size'], level=1)
group.Tot.size = sizes
</code></pre>

<p>It will end up looking like this:</p>

<pre><code>                 Col1 Col2 Col3  Tot
                  sum  sum  sum size
year cntry State
2015 US    CA      20    0    4    1
           FL      40    3    5    1
           MASS     8    1    3    1
           MN      12    2    3    1
</code></pre>

python pandas