Joining and Merging DataFrames

Joins are one of the easiest places to create believable but wrong data. An inner join can silently remove unmatched transactions; duplicate reference keys can multiply amounts; and a successful merge can still violate the intended grain.

Problem

You need to enrich orders with customer name, country, and segment. Most customer IDs match, but two order rows use identifiers absent from the customer file. The customer file also contains one exact duplicate, which can turn an expected many-to-one relationship into many-to-many behavior.

Datasets used

Use the Customer Dataset and Orders Dataset:

• customers.csv
• orders.csv

The imperfections are intentional so the join produces evidence worth investigating.

Code: inspect keys before merging

from pathlib import Path

import pandas as pd

data = Path("data")
customers = pd.read_csv(data / "customers.csv")
orders = pd.read_csv(data / "orders.csv", parse_dates=["order_date"])

print("customer rows:", len(customers))
print("unique customer IDs:", customers["customer_id"].nunique())
print("order rows:", len(orders))

duplicate_customer_keys = customers.loc[
    customers.duplicated(subset=["customer_id"], keep=False)
].sort_values("customer_id")
print(duplicate_customer_keys)

The duplicate customer row must be resolved before asserting that the customer side is unique. Because it is an exact duplicate in this learning file, removing exact duplicates is defensible. In production, conflicting versions require a survivorship rule based on timestamps, source priority, or governance.

Code: left join with cardinality validation

customer_dimension = (
    customers
    .drop_duplicates()
    [["customer_id", "customer_name", "country", "segment"]]
)

enriched_orders = orders.merge(
    customer_dimension,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)

print(enriched_orders["_merge"].value_counts())
assert len(enriched_orders) == len(orders)

The left join retains every order. validate="many_to_one" states that many orders may reference one customer, while each customer key must appear at most once on the right. If that contract fails, pandas raises instead of multiplying rows silently.

Output

The merge indicator distinguishes matched and unmatched rows:

_merge value	Meaning
both	Customer ID exists in both DataFrames
left_only	Order retained, but no customer matched
right_only	Possible with outer/right joins, not this left join

Inspect the exceptions rather than filling customer attributes immediately.

unmatched_orders = enriched_orders.loc[
    enriched_orders["_merge"].eq("left_only"),
    ["order_id", "customer_id", "order_date", "status"],
]

print(unmatched_orders)

The unmatched customer IDs are controlled bad references. Depending on the pipeline, you might quarantine those orders, retain them with an Unknown dimension member, or fail the load above a tolerance. An inner join would hide the issue and reduce order count.

Explanation: choose the join from the output requirement

Use a left join when the left-side population must be preserved, such as retaining every order while enriching available customers. Use an inner join when only matched pairs are explicitly required. An outer join is useful for reconciliation because it exposes keys present on either side.

Pandas also supports joins on differently named keys:

result = orders.merge(
    customer_dimension,
    left_on="customer_id",
    right_on="customer_id",
    how="left",
    validate="many_to_one",
)

For composite keys, pass lists to on, left_on, and right_on. Confirm that data types align before merging. A numeric identifier on one side and a string identifier on the other may fail or lead to unsafe coercion. Preserve leading zeros when they are part of an identifier rather than a number.

After a merge, reconcile row count, distinct business keys, match rate, and important measures. Row count alone is insufficient: a duplicated reference can multiply one subset while another subset is lost.

match_rate = enriched_orders["_merge"].eq("both").mean()
print(f"customer match rate: {match_rate:.1%}")
print("distinct orders before:", orders["order_id"].nunique())
print("distinct orders after:", enriched_orders["order_id"].nunique())

Common mistakes

• Defaulting to an inner join and silently discarding unmatched facts.
• Merging before checking key uniqueness.
• Omitting validate even when cardinality is known.
• Filling missing dimension fields before measuring match quality.
• Joining columns with incompatible types or inconsistent normalization.
• Selecting every right-side column and creating ambiguous suffixes.
• Checking only final row count and not key or measure reconciliation.

Try it yourself

First, attempt the many-to-one merge without removing the duplicate customer row and observe the validation error. Then resolve only exact duplicates and rerun it. Produce a two-row unmatched-customer report and calculate the match percentage.

Next, download the Products Dataset and enrich orders with product name and category using another validated many-to-one left join. Find the intentionally unmatched product key. Finally, compare inner and left join row counts and explain which result is acceptable for revenue reporting and which is useful for data-quality monitoring.

Production notes

Join metrics deserve first-class monitoring. Record left rows, right rows, output rows, matched rows, unmatched keys on each side, and duplicate key counts before execution. Compare those values with a normal range. A merge that completes without an exception can still represent a serious upstream drift if match rate falls from 99.9 percent to 80 percent.

Normalize keys before the join only through an approved rule. Trimming whitespace or harmonizing casing may be appropriate for codes, but it can also collapse genuinely distinct identifiers. Keep original keys when normalized versions are introduced so exceptions can be traced to source records.

Large joins can create substantial temporary memory. Select only required columns, resolve right-side uniqueness early, and estimate the output grain. If both sides contain repeated keys, calculate the potential multiplication before merging. For pipelines that must retain bad records, write unmatched rows to a review output with the run identifier and reason rather than merely printing them to a transient console.