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)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.