Home > OS >  Pandas groupby agg according to one column's values
Pandas groupby agg according to one column's values

Time:06-05

Datasource:


    Name    Country Height
0   Ben     UK  170
1   Bob     UK  159
2   Alice   UK  182
3   Craig   UK  172
4   Steve   UK  166
5   Jobs    UK  166
6   Jams    ZA  153
7   Tommy   ZA  176
8   Jacab   ZA  190

Target:

enter image description here

Tried:

h_range_labels=["150-159", "160-169", "170-179", "180-190"]
h_range=[150,160,170,180,190]
source.groupby(pd.cut(source["Height"], h_range, labels=h_range_labels)).agg({"Name":"count"}).cumsum()

How do I fulfillment the target as the picture above?

----------------updated question----------------------

Target 2: how to show as sum of row's percentage?

Country      UK      ZA
Height            
[150, 160)   16.67%   33.33%
[160, 170)   33.33%   0
[170, 180)   33.33%   33.33%
[180, 191)   16.67%   33.33%

CodePudding user response:

Try this:

df = df.groupby([pd.cut(df['Height'],bins = [150,160,170,180,191],right = False),'Country']).size().unstack()

Output:

Country     UK  ZA
Height            
[150, 160)   1   1
[160, 170)   2   0
[170, 180)   2   1
[180, 191)   1   1

Do get your additional output the following can be used:

df.div(df.sum(),axis=1).mul(100).applymap('{:.2f}%'.format)
  • Related