Interaction with Margins

[mshiner]

Hi Chris

Dumb question - I'd like to get the totals of columns as well as sub-totals. If I add margins=True I get a subtotal for all as well as a grand total from sidetable

data = req.get('http://localhost:43000/mi_salesoverview').json()
    df = pd.DataFrame.from_dict(data) 
    dfq = df.query("year == [2020]")
    report = pd.pivot_table(dfq,index=["company","item"],columns=["month"],values='sales_value', margins=True, aggfunc={"sales_value": np.sum}).stb.subtotal(sub_level=[1],grand_label="Total Sales")

M

[chris1610]

If I'm understanding correctly, don't use margins=True. Complete your pivot, then use assign at the end to add the row totals.

Here's a full example with the titanic data:

import pandas as pd
import sidetable
import seaborn as sns
df = sns.load_dataset('titanic')

tmp = df[['class', 'deck', 'sex', 'fare']]
pd.pivot_table(tmp, index=['class', 'deck'], 
               columns=['sex'], 
               aggfunc={'fare': 'sum'}).stb.subtotal(sub_level=[1], 
                                                     grand_label='Total Fare').assign(total=lambda x: x.sum(axis=1))

Is that what you were trying to do?

[mshiner]

Chris

Perfect!

Thank you so much