Search code examples
pythonpandasdataframepivot-tablemulti-index

Pandas pivot_table column header Order

I am trying to make a pivot table with pandas and display it.

I am using this dataset 50000 Sales Records from https://eforexcel.com/wp/downloads-18-sample-csv-files-data-sets-for-testing-sales/

This is what it looks like

enter image description here

This is the code that I am using to generate the pivot table.

index = ['Region', 'Country']

columns = ['Item Type']

values = ['Order ID', 'Units Sold']

aggfunc = ['count', 'mean']

aggfunc = {i: j for i, j in zip(values, aggfunc)}

df_ = df[list(set(index + columns + values))]

df__ = pd.pivot_table(
    data=df_,
    index=index,
    values=values,
    columns=columns,
    aggfunc=aggfunc,
).reset_index()

df__

This is the result that I am getting. Here, the column headers are in the order values -> columns

enter image description here

Is there any way to get the result in the form of columns -> values i.e.

Thanks

enter image description here

Solution

  • Use DataFrame.swaplevel with sorting MultiIndex:

    df__ = pd.pivot_table(
    data=df_,
    index=index,
    values=values,
    columns=columns,
    aggfunc=aggfunc,
).swaplevel(1,0, axis=1)
 .sort_index([0,1], ascending=[True, False])
 .reset_index()