Subset of levels sorting in pandas MultiIndex Series troubles

Question

I have a Series with 3-level MultiIndex:

print(ser_test):
                            Value
Date       Group Country         
2014-01-31 3     AE       example
                 AR       example
2014-02-28 3     AE       example
                 AR       example
2014-03-31 3     AE       example
                 AR       example
2014-04-30 3     AE       example
                 AR       example
2014-05-30 3     AR       example
2014-06-30 2     AE       example
           3     AR       example
2014-07-31 2     AE       example
           3     AR       example
2014-08-29 2     AE       example
           3     AR       example
2014-09-30 2     AE       example
           3     AR       example
2014-10-31 2     AE       example
           3     AR       example
2014-11-28 2     AE       example
           3     AR       example
2014-12-31 2     AE       example
           3     AR       example

My goal is to sort Series firstly by Country and then by Date, ignoring Group level to achieve the next result:

                            Value
Date       Group Country         
2014-01-31 3     AE       example
2014-02-28 3     AE       example
2014-03-31 3     AE       example
2014-04-30 3     AE       example
2014-06-30 2     AE       example
2014-07-31 2     AE       example
2014-08-29 2     AE       example
2014-09-30 2     AE       example
2014-10-31 2     AE       example
2014-11-28 2     AE       example
2014-12-31 2     AE       example
2014-01-31 3     AR       example
2014-02-28 3     AR       example
2014-03-31 3     AR       example
2014-04-30 3     AR       example
2014-05-30 3     AR       example
2014-06-30 3     AR       example
2014-07-31 3     AR       example
2014-08-29 3     AR       example
2014-09-30 3     AR       example
2014-10-31 3     AR       example
2014-11-28 3     AR       example
2014-12-31 3     AR       example

And I need Group level further, so I can't simply eliminate it.

So I tried to use sort_index method like this:

print(ser_test.sort_index(level = ['Country', 'Date']))

or like that:

print(ser_test.sort_index(level = ['Country', 'Date'], sort_remaining = False))

In both cases I received a result, where Group level is involved in sorting process and have a priority before Date level:

                            Value
Date       Group Country         
2014-06-30 2     AE       example
2014-07-31 2     AE       example
2014-08-29 2     AE       example
2014-09-30 2     AE       example
2014-10-31 2     AE       example
2014-11-28 2     AE       example
2014-12-31 2     AE       example
2014-01-31 3     AE       example
2014-02-28 3     AE       example
2014-03-31 3     AE       example
2014-04-30 3     AE       example
2014-01-31 3     AR       example
2014-02-28 3     AR       example
2014-03-31 3     AR       example
2014-04-30 3     AR       example
2014-05-30 3     AR       example
2014-06-30 3     AR       example
2014-07-31 3     AR       example
2014-08-29 3     AR       example
2014-09-30 3     AR       example
2014-10-31 3     AR       example
2014-11-28 3     AR       example
2014-12-31 3     AR       example

I tried to use all the options of sort_index and achieved an unexpected success with this piece of code:

print(ser_test.sort_index(level = ['Country', 'Date'], ascending = [True, True]))

                            Value
Date       Group Country         
2014-01-31 3     AE       example
2014-02-28 3     AE       example
2014-03-31 3     AE       example
2014-04-30 3     AE       example
2014-06-30 2     AE       example
2014-07-31 2     AE       example
2014-08-29 2     AE       example
2014-09-30 2     AE       example
2014-10-31 2     AE       example
2014-11-28 2     AE       example
2014-12-31 2     AE       example
2014-01-31 3     AR       example
2014-02-28 3     AR       example
2014-03-31 3     AR       example
2014-04-30 3     AR       example
2014-05-30 3     AR       example
2014-06-30 3     AR       example
2014-07-31 3     AR       example
2014-08-29 3     AR       example
2014-09-30 3     AR       example
2014-10-31 3     AR       example
2014-11-28 3     AR       example
2014-12-31 3     AR       example

It's quite strange and I'm not sure that this is a universal way to get a guaranteed expected sorting result while using a MultiIndex is a critical option for me.

So, can you help me to understand sort_index principles and share to me a piece of code for this particular case?

jezrael · Accepted Answer · 2019-10-18 08:46:28Z

1

You can try upgrade to last version of pandas, tested in pandas 0.25.0 and working nice:

print(df.sort_index(level = ['Country', 'Date']))
                            Value
Date       Group Country         
2014-01-31 3     AE       example
2014-02-28 3     AE       example
2014-03-31 3     AE       example
2014-04-30 3     AE       example
2014-06-30 2     AE       example
2014-07-31 2     AE       example
2014-08-29 2     AE       example
2014-09-30 2     AE       example
2014-10-31 2     AE       example
2014-11-28 2     AE       example
2014-12-31 2     AE       example
2014-01-31 3     AR       example
2014-02-28 3     AR       example
2014-03-31 3     AR       example
2014-04-30 3     AR       example
2014-05-30 3     AR       example
2014-06-30 3     AR       example
2014-07-31 3     AR       example
2014-08-29 3     AR       example
2014-09-30 3     AR       example
2014-10-31 3     AR       example
2014-11-28 3     AR       example
2014-12-31 3     AR       example

answered Oct 18, 2019 at 8:46

jezrael

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

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

1 Comment

igharok Over a year ago

Thanks a lot, @jezrael ! I've updated pandas from 0.24.2 to 0.25.1 and sort_index is now working according to my expectations!

Collectives™ on Stack Overflow

Subset of levels sorting in pandas MultiIndex Series troubles

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