I’ve a sample dataframe
region headquarter sales base %growth month_year X Los Angeles 1000 2000 30 202101 X Florida City 2000 2000 20 202101 X Los Angeles 5000 6000 70 202001 X Florida City 4000 4500 45 202001
I’m trying to pivot the data using
data = data.pivot_table(columns=['month_year'], values=['sales', 'base', '%growth'], index=['region', 'headquarter']) print(data) > %growth base sales | | 202001 202101 202001 202101 202001 202101 ----------------------------------------------------------------------- region | headquarter | x | Los Angeles | 70 30 6000 2000 5000 1000 | Florida City | 45 20 4000 2000 4500 2000
The values are not in order which I’ve mentioned in the above snippet.
How can I re-structure my data to (by also repeating the row labels)
> 202001 202101 Sales base %growth Sales base %growth region headquarter X Los Angeles 5000 6000 70 1000 2000 30 X Florida City 4500 4000 45 2000 2000 20
Advertisement
Answer
Use DataFrame.swaplevel
with DataFrame.reindex
:
mux = pd.MultiIndex.from_product([data['month_year'].unique(), ['Sales','base','%growth']]) data = data.pivot_table(columns=['month_year'], values=['Sales', 'base', '%growth'], index=['headquarter']).swaplevel(1, 0, axis=1).reindex(mux, axis=1) print(data) 202101 202001 Sales base %growth Sales base %growth headquarter Florida City 2000 2000 20 4000 4500 45 Los Angeles 1000 2000 30 5000 6000 70
EDIT:
mux = pd.MultiIndex.from_product([data['month_year'].unique(), ['sales','base','%growth']]) data = data.pivot_table(columns=['month_year'], values=['sales', 'base', '%growth'], index=['region', 'headquarter']).swaplevel(1, 0, axis=1).reindex(mux, axis=1) print (data) 202101 202001 sales base %growth sales base %growth region headquarter X Florida City 2000 2000 20 4000 4500 45 Los Angeles 1000 2000 30 5000 6000 70