One row per what?

Say the grain out loud

The grain of a table is what one row is. One household interview. One child screened on one day. One facility-month-antigen. One admission episode.

It sounds like a formality. It is the single most useful sentence you can write above a table, because almost every join failure and almost every denominator argument is really two people holding different grains in their heads.

# grain: one household interview
wash = pd.read_csv("wash-household-survey-2024.v1.csv")

# grain: one student per school day
attendance = pd.read_csv("school-attendance-2024.v1.csv")

# grain: one facility x month x antigen
vax = pd.read_csv("vaccination-coverage-2024.v1.csv")

# grain: one household interview
wash <- read_csv("wash-household-survey-2024.v1.csv")

# grain: one student per school day
attendance <- read_csv("school-attendance-2024.v1.csv")

# grain: one facility x month x antigen
vax <- read_csv("vaccination-coverage-2024.v1.csv")

Three comments, and they will prevent more damage than any assertion in this course. A join is only safe when you can name the grain of both sides and the grain of the result.

The grain decides what the number means

The WASH survey is 2,403 households covering 13,004 people, a mean household of 5.52. Two perfectly correct sentences come out of that file:

sized = wash[wash["household_size"].notna() & wash["litres_per_person_day"].notna()]

by_household = (sized["litres_per_person_day"] < 15).mean()
by_person = (
    sized.loc[sized["litres_per_person_day"] < 15, "household_size"].sum()
    / sized["household_size"].sum()
)

print(f"households below 15 L/p/d: {by_household:.1%}")
print(f"people in those households: {by_person:.1%}")

sized <- wash |> filter(!is.na(household_size), !is.na(litres_per_person_day))

sized |>
  summarise(
    by_household = mean(litres_per_person_day < 15),
    by_person = sum(household_size[litres_per_person_day < 15]) / sum(household_size)
  )

13.3% of households, 13.5% of people. And on open defecation, 13.4% of households against 13.9% of people.

The gaps are small here, and that is worth saying plainly: on this file the choice barely moves the number. It moves what the number is. Sphere states its water quantity standard per person, so the person-level figure is the one that answers the standard; a household-level figure answers “how many households are affected”, which is what a distribution plan needs.

Where the gap is not small is where it matters most. Large households are systematically more likely to be short of water per person, so in a survey with more variation in household size the two figures separate — and then reporting the household figure against a per-person standard understates the problem.

Report the grain in the label. share_of_households_below_15l and share_of_people_below_15l cannot be confused; water_below_minimum can.

Expanding households to people

Sometimes you genuinely need one row per person — to join to an age-sex distribution, to feed a per-person weight, or to compute a population denominator from the survey itself.

people = wash.loc[wash["household_size"].notna()].copy()
people["household_size"] = people["household_size"].astype(int)
people = people.loc[people.index.repeat(people["household_size"])]
people["person_index"] = people.groupby("household_id").cumcount() + 1

print(len(wash), "households ->", len(people), "people")

people <- wash |>
  filter(!is.na(household_size)) |>
  tidyr::uncount(household_size, .remove = FALSE, .id = "person_index")

cat(nrow(wash), "households ->", nrow(people), "people\n")

2,403 rows become 13,004. Three things to hold about that operation.

• It is a claim, not a transformation. Every person in a household is now assumed to have the household’s water access, sanitation and hygiene. For water quantity that is reasonable; for “who fetches the water” it is false.
• Nothing about the individuals is real. person_index is a position, not a person. Do not disaggregate by it, and do not let it look like an identifier.
• The forty-six households with no recorded size vanish. State that, or the expanded population is quietly 46 households short of the survey it came from.

Weighting is usually better than expanding

Expansion is memory-hungry and easy to misuse. Where you only need population-weighted statistics, weight instead:

sized["weight"] = sized["household_size"]

weighted_share = (
    (sized["litres_per_person_day"] < 15) * sized["weight"]
).sum() / sized["weight"].sum()

sized |>
  summarise(share = weighted.mean(litres_per_person_day < 15, w = household_size))

Same answer, one table, and the weight is visible as a column — which means a reviewer can see what you weighted by. An expanded frame hides the weighting inside the row count.

Aggregate to the grain before you join

This is the operational rule the whole lesson exists for.

You have a daily attendance file and you want a per-student summary joined to the roster. The wrong order is to join first and aggregate second — it works, but it carries 70,245 rows through the join and makes the relationship many-to-one when it did not have to be.

per_student = (
    attendance.assign(present=attendance["present"].map({"true": True, "false": False}))
    .groupby("student_id")
    .agg(days_marked=("present", "size"),
         days_present=("present", "sum"))
    .reset_index()
)
per_student["attendance_rate"] = per_student["days_present"] / per_student["days_marked"]

# grain: one student. Now the join is one-to-one.
summary = roster.merge(per_student, on="student_id", how="left", validate="one_to_one")

per_student <- attendance |>
  summarise(
    days_marked  = sum(present %in% c(TRUE, FALSE)),
    days_present = sum(present %in% TRUE),
    .by = student_id
  ) |>
  mutate(attendance_rate = days_present / days_marked)

summary <- roster |>
  left_join(per_student, by = "student_id", relationship = "one-to-one")

Two gains, and the second is the real one. The join is now one_to_one, so the strongest possible check applies. And the aggregation is written where you can see its denominator: days_marked counts the days a mark exists, which is not the same as the days the school was open — the next lesson but one is entirely about that distinction.

The many-to-many nobody chose

A many-to-many is almost never intended. It arrives when the key is not the key you thought.

# household file: one row per household. member file: one row per person.
# Both carry `community`. Joining on it instead of household_id:
bad = households.merge(members, on="community", how="inner")

bad <- households |> inner_join(members, by = "community")

Every household in a community now matches every person in that community. Four hundred households of six people each, in one community, produce 400 × 2,400 rows — nine hundred and sixty thousand — from a file whose honest grain is 2,400 people.

The tell is always the same: a row count that is a product rather than a sum, and a total that is suspiciously round in orders of magnitude. validate="one_to_many" catches it before you have to notice.

Episodes, not people

One more grain worth naming, because it is where treatment programme figures go wrong. In a CMAM register a row is usually an admission episode, not a child. A child readmitted after relapse has two rows.

That means:

• Caseload is a count of episodes. Two admissions of the same child are two admissions, and reporting them as one understates the work done.
• Children reached is a count of distinct children, which is smaller.
• Cured rate has episodes in both numerator and denominator, and mixing an episode numerator with a child denominator produces a rate above 100% that someone will spend an afternoon explaining.

print("episodes:", len(admissions))
print("children:", admissions["child_id"].nunique())
print("readmitted:", (admissions.groupby("child_id").size() > 1).sum())

c(episodes = nrow(admissions),
  children = n_distinct(admissions$child_id),
  readmitted = sum(count(admissions, child_id)$n > 1))

Publishing both numbers, with their labels, ends the argument before it starts.

Write the grain into the file name

A last small habit that pays. When you save an intermediate table, put the grain in the name:

outputs/tables/attendance_by_student.csv
outputs/tables/attendance_by_school_month.csv
outputs/tables/coverage_by_facility_period_antigen.csv

Six months later, summary.csv tells you nothing and one of these tells you everything. It also makes a wrong join obvious at the point where someone reads two file names side by side.

What comes next

Grain answers what a row is. The next lesson answers what a column is — the flattened repeat group a form platform exports as symptom_1, symptom_2, symptom_3, and the pivot that turns it back into rows you can count.