How to sort a Pandas pivot table but keep totals at end of table

user_7598896 0
Viewed 1011

I built a pivot table doing this:

prima_neta = df.pivot_table(index = ["seccion"], columns = "operacion", values = "prima_pesos", aggfunc = "sum", margins=True).fillna(0)

and then tried to sort the table in descending order by 'All' column (generated by margins=True):

prima_neta.sort_values(by='All', ascending=False)

This works fine, but the 'All' total (which of course is the highest amount) at the end of the original table output is brought up to the top as first line.

I would like to sort the table in descending order, but keep the 'All' (Totals) amounts at the last line.

How can I achieve this?

pandas pivot-table python
2 Answers

Let's try this:

import pandas as pd
import numpy as np
np.random.seed(123)

# Create dummy dataframe
df = pd.DataFrame({'A':np.random.choice([*'ABC'], 36)
                  ,'B':np.random.choice([*'xyz'], 36)
                  ,'C':np.random.randint(0,100,36)})

# Pivot table with margins
results = df.pivot_table('C', 'A', 'B', aggfunc='sum', margins=True)

#Create temporary sortkey sort on sortkey and values, drop sortkey
result = results.assign(sortkey=results.index == 'All')\
                .sort_values(['sortkey','All'], ascending=[True, False])\
                .drop('sortkey', axis=1)
result

Output:

B      x    y    z   All
A                       
B    368  215  275   858
A    155  202  218   575
C    206  149   45   400
All  729  566  538  1833
user_63615310

You can just swap the two rows.

ndf = df.reset_index()
totalind = ndf.index[ndf.All=='total'].tolist()[0]
ind = np.array(ndf.index)
ind[totaling], ind[-1] = ind.iloc[-1], ind.iloc[totalind]
ndf.reindex(ind)

There should be a less painful way, but I don't know what it is.

user_29772560
Related