Sorting columns of multiindex dataframe

Question

I have a very large multi index dataframe with about 500 columns and each column has 2 sub columns.

The dataframe df looks as:

                  B2                  B5             B3
bkt              A1      A2           A2      A1     Z2      C1
Date                                                                        
2019-06-11       0.8     0.2          -6.0    -0.8   -4.1    -0.6    
2019-06-12       0.8     0.2          -6.9    -1.6   -5.3    -1.2    

df.columns
MultiIndex(levels=[['B2', 'B5', 'B3', .....], ['A1', 'A2' ......]],
           labels=[[1, 1, ....], [1, 0, ....]],
           names=[None, 'bkt'])

I am trying to sort only the column names and keep the values as it is within each column to get the following desired output:

                 B2                  B3             B5
bkt              A1      A2          C1      Z2     A1      A2
Date                                                                        
2019-06-11       ..
2019-06-12       ..

.. represents the values from the original dataframe. I just didn't retype them.

Setup

df = pd.DataFrame([
    [.8, .2, -6., -.8, -4.1, -.6],
    [.8, .2, -6.9, -1.6, -5.3, -1.2]
],
    pd.date_range('2019-06-11', periods=2, name='Date'),
    pd.MultiIndex.from_arrays([
        'B2 B2 B5 B5 B3 B3'.split(),
        'A1 A2 A2 A1 Z2 C1'.split()
    ], names=[None, 'bkt'])
)

sort_index(level=0, axis=1)?

BENY
– BENY

2019-06-10 14:05:40 +00:00
Commented Jun 10, 2019 at 14:05 — BENY
– BENY, Commented Jun 10, 2019 at 14:05

Saaru Lindestøkke · Accepted Answer · 2021-06-08 11:03:34Z

13

Using sort_index and assign it back

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

And from piR , we do not need create the copy of df, just do modification with the columns

df.columns=df.columns.sort_values(ascending=[True, False])

edited Jun 8, 2021 at 11:03

Saaru Lindestøkke

2,6051 gold badge33 silver badges64 bronze badges

answered Jun 10, 2019 at 14:09

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

piRSquared Over a year ago

Do this to avoid creating a new dataframe and only focus on sorting the index itself. df.columns = df.columns.sort_values(ascending=[True, False])

BENY Over a year ago

@piRSquared that is true :-)

Zanam Over a year ago

I am not sure @piRSquared solution works however. Your original solution does work.

Aaron Over a year ago

also, this only rearranges to columns and does not move the column data along with it. To do that it should be df.columns=df.sort_index(axis=1,level=[0,1],ascending=[True,False]).columns

PiWi Over a year ago

WARNING if you apply this: this move columns header but not the values inside ! It only rearrange header, not moving the values!! That is usually not what you want...

|

cstoafer · Accepted Answer · 2022-03-01 02:57:23Z

6

This should be done using sort_index to move both the column names and data:

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

answered Mar 1, 2022 at 2:57

cstoafer

611 silver badge2 bronze badges

Collectives™ on Stack Overflow

Sorting columns of multiindex dataframe

Setup

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Setup

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related