Pandas Vlookup

Pandas Vlookup

  1. Use Inner Join to Perform Vlookup in Pandas
  2. Use Left Join to Perform Vlookup in Pandas
  3. Use Right Join to Perform Vlookup in Pandas
  4. Use Outer Join to Perform Vlookup in Pandas

Vlookup stands for vertical lookup. Vlookup is used for merging two different tables where there should be at least one common attribute between the two tables.

As an output, we will get a single table consisting of data from both tables. This process works like the join() query in SQL.

To join two data frames in Python, we can use the merge() method available in the Pandas library. There are many ways to merge two tables.

Use Inner Join to Perform Vlookup in Pandas

Inner join merges those rows from the tables, which have matching values for the key. It returns all the rows from both tables where the condition is satisfied.

In the following example, we have opened two tables. The Student table consists of ST_ID, ST_NAME, and Department columns.

The other one is the Course table consisting of information about courses enrolled by the student. Columns for the course table are ST_ID and Course.

We have performed inner join by calling the merge() method with Pandas object and have passed both tables, the column name based on which the table will be merged, and specified inner join.

The two tables will be combined into a single table based on the ST_ID value as an output.

If the condition where the ST_ID value from the student table matches with the ST_ID value of the course table, those records will be selected for merging. Here the ST_ID works just like the foreign key in the case of SQL.

#Python 3.x
import pandas as pd
student = pd.read_csv('student.csv')
course = pd.read_csv('Course.csv')
print(student)
print(course)
inner_join = pd.merge(student, course, on ='ST_ID', how ='inner')
print(inner_join)

Output:

inner join

Use Left Join to Perform Vlookup in Pandas

Left join works like inner join, but the only difference is that it includes all the left table/first table rows and the matching rows of the second table. In the following example, we have performed left join by specifying left in the merge() method call.

As an output, we can see all the matching records, plus the extra records from the left table are also included in the final table. If no matching record is found in the second table, that row’s value will be NaN (not a number).

#Python 3.x
import pandas as pd
student = pd.read_csv('student.csv')
course = pd.read_csv('Course.csv')
print(student)
print(course)
left_join = pd.merge(student, course, on ='ST_ID', how ='left')
print(left_join)

Output:

left join

Use Right Join to Perform Vlookup in Pandas

Right join works opposite to left join. Right join is like inner join; however, the only difference is that it includes all the right table/second table rows and the matching rows of both tables.

The example below has performed the right join by specifying right in the merge() method call. As an output, we can see all the matching records plus the extra records from the right table are included in the final table.

If no matching record is found in the first table, that row’s value will be NaN (not a number).

#Python 3.x
import pandas as pd
student = pd.read_csv('student.csv')
course = pd.read_csv('Course.csv')
print(student)
print(course)
right_join = pd.merge(student, course, on ='ST_ID', how ='right')
print(right_join)

Output:

right join

Use Outer Join to Perform Vlookup in Pandas

Outer join is a combination of left and right join. It combines all the rows from the left table and the right table.

If no matching values are found, NaN will appear in that row.

#Python 3.x
import pandas as pd
student = pd.read_csv('student.csv')
course = pd.read_csv('Course.csv')
print(student)
print(course)
outer_join = pd.merge(student, course, on ='ST_ID', how ='outer')
print(outer_join)

Output:

outer join

Author: Fariba Laiq
Fariba Laiq avatar Fariba Laiq avatar

I am Fariba Laiq from Pakistan. An android app developer, technical content writer, and coding instructor. Writing has always been one of my passions. I love to learn, implement and convey my knowledge to others.

LinkedIn