Join the roster to the household

The problem

The household table has one row per household. The roster has one row per person. You need them together — to disaggregate by district, or to count school-age children per household.

The merge takes one line and runs without complaint. It also invents 88 people.

What you will have at the end

A correctly joined table, and the habit of counting rows before and after every merge you ever write.

The dataset

This lesson needs both files: household-survey-roster.csv from the link above, and household-survey-messy.csv from the earlier lessons.

The steps

Look at both tables first

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)

print("households:", len(hh), "unique:", hh["_uuid"].nunique())
print("roster rows:", len(ro), "households referenced:", ro["_submission__uuid"].nunique())

households: 618 unique: 600
roster rows: 3297 households referenced: 573

Three numbers worth sitting with. 618 rows for 600 households — the duplicates from the export lesson. And 573 households in the roster, not 600, because the 27 that declined were never asked who lives there.

The merge that goes wrong

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

The roster had 3,297 rows. The result has 3,385. A left join gained 88 rows.

This is the thing to internalise: a left merge is only "safe" when the right table has at most one row per key. Here 18 households appear twice, so every person in those households matched twice and was duplicated.

Nothing warned you. The merge succeeded.

Count before and after, every time

before = len(ro)
merged = ro.merge(hh.drop_duplicates("_uuid"), left_on="_submission__uuid",
                  right_on="_uuid", how="left")
print(f"{before} -> {len(merged)}")
assert len(merged) == before, "merge changed the row count"

3297 -> 3297

That assert is three seconds of typing and it is the difference between finding this today and finding it in a report. Put one after every merge where you expect the row count to hold.

Check the key before you use it

print("right-side duplicate keys:", hh["_uuid"].duplicated().sum())

If that is not zero, decide what to do about it before merging — do not let the merge decide for you.

Find out who did not match

indicator=True adds a column saying where each row came from:

check = ro.merge(hh.drop_duplicates("_uuid"), left_on="_submission__uuid",
                 right_on="_uuid", how="outer", indicator=True)
print(check["_merge"].value_counts())

both          3297
right_only      27
left_only        0

Read it as a sentence: 3,297 people matched a household; 27 households have no people listed; no person belongs to a household that is not in the file.

The 27 are the refusals and they are correct. left_only being zero is the important one — a person with no household would mean the roster and the household file came from different exports, and nothing downstream could be trusted.

Then aggregate back

Once the join is right, the useful direction is usually roster up to household:

per_household = (
    ro.assign(age=pd.to_numeric(ro["age"], errors="coerce"))
      .groupby("_submission__uuid")
      .agg(people=("person_name", "size"),
           children=("age", lambda s: ((s >= 4) & (s <= 18)).sum()),
           mean_age=("age", "mean"))
      .reset_index()
)

hh_clean = hh.drop_duplicates("_uuid").merge(
    per_household, left_on="_uuid", right_on="_submission__uuid", how="left"
)
print(len(hh_clean))          # 600 - one row per household, as it should be

Aggregate first, then merge. Merging first and aggregating after is how you get means weighted by household size without meaning to.

Where it breaks

`how="left"` is not a guarantee

It guarantees you keep every left row. It does not guarantee you keep only them. Row count is the check, not the join type.

Merging on a key with different whitespace

"abc " does not match "abc". Strip both sides before merging, every time:

ro["_submission__uuid"] = ro["_submission__uuid"].str.strip()

Suffixes you did not notice

When both tables have a column of the same name, pandas appends _x and _y. If you see those in your output you have merged two columns you meant to reconcile. Name them explicitly:

ro.merge(hh, ..., suffixes=("_person", "_household"))

Silent type mismatch

An ID read as a number in one file and text in the other matches nothing, and you get an empty result rather than an error. Read IDs as str in both.

Your turn

Build the per-household table above, then answer: what is the mean household size, and how does it compare with the household_size the respondent stated?

If they agree, you have a cross-check you can run every round. If they do not, you have found something — and the next lesson is about turning that into a routine.

Validating a merge — the checks that catch a silent duplication before anyone else sees it.