ReOrder Column in Pivot table

Question

I am looking for a way to reorder column in my Pivot Table

Here rare my columns :

pivot.columns
pivot = dfpivot.pivot_table(index=['DEPARTMENT_NAME','LEVEL_NAME','NAME','CLTNAME'],columns=['StaffitWeek'], values=['ASSIGN_TIME'], margins=False, aggfunc=np.sum,fill_value=0)
pivot.columns

output :

MultiIndex(levels=[['ASSIGN_TIME'], ['S10', 'S11', 'S12', 'S13', 'S14', 'S15', 'S16', 'S17', 'S18', 'S19', 'S6', 'S7', 'S8', 'S9']],
           labels=[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0], [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]],
           names=[None, 'StaffitWeek'])

I would like to reorder Level column and get in order S6 S7 S8 S9 S10 S11 S12 S13 S14 S15 S16 S17 S18 S19 instead.

I have tried many topics but I cannot manage to do it.

Thanks for your help

jezrael · Accepted Answer · 2019-02-14T08:22:26.137

First remove [] for avoid MultiIndex in columns:

df = dfpivot.pivot_table(index=['DEPARTMENT_NAME','LEVEL_NAME','NAME','CLTNAME'],
                            columns='StaffitWeek', 
                            values='ASSIGN_TIME', 
                            aggfunc=np.sum,
                            fill_value=0)

And then sorting by pass columns names to sorted function with removing first letter by indexing:

df = df[sorted(df.columns, key=lambda x: int(x[1:]))]

Or use str.extract for extract integer, convert and get position by argsort:

df = df.iloc[:, df.columns.str.extract('(\d+)', expand=False).astype(int).argsort()]

Or use natsort:

import natsort as ns
df = df[sorted(ns.natsorted(df.columns), key=lambda x: not x.isdigit())]

ReOrder Column in Pivot table

1 Answers1