[FIXED] how to calculate % of row and % of column for a python pivot with row and column headings?

Issue

For this simple pivot, how to turn value into % of row, and likewise to % of column?

import pandas as pd
df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
                   'B' : ['A', 'B', 'C'] * 4,
               'C' : range(12)})
pd.pivot_table(df, index='A', columns='B', aggfunc=sum)

Somehow after some searching I don't find answer to this simple question.

Expected result (if getting % of column)

      A   B  C
ONE   50% 24% 50%
THREE 13% 31% 42%
TWO  36% 45% 8%

Thanks

Solution

You can the desired output by using pd.crosstab:

import numpy as np
import pandas as pd

df = pd.DataFrame({'A' : ['one', 'one', 'two', 'three'] * 3,
                   'B' : ['A', 'B', 'C'] * 4,
               'C' : range(12)})

pd.crosstab(df.A, df.B, values=df.C, aggfunc=np.sum, normalize='columns')

This should yield:

B             A         B         C
A                                  
one    0.500000  0.227273  0.500000
three  0.166667  0.318182  0.423077
two    0.333333  0.454545  0.076923

You can reformat the output to display the percentages in hundredth with applymap:

pd.crosstab(df.A, df.B,values=df.C,aggfunc=np.sum,normalize='columns').applymap(lambda x: "{0:.0f}%".format(100*x))

This should yield:

B        A    B    C
A                   
one    50%  23%  50%
three  17%  32%  42%
two    33%  45%   8%

Edit:

If the normalize parameter is not working, you can get the percentage with apply:

pd.crosstab(df.A, df.B, values=df.C, aggfunc=np.sum).apply(lambda x: x/x.sum()).applymap(lambda x: "{:.0f}%".format(100*x))

I hope this proves useful.

Answered By - Abdou

Answer Checked By - David Goodson (PHPFixing Volunteer)