#Updated: pandas version 0.23.0 solves this problem with
Sorting by a combination of columns and index levels
I have struggled with this and I suspect there is a better way. How do I sort the following dataframe by index level name 'idx_0', level=0 and by column, 'value_1' descending such that the column 'MyName' reads vertical 'SCOTTBOSTON'.
import pandas as pd
import numpy as np
df = pd.DataFrame({'idx_0':[2]*6+[1]*5,
'idx_1':[6,4,2,10,18,5,11,1,7,9,3],
'value_1':np.arange(11,0,-1),
'MyName':list('BOSTONSCOTT')})
df = df.set_index(['idx_0','idx_1'])
df
Output:
MyName value_1
idx_0 idx_1
2 6 B 11
4 O 10
2 S 9
10 T 8
18 O 7
5 N 6
1 11 S 5
1 C 4
7 O 3
9 T 2
3 T 1
#Excepted output using:
df.sort_values(['value_1'], ascending=False)\
.reindex(sorted(df.index.get_level_values(0).unique()), level=0)
I suspect there is an easier way without resetting indexes
MyName value_1
idx_0 idx_1
1 11 S 5
1 C 4
7 O 3
9 T 2
3 T 1
2 6 B 11
4 O 10
2 S 9
10 T 8
18 O 7
5 N 6
df.sort_values('value_1', ascending=False).sort_index(level=0)
Sort by values first then sort index level=0, but level=1 get sorted also.
MyName value_1
idx_0 idx_1
1 1 C 4
3 T 1
7 O 3
9 T 2
11 S 5
2 2 S 9
4 O 10
5 N 6
6 B 11
10 T 8
18 O 7
df.sort_index(level=0).sort_values('value_1', ascending=False)
Sort by index level=0 then sort by values, but index=0 gets jumbled again.
MyName value_1
idx_0 idx_1
2 6 B 11
4 O 10
2 S 9
10 T 8
18 O 7
5 N 6
1 11 S 5
1 C 4
7 O 3
9 T 2
3 T 1
Here are some potential solutions for your needs:
Method-1:
(df.sort_values('value_1', ascending=False)
.sort_index(level=[0], ascending=[True]))
Method-2:
(df.set_index('value_1', append=True)
.sort_index(level=[0,2], ascending=[True,False])
.reset_index('value_1'))
Tested on pandas 0.22.0, Python 3.6.4