Validating a merge

The problem

The previous lesson gained 88 rows in a merge. We caught it by counting rows, which works when you remember. This lesson is about the checks that catch it when you forget — and about reading what a failure is telling you.

What you will have at the end

A short validation routine you can paste after any merge, and the ability to tell a join problem from a data problem by the shape of the error.

The steps

Check the thing the data already knows

Survey tables usually contain the same fact twice. Here, every household states its size, and the roster implies it. Comparing them is free:

import pandas as pd

hh = pd.read_csv("household-survey-messy.csv", dtype=str, keep_default_na=False)
ro = pd.read_csv("household-survey-roster.csv", dtype=str, keep_default_na=False)

naive = ro.merge(hh, left_on="_submission__uuid", right_on="_uuid", how="left")

stated = pd.to_numeric(
    hh.drop_duplicates("_uuid").set_index("_uuid")["household_size"], errors="coerce"
)
counted = naive.groupby("_submission__uuid").size()

check = pd.DataFrame({"stated": stated, "counted": counted}).dropna(subset=["counted"])
bad = check[check["stated"] != check["counted"]]

print(f"households where stated != counted: {len(bad)}")
print(bad.head(4))

households where stated != counted: 17
               stated  counted
07c39847-f21c     2.0      4.0
23e75214-41ab     9.0     18.0
313f5430-103c     9.0     18.0
3fa0535e-2676     4.0      8.0

Read the shape of the failure

Look at those numbers before reaching for a fix. 2 and 4. 9 and 18. 4 and 8.

print("every mismatch is exactly double:", (bad["counted"] == bad["stated"] * 2).all())

every mismatch is exactly double: True

That is the diagnosis. A data problem is messy — one household off by one, another off by three. A clean multiple across every failing row is a join fanning out, and the multiple tells you how many times each key matched.

If the ratio were 3, you would look for keys appearing three times. If the mismatches were scattered and small, you would be looking at the data instead: a person missed off the roster, a respondent who miscounted.

Same symptom, two completely different investigations, and the ratio tells you which one you are in.

Confirm the fix

clean = ro.merge(hh.drop_duplicates("_uuid"), left_on="_submission__uuid",
                 right_on="_uuid", how="left")
counted = clean.groupby("_submission__uuid").size()
check = pd.DataFrame({"stated": stated, "counted": counted}).dropna(subset=["counted"])
print("mismatches after dedup:", (check["stated"] != check["counted"]).sum())

mismatches after dedup: 0

Make it a function

def check_merge(before, after, name="merge"):
    """Fail loudly if a merge changed the row count."""
    if len(before) != len(after):
        delta = len(after) - len(before)
        raise ValueError(
            f"{name}: row count changed by {delta:+d} "
            f"({len(before)} -> {len(after)}). "
            "The right-hand key is probably not unique."
        )
    print(f"{name}: {len(after)} rows, unchanged")

def check_keys(df, key, name="table"):
    """Fail loudly if a merge key is not unique."""
    dupes = df[key].duplicated().sum()
    if dupes:
        raise ValueError(f"{name}: {dupes} duplicate values of {key!r}")
    print(f"{name}: {key} is unique across {len(df)} rows")

Used together:

check_keys(hh.drop_duplicates("_uuid"), "_uuid", "households")
merged = ro.merge(hh.drop_duplicates("_uuid"), left_on="_submission__uuid",
                  right_on="_uuid", how="left")
check_merge(ro, merged, "roster to households")

Raising rather than printing is deliberate. A warning scrolls past; an exception stops the script, and a pipeline that stops is one you will fix.

Where it breaks

Validating the output instead of the join

A total that looks plausible is not evidence. 3,385 people across 573 households is 5.9 per household — entirely believable, and wrong. Check the mechanism, not the plausibility.

Checking only one direction

Row count catches duplication. It does not catch rows that failed to match and silently became blanks. Use indicator=True as well, as in the previous lesson.

Running the check after you have already aggregated

Aggregate and the inflation disappears into a mean, where nothing can see it. Validate immediately after the merge, before anything else touches the frame.

A check that cannot fail

If stated and counted can only ever agree because one is computed from the other, the check proves nothing. It works here because the respondent stated a number and the enumerator listed people separately. Know which of your checks are independent and which are circular.

Your turn

Write a check comparing the number of districts in the merged table against the number in the household table. Then deliberately break the merge — drop the drop_duplicates — and confirm your check catches it.

A check you have never seen fail is a check you do not know works.

Long and wide — reshaping the roster, and why repeat groups force the issue.