Add a column matching other two columns, one contains representative values

user_4168405 0
Viewed 57

I have three data frames as follows:

df1
col1 CAND_SNP
1     a1
1     a2
1     a3
1     a4
2     b1
3     c1
3     c2
3     c3

df2
col1 LEAD_SNP
1     a1
2     b1
3     c1
    


df3
snp col2
a3     x1
a21    x2
a31    x3
a41    x4
b11    x5
c11    x6
c21    x7
c31    x8

I need to match CAND_SNP of df1 with snp of df3 to populate a new column in df2 with values "yes" or "no". The match needs to be groupwise for col1 of df1. In the above example, there are 3 groups in col1 of df1. If any of these group's corresponding value in CAND_SNP matches with snp of df3 then the new column of df2 would be "yes" as below: Any help?

df2
    col1 LEAD_SNP   col3
    1     a1        Yes
    2     b1        No
    3     c1        No
pandas python
1 Answers

If I understand correctly, you can group df1 by col1 and look up whether a value of col2 exists in col1 of df3. Then merge with df2:

df1['col3'] = df1.groupby('col1')['CAND_SNP'].apply(lambda s: s.isin(df3['snp']))
df2 = df2.merge(df1.groupby('col1')['col3'].any(), left_on='col1', right_index=True, how='left')

And if you need 'Yes'/'No' as values, use

df2.col3.map({True: 'Yes', False: 'No'})
user_104665560
Related