How to sort MultiIndex using values from a given column

Question

I have a DataFrame with 2-level index and column with the numerical values. I want to sort it by level-0 and level-1 index in such a way that the the order of 0-level index is determined by the sum of values from Value column (descending), and the order of 1-level index is also determined by the values in Value column. This is my code:

import pandas as pd

df = pd.DataFrame()
df["Index1"] = ["A", "A", "B", "B", "C", "C"]
df["Index2"] = ["X", "Y", "X", "Y", "X", "Y"]
df["Value"] = [1, 4, 7, 3, 2, 7]
df = df.set_index(["Index1", "Index2"])
df

And this is the desired output (B is at the top because the sum is 10 and then we have X first because 7 >3):

Your desired output does not match your input. E.g, B has 6 and 3 (I) while 7 and 3 (O). — Timeless
– Timeless, Commented Nov 23, 2022 at 20:43

Timeless · Accepted Answer · 2022-11-23 20:49:14Z

2

You can do this with pandas.DataFrame.sort_values :

out= (
        df
         .assign(temp_col = df.groupby(level=0).transform("sum"))
         .sort_values(by=["temp_col", "Value"], ascending=[False, False])
         .drop(columns="temp_col")
     )

# Output :

print(out)

               Value
Index1 Index2       
B      X           7
       Y           3
C      Y           7
       X           2
A      Y           4
       X           1

answered Nov 23, 2022 at 20:49

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

1 Comment

Jason Over a year ago

Thank you, it works great. What If I would like to add three rows with indices ("A", "Total"), ("B", "Total"), and ("C", "Total") which represents the subtotals for each 0-level index?

Collectives™ on Stack Overflow

How to sort MultiIndex using values from a given column

1 Answer 1

# Output :

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

# Output :

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related