How to sort multilevel index on column values

Question

I'm trying to sort data in a multilevel index for visualisation. At this point it is purely to order the data based on the values

I've tried working with sort_index and sort_values, however neither have worked. I'm assuming there is a way to combine the 2 that is not clear to me.

Example code

import pandas as pd

data = {'lev1':[1,1,2,2], 
        'lev2':['item1', 'item2', 'item3', 'item2'], 
        'col1':[.55, .44, .22, .34],
        'col2':[.54, .86, .55, .44]}

df = pd.DataFrame(data=data)
df.set_index(['lev1', 'lev2'], inplace=True)

This should result in :

                col1    col2
    lev1 lev2       
    1   item1   0.55    0.54
        item2   0.44    0.86
    2   item3   0.22    0.55
        item2   0.34    0.44

What I would like to see is the output ordered based on the values in col2. However, keeping the multilevel index intact.

Meaning, the results should show:

                col1    col2
    lev1 lev2       
    1   item2   0.44    0.86
        item1   0.55    0.54
    2   item3   0.22    0.55
        item2   0.34    0.44

Any ideas or suggestions are welcome.

Thank you!

Possible duplicate of How to sort a dataFrame in python pandas by two or more columns? — Chris Adams
– Chris Adams, Commented May 20, 2019 at 11:38

jezrael · Accepted Answer · 2019-05-20 11:36:30Z

5

For pandas 0.23+ is possible sorting by index and by columns by DataFrame.sort_values:

df = df.sort_values(['lev1','col2'], ascending=[True, False])
print (df)
            col1  col2
lev1 lev2             
1    item2  0.44  0.86
     item1  0.55  0.54
2    item3  0.22  0.55
     item2  0.34  0.44

For lower versions of pandas is necessary DataFrame.reset_index, sorting and then DataFrame.set_index:

df = (df.reset_index()
        .sort_values(['lev1','col2'], ascending=[True, False])
        .set_index(['lev1','lev2']))

answered May 20, 2019 at 11:36

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

How to sort multilevel index on column values

Example code

This should result in :

Meaning, the results should show:

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Example code

This should result in :

Meaning, the results should show:

1 Answer 1

Comments

Linked

Related