Indexing with values in a dataframe

Question

I have two pandas dataframes, both indexed by overlapping dates, call them df1 and df2.

df1 = pd.DataFrame(np.random.randn(5,4), index=pd.date_range('01/02/2014',periods=5,freq='D'), columns=['a','b','c', 'd'] )
df2 = pd.DataFrame({0 : [a,b,c,d], 1 : [b,c,b,a], 2 : [c,d,a,b], 3 : [d,a,d,c]}index=pd.date_range('01/03/2014',periods=4,freq='D'))
df1
                   a         b         c         d
2014-01-02  1.248568 -1.844952 -0.275825 -0.496379
2014-01-03  2.450789  0.864446 -0.163374 -0.437771
2014-01-04  0.223496  0.877333  1.580202  0.749173
2014-01-05  0.285521 -1.289847  0.273195 -0.747948
2014-01-06 -0.113029 -1.785885  0.078580 -1.156271
df2
            0  1  2  3
2014-01-03  a  b  c  d
2014-01-04  b  c  d  a
2014-01-05  c  b  a  d
2014-01-06  d  a  b  c

The columns of df1 and the values of df2 are all different names of commodities.

I want to create a new dataframe, df3, such that df3 has the same index as df2, and the values of df3 are equal to the value in df1 for the column of the value of df2.

df3
                   0         1         2         3
2014-01-03  2.450789  0.864446 -0.163374 -0.437771
2014-01-04  0.877333  1.580202  0.749173  0.223496
2014-01-05  0.285521 -1.289847  0.285521 -0.747948
2014-01-06 -1.156271 -0.113029 -1.785885  0.078580

I tried the following but cannot get it to work.

df2.apply(lambda x: df1.loc[x.index,str(x)])

Any advice?

BENY BENY · Accepted Answer · 2018-04-27T23:09:45

You can using lookup with apply

df2.apply(lambda x : df1.lookup(df2.index,x))
Out[469]: 
                   0         1         2         3
2014-01-03  2.450789  0.864446 -0.163374 -0.437771
2014-01-04  0.877333  1.580202  0.749173  0.223496
2014-01-05  0.273195 -1.289847  0.285521 -0.747948
2014-01-06 -1.156271 -0.113029 -1.785885  0.078580

Indexing with values in a dataframe

1 Answers