How To Sort Pivot Table In Pandas

Here is the code: test = pd.DataFrame({'country':['us','ca','ru','cn','ru','cn','us','ca','ru','cn','us','ca','ru','cn','us','ca'], 'month':[5,6,7,5,6,7,5,5,6,7,5,6,6,5,5,6], 'id':

Solution 1:

You need DataFrame.reset_index, DataFrame.sort_values and DataFrame.set_index:

p1 = p.reset_index()
      .sort_values(['month','id'], ascending=[1,0])
      .set_index(['month','country'])
print (p1)
               id
month country    
5     us        4
      cn        2
      ca        1
6     ca        3
      ru        3
7     cn        2
      ru        1

because this solution does not work :(

p1 = p.sort_index(level='month', sort_remaining=True) \
      .sort_values('id', ascending=False)
print (p1)
               id
month country    
5     us        46     ca        3
      ru        35     cn        27     cn        25     ca        17     ru        1

Solution 2:

Option 1 This sorts by id within groups defined by the month level within the index

p.groupby(
    level='month', group_keys=False
).apply(pd.DataFrame.sort_values, by='id', ascending=False)

               id
month country    
5     us        4
      cn        2
      ca        16     ca        3
      ru        37     cn        2
      ru        1

---

Option 2 This first sorts the entire dataframe by id then sorts again by the month level within the index. However, I had to use sort_remaining=False for self-explanatory reasons and kind='mergesort' because mergesort is a stable sort and won't mess with the pre-existing order within groups defined by the 'month' level.

p.sort_values('id', ascending=False) \
 .sort_index(level='month', sort_remaining=False, kind='mergesort')

               id
month country    
5     us        4
      cn        2
      ca        16     ca        3
      ru        37     cn        2
      ru        1

---

Option 3 This uses numpy's lexsort... this works, but I don't like it because it depends on id being numeric and my being able to put a negative in front of it to get descending ordering. /shrug

p.iloc[np.lexsort([-p.id.values, p.index.get_level_values('month')])]

               id
month country    
5     us        4
      cn        2
      ca        16     ca        3
      ru        37     cn        2
      ru        1