You can use the following basic syntax to perform a VLOOKUP (similar to Excel) in pandas:
pd.merge(df1, df2, on ='column_name', how ='left')
The following step-by-step example shows how to use this syntax in practice.
Step 1: Create Two DataFrames
First, let’s import pandas and create two pandas DataFrames:
import pandas as pd #define first DataFrame df1 = pd.DataFrame({'player': ['A', 'B', 'C', 'D', 'E', 'F'], 'team': ['Mavs', 'Mavs', 'Mavs', 'Mavs', 'Nets', 'Nets']}) #define second DataFrame df2 = pd.DataFrame({'player': ['A', 'B', 'C', 'D', 'E', 'F'], 'points': [22, 29, 34, 20, 15, 19]}) #view df1 print(df1) player team 0 A Mavs 1 B Mavs 2 C Mavs 3 D Mavs 4 E Nets 5 F Nets #view df2 print(df2) player points 0 A 22 1 B 29 2 C 34 3 D 20 4 E 15 5 F 19
Step 2: Perform VLOOKUP Function
The VLOOKUP function in Excel allows you to look up a value in a table by matching on a column.
The following code shows how to look up a player’s team by using pd.merge() to match player names between the two tables and return the player’s team:
#perform VLOOKUP joined_df = pd.merge(df1, df2, on ='player', how ='left') #view results joined_df player team points 0 A Mavs 22 1 B Mavs 29 2 C Mavs 34 3 D Mavs 20 4 E Nets 15 5 F Nets 19
Notice that the resulting pandas DataFrame contains information for the player, their team, and their points scored.
You can find the complete online documentation for the pandas merge() function here.
Additional Resources
The following tutorials explain how to perform other common operations in Python:
How to Create Pivot Tables in Python
How to Calculate Correlation in Python
How to Calculate Percentiles in Python