Sort Pandas Pivot Table by the margin ('All

问题:

I'm trying to do a descending sort on the last column/margins/aggrfunc by the sum of the rows in a pandas pivot table. I know I'm missing something simple here, but I can't figure it out.

dataframe/pivot table:

WIDGETS         
DATE    2/1/16  2/2/16  2/3/16  All
NAME                
PERSON1 43      5               48
PERSON2         4       7       11
PERSON3         56      143     199

What I need it to do is also sort by aggfunc/margins:

WIDGETS         
DATE    2/1/16  2/2/16  2/3/16  All
NAME                
PERSON3         56      143     199
PERSON1 43      5               48
PERSON2         4       7       11

pt = pd.pivot_table(df,values=['WIDGETS'],index=['NAME'],columns=['DATE'],aggfunc=len,fill_value='',margins=True,margins_name='WIDGETS')
pt.sort_values(by='WIDGETS',ascending=False,inplace=True)

Error: ValueError: Cannot sort by column WIDGETS in a multi-index you need to explicity provide all the levels

Suggestions?

回答1:

You can use tuple in function sort_values and parameter ascending:

print pt
        WIDGETS                  
DATE     2/1/16 2/2/16 2/3/16 All
NAME                             
PERSON1       1      2          3
PERSON2       2      4      3   9
PERSON3       1             1   2
All           4      6      4  14

pt.sort_values(by=('WIDGETS', 'All'), ascending=False,inplace=True)
print pt
        WIDGETS                  
DATE     2/1/16 2/2/16 2/3/16 All
NAME                             
All           4      6      4  14
PERSON2       2      4      3   9
PERSON1       1      2          3
PERSON3       1             1   2