Merging, Joining & Concatenating DataFrames

pd.concat() — Stacking DataFrames

Real data rarely arrives as one tidy file. You might get a separate CSV export for every month, every region, or every data source — and the first job is often just gluing them into one DataFrame. pd.concat() is the tool for that: it stacks DataFrames together along an axis, with no key-matching logic involved.

import pandas as pd

jan_sales = pd.DataFrame({
    "product": ["Laptop", "Mouse", "Keyboard"],
    "sales": [1200, 45, 65],
})
feb_sales = pd.DataFrame({
    "product": ["Laptop", "Monitor"],
    "sales": [980, 910],
})

# Default: axis=0, stacks rows on top of each other
combined = pd.concat([jan_sales, feb_sales])
print(combined)
#     product  sales
# 0    Laptop   1200
# 1     Mouse     45
# 2  Keyboard     65
# 0    Laptop    980   ← index restarted from feb_sales, now duplicated
# 1   Monitor    910

combined = pd.concat([jan_sales, feb_sales], ignore_index=True)
print(combined)
#     product  sales
# 0    Laptop   1200
# 1     Mouse     45
# 2  Keyboard     65
# 3    Laptop    980
# 4   Monitor    910

You can also concatenate side by side instead of stacking, by setting axis=1. In that mode, Pandas aligns the pieces by their index rather than gluing rows on top of each other.

names = pd.DataFrame({"name": ["Ana", "Ben", "Cy"]})
scores = pd.DataFrame({"score": [88, 92, 79]})

side_by_side = pd.concat([names, scores], axis=1)
print(side_by_side)
#   name  score
# 0  Ana     88
# 1  Ben     92
# 2   Cy     79

pd.merge() — Inner Join

A merge combines two DataFrames based on matching values in one or more key columns — exactly like a SQL JOIN. We'll use the same pair of small DataFrames for every join type in this lesson, so you can see clearly how each one changes the result.

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4],
    "name": ["Ana", "Ben", "Cy", "Deb"],
})
orders = pd.DataFrame({
    "order_id": [101, 102, 103, 104],
    "customer_id": [1, 2, 2, 5],  # note: customer 5 doesn't exist in "customers"
    "amount": [250, 80, 150, 60],
})

Notice on purpose: customers 3 and 4 have never placed an order, and order 104 belongs to a customer_id (5) that doesn't exist in customers at all. This lets each join type show a different result.

# how="inner" is the default — you can omit it
inner = pd.merge(customers, orders, on="customer_id", how="inner")
print(inner)

Left Join & Right Join

Inner join drops anything that doesn't match. Often you want to keep every row from one side regardless of whether a match exists — that's what how="left" and how="right" are for.

# Keep EVERY row from customers (the "left" DataFrame)
left = pd.merge(customers, orders, on="customer_id", how="left")
print(left)

Every customer survives the left join — customers 3 and 4 just get NaN in the order columns, since Pandas has nothing to fill them with. A right join is the mirror image: keep every row from the second DataFrame instead.

# Keep EVERY row from orders (the "right" DataFrame)
right = pd.merge(customers, orders, on="customer_id", how="right")
print(right)

Outer Join

An outer join keeps everything from both sides — the union of the two keys. Anywhere a match doesn't exist, Pandas fills with NaN. It's the safest choice when you want to make sure you don't silently lose any rows while exploring how two datasets relate.

outer = pd.merge(customers, orders, on="customer_id", how="outer")
print(outer)

.join() — Combining on the Index

.join() is a convenience method for the common case where you're combining DataFrames using their index rather than a shared column. It's shorter to write than the equivalent pd.merge() call once your data is indexed the right way.

customers_idx = customers.set_index("customer_id")

# Total amount spent per customer, indexed by customer_id
totals = orders.groupby("customer_id")["amount"].sum().rename("total_spent")

# join() matches customers_idx's index against totals' index
joined = customers_idx.join(totals)
print(joined)

By default, .join() behaves like a left join (how="left"), keeping every row of customers_idx — which is why customer 5 from totals is dropped, and customers 3/4 get NaN. You can also skip the set_index() step entirely with the on parameter, matching a plain column in the caller against the other object's index:

# customers (not re-indexed) joined against totals' index, matched via customer_id
result = customers.join(totals, on="customer_id")
print(result)

# You can override the join direction, just like with merge()
customers_idx.join(totals, how="inner")   # only customers who actually ordered
customers_idx.join(totals, how="outer")   # every customer AND every order id, union style

When to Use Which

All three tools combine DataFrames, but they solve different problems. Here's the decision that matters:

Lesson Summary

Let's recap everything you learned in this lesson: