Merging & Joining DataFrames

Reviewed & published by Brayan K

Merging in pandas is the operation of combining two DataFrames into one by matching rows on shared key columns or indexes — the same idea as a SQL JOIN, letting you stitch related tables (like customers and their orders) into a single analysis-ready table.

Part of the free Pandas course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• How pd.merge joins two DataFrames on a key column
• The four join types: inner, left, right, and outer
• Using left_on / right_on when key names differ
• Joining on the index with df.join()
• Renaming overlapping columns with suffixes
• Auditing matches with indicator=True

1 Your First Merge — Matching on a Key

Real data almost never lives in one table. You might have a customers table and a separate orders table that share a customer_id. pd.merge() lines them up by that shared key.

The most common merge is a many-to-one join: many orders point to one customer. Each order row gets the matching customer details attached.

import pandas as pd

# Left table: one row per customer
customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Carol"],
    "city": ["London", "Paris", "Berlin"],
})

# Right table: many orders, each tied to a customer_id
orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 2, 1, 3],
    "amount": [50, 30, 20, 75],
})

# Inner join on the shared "customer_id" column
merged = pd.merge(customers, orders, on="customer_id", how="inner")
print(merged)
# Each order row now carries the customer's name and city.
# Output columns: customer_id, name, city, order_id, amount
# 4 rows (one per order) because it is a many-to-one join.

# ✅ Expected output:
#    customer_id   name    city  order_id  amount
# 0            1  Alice  London       101      50
# 1            1  Alice  London       103      20
# 2            2    Bob   Paris       102      30
# 3            3  Carol  Berlin       104      75

2 The Four Join Types (how=)

The how argument decides which keys survive the merge:

Only keys present in BOTH frames — the intersection.

All keys from the left frame; non-matches get NaN.

All keys from the right frame; non-matches get NaN.

Every key from both frames — the union.

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Carol", "Dan"],
})

orders = pd.DataFrame({
    "customer_id": [1, 2, 2, 5],
    "amount": [50, 30, 20, 99],
})

# LEFT join: keep ALL customers, even those with no orders
left = pd.merge(customers, orders, on="customer_id", how="left")
print(left)
# Dan (id 4) has no order -> amount is NaN.
# Customer 5 from orders is dropped because it is not in customers.

print("---")

# OUTER join: keep every id from both sides (union)
outer = pd.merge(customers, orders, on="customer_id", how="outer")
print(outer)
# Includes Dan (no orders) AND id 5 (no customer name) -> NaNs on both sides.

# ✅ Expected output:
#    customer_id   name  amount
# 0            1  Alice    50.0
# 1            2    Bob    30.0
# 2            2    Bob    20.0
# 3            3  Carol     NaN
# 4            4    Dan     NaN
# ---
#    customer_id   name  amount
# 0            1  Alice    50.0
# 1            2    Bob    30.0
# 2            2    Bob    20.0
# 3            3  Carol     NaN
# 4            4    Dan     NaN
# 5            5    NaN    99.0

3 Different Key Names, Suffixes, Indicators & .join()

When the key columns have different names, use left_on and right_on. When both frames have a column with the same name (other than the key), pandas appends suffixes so nothing is overwritten. And df.join() is a shortcut for joining on the index.

import pandas as pd

customers = pd.DataFrame({
    "cust_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Carol"],
    "country": ["UK", "FR", "DE"],
})

orders = pd.DataFrame({
    "buyer_id": [1, 2, 1],
    "amount": [50, 30, 20],
    "country": ["UK", "FR", "UK"],  # same column name as customers!
})

# Key columns have different names -> use left_on / right_on
m = pd.merge(
    customers, orders,
    left_on="cust_id", right_on="buyer_id",
    how="inner",
    suffixes=("_cust", "_order"),  # disambiguate the shared 'country' column
)
print(m.columns.tolist())
# ['cust_id', 'name', 'country_cust', 'buyer_id', 'amount', 'country_order']

print("---")

# indicator=True tags where each row came from
audit = pd.merge(customers, orders,
                 left_on="cust_id", right_on="buyer_id",
                 how="outer", indicator=True)
print(audit["_merge"].value_counts())
# Shows counts of 'both', 'left_only', 'right_only'.

print("---")

# df.join() joins on the INDEX and defaults to a LEFT join
left_df = customers.set_index("cust_id")
right_df = orders.set_index("buyer_id")[["amount"]]
joined = left_df.join(right_df)
print(joined)

# ✅ Expected output:
# ['cust_id', 'name', 'country_cust', 'buyer_id', 'amount', 'country_order']
# ---
# _merge
# both          3
# left_only     1
# right_only    0
# Name: count, dtype: int64
# ---
#           name country  amount
# cust_id                       
# 1        Alice      UK    50.0
# 1        Alice      UK    20.0
# 2          Bob      FR    30.0
# 3        Carol      DE     NaN

🎯 Your Turn: Complete the Merge

Fill in the blank so every product appears, even ones that were never sold. Which how keeps all rows from the left frame?

import pandas as pd

products = pd.DataFrame({
    "product_id": [1, 2, 3],
    "product": ["Pen", "Notebook", "Eraser"],
})

sales = pd.DataFrame({
    "product_id": [1, 1, 2],
    "units": [10, 5, 3],
})

# 🎯 YOUR TURN: keep ALL products, even the Eraser which had no sales.
result = pd.merge(products, sales, on="product_id", how="___")
print(result)

# EXPECTED OUTPUT (4 rows):
#    product_id   product  units
# 0           1       Pen   10.0
# 1           1       Pen    5.0
# 2           2  Notebook    3.0
# 3           3    Eraser    NaN   <- Eraser kept, units is NaN

📋 Quick Reference

🏆 Mini Challenge: Build a Sales Report

Join three pieces of information — customers, orders, and a region lookup — into one report, then total the amount spent per city.

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Carol"],
    "city": ["London", "Paris", "London"],
})

orders = pd.DataFrame({
    "customer_id": [1, 2, 1, 3, 2],
    "amount": [50, 30, 20, 75, 40],
})

# 1) Attach customer details to every order (many-to-one left join)
report = pd.merge(orders, customers, on="customer_id", how="left")
print(report)

# 2) Total spend per city
by_city = report.groupby("city")["amount"].sum()
print(by_city)
# EXPECTED: London = 50 + 20 + 75 = 145, Paris = 30 + 40 = 70

# ✅ Expected output:
#    customer_id  amount   name    city
# 0            1      50  Alice  London
# 1            2      30    Bob   Paris
# 2            1      20  Alice  London
# 3            3      75  Carol  London
# 4            2      40    Bob   Paris
# city
# London    145
# Paris      70
# Name: amount, dtype: int64

❓ Frequently Asked Questions

Lesson complete — you can now combine tables like a pro!

You can merge on columns or indexes, choose the right join type, handle mismatched key names and overlapping columns, and audit which rows matched. This is the backbone of every multi-table analysis.

Practice quiz

Continue this course

• Previous: Aggregation & Transformation
• Next: Concatenating & Appending — Stack DataFrames vertically and horizontally with pd.concat
• Quick reference: Pandas cheat sheet › Merging & Reshaping

Frequently asked questions

What is the difference between pd.merge and df.join?

pd.merge is the general-purpose function that joins on one or more columns (or indexes) and supports inner, left, right, and outer joins. df.join is a convenience method that joins primarily on the index of the other DataFrame and defaults to a left join. Under the hood, join calls merge — use merge when you need to match on columns, and join for quick index-aligned combines.

What does how='inner' versus how='outer' actually do?

how controls which keys survive. 'inner' keeps only keys present in BOTH frames (the intersection). 'outer' keeps every key from both frames (the union), filling missing values with NaN. 'left' keeps all keys from the left frame, and 'right' keeps all keys from the right frame.

How do I find rows that did not match during a merge?

Pass indicator=True to pd.merge. Pandas adds a _merge column whose value is 'both', 'left_only', or 'right_only' for each row. Combined with how='outer' this is the standard way to audit which keys failed to join.