Closest value join with pandas-CodePudding

I have a left table

and a right table

And I would like to get the final table

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.

CodePudding user response：

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

CodePudding user response：

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').