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><strong><em>Setup</em></strong>  </p>

<pre><code>d1 = pd.DataFrame(dict(
        year=np.random.choice((2014, 2015, 2016), 100),
        cntry=['United States' for _ in range(100)],
        State=np.random.choice(states, 100),
        Col1=np.random.randint(0, 20, 100),
        Col2=np.random.randint(0, 20, 100),
        Col3=np.random.randint(0, 20, 100),
    ))

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

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

<hr>

<p><strong><em>Answer</em></strong><br>
Easiest way would have been to only run <code>size</code> after <code>groupby</code></p>

<pre><code>d1.groupby(['year', 'cntry', 'State']).size()

year  cntry          State        
2014  United States  California       10
                     Florida           9
                     Massachusetts     8
                     Minnesota         5
2015  United States  California        9
                     Florida           7
                     Massachusetts     4
                     Minnesota        11
2016  United States  California        8
                     Florida           8
                     Massachusetts    11
                     Minnesota        10
dtype: int64
</code></pre>

<hr>

<p>To use the calculated <code>df</code></p>

<pre><code>df.xs('size', axis=1, level=1)
</code></pre>

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

<p>And that would be useful if the <code>size</code> were different for each column.  But because the <code>size</code> column is the same for <code>['Col1', 'Col2', 'Col3']</code>, we can just do</p>

<pre><code>df[('Col1', 'size')]

year  cntry          State        
2014  United States  California       10
                     Florida           9
                     Massachusetts     8
                     Minnesota         5
2015  United States  California        9
                     Florida           7
                     Massachusetts     4
                     Minnesota        11
2016  United States  California        8
                     Florida           8
                     Massachusetts    11
                     Minnesota        10
Name: (Col1, size), dtype: int64
</code></pre>

<hr>

<p><strong><em>Combined View 1</em></strong></p>

<pre><code>pd.concat([df[('Col1', 'size')].rename('size'),
           df.xs('sum', axis=1, level=1)], axis=1)
</code></pre>

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

<hr>

<p><strong><em>Combined View 2</em></strong>  </p>

<pre><code>pd.concat([df[('Col1', 'size')].rename(('', 'size')),
           df.xs('sum', axis=1, level=1, drop_level=False)], axis=1)
</code></pre>

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

python pandas