I'm trying to create a pivot table that has, besides the general total, a subtotal between column levels and lines as well.
The lines I've already achieved through the following function:
table = df_consulta.pivot_table(index=["CAT1","CAT2"],values=["ID_CAT"],
columns=["COL1","COL2"],aggfunc=[np.count_nonzero],fill_value = 0,margins = True,margins_name = 'zzzTotal')
tab_tots = table.groupby(level='CAT1').sum()
tab_tots.index = [tab_tots.index, ['zzTotal'] * len(tab_tots)]
tabela_ok = pd.concat([table, tab_tots]).sort_index()
tabela_ok
andtheoveralltotalalso,usingthe'margin'parameter.
Itriedthesamefunctiontogetthesumofthecolumn:
tab_tots=table.groupby(level="COL1").sum()
however I get the error:
KeyError: 'Level COL1 not found'
Because the columns are not a table level.
But I could not think of a way to mount this subtotal for the columns in the case ("A", "B" and "C")