Python – Sort Pandas Pivot Table by the margin (‘All’) values column

dataframepandaspivot tablepython

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?

Best Answer

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

Related Solutions

Python – How to sort a list of dictionaries by a value of the dictionary

The sorted() function takes a key= parameter

newlist = sorted(list_to_be_sorted, key=lambda d: d['name'])

Alternatively, you can use operator.itemgetter instead of defining the function yourself

from operator import itemgetter
newlist = sorted(list_to_be_sorted, key=itemgetter('name'))

For completeness, add reverse=True to sort in descending order

newlist = sorted(list_to_be_sorted, key=itemgetter('name'), reverse=True)

R – How to sort a dataframe by multiple column(s)

You can use the order() function directly without resorting to add-on tools -- see this simpler answer which uses a trick right from the top of the example(order) code:

R> dd[with(dd, order(-z, b)), ]
    b x y z
4 Low C 9 2
2 Med D 3 1
1  Hi A 8 1
3  Hi A 9 1

Edit some 2+ years later: It was just asked how to do this by column index. The answer is to simply pass the desired sorting column(s) to the order() function:

R> dd[order(-dd[,4], dd[,1]), ]
    b x y z
4 Low C 9 2
2 Med D 3 1
1  Hi A 8 1
3  Hi A 9 1
R>

rather than using the name of the column (and with() for easier/more direct access).

Best Answer

Related Solutions

Python – How to sort a list of dictionaries by a value of the dictionary

R – How to sort a dataframe by multiple column(s)

Related Topic