Pivot tables in Python - how to add percentages and subtotals

Question

How can I easily and subtotals for each level and express the numbers in percentages in Python? I have the data below and I would like to add one column which will express the numbers as the percentage of total values in column or as a percentage of total values for a given level. How can I do that easily?

import pandas as pd
df=pd.DataFrame({'A':['x','y','z','x','y','z'],
                 'B':['one','one','one','two','two','two'],
                 'C':[2,18,2,8,2,18]})
df

table = pd.pivot_table(df, index=['A', 'B'],aggfunc=np.sum)

Is there any other solution than the one proposed here Pandas pivot table Percent Calculations?

Can you provide the explicit expected output? – mozway Aug 11 '22 at 21:25 — mozway, Aug 11 '22 at 21:25

score 2 · Accepted Answer · answered Aug 11 '22 at 21:30

IIUC, you can use a simple division or a division by the sum per group:

table.assign(**{'%total': lambda d: d['C'].div(d['C'].sum()),
                '%level': lambda d: d['C'].div(d.groupby(level=0)['C'].transform('sum')),
               })

Output:

        C  %total  %level
A B                      
x one   2    0.04     0.2
  two   8    0.16     0.8
y one  18    0.36     0.9
  two   2    0.04     0.1
z one   2    0.04     0.1
  two  18    0.36     0.9

NB. The command is chainable, you can pipe it directly after your pivot_table

table = (pd.pivot_table(...)
           .assign(...)
        )

Pivot tables in Python - how to add percentages and subtotals

1 Answers1