Chapter 15 Merging, Concatenating, and Reshaping

Joining tables is a common source of silent analytical errors. Correct syntax is not enough: you must understand the expected key relationship, unmatched records, and whether the join changes the unit of observation.

15.1 Start with the expected relationship

Suppose customers contains one row per customer and orders contains many orders per customer. This is a one-to-many relationship.

import pandas as pd

customers = pd.DataFrame(
    {
        "customer_id": [1, 2, 3],
        "segment": ["A", "B", "A"],
    }
)

orders = pd.DataFrame(
    {
        "order_id": [101, 102, 103],
        "customer_id": [1, 1, 2],
        "revenue": [25.0, 40.0, 30.0],
    }
)

Use validate to make the expected cardinality executable. The merge fails instead of silently multiplying rows when the relationship is violated.

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

assert len(customer_orders) == 4
assert customer_orders["order_id"].isna().sum() == 1
assert set(customer_orders["_merge"]) == {"both", "left_only"}

The left_only record identifies a customer without an order. Whether that is expected depends on the business question.

15.2 Check key uniqueness before joining

assert customers["customer_id"].is_unique
assert orders["order_id"].is_unique
assert not orders["customer_id"].is_unique

Duplicate keys are not automatically errors; they are errors when they violate the intended unit of observation. State that unit explicitly before joining.

15.3 Choose join type from the question

Inner join: keep only keys present in both tables.
Left join: preserve the left table’s population.
Right join: preserve the right table’s population.
Outer join: retain keys from both and expose nonmatches.

Do not select inner simply because it removes missing rows. It may silently change the study population.

15.4 Concatenating compatible tables

Use concat() to stack tables with compatible schemas. ignore_index=True creates a new sequential row index; it does not create a business key.

january = pd.DataFrame({"customer_id": [1, 2], "revenue": [10, 20]})
february = pd.DataFrame({"customer_id": [1, 3], "revenue": [15, 25]})

monthly_revenue = pd.concat([january, february], ignore_index=True)

assert monthly_revenue.shape == (4, 2)

15.5 Wide and long formats

Long format usually stores one observation per row and one variable per column. melt() converts repeated measurement columns to a long representation.

wide = pd.DataFrame(
    {
        "customer_id": [1, 2],
        "revenue_jan": [10, 20],
        "revenue_feb": [15, 25],
    }
)

long = wide.melt(
    id_vars="customer_id",
    var_name="month",
    value_name="revenue",
)

assert long.shape == (4, 3)
assert long["revenue"].sum() == 70

pivot() reverses this pattern only when each index/column combination is unique. Use pivot_table() with an explicit aggregation when duplicates are expected.

15.6 Post-join validation

After a merge, check more than whether the code ran:

Row counts and the unit of observation
Key uniqueness where expected
Counts of matched and unmatched records
Missingness introduced in important fields
Aggregate reconciliation, such as total revenue before and after joining

15.7 Exercises

Create a duplicate customer key and confirm that validate="one_to_many" rejects the merge.
Use an outer join to identify keys that occur in only one table.
Explain how a many-to-many join can inflate a revenue total.