Flatten hierarchically indexed pandas.DataFrame from groupby and multiple aggregation
aggregate, indexing, pandas, python
Solution
In [36]: grouped.columns = grouped.columns.map('_'.join)
In [37]: grouped = grouped.reset_index()
In [38]: grouped
Out[38]:
city day cats_mean cats_std people_mean people_std
0 Berlin Friday 5.852991 1.085163 11.078541 0.839688
1 Berlin Monday 6.978343 0.630983 9.876106 1.846204
2 Oslo Friday 6.096773 1.278176 9.710216 0.691672
Problem
I'm grouping a dataframe by multiple columns and aggregating to obtain multiple statistics. How to obtain a totally flat structure with each possible combination of group-keys enumerated as rows and each statistic present as columns? ``` import numpy as np import pandas as pd cities = ['Berlin', 'Oslo'] days = ['Monday', 'Friday'] data = pd.DataFrame({ 'city': np.random.choice(cities, 12), 'day': np.random.choice(days, 12), 'people': np.random.normal(loc=10, size=12), 'cats': np.random.normal(loc=6, size=12)}) grouped = data.groupby(['city', 'day']).agg([np.mean, np.std]) ``` This way I'm getting: ``` cats people mean std mean std city day Berlin Friday 6.146924 0.721263 10.445606 0.730992 Monday 5.239267 NaN 9.022811 NaN Oslo Friday 6.322276 0.866899 11.579813 0.114341 Monday 5.028919 0.815674 10.458439 1.182689 ``` I need to get it flat: ``` city day cats_mean cats_std people_mean people_std Berlin Friday 6.146924 0.721263 10.445606 0.730992 Berlin Monday 5.239267 NaN 9.022811 NaN Oslo Friday 6.322276 0.866899 11.579813 0.114341 Oslo Monday 5.028919 0.815674 10.458439 1.182689 ```