How to sort pivot table in Pandas

Question

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':[x for x in range(16)]})
p = test.pivot_table(index=['month', 'country'], aggfunc='count')[['id']]

The output looks like this:

I'd like to sort the table by the id column, so that the largest number appear on top like:

                    id
month    country
           us       4
  5        cn       2
           ca       1

Answer 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        4
6     ca        3
      ru        3
5     cn        2
7     cn        2
5     ca        1
7     ru        1

Answer 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        1
6     ca        3
      ru        3
7     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        1
6     ca        3
      ru        3
7     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        1
6     ca        3
      ru        3
7     cn        2
      ru        1