How to Perform a VLOOKUP in Pandas


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

Leave a Reply

Your email address will not be published.