Search code examples
python-3.xpandaspivot-tablesubtotal

Subtotal for each level in Pivot table

I'm trying to create a pivot table that has, besides the general total, a subtotal between each row level.

I created my df.

import pandas as pd
df = pd.DataFrame(
    np.array([['SOUTH AMERICA', 'BRAZIL', 'SP', 500],
             ['SOUTH AMERICA', 'BRAZIL', 'RJ', 200],
             ['SOUTH AMERICA', 'BRAZIL', 'MG', 150],
             ['SOUTH AMERICA', 'ARGENTINA', 'BA', 180],
             ['SOUTH AMERICA', 'ARGENTINA', 'CO', 300],
             ['EUROPE', 'SPAIN', 'MA', 400],
             ['EUROPE', 'SPAIN', 'BA', 110],
             ['EUROPE', 'FRANCE', 'PA', 320],
             ['EUROPE', 'FRANCE', 'CA', 100],
             ['EUROPE', 'FRANCE', 'LY', 80]], dtype=object),
    columns=["CONTINENT", "COUNTRY","LOCATION","POPULATION"]
)

After that i created my pivot table as shown bellow

table = pd.pivot_table(df, values=['POPULATION'], index=['CONTINENT', 'COUNTRY', 'LOCATION'], fill_value=0, aggfunc=np.sum, dropna=True)
table

enter image description here

To do the subtotal i started sum CONTINENT level

tab_tots = table.groupby(level='CONTINENT').sum()
tab_tots.index = [tab_tots.index, ['Total'] * len(tab_tots)]

enter image description here

And concatenated with my first pivot to get subtotal.

pd.concat([table, tab_tots]).sort_index()

And got it: enter image description here

How can i get the values separated in level like the first table?

I'm not finding a way to do this.

Solution

  • IIUC:

    contotal = table.groupby(level=0).sum().assign(COUNTRY='TOTAL', LOCATION='').set_index(['COUNTRY','LOCATION'], append=True)
coutotal = table.groupby(level=[0,1]).sum().assign(LOCATION='TOTAL').set_index(['LOCATION'], append=True)

df_out = (pd.concat([table,contotal,coutotal]).sort_index())
df_out

    Output:

                                      POPULATION
CONTINENT     COUNTRY   LOCATION            
EUROPE        FRANCE    CA               100
                        LY                80
                        PA               320
                        TOTAL            500
              SPAIN     BA               110
                        MA               400
                        TOTAL            510
              TOTAL                     1010
SOUTH AMERICA ARGENTINA BA               180
                        CO               300
                        TOTAL            480
              BRAZIL    MG               150
                        RJ               200
                        SP               500
                        TOTAL            850
              TOTAL                     1330