Merging and Joining Pandas DataFrames: A Complete Guide (2026)

Quick Answer: Merging and Joining Pandas DataFrames

Wrong Tool, Wrong Table

A customer list and an order list share one thing: a customer_id column. The instinct might be to reach for pd.concat() to put them together.

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alex", "Sam", "Priya"]
})

orders = pd.DataFrame({
    "customer_id": [1, 1, 2, 4],
    "item": ["mug", "pen", "notebook", "mug"],
    "amount": [15.0, 5.0, 10.0, 8.0]
})

pd.concat([customers, orders])

The result is not what anyone actually wants. concat() never looks at customer_id as a key. It just stacks the two tables on top of each other, lining up whatever column names happen to match and filling the rest with missing values. Three customer rows and four order rows become seven ragged rows, none of them actually combined.

What the table really needs is a merge, matching each order to its customer through the shared customer_id column:

customers.merge(orders, on="customer_id")

The Four Join Types

The how parameter controls which rows survive when a key does not appear on both sides. Using the same customers and orders tables from above, where Priya has no orders and one order belongs to a customer_id that does not exist:

customers.merge(orders, on="customer_id", how="inner")
#    customer_id   name      item  amount
# 0            1   Alex       mug    15.0
# 1            1   Alex       pen     5.0
# 2            2    Sam  notebook    10.0

Only customer_id values present on both sides survive. Priya (no orders) and the orphan order for customer_id 4 both disappear.

customers.merge(orders, on="customer_id", how="left")
#    customer_id   name      item  amount
# 0            1   Alex       mug    15.0
# 1            1   Alex       pen     5.0
# 2            2    Sam  notebook    10.0
# 3            3  Priya       NaN     NaN

Every customer survives, matched or not. Priya now appears with NaN in the order columns. The orphan order is still dropped, since it has no match in the left table.

customers.merge(orders, on="customer_id", how="right")   # every order survives; Priya drops out
customers.merge(orders, on="customer_id", how="outer")  # nothing is dropped on either side

right keeps every order, including the one from customer_id 4, which now shows NaN for name. outer keeps everything from both tables at once, five rows in total: nothing is lost either direction.

merge vs. join vs. concat

.join() is really a convenience wrapper around merge, built for the common case of combining on the index instead of a named column:

customers.set_index("customer_id").join(orders.set_index("customer_id"))

Safety Nets: validate and indicator

Two arguments turn a merge from a guess into something checked. validate states the relationship the code assumes and raises an error if the data does not actually match it:

customers.merge(orders, on="customer_id", how="left", validate="one_to_many")

If customer_id unexpectedly turned out to have duplicates in the customers table, this line would raise a MergeError immediately instead of silently returning a distorted result. indicator is the diagnostic counterpart, adding a column that labels exactly where each row came from:

customers.merge(orders, on="customer_id", how="outer", indicator=True)
# adds a _merge column: "both", "left_only", or "right_only"

The Row Explosion Gotcha

Every example above matches at most one order row per customer at a time. Real data is not always that tidy. When a key is duplicated on both sides of a merge, pandas returns every possible combination of the matching rows, not just one. Two duplicate keys on the left times three on the right produces six rows, not two or three. On a large table, that kind of many-to-many merge can multiply row counts fast enough to exhaust memory before anyone notices the mistake.

validate="one_to_many" or validate="many_to_one", matching whichever relationship the data is actually supposed to have, is the direct defense against this: pandas checks key uniqueness before the merge runs, and stops with an error rather than quietly returning a table with far more rows than expected.

Mistakes That Give Away a Beginner

• Reaching for .concat() when the tables need to be matched on a key, not just stacked, the exact mistake in this article's hook.
• Forgetting .join() defaults to left while .merge() defaults to inner, then being surprised by a different row count between the two.
• Skipping validate and not noticing a merge quietly multiplied rows through an unexpected many-to-many match.
• Assuming missing keys never match. Pandas matches null keys against each other by default, unlike standard SQL join behavior.
• Leaving the default _x and _y suffixes on overlapping column names instead of renaming them, making the result harder to read later.

Practice: Fix These Three Yourself

Frequently Asked Questions

Conclusion: One Key, Four Outcomes

Four join types and three tools, all built around one idea: combining data that lives in separate tables because it shares a key. .merge() and .join() do the real matching work, while .concat() only stacks tables that are already aligned. validate and indicator turn a silent mismatch into an error or a labeled row instead of a table that quietly has the wrong number of rows in it.

Practice the three exercises above, then move to the next article in this cluster, which covers list comprehensions and generator expressions: a faster, more compact way to write the loops that built these tables in the first place.