Pandas pivot_table to calculate share of margin
<p>I have a <code>Pandas</code> <code>DataFrame</code> named <code>df</code> that contains <code>n</code> <code>columns</code>. One of the <code>columns</code> is named <code>COUNT</code>, which shows how many times values in <code>A</code> occurs. <code>A</code> contains unique identifiers so every row has the value <code>1</code> in the <code>column</code> <code>COUNT</code>. It looks like this:</p>

<pre><code> A      B       C       D   E       COUNT
id1     cat1    1       a   15      1       
id2     cat2    2       b   14      1
id3     cat2    2       c   14      1
id4     cat1    1       d   15      1
id5     cat3    2       e   14      1
.....
</code></pre>

<p>Now I want to transform my <code>df</code> to look like this:</p>

<pre><code>                14      15      
cat1_tot        NaN     2       
cat1_share      NaN     1       
cat2_tot        2       NaN     
cat2_share      0.6666  NaN     
cat3_tot        1       NaN     
cat3_share      0.3333  NaN
All             3       2
</code></pre>

<p>I can get <code>catx_tot</code> by using <code>pd.pivot_table</code> </p>

<pre><code>pd.pivot_table(
     df, 
     values='COUNT', 
     index=['B'], 
     columns=['E'], 
     margins=True, 
     aggfunc=np.sum
)
</code></pre>

<p>But how do I add share to this?</p>

<p>combine <code>groupby.size</code> with <code>groupby.transform</code></p>

<pre><code>size = df.groupby(['B', 'E']).size()
sums = size.groupby(level='E').transform(np.sum)
aggd = pd.concat([size, size / sums], axis=1, keys=['total', 'share'])

aggd.unstack().stack(0)
</code></pre>

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

<hr>

<p>to get the <code>All</code> row</p>

<pre><code>all_ = aggd.groupby(level='E').sum().total.rename(('All', 'total'))
aggd.unstack().stack(0).append(all_)
</code></pre>

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

python pandas pivot-table