pandas Multiindex, sort only values of one level

Question

I am a bit stuck with sorting a pandas MultiIndex that is used for the columns of one of my datasets:

MultiIndex(levels=[['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], ['Number of Visitors', 'Profit']],
       labels=[[2, 2, 5, 5, 8, 8, 11, 11], [0, 1, 0, 1, 0, 1, 0, 1]],
       names=['Month', None])

I want profit to be displayed before number of visitors for each month, so in general I am happy with the levels as they are, but want the labels for the second level to be [1,0,1,0,1,0,1,0] instead of [0,1,0,1,0,1,0,1]. What is a way to obtain this? I have tried sort_index, but cannot get it to work the way I want.

Thank you in advance!

So if use df = df.sort_index(level=1, ascending=False) it not working? — jezrael
– jezrael, Commented Feb 1, 2019 at 13:25
How is your index being created? Are you able to just tell it to put Profit first? That should be all that’s required. In a groupby the order of the list determines the order of the grouping. — Jorden
– Jorden, Commented Feb 1, 2019 at 13:26
I assume with added axis=1. But unfortunately that isn't doing what i want, the column headers that I currently have (before making changes) are: (Mar, Number of Visitors), (Mar, Profit), (Jun, Number of Visitors), (Jun, Profit), etc. If I use df.sort_index(level=1, ascending=False, axis=1), I get: (Dec, Profit), (Sep, Profit), (Jun, Profit), (Mar, Profit), (Dec, Number of Visitors), etc. — stookie
– stookie, Commented Feb 1, 2019 at 13:36
@Jorden: The index is created from using pivot on a pandas dataframe. But yeah, in general swapping Profit and Number of Visitors is all I want, while leaving the rest of the index untouched, i.e. what I need: (Mar, Profit), (Mar, Number of Visitors), (Jun, Profit), (Jun, Number of Visitors), etc — stookie
– stookie, Commented Feb 1, 2019 at 13:38

jezrael · Accepted Answer · 2019-02-01 14:39:38Z

Use reindex by second level:

mux = pd.MultiIndex(levels=[['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 
                             'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'], 
                            ['Number of Visitors', 'Profit']],
       labels=[[2, 2, 5, 5, 8, 8, 11, 11], [0, 1, 0, 1, 0, 1, 0, 1]],
       names=['Month', None])
print (mux)

df = pd.DataFrame({'A':range(8)}, index=mux)
print (df)
                          A
Month                      
Mar   Number of Visitors  0
      Profit              1
Jun   Number of Visitors  2
      Profit              3
Sep   Number of Visitors  4
      Profit              5
Dec   Number of Visitors  6
      Profit              7

df = df.reindex(['Profit','Number of Visitors'], level=1)
print (df)
                          A
Month                      
Mar   Profit              1
      Number of Visitors  0
Jun   Profit              3
      Number of Visitors  2
Sep   Profit              5
      Number of Visitors  4
Dec   Profit              7
      Number of Visitors  6

Collectives™ on Stack Overflow

pandas Multiindex, sort only values of one level

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related