Pandas Vlookup
-
Use
Inner Join
to Perform Vlookup in Pandas -
Use
Left Join
to Perform Vlookup in Pandas -
Use
Right Join
to Perform Vlookup in Pandas -
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:
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:
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:
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:
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