Add subtotal columns in pandas with multi-index
Here is a way without loops:
s = df.sum(axis=1, level=[0,1]).Ts["shape"] = "sum(shape)"s.set_index("shape", append=True, inplace=True)df.combine_first(s.T)The trick is to use the transposed sum. So we can insert another column (i.e. row) with the name of the additional level, which we name exactly like the one we summed over. This column can be converted to a level in the index with set_index. Then we combine df with the transposed sum. If the summed level is not the last one you might need some level reordering.
Here's my brute-force way of doing it.
After running your well written (thank you) sample code, I did this:
attributes = pd.unique(df.columns.get_level_values('attribute'))colors = pd.unique(df.columns.get_level_values('color'))for attr in attributes: for clr in colors: df[(attr, clr, 'sum')] = df.xs([attr, clr], level=['attribute', 'color'], axis=1).sum(axis=1)dfWhich gives me:
