Pandas pivot table Nested Sorting

Question

Given this data frame and pivot table:

import pandas as pd
df=pd.DataFrame({'A':['x','y','z','x','y','z'],
                 'B':['one','one','one','two','two','two'],
                 'C':[7,5,3,4,1,6]})
df


    A   B       C
0   x   one     7
1   y   one     5
2   z   one     3
3   x   two     4
4   y   two     1
5   z   two     6

table = pd.pivot_table(df, index=['A', 'B'],aggfunc=np.sum)

table
A  B  
x  one    7
   two    4
y  one    5
   two    1
z  one    3
   two    6
Name: C, dtype: int64

I want to sort the pivot table such that the order of 'A' is z, x, y and the order of 'B' is based on the descendingly-sorted values from data frame column 'C'.

Like this:

A  B  
z  two    6
   one    3
x  one    7
   two    4
y  one    5
   two    1

    Name: C, dtype: int64

Thanks in advance!

Answer 1

I don't believe there is an easy way to accomplish your objective. The following solution first sorts your table is descending order based on the values of column C. It then concatenates each slice based on your desired order.

order = ['z', 'x', 'y']
table = table.reset_index().sort_values('C', ascending=False)
>>> pd.concat([table.loc[table.A == val, :].set_index(['A', 'B']) for val in order])
       C
A B     
z two  6
  one  3
x one  7
  two  4
y one  5
  two  1