Pandas Pivot Table Nested Sorting

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'],

Solution 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 inorder])
       C
A B     
z two  6one3
x one7
  two  4
y one5
  two  1

Solution 2:

If you can read in column A as categorical data, then it becomes much more straightforward. Setting your categories as list('zxy') and specifying ordered=True uses your custom ordering.

You can read in your data using something similar to:

'A':pd.Categorical(['x','y','z','x','y','z'], list('zxy'), ordered=True)

Alternatively, you can read in the data as you currently are, then use astype to convert A to categorical:

df['A'] = df['A'].astype('category', categories=list('zxy'), ordered=True)

Once A is categorical, you can pivot the same as before, and then sort with:

table = table.sort_values(ascending=False).sortlevel(0, sort_remaining=False)

Solution 3:

Solution

custom_order = ['z', 'x', 'y']
kwargs = dict(axis=0, level=0, drop_level=False)

new_table = pd.concat(
    [table.xs(idx_v, **kwargs).sort_values(ascending=False) for idx_v in custom_order]
)

Alternate one liner

pd.concat([table.xs(i, drop_level=0).sort_values(ascending=0) for i in list('zxy')]

Explanation

custom_order is your desired order. kwargs is a convenient way to improve readability (in my opinion). Key elements to note, axis=0 and level=0 might be important for you if you want to leverage this further. However, those are also the default values and can be left out. drop_level=False is the key argument here and is necessary to keep the idx_v we are taking a xs of such that the pd.concat puts it all together in the way we'd like.

I use a list comprehension in almost the exact same manner as Alexander within the pd.concat call.

Demonstration

print new_table

A  B  
z  two    6one3
x  one7
   two    4
y  one5
   two    1
Name: C, dtype: int64