CODEWITHSUNDEEP
pythonpandasdataframe
Rookie_123
Rookie_123

Reputation: 2007

Copying data from one pandas dataframe to other based on column value

I have two dataframe df1 and df2 df1 is like below

Index YH   HE  MT  CU  EI
 0    Dot  Sf  Sy  Lc  
 1    Rls  Bd  Sa  Ta  
 2    Fs       Ft  Rg

df2 is like 

Index   Z1   Z2  Z3
 0      YH       HE
 1      HE       EI
 2      MT       CU

I want to copy values from df1 to df2 depending upon the cell value in df2 which matches with column name in df1 So my df3 should look like

df3

Index Z1   Z2   Z3
  0   YH        HE
  1   Dot       Sf  
  2   Rls       Bd
  3   Fs        EI
  4   HE        CU
  5   Sf        Lc
  6   Bd        Ta
  7   MT        Rg
  8   Sy
  9   Sa
  10  Ft

Basically I want to copy the column from df1 in df2 depending upon cell value of df2 which is a column name in df1

If its still confusing please tell me

Upvotes: 2

Views: 892

Answers (1)

BENY
BENY

Reputation: 323226

This involve a lot of function ...

s=df2.set_index('Index').astype(object).apply(lambda x : x.map(df1.set_index('Index').to_dict('l')))
m=pd.concat([df2.set_index('Index').applymap(lambda x : [x]),s]).sort_index().sum(level=0).sum()
pd.DataFrame(m.tolist(),index=m.index).T.reindex(columns=df2.columns[1:]).fillna('')
Out[1721]: 
     Z1 Z2  Z3
0    YH     HE
1   Dot     Sf
2   Rls     Bd
3    Fs       
4    HE     EI
5    Sf       
6    Bd       
7             
8    MT     CU
9    Sy     Lc
10   Sa     Ta
11   Ft     Rg

To get what you need 

pd.DataFrame(m.tolist(),index=m.index).T.reindex(columns=df2.columns[1:]).replace('',np.nan).dropna(thresh=1,axis=0).fillna('')
Out[1724]: 
     Z1 Z2  Z3
0    YH     HE
1   Dot     Sf
2   Rls     Bd
3    Fs       
4    HE     EI
5    Sf       
6    Bd       
8    MT     CU
9    Sy     Lc
10   Sa     Ta
11   Ft     Rg

Upvotes: 1

Related Questions