alexprice
alexprice

Reputation: 404

easy way to create new columns in multiindex dataframe based on values of other columns

i have multiindex dataframe:

df=pd.DataFrame(np.zeros((2,4)))
df.columns=pd.MultiIndex.from_arrays([['a','a','b','b'],[1,2,1,2]])
df['a']=10
df['b']=20

   a       b   
   1  2    1  2
0  10  10  20  20
1  10  10  20  20

i'd like to create a new upper level column which is sum of 'a' and 'b' i.e.

wanted result:

   a       b      c   
   1  2    1  2   1  2  
0  10  10  20  20 30 30
1  10  10  20  20 30 30

ideally i'd like to write something similar to

df['c']=df['a']+df['b'] 

but it does not work in multiindex dataframes

Upvotes: 2

Views: 1176

Answers (2)

jezrael
jezrael

Reputation: 863801

The simpliest is use stack and unstack:

df = df.stack()
df['c']=df['a']+df['b'] 
df = df.unstack()
print (df)
    a       b       c    
    1   2   1   2   1   2
0  10  10  20  20  30  30
1  10  10  20  20  30  30

Another solution with add and concat:

df1 = df.a.add(df.b)
#create MultiIndex with c level
df1.columns = [['c'] * len(df1.columns), df1.columns]
print (df1)
    c    
    1   2
0  30  30
1  30  30

df2 = pd.concat([df, df1], axis=1)
print (df2)
    a       b       c    
    1   2   1   2   1   2
0  10  10  20  20  30  30
1  10  10  20  20  30  30

Upvotes: 3

YGouddi
YGouddi

Reputation: 361

I'm going to assume you meant 'b' instead of 'c' when adding the column value. Check this question it might help.

Upvotes: 0

Related Questions