Custom sorting multiple levels of column index in pandas-CodePudding

I have the following dataframe:

What is the best way to maintain the ordering of level2 (Checks, Check Avg, Checks, Check AVg etc..), but sort the dates of the parent level in ascending order?

CodePudding user response：

Add sort_remaining=False to sort_index to prevent sorting levels below specified:

df = df.sort_index(level=0, axis=1, sort_remaining=False)

df:

Week        2021-10-11           2021-10-18           2021-10-25          
                Checks Check Avg     Checks Check Avg     Checks Check Avg
11th Street          4         5          2         3          0         1
16th Street         10        11          8         9          6         7
Bala Cynwyd         16        17         14        15         12        13

Sample Data Used:

import numpy as np
import pandas as pd

df = pd.DataFrame(
    np.arange(18).reshape((-1, 6)),
    index=['11th Street', '16th Street', 'Bala Cynwyd'],
    columns=pd.MultiIndex.from_arrays(
        [np.repeat(pd.to_datetime(['2021-10-25', '2021-10-18', '2021-10-11']),
                   2),
         ['Checks', 'Check Avg'] * 3],
        names=['Week', None]
    )
)

df:

Week        2021-10-25           2021-10-18           2021-10-11          
                Checks Check Avg     Checks Check Avg     Checks Check Avg
11th Street          0         1          2         3          4         5
16th Street          6         7          8         9         10        11
Bala Cynwyd         12        13         14        15         16        17

CodePudding user response：

You can use sort_index on axis=1. You can specify the sorting order per level.

Example input:

   a  b     c     a  c  b  a
   X  Y  Z  Y  X  Z  Z  X  Y
0  -  -  -  -  -  -  -  -  -

sorting:

df = df.sort_index(level=[0,1], ascending=[True, False], axis=1)

output:

   a        b        c      
   Z  Y  X  Z  Y  X  Z  Y  X
0  -  -  -  -  -  -  -  -  -