4
votes

Just trying out pandas for the first time, and I am trying to sort a pivot table first by an index, then by the values in a series.

So far I've tried:

table = pivot_table(sheet1, values='Value', rows=['A','B'], aggfunc=np.sum)

# Sorts by value ascending, can't change to descending
table.copy().sort()
table

# The following gives me the correct ordering in values, but ignores index 
sorted_table = table.order(ascending=False)
sorted_table

# The following brings me back to the original ordering
sorted_table = table.order(ascending=False)
sorted_table2 = sorted_table.sortlevel(0)
sorted_table2

What's the correct way to sort a pivot table by index then value?

1
Can you give an example of what you expect the result to look like? It sounds like you want to sort by level 'A' and then sort within group by value, is that right? - Wes McKinney

1 Answers

9
votes

Here is a solution that may do what you want:

key1 = table.index.labels[0]
key2 = table.rank(ascending=False)

# sort by key1, then key2
sorter = np.lexsort((key2, key1))

sorted_table = table.take(sorter)

The result would look like this:

In [22]: table
Out[22]: 
A    B    
bar  one      0.698202
     three    0.801326
     two     -0.205257
foo  one     -0.963747
     three    0.120621
     two      0.189623
Name: C

In [23]: table.take(sorter)
Out[23]: 
A    B    
bar  three    0.801326
     one      0.698202
     two     -0.205257
foo  two      0.189623
     three    0.120621
     one     -0.963747
Name: C

This would be good to build into pandas as an API method. Not sure what it should look like though.