How to sum specific hierarchical columns in pandas?

by binaryEcon   Last Updated May 15, 2019 16:26 PM - source

I have following dataframe:

import pandas as pd
df = pd.DataFrame(data=[[1,2,3,4,5,6,1,2,3], [7,8,9,10,11,12,7,8,9], [13,14,15,16,17,18,4,5,6]], index=pd.date_range('2004-01-01', '2004-01-03'))
df.columns = pd.MultiIndex.from_product([['x', 'y', 'z'], list('abc')])

df
                 x           y          z      
             a   b   c   a   b   c  a  b  c
2004-01-01   1   2   3   4   5   6  1  2  3
2004-01-02   7   8   9  10  11  12  7  8  9
2004-01-03  13  14  15  16  17  18  4  5  6

I want to sum groups of columns at the first level (level=0) and get something like this:

                (x+z)           y      
             a   b   c   a   b   c
2004-01-01   2   4   6   4   5   6
2004-01-02  14  16  18  10  11  12
2004-01-03  17  19  21  16  17  18

What I have tried:

mapping = {'x': 'r1', 'y': 'r2', 'z': 'r1'}
df.groupby(mapping, axis=1, level=0).apply(lambda g: g.sum(axis=1, level=1))

But I got an error.

Thanks for any advise.

Tags : python pandas


Related Questions


pandas groupby apply is really slow

Updated November 05, 2017 15:26 PM

Do not map item to any output using apply()

Updated July 30, 2018 21:26 PM