Types of Joins on Data Objects

When merging two DataFrames, the how parameter controls what happens to rows whose key doesn't exist in the other DataFrame. Pandas supports the same four join types found in SQL.

Sample Data

import pandas as pd

students = pd.DataFrame({"StudentID": [1, 2, 3], "Name": ["Abhay", "Priya", "Rohit"]})
marks = pd.DataFrame({"StudentID": [2, 3, 4], "Score": [92, 78, 65]})

Inner Join (default)

Keeps only the rows whose key appears in both DataFrames.

pd.merge(students, marks, on="StudentID", how="inner")
#    StudentID   Name  Score
# 0          2  Priya     92
# 1          3  Rohit     78

Left Join

Keeps every row from the left DataFrame, filling with NaN where there's no match on the right.

pd.merge(students, marks, on="StudentID", how="left")

Right Join

Keeps every row from the right DataFrame, filling with NaN where there's no match on the left.

pd.merge(students, marks, on="StudentID", how="right")

Outer Join

Keeps every row from both DataFrames, filling with NaN wherever a key is missing on either side.

pd.merge(students, marks, on="StudentID", how="outer")

Picking the right join type is really about deciding what to do with unmatched rows - inner join is the strictest (drop anything unmatched), while outer join is the most inclusive (keep everything and mark the gaps as NaN).

Coming Up Next

Next, you'll learn how to clean messy, real-world data using Pandas.