CODEWITHSUNDEEP
pythonpandasdataframemulti-indexhierarchical-data
MathMan 99
MathMan 99

Reputation: 755

Pandas: How to sum second level columns in hierarchical dataframe

I have a DataFrame that looks as follows:

import pandas as pd
df = pd.DataFrame(data=[[1,2,4,5,1,2], [7,8,10,11,7,8], [13,14,16,17,4,5]], index=pd.date_range('2004-01-01', '2004-01-03'))
df.columns = pd.MultiIndex.from_product([['x', 'y', 'z'], list('ab')])
df
             x       y       z     
             a   b   a   b   a  b  
2004-01-01   1   2   4   5   1  2  
2004-01-02   7   8   10  11  7  8  
2004-01-03  13  14   16  17  4  5

I would like to sum the second level columns (a+b) for each first level column and have the first level columns as column names

Upvotes: 1

Views: 1278

Answers (1)

akuiper
akuiper

Reputation: 214967

You can specify level and axis parameter in groupby to aggregate by level 0 of the column index (axis=1):

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

             x   y   z
2004-01-01   3   9   3
2004-01-02  15  21  15
2004-01-03  27  33   9

Upvotes: 7

Related Questions