Table of Contents
To perform a VLOOKUP in Pandas, use the merge() function. This function merges two data frames based on a common column, and returns a DataFrame containing only the matched rows from the left data frame. The syntax for this is: pd.merge(left_df, right_df, on=’common_column’). The left_df and right_df are the two data frames that will be compared, and the common_column is the column that will be used to match the data frames. The result of the merge() function is a new DataFrame containing only the matched rows.
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 .
The following tutorials explain how to perform other common operations in Python: