Calculate Column in Pandas between Single Index and MultiIndex DataFrames

Question

Say I have the following MultiIndex empty DataFrame:

>>> df1=pd.DataFrame(data=[['a',1], ['a',2], ['b',1], ['b',2]], columns=['key1','key2']).set_index(['key1','key2'])
>>> print df1
Empty DataFrame
Columns: []
Index: [(a, 1), (a, 2), (b, 1), (b, 2)]

And I have the following DataFrame that contains my data:

>>> data=pd.DataFrame(index=['a','b'], data=[11,22], columns=['Var1'])
>>> data.index.name='key1'
>>> print data
      Var1
key1      
a       11
b       22

Given that the common index is named "key1" in both DataFrames, I would think that I could calculate a variable equal to Var1 in my empty DataFrame doing the following:

>>> df1['TestVar']=data['Var1']
>>> print df1
           TestVar
key1 key2         
a    1         NaN
     2         NaN
b    1         NaN
     2         NaN

However, this does not appear to work. Is there something I am doing wrong here? Instead I resort to the following to get my desired output

>>> df1.reset_index([1]).join(data).set_index('key2',append=True)
           Var1
key1 key2      
a    1       11
     2       11
b    1       22
     2       22

Is there a better way to do this?

Dickster · Accepted Answer · 2015-09-25 19:56:24Z

2

How about adding an index name to data dataframe and then use a join?

df1=pd.DataFrame(data=[['a',1], ['a',2], ['b',1], ['b',2]], columns=['key1','key2']).set_index(['key1','key2'])
data=pd.DataFrame(index=['a','b'], data=[11,22], columns=['Var1'])


data.index.names =['key1']

print df1.join(data)


           Var1
key1 key2      
a    1       11
     2       11
b    1       22
     2       22

answered Sep 25, 2015 at 19:56

Dickster

3,0193 gold badges25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Calculate Column in Pandas between Single Index and MultiIndex DataFrames

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related