Pandas multiindex not sorted after sort_index method

Question

This is the head of my dataFrame

            McDonald's  Python  CSS  Microsoft Office        day  week day
Jour                                                                      
2017-06-11          87      22   12                31     Sunday         6
2017-06-12          63      38   24                55     Monday         0
2017-06-13          63      41   25                56    Tuesday         1
2017-06-14          73      41   25                55  Wednesday         2
2017-06-15          72      39   24                53   Thursday         3

I did a groupby operation on the dataFrame and I get :

df_week = df.groupby(["day", "week day"]).mean()
df_week

                    McDonald's     Python        CSS  Microsoft Office
day       week day                                                    
Friday    4          76.076923  36.615385  22.384615         51.769231
Monday    0          68.230769  37.000000  22.230769         54.230769
Saturday  5          87.416667  21.500000  11.416667         30.750000
Sunday    6          90.000000  21.615385  11.000000         30.538462
Thursday  3          69.923077  40.076923  24.615385         55.846154
Tuesday   1          66.230769  39.461538  24.153846         57.000000
Wednesday 2          68.923077  40.000000  24.846154         56.538462

Then I sorted my dataFrame using the weekday index.

df_week.sort_index(level="week day", inplace=True)

At the end the dataFrame looks like well sorted :

                    McDonald's     Python        CSS  Microsoft Office
day       week day                                                    
Monday    0          68.230769  37.000000  22.230769         54.230769
Tuesday   1          66.230769  39.461538  24.153846         57.000000
Wednesday 2          68.923077  40.000000  24.846154         56.538462
Thursday  3          69.923077  40.076923  24.615385         55.846154
Friday    4          76.076923  36.615385  22.384615         51.769231
Saturday  5          87.416667  21.500000  11.416667         30.750000
Sunday    6          90.000000  21.615385  11.000000         30.538462

But now, If I try to use the index values, they are still not sorted :

print(df_week.index.levels[0])
print(df_week.index.levels[1])

Index(['Friday', 'Monday', 'Saturday', 'Sunday', 'Thursday', 'Tuesday',
       'Wednesday'],
      dtype='object', name='day')
Int64Index([0, 1, 2, 3, 4, 5, 6], dtype='int64', name='week day')

If I look at the whole MultiIndex object it is clear that, index label and index lines are store separately.

MultiIndex(levels=[['Friday', 'Monday', 'Saturday', 'Sunday', 'Thursday', 'Tuesday', 'Wednesday'], [0, 1, 2, 3, 4, 5, 6]],
           labels=[[1, 5, 6, 4, 0, 2, 3], [0, 1, 2, 3, 4, 5, 6]],
           names=['day', 'week day'])

Thus, how can I access the index values in the right order ?

Bharath M Shetty · Accepted Answer · 2017-09-11 16:21:18Z

1

It is because multiindex levels is a frozenlist is always appears to be sorted and they hold the reference. So if you want the order then convert them to list from frozenlist. i.e if you use df.index.tolist() you can see the real order based of the dataframe. i.e

df.index.tolist()

[('Monday', 0),
 ('Tuesday', 1),
 ('Wednesday', 2),
 ('Thursday', 3),
 ('Friday', 4),
 ('Saturday', 5),
 ('Sunday', 6)]

answered Sep 11, 2017 at 16:21

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

1 Comment

Ger Over a year ago

The tolist() method does not work if you did it on one specific level. For example : df_week.index.levels[0].tolist() returns the unordered list. So you still have to extract the index you need from the list of tuples.

Collectives™ on Stack Overflow

Pandas multiindex not sorted after sort_index method

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related