Menu
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
206
Approximate match - 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE).
In Pandas, VLOOKUP merges two DataFrames if both have a common attribute (column). You can perform VLOOKUP in Pandas using map() and merge() methods as discussed in this article: The map() method. The merge() method.
We can use merge() function to perform Vlookup in pandas. The merge function does the same job as the Join in SQL We can perform the merge operation with respect to table 1 or table 2.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
