Back to the lessonLesson 3 of 8One row per what?

One row per what?

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDF Slides · PowerPoint

Slide 1 / 21
What this lesson covers
- Say the grain out loud
- The grain decides what the number means
- Expanding households to people
- Weighting is usually better than expanding
- Aggregate to the grain before you join
- The many-to-many nobody chose
- Episodes, not people
- Write the grain into the file name
- What comes next
Speaker notes
The grain of a table decides what every number computed from it means. Households against people, episodes against patients, and the many-to-many that turns 2,403 rows into 13,004 without anyone deciding it should.
Slide 2 / 21
Say the grain out loud — In Python
```
# 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")
```
Speaker notes
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.

Slide 3 / 21

Say the grain out loud — In R

# 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")

Speaker notes

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.

Slide 4 / 21

The grain decides what the number means — In Python

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%}")

Speaker notes

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:

Slide 5 / 21

The grain decides what the number means — In R

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)
  )

Slide 6 / 21
The grain decides what the number means
- Report the grain in the label — share_of_households_below_15l and share_of_people_below_15l cannot be confused;…
Speaker notes
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.

Slide 7 / 21

Expanding households to people — In Python

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")

Speaker notes

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.

Slide 8 / 21

Expanding households to people — In R

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

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

Slide 9 / 21
Expanding households to people
- It is a claim, not a transformation. Every person in a household is now assumed to have the household's water…
- Nothing about the individuals is real. person_index is a position, not a person. Do not disaggregate by it, and…
- The forty-six households with no recorded size vanish. State that, or the expanded population is quietly 46…
Speaker notes
2,403 rows become 13,004. Three things to hold about that operation.
Slide 10 / 21
Weighting is usually better than expanding — In Python
```
sized["weight"] = sized["household_size"]

weighted_share = (
    (sized["litres_per_person_day"] < 15) * sized["weight"]
).sum() / sized["weight"].sum()
```
Speaker notes
Expansion is memory-hungry and easy to misuse. Where you only need population-weighted statistics, weight instead:
Slide 11 / 21
Weighting is usually better than expanding — In R
```
sized |>
  summarise(share = weighted.mean(litres_per_person_day < 15, w = household_size))
```
Speaker notes
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.

Slide 12 / 21

Aggregate to the grain before you join — In Python

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")

Speaker notes

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.

Slide 13 / 21
Aggregate to the grain before you join — In R
```
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")
```
Speaker notes
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.

Slide 14 / 21

The many-to-many nobody chose — In Python

# 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")

Speaker notes

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

Slide 15 / 21
The many-to-many nobody chose — In R
```
bad <- households |> inner_join(members, by = "community")
```
Speaker notes
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.
Slide 16 / 21
Episodes, not people
- Caseload is a count of episodes. Two admissions of the same child are two admissions, and reporting them as one…
- 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…
Speaker notes
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:

Slide 17 / 21

Episodes, not people — In Python

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

Slide 18 / 21
Episodes, not people — In R
```
c(episodes = nrow(admissions),
  children = n_distinct(admissions$child_id),
  readmitted = sum(count(admissions, child_id)$n > 1))
```
Speaker notes
Publishing both numbers, with their labels, ends the argument before it starts.
Slide 19 / 21
Write the grain into the file name — Example
```
outputs/tables/attendance_by_student.csv
outputs/tables/attendance_by_school_month.csv
outputs/tables/coverage_by_facility_period_antigen.csv
```
Speaker notes
A last small habit that pays. When you save an intermediate table, put the grain in the name: 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.
Slide 20 / 21
What comes next
- Grain answers what a row is.
Speaker notes
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.
Slide 21 / 21
Where this goes next
Read the full lesson, with runnable code Back to the lesson

What this lesson covers

Say the grain out loud — In Python

Say the grain out loud — In R

The grain decides what the number means — In Python

The grain decides what the number means — In R

The grain decides what the number means

Expanding households to people — In Python

Expanding households to people — In R

Expanding households to people

Weighting is usually better than expanding — In Python

Weighting is usually better than expanding — In R

Aggregate to the grain before you join — In Python

Aggregate to the grain before you join — In R

The many-to-many nobody chose — In Python

The many-to-many nobody chose — In R

Episodes, not people

Episodes, not people — In Python

Episodes, not people — In R

Write the grain into the file name — Example

What comes next

Where this goes next