Closest value join with pandas

usergEek 0
Viewed 32

I have a left table

enter image description here

and a right table

enter image description here

And I would like to get the final table

enter image description here

For this, I would like to use a function (merge or other more advanced verions like merge_asof or merge_ordered) in pandas

  • the match for v1 and v2 is exact
  • then the match for v3 is approximate, with the closest values.
merge pandas python
2 Answers

Try with merge_asof

out = pd.merge_asof(df1.sort_values('v3'),
                    df2.sort_values('v3'), 
                    by = ['v1','v2'], 
                    on = 'v3', 
                    direction = 'nearest')

Out[10]: 
  v1 v2  v3    c
0  a  c  11  100
1  a  d  22  200
2  b  c  33  200
BENY0

You need merge_asof:

pd.merge_asof(df1, df2, by=['v1', 'v2'], on='v3', direction='nearest')

NB. 'v3' must be sorted in both df1/df2, if this not the case add .sort_values('v3').

mozway0
Related