How to merge two dataframe based on multiple criteria plus id

March 30, 2022

Here’s my dataframe df

Id     Value
3      104
6      108

Here’s the reference data ref

Id    Code  Long     Lat
1     ABC     89      82
2     DEF     87      81
3     GHI     89      82
4     IJK     90      81
5     LMN     90      81
6     OPQ     87      81

Here’s what I did

df.merge(ref, how='left', left_on=['Id'], right_on = ['Id'])

The Result is

Id     Value    Code    Long     Lat
3      104      GHI       89      82
6      108      OPQ       87      81

What I expect is
Id 1 is give Value 104, because it has same Long and Lat with Id 3 ,
Id 2 is give Value 108, because it has same Long and Lat with Id 6

So the output dataframe going to be like this

Id     Value    Code    Long     Lat
1      104      ABC       89      82
2      108      DEF       87      81
3      104      GHI       89      82
6      108      OPQ       87      81

>Solution :

You can do merge twice

temp = df.merge(ref, how='left', left_on=['Id'], right_on = ['Id'])
out = ref.merge(temp[['Value','Long','Lat']])
Out[473]: 
   Id Code  Long  Lat  Value
0   1  ABC    89   82    104
1   3  GHI    89   82    104
2   2  DEF    87   81    108
3   6  OPQ    87   81    108