Sort pivot table by groups

Question

I'm struggling with the usage of pivot tables for pandas.

I have a dataframe with 3 columns: Company name, date and number of orders.

I'm trying to pivot the dataframe in such a way that I'd be able to see top 10 biggest customers.

So for example, lets say I have

name | date | orders
John | 2017 | 100
John | 2018 | 200
John | 2019 | 300
Mary | 2017 | 50
Mary | 2018 | 50
Mary | 2019 | 1000

I want to pivot this in a way where I'd be able to see it like this

name
date  2017 | 2018 | 2019
John  100  | 200  | 300
Mary  50   | 50   | 1000

And sorted by total amount of orders, so in this case, Mary should go first because she has 1100 orders in total.

I cannot do the sorting in groups, it's outputting Mary 1000 1st, John 300 2nd and John 200 3rd (to give an example)

Also, maybe a bit unrelated but I also noticed that when pivoting, it's throwing the results in scientfic notation (despite the df displaying the numbers without it) and if I disable SciNot, it's converting the values to floats, any ideas as to why that is?

The link does not cover how to sort the pivots at all – Dasphillipbrau Dec 11 '19 at 21:16 — Dasphillipbrau, Dec 11 '19 at 21:16

score 0 · Answer 1 · answered Dec 11 '19 at 21:05

Try:

df_out = df.pivot('name','date')['orders']
df_out = df_out.assign(sortkey = df_out.sum(axis=1))\
               .sort_values('sortkey', ascending=False)\
               .drop('sortkey', axis=1)

Output:

date  2017  2018  2019
name                  
Mary    50    50  1000
John   100   200   300

Sort pivot table by groups

1 Answers1