How can I create df3 according to df1 and df2?
df1 = pd.DataFrame([[1,2,3],[10,20,30],[100,200,300]], index=['a','b','c'],columns=['A','B','C'])
df2 = pd.DataFrame([['A','C'],['B','A'],['C','B']],index=['a','b','c'],columns=[0,1])
df3 = pd.DataFrame([[1,3],[20,10],[300,200]], index=['a','b','c'],columns=[0,1])
Here is my code,
df1.apply(lambda x: x.loc[df2.loc[x.name,:]], axis=1)
This is df1
This is df2
This is df3
Seems like you can do with lookup after stack with df2
s=df2.stack()
s
Out[321]:
a 0 A
1 C
b 0 B
1 A
c 0 C
1 B
dtype: object
pd.Series(df1.lookup(s.index.get_level_values(0),s),index=s.index).unstack()
Out[322]:
0 1
a 1 3
b 20 10
c 300 200
Or with apply
df2.apply(lambda x : df1.loc[x.name,x].values,axis=1)
Out[327]:
0 1
a 1 3
b 20 10
c 300 200
use lookup
In [993]: pd.DataFrame({k: df1.lookup(df2.index, df2[c]) for k, c in enumerate(df2)},
index=df1.index)
Out[993]:
0 1
a 1 3
b 20 10
c 300 200
Or
In [973]: df2.apply(lambda x: pd.Series(df1.loc[x.name, y] for y in x), axis=1)
Out[973]:
0 1
a 1 3
b 20 10
c 300 200
