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
CodePudding user response:
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