Pandas Joins and Merges

Real-World Scenario

A data engineer builds a customer analytics report by combining data from three different sources: an orders table, a customers table, and a products table — all from different systems with different schemas. This is a join problem. Knowing which join type to use, how to handle mismatched keys, and how to debug unexpected row counts prevents silent data quality issues in production.

Merge (SQL-Style Joins)

import pandas as pd

customers = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "name":        ["Alice", "Bob", "Carol", "Dave", "Eve"],
    "country":     ["US", "UK", "US", "DE", "US"],
})

orders = pd.DataFrame({
    "order_id":    [101, 102, 103, 104, 105, 106],
    "customer_id": [1, 2, 1, 3, 6, 4],   # customer 6 doesn't exist; customer 5 has no orders
    "amount":      [250, 180, 90, 420, 310, 150],
})

# INNER JOIN — only rows where key exists in both DataFrames
inner = pd.merge(customers, orders, on="customer_id", how="inner")
print(f"Inner: {len(inner)} rows")   # 5 (customer 5 dropped, customer 6 dropped)
print(inner)

# LEFT JOIN — all customers, NaN for customers with no orders
left = pd.merge(customers, orders, on="customer_id", how="left")
print(f"Left: {len(left)} rows")     # 6 (Eve has NaN order columns)

# RIGHT JOIN — all orders, NaN for orders with unknown customers
right = pd.merge(customers, orders, on="customer_id", how="right")
print(f"Right: {len(right)} rows")   # 6 (order 105 has NaN customer columns)

# OUTER JOIN — all rows from both, NaN where no match
outer = pd.merge(customers, orders, on="customer_id", how="outer")
print(f"Outer: {len(outer)} rows")   # 7

Joining on Different Column Names

import pandas as pd

users = pd.DataFrame({
    "user_id": [1, 2, 3],
    "name":    ["Alice", "Bob", "Carol"],
})

events = pd.DataFrame({
    "uid":      [1, 1, 2, 3, 3, 3],
    "event":    ["login", "purchase", "login", "login", "purchase", "logout"],
    "amount":   [0, 120, 0, 0, 340, 0],
})

# left_on / right_on when key column names differ
result = pd.merge(users, events, left_on="user_id", right_on="uid", how="left")
result.drop(columns=["uid"], inplace=True)   # remove duplicate key column
print(result)

Multi-Key Joins

import pandas as pd

inventory = pd.DataFrame({
    "warehouse": ["A", "A", "B", "B"],
    "product":   ["Widget", "Gadget", "Widget", "Gadget"],
    "stock":     [100, 50, 200, 80],
})

prices = pd.DataFrame({
    "warehouse": ["A", "A", "B"],
    "product":   ["Widget", "Gadget", "Widget"],
    "unit_price":[9.99, 24.99, 9.99],
})

# Join on both warehouse and product columns
merged = pd.merge(inventory, prices, on=["warehouse", "product"], how="left")
print(merged)
# B/Gadget has no price — NaN in unit_price

Validating Join Assumptions

import pandas as pd

products = pd.DataFrame({
    "product_id": [1, 2, 3],
    "name":       ["Widget", "Gadget", "Gizmo"],
})

order_lines = pd.DataFrame({
    "line_id":    [1, 2, 3, 4],
    "product_id": [1, 2, 1, 3],
    "quantity":   [2, 1, 5, 3],
})

# Validate: products is unique, orders has many rows per product → one_to_many
try:
    result = pd.merge(
        products, order_lines,
        on="product_id",
        how="inner",
        validate="one_to_many",   # raises if products has duplicate product_ids
    )
    print(f"Validated merge: {len(result)} rows")
except pd.errors.MergeError as e:
    print(f"Merge validation failed: {e}")

Concatenating DataFrames

import pandas as pd

# Stack DataFrames vertically (add rows)
q1 = pd.DataFrame({"date": ["2024-01-01", "2024-02-01"], "revenue": [10000, 12000]})
q2 = pd.DataFrame({"date": ["2024-04-01", "2024-05-01"], "revenue": [15000, 13000]})

full_year = pd.concat([q1, q2], ignore_index=True)  # reset index to 0,1,2,3
print(full_year)

# Concatenate with a key to identify the source
combined = pd.concat(
    {"Q1": q1, "Q2": q2},
    names=["quarter", "original_idx"]
)
print(combined)

# Concatenate horizontally (add columns) — use merge for key-aligned joins
df1 = pd.DataFrame({"a": [1, 2, 3]})
df2 = pd.DataFrame({"b": [4, 5, 6]})
side_by_side = pd.concat([df1, df2], axis=1)
print(side_by_side)

Real-World: Building an Analytics Dataset from Three Tables

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)

# Three source tables — as if loaded from a database
customers = pd.DataFrame({
    "customer_id": range(1, 101),
    "name":        [f"Customer_{i}" for i in range(1, 101)],
    "country":     rng.choice(["US", "UK", "DE", "FR"], 100),
    "tier":        rng.choice(["Bronze", "Silver", "Gold", "Platinum"], 100),
})

orders = pd.DataFrame({
    "order_id":    range(1001, 1501),
    "customer_id": rng.integers(1, 101, 500),
    "product_id":  rng.integers(1, 21, 500),
    "quantity":    rng.integers(1, 10, 500),
    "order_date":  pd.date_range("2024-01-01", periods=500, freq="h").date,
})

products = pd.DataFrame({
    "product_id": range(1, 21),
    "name":       [f"Product_{i}" for i in range(1, 21)],
    "category":   rng.choice(["Electronics", "Accessories", "Software"], 20),
    "unit_price": rng.uniform(10, 500, 20).round(2),
})

# Step 1: Join orders → products to get price per order line
orders_with_price = pd.merge(
    orders, products[["product_id", "unit_price", "category"]],
    on="product_id", how="left", validate="many_to_one"
)
orders_with_price["revenue"] = orders_with_price["quantity"] * orders_with_price["unit_price"]

# Step 2: Join → customers to get customer attributes
full = pd.merge(
    orders_with_price,
    customers[["customer_id", "country", "tier"]],
    on="customer_id", how="left", validate="many_to_one"
)

# Step 3: Aggregate to customer-level metrics
customer_summary = full.groupby("customer_id").agg(
    total_orders  = ("order_id",  "count"),
    total_revenue = ("revenue",   "sum"),
    avg_order_val = ("revenue",   "mean"),
    categories    = ("category",  "nunique"),
).reset_index()

# Step 4: Join summary back to customer table
result = pd.merge(customers, customer_summary, on="customer_id", how="left")
result["total_orders"].fillna(0, inplace=True)

print(result.head(10))
print(f"\nTotal revenue: ${result['total_revenue'].sum():,.2f}")