This is a follow up of my question. Rather than a pivot table, is it possible to flatten table to look like the following:
data = {'year': ['2016', '2016', '2015', '2014', '2013'],
'country':['uk', 'usa', 'fr','fr','uk'],
'sales': [10, 21, 20, 10,12],
'rep': ['john', 'john', 'claire', 'kyle','kyle']
}
pd.DataFrame(data).pivot_table(index='country', columns='year', values=['rep','sales'])
rep sales
year 2013 2014 2015 2016 2013 2014 2015 2016
country
fr None kyle claire None None 10 20 None
uk kyle None None john 12 None None 10
usa None None None john None None None 21
Flattened table:
rep_2013 rep_2014 rep_2015 rep_2016 sales_2013 sales_2014 sales_2015 sales_2016
country
fr None kyle claire None None 10 20 None
uk kyle None None john 12 None None 10
usa None None None john None None None 21
Click the Home tab on the Ribbon in the PowerPivot window. Click PivotTable. Select Flattened PivotTable from the dropdown list.
Flatten columns: use get_level_values() Flatten columns: use to_flat_index() Flatten columns: join column labels. Flatten rows: flatten all levels.
Index.flatten(order='C') Return a copy of the array collapsed into one dimension. Parameters : order : {'C', 'F', 'A'}, optional. Whether to flatten in C (row-major), Fortran (column-major) order, or preserve the C/Fortran ordering from a .
see collapse a pandas MultiIndex
df.columns = df.columns.to_series().str.join('_')
Try this:
df.columns = df.columns.get_level_values(0)
followed by:
df.columns = [' '.join(col).strip() for col in df.columns.values]
This should flatten your multi-index
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With