Long and wide

The problem

The roster is long: one row per person, 3,297 rows for 573 households.

The report wants one row per household. Someone else sends you a file that is wide: person_1_age, person_2_age, out to person_11_age, about half of it empty.

You need to move between the two, and you need to know that most of the time the right answer is neither.

What you will have at the end

pivot and melt working in both directions, a round-trip you can verify, and a clear sense of when to aggregate instead.

The steps

Long to wide: pivot

import pandas as pd

ro = pd.read_csv("household-survey-roster.csv", dtype=str, keep_default_na=False)
ro["age"] = pd.to_numeric(ro["age"], errors="coerce")

wide = ro.pivot(index="_submission__uuid", columns="_index", values="age")
wide.columns = [f"person_{c}_age" for c in wide.columns]
print(wide.shape)
print(wide.columns.tolist()[:4])

(573, 11)
['person_1_age', 'person_10_age', 'person_11_age', 'person_2_age']

573 households, 11 columns because the largest household has 11 people — and look at the column order. person_10 comes before person_2, because _index was read as text and text sorts lexically. Fix the type before you pivot:

ro["_index"] = pd.to_numeric(ro["_index"])
wide = ro.pivot(index="_submission__uuid", columns="_index", values="age")

That ordering bug is silent in a frame and loud in a chart, where person 10 appears second along the axis.

How empty wide really is

print(f"{wide.isna().mean().mean():.0%} of the cells are empty")

48% of the cells are empty

Nearly half the table is blank, because most households have far fewer than eleven people — the person_11_age column is empty for 94% of them. Wide is a presentation format. It is a poor storage format and a worse analysis format.

Wide to long: melt

back = (
    wide.reset_index()
        .melt(id_vars="_submission__uuid", var_name="slot", value_name="age")
)
print("before dropping empties:", len(back))

back = back.dropna(subset=["age"])
print("after:", len(back))

before dropping empties: 6303
after: 3297

6,303 is 573 × 11 — every household given a slot for every possible person. Dropping the empties returns exactly 3,297, the roster we started with. That round-trip is the check: if melting back does not reproduce your original row count, the reshape lost something.

What you usually actually want

Neither. Most questions are about the household, not about person 7:

per_household = ro.groupby("_submission__uuid").agg(
    people=("person_name", "size"),
    children_4_18=("age", lambda s: ((s >= 4) & (s <= 18)).sum()),
    oldest=("age", "max"),
)
print(per_household.head(3))
print("mean children aged 4-18:", f"{per_household['children_4_18'].mean():.2f}")

                   people  children_4_18  oldest
0127e72f-fced           6              2      78
015d1b0e-d66a           4              1      54
01cc5a2d-fd17           8              2      47

mean children aged 4-18: 1.10

One row per household, no empty columns, and it joins straight onto the household table. Reach for pivot when a human needs to read the result; reach for groupby the rest of the time.

Where it breaks

Bad values arriving through an aggregate

print(per_household["oldest"].max())
print((per_household["oldest"] > 110).sum())

999
14

Fourteen households have an "oldest person" over 110, because the roster contains ages of 150, 999 and -2 that a tablet accepted without complaint. max() is the worst aggregate for this — it seeks out exactly the wrong value.

Clean the column before you aggregate it, not after:

ro.loc[(ro["age"] < 0) | (ro["age"] > 110), "age"] = pd.NA

A mean would have hidden this. An aggregate never makes data cleaner, and the extreme ones put the error in the answer.

`pivot` raising on duplicate keys

ValueError: Index contains duplicate entries, cannot reshape

Two rows share the same household and the same _index — usually a repeat group that was edited. pivot_table will silently average them instead, which is worse. Find the duplicates and decide:

print(ro[ro.duplicated(["_submission__uuid", "_index"], keep=False)])

Losing the people with no age

dropna(subset=["age"]) after melting removes real people whose age was never recorded, as well as the empty slots. If you need everyone, melt a column that is always present — person_name — and carry age alongside.

Assuming slot numbers mean something

person_1 is whoever the enumerator entered first. It is usually the head of household, and in this data it always is — but that is the form's convention, not a guarantee. Use relationship, which says so explicitly.

Your turn

Build a household-level table with: number of people, number of children aged 4–18, number of school-age children attending school, and the age of the head.

Then check it against household_size from the household file, the way the validation lesson does. If they agree for all 573, your reshape is sound.

That is Module 2 — the table is now the right shape. The full curriculum continues with analysis: descriptives that survive a review, survey weights, and disaggregation.