Sum columns by level in a pandas MultiIndex DataFrame
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).
Keep in mind that df.sum(level, axis)
will only work if you set your columns to the multi-index. Example,
D = {'one': range(6),
'two': range(1,7),
'CAT1': 'A A A A A A'.split(),
'CAT2': 'B B B C C C'.split(),
'CAT3': 'D D E E F F'.split()}
df = pd.DataFrame(D)
df = df.set_index('CAT1 CAT2 CAT3'.split())
df
one two
CAT1 CAT2 CAT3
A B D 0 1
D 1 2
E 2 3
C E 3 4
F 4 5
F 5 6
If your data is in this form, you will have to use df.groupby(level=n).sum(axis=1)
df.groupby(level = 0).sum(axis=1)
one two
CAT1
A 15 21
df.groupby(level = 1).sum(axis=1)
one two
CAT2
B 3 6
C 12 15
df.groupby(level = 2).sum(axis=1)
one two
CAT3
D 1 3
E 5 7
F 9 11
If you try skipping the groupby
,
df.sum(level = 1, axis=1)
ValueError: level > 0 or level < -1 only valid with MultiIndex
Which is an interesting error since,
df.index
MultiIndex(levels=[[u'A'], [u'B', u'C'], [u'D', u'E', u'F']],
labels=[[0, 0, 0, 0, 0, 0], [0, 0, 0, 1, 1, 1], [0, 0, 1, 1, 2, 2]],
names=[u'CAT1', u'CAT2', u'CAT3'])