CODEWITHSUNDEEP
pythonpandasdataframe
Rookie_123
Rookie_123

Reputation: 2017

Copying data from one pandas dataframe to other based on column value and separated by comma

I have two dataframes viz., df1 and df2.

df1 is like

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

And df2 is like 

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

I want a df3 which looks like this

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

Basically, in one cell only but separated by a comma. The comma separated cell is obtained from df1.

In df3, the rows 3,4,5 correspond to rows 0,1,2

(0,Z1) of df2 is YH and column YH in df1 is Dot,Rls,Fs 
So Dot,Rls,Fs comes at (3,Z1) in df3

(1,Z1) of df2 is HE and HE column has Sf,BD in df1 
So Sf,Bd comes at (4,Z1) in df3

Similarly, for all others.

Please let me know if it is still not clear.

Upvotes: 2

Views: 46

Answers (1)

BENY
BENY

Reputation: 323326

The answer already contain in the previous question 

s=df2.set_index('Index').astype(object).apply(lambda x : x.map(df1.set_index('Index').to_dict('l')))


pd.concat([df2.set_index('Index'),s.fillna('').applymap(','.join)])

Out[1798]: 
               Z1 Z2        Z3
Index                         
0              YH           HE
1              HE           EI
2              MT           CU
0      Dot,Rls,Fs       Sf,Bd,
1          Sf,Bd,           ,,
2        Sy,Sa,Ft     Lc,Ta,Rg

Update 

s=df2.set_index('Index').astype(object).apply(lambda x : x.map(df1.set_index('Index').replace('',np.nan).stack().groupby(level=1).apply(list).to_dict()))
pd.concat([df2.set_index('Index'),s.fillna('').applymap(','.join)])
Out[1815]: 
               Z1 Z2        Z3
Index                         
0              YH           HE
1              HE           EI
2              MT           CU
0      Dot,Rls,Fs        Sf,Bd
1           Sf,Bd             
2        Sy,Sa,Ft     Lc,Ta,Rg

Upvotes: 1

Related Questions