pandas groupby with aggregate function

Question

I have this code:

october_data_grouped = october_data.groupby(["Department", "Headcount", "Day"]).agg({'User ID': 'nunique'})
october_data_grouped.unstack(fill_value=0)

And this gives the output below:

Is there a way I can make a calculation that each individual entry is divided by the headcount [e.g. for row Accounts, day 1 would be 17% (6/35), day 4 would be 25% (9/35) etc.]

[How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — It_is_Chris, Jan 25 '22 at 14:21

score 0 · Accepted Answer · answered Jan 25 '22 at 14:25

0

Use MultiIndex.get_level_values with DataFrame.div:

#for avoid MultiIndex in columns
october_data_grouped = (october_data.groupby(["Department", "Headcount", "Day"])['User ID']
                                    .nunique()
                                    .unstack(fill_value=0))

idx = october_data_grouped.index.get_level_values('Headcount')
october_data_grouped = october_data_grouped.div(idx, axis=0)

answered Jan 25 '22 at 14:25

jezrael

822,522
95
1,334
1,252

works great, and how can i also add an average for each row at the end of the table ? thank you ! – Gabriel Bou Karam Jan 25 '22 at 14:57
@GabrielBouKaram - Use `october_data_grouped['avg'] = october_data_grouped.mean(axis=1)` – jezrael Jan 25 '22 at 14:58
1

great, thanks a lot! – Gabriel Bou Karam Jan 25 '22 at 15:02

pandas groupby with aggregate function

1 Answers1