Vlookup in Pandas with approximate match

user_541729 0
Viewed 6408

I need to do a vlookup style operation on two pandas dataframes

The Vlookup function in Excel has an extra parameter whether it should find an approximate or exact match. For exact match I know I can using the join function. But how would I do the approximate match where I find the next larger value?

For instance, if I have a marks and grades definition dataframe, like this:

Student Mark
John    65
Peter   75
Jason   79

And

Mark    Symbol
50      D
60      C  # table indicates a mark between 60 and 69 is a C symbol
70      B
80      A

How can I get a table like this:

Student Mark    Symbol
John    65      C
Peter   75      B
Jason   79      B
pandas
1 Answers

Use merge_asof for merge on nearest key

In [2484]: pd.merge_asof(df1, df2, on='Mark')
Out[2484]:
  Student  Mark Symbol
0    John    65      C
1   Peter    75      B
2   Jason    79      B

Details

In [2485]: df1
Out[2485]:
  Student  Mark
0    John    65
1   Peter    75
2   Jason    79

In [2486]: df2
Out[2486]:
   Mark Symbol
0    50      D
1    60      C
2    70      B
3    80      A
user_21372550
Related