python - Sum columns by level in a pandas MultiIndex DataFrame

Question

I have my df with multi-index columns. All of my values are in float, and I want to merge values with in first level of multi-index. Please see below for detail.

first        bar                 baz                 foo   
second       one       two       one       two       one    
A       0.895717  0.805244  1.206412  2.565646  1.431256    
B       0.410835  0.813850  0.132003  0.827317  0.076467    
C       1.413681  1.607920  1.024180  0.569605  0.875906 

first        bar                 baz                 foo   

A       (0.895717+0.805244) (1.206412+2.565646)  1.431256    
B       (0.410835+0.813850) (0.132003+0.827317)  0.076467    
C       (1.413681+1.607920) (1.024180+0.569605)  0.875906 

The values are actually added (I just didn't feel like doing all this :)). Bottom line is that I just want to level-up(higher level I guess) and within the index, add all the values. Please let me know a good way to do this. Thank you!

Answer 1

I believe you're looking for a groupby along the first axis.

df.groupby(level=0, axis=1).sum()

Or (more succinctly),

df.sum(level=0, axis=1)

The level argument to sum implies grouping.

---

df

first  bar     baz     foo    
second one two one two one two
A        2   3   3   4  10   8
B       22  16   7   3   2  26
C        4   5   1   9   6   5

df.sum(level=0, axis=1)

first  bar  baz  foo
A        5    7   18
B       38   10   28
C        9   10   11

Performance wise, there's hardly any difference between the two methods outlined above (the latter is a few ticks faster).