Python (Pandas) Add subtotal on each lvl of multiindex dataframe

Question

Assuming I have the following dataframe:

a       b       c      Sce1     Sce2    Sce3    Sce4    Sce5    Sc6
Animal  Ground  Dog    0.0      0.9     0.5     0.0     0.3     0.4  
Animal  Ground  Cat    0.6      0.5     0.3     0.5     1.0     0.2 
Animal  Air     Eagle  1.0      0.1     0.1     0.6     0.9     0.1 
Animal  Air     Owl    0.3      0.1     0.5     0.3     0.5     0.9     
Object  Metal   Car    0.3      0.3     0.8     0.6     0.5     0.6 
Object  Metal   Bike   0.5      0.1     0.4     0.7     0.4     0.2 
Object  Wood    Chair  0.9      0.6     0.1     0.9     0.2     0.8 
Object  Wood    Table  0.9      0.6     0.6     0.1     0.9     0.7

I want to create a MultiIndex, which will contain the sum of each lvl. The output will look like this:

a      b      c     Sce1    Sce2    Sce3    Sce4    Sce5    Sce6
Animal              1.9     1.6     1.4     1.3     2.7     1.6 
       Ground       0.6     1.4     0.8     0.5     1.3     0.6 
              Dog   0.0     0.9     0.5     0.0     0.3     0.4 
              Cat   0.6     0.5     0.3     0.5     1.0     0.2 
       Air          1.3     0.2     0.7     0.8     1.4     1.0 
              Eagle 1.0     0.1     0.1     0.6     0.9     0.1 
              Owl   0.3     0.1     0.5     0.3     0.5     0.9 
Object              2.6     1.6     1.8     2.3     2.0     2.3 
       Metal        0.8     0.3     1.1     1.3     0.9     0.8 
              Car   0.3     0.3     0.8     0.6     0.5     0.6 
              Bike  0.5     0.1     0.4     0.7     0.4     0.2 
       Wood         1.8     1.3     0.6     1.0     1.1     1.5 
              Chair 0.9     0.6     0.1     0.9     0.2     0.8 
              Table 0.9     0.6     0.6     0.1     0.9     0.7

At the moment I am using a loop to create three different dataframes on each level and then manipulate them on excel, as below. So I wanted to take this calculation in python if possible.

for i in range range(0,3):
    df = df.groupby(list(df.columns)[0:lvl], as_index=False).sum()
    return df

Many thanks in advance.

I think that there is no default way of doing it in pandas. Note that you can produce the groups including their sums with multilevel index with `df.groupby(['a','b','c']).sum()`. However, this is lacking the total per group. You should look at [this question](http://stackoverflow.com/questions/29413496/pandas-computing-total-sum-on-each-multiindex-sublevel) which is very similar to your problem. — Jan Trienes, Apr 05 '17 at 18:54

piRSquared · Accepted Answer · 2017-04-05T19:13:20.627

25

With some liberal use of MAGIC

pd.concat([
        df.assign(
            **{x: 'Total' for x in 'abc'[i:]}
        ).groupby(list('abc')).sum() for i in range(4)
    ]).sort_index()

                     Sce1  Sce2  Sce3  Sce4  Sce5  Sc6
a      b      c                                       
Animal Air    Eagle   1.0   0.1   0.1   0.6   0.9  0.1
              Owl     0.3   0.1   0.5   0.3   0.5  0.9
              Total   1.3   0.2   0.6   0.9   1.4  1.0
       Ground Cat     0.6   0.5   0.3   0.5   1.0  0.2
              Dog     0.0   0.9   0.5   0.0   0.3  0.4
              Total   0.6   1.4   0.8   0.5   1.3  0.6
       Total  Total   1.9   1.6   1.4   1.4   2.7  1.6
Object Metal  Bike    0.5   0.1   0.4   0.7   0.4  0.2
              Car     0.3   0.3   0.8   0.6   0.5  0.6
              Total   0.8   0.4   1.2   1.3   0.9  0.8
       Total  Total   2.6   1.6   1.9   2.3   2.0  2.3
       Wood   Chair   0.9   0.6   0.1   0.9   0.2  0.8
              Table   0.9   0.6   0.6   0.1   0.9  0.7
              Total   1.8   1.2   0.7   1.0   1.1  1.5
Total  Total  Total   4.5   3.2   3.3   3.7   4.7  3.9

I can get exactly what you asked for with

pd.concat([
        df.assign(
            **{x: '' for x in 'abc'[i:]}
        ).groupby(list('abc')).sum() for i in range(1, 4)
    ]).sort_index()

                     Sce1  Sce2  Sce3  Sce4  Sce5  Sc6
a      b      c                                       
Animal                1.9   1.6   1.4   1.4   2.7  1.6
       Air            1.3   0.2   0.6   0.9   1.4  1.0
              Eagle   1.0   0.1   0.1   0.6   0.9  0.1
              Owl     0.3   0.1   0.5   0.3   0.5  0.9
       Ground         0.6   1.4   0.8   0.5   1.3  0.6
              Cat     0.6   0.5   0.3   0.5   1.0  0.2
              Dog     0.0   0.9   0.5   0.0   0.3  0.4
Object                2.6   1.6   1.9   2.3   2.0  2.3
       Metal          0.8   0.4   1.2   1.3   0.9  0.8
              Bike    0.5   0.1   0.4   0.7   0.4  0.2
              Car     0.3   0.3   0.8   0.6   0.5  0.6
       Wood           1.8   1.2   0.7   1.0   1.1  1.5
              Chair   0.9   0.6   0.1   0.9   0.2  0.8
              Table   0.9   0.6   0.6   0.1   0.9  0.7

As for the how! I'll leave that as an exercise for the reader.

edited Apr 05 '17 at 19:13

answered Apr 05 '17 at 19:05

piRSquared

285,575
57
475
624

3

Brilliant! @piRSquared – Scott Boston Apr 05 '17 at 19:06
@ScottBoston Thank you :-) – piRSquared Apr 05 '17 at 19:08
How is all_cols defined? – Scott Boston Apr 05 '17 at 19:11
@piRSquared Thank you so much for this! – pbou Apr 05 '17 at 19:13
@piRSquared Can you please let me know what the "**" after the df.assign do? Thanks in advance. – pbou Apr 06 '17 at 09:38
1

@pbou I'm unpacking the dictionary so that the key value pairs are passed as arguments to the assign method. https://www.python.org/dev/peps/pep-0448/ – piRSquared Apr 06 '17 at 13:48
1

Thx @Wen. I saw you link it and wanted to let you know I appreciated it. – piRSquared Sep 25 '17 at 15:33
1

I love this, but it is now raising a FutureWarning: "'a' is both a column name and an index level. Defaulting to column but this will raise an ambiguity error in a future version". Do you have some updated magic? – rhaskett May 02 '18 at 19:11
It depends on your specific dataframe. Use `pandas.DataFrame.rename_axis` to rename you index levels inline. Or rename your columns. It is complaining that your index level names can collide with the column names. In the example I give, it shouldn't be an issue. – piRSquared May 02 '18 at 19:19
1

Oh I see, i started with a frame that already had a multiindex [a,b,c] in place and replaced 'abc' above with df.index.names. The OP didn't have a multiindex yet. – rhaskett May 02 '18 at 19:33
@rhaskett yes, you just had to call `reset_index` before calling `assign` – nirvana-msu Jul 22 '19 at 10:31
thanks for the tips! @piRSquared what if I want to stop the sum at a,b and exclude c's aggregation? – mithocondria Sep 02 '19 at 04:11
Hey @piRSquared, amazing! Just wondering: Could you provide also a column-wise version? – Lukasavicus Mar 16 '20 at 20:47
1

Thank you so much ! Note that you can use a list of columns names instead of 'abc' – Grall Apr 25 '23 at 14:15

score 1 · Answer 2 · edited May 23 '17 at 12:09

1

you need to do two group by to get subtotals at every level of aggregation. Then add those back in to the initial DF. Here's a related question.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 05 '17 at 18:56

JD Long

59,675
58
202
294

Python (Pandas) Add subtotal on each lvl of multiindex dataframe

2 Answers2

Linked