How do I check that a merge behaved as expected in pandas?

The short version

Pass validate="one_to_one" (or "one_to_many", and so on) to merge so it checks the key relationship and raises if the data breaks it, rather than silently producing extra rows. Pass indicator=True to add a column showing whether each row matched on the left only, the right only, or both. Together these turn a merge from a silent risk into a checked operation.

The silent danger validate prevents

A merge multiplies rows when a key repeats on both sides, which can quietly turn 1,000 rows into 5,000. validate makes your assumption explicit and enforced.

import pandas as pd

a = pd.DataFrame({"id": [1, 2], "x": [1, 2]})
bad = pd.DataFrame({"id": [1, 1], "y": [9, 8]})   # id 1 is duplicated

a.merge(bad, on="id", validate="one_to_one")
# raises MergeError: Merge keys are not unique in right dataset

Without validate, this merge would succeed and double the matching rows. With it, you find out immediately that the right side violates the one-to-one assumption. Use "one_to_one", "one_to_many", "many_to_one" or "many_to_many" to state what you expect.

Seeing which side matched with indicator

indicator=True adds a _merge column recording the source of each row.

a = pd.DataFrame({"id": [1, 2], "x": [1, 2]})
b = pd.DataFrame({"id": [1, 3], "y": [9, 8]})

m = a.merge(b, on="id", how="left", indicator=True)
print(m["_merge"].tolist())
# ['both', 'left_only']

This tells you that id 2 had no match on the right (left_only). It is the quickest way to audit a join and to count how many rows failed to match.

Using indicator to find non-matches

The _merge column is the standard tool for finding rows that did not join, which is the basis of an anti-join.

outer = a.merge(b, on="id", how="outer", indicator=True)
outer[outer["_merge"] == "left_only"]    # in a but not b

This surfaces exactly which keys are present on one side only, which is often the real question behind "why did my merge lose rows".

Why this matters for correctness

Merges are where quiet data errors creep in: a duplicated key inflates every downstream total, and a failed match silently drops rows. Adding validate and checking indicator costs one argument each and catches both problems at the point they happen, rather than leaving you to discover a wrong total three steps later. For any merge on data you do not fully control, make them a habit.

A practical routine

State the expected cardinality with validate, run the merge with indicator=True during development, and check the counts of both, left_only and right_only before removing the indicator for production. This small routine prevents most merge-related surprises.