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

Long, wide, and the repeat group in between

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 / 24
What this lesson covers
- Two shapes, and what each is for
- The flattened repeat group
- The empty slots, and the ones that mean something
- Long to wide: the DHIS2 pivot
- The fill value that invents data
- The wide table you cannot filter
- Round-trip, and check it
- What comes next
Speaker notes
Pivot because the analysis needs the shape, not because the export arrived in it. The flattened repeat group, the DHIS2 pivot table, and the fill value that turns "not reported" into zero.
Slide 2 / 24
Two shapes, and what each is for
- Long — one row per observation, one column per variable
- Wide — one row per unit, one column per measurement
Speaker notes
The same data, twice: Long — one row per observation, one column per variable. 2,736 rows of facility, period, antigen, doses. Wide — one row per unit, one column per measurement. 456 rows of facility and period, with six antigen columns. Neither is correct in general. The rule that actually works:
Slide 3 / 24
Two shapes, and what each is for
Long is for computing. Wide is for reading.
Speaker notes
Group, filter, join and summarise on long data, because every one of those verbs takes a column name and long data has one column per concept. Pivot to wide as the last step before a human looks at it, because a person reading a table wants the six antigens side by side. Most trouble in this lesson comes from doing the reverse — receiving a wide export and analysing it wide, which turns "compute the coverage for every antigen" from one line into six.
Slide 4 / 24
The flattened repeat group — Example
```
household_id, member_1_age, member_1_sex, member_2_age, member_2_sex, member_3_age, ...
```
Speaker notes
A form platform asks a repeating question — every child in the household, every symptom observed, every water source used — and exports it as numbered columns:
Slide 5 / 24
The flattened repeat group — In Python
```
long = members_wide.melt(
    id_vars="household_id",
    var_name="field",
    value_name="value",
)
long[["slot", "attribute"]] = long["field"].str.extract(r"member_(\d+)_(\w+)")

members = (
    long.dropna(subset=["slot"])
    .pivot(index=["household_id", "slot"], columns="attribute", values="value")
    .reset_index()
)
```
Speaker notes
This is one of the most common shapes in this sector and it is unusable as it stands. You cannot count members, cannot filter to children under five, and cannot join to anything, because the thing you want to work with is spread across columns instead of down rows.

Slide 6 / 24

The flattened repeat group — In R

members <- members_wide |>
  tidyr::pivot_longer(
    cols = tidyr::starts_with("member_"),
    names_to = c("slot", "attribute"),
    names_pattern = "member_(\\d+)_(\\w+)",
    values_to = "value"
  ) |>
  tidyr::pivot_wider(names_from = attribute, values_from = value)

Slide 7 / 24
The flattened repeat group
- Two pivots, not one — The first turns every numbered column into rows; the second turns the attribute names back into…
Speaker notes
Two pivots, not one. The first turns every numbered column into rows; the second turns the attribute names back into columns. Trying to do it in a single step is where people get stuck, because the column name carries two facts — which member, and which attribute — and they have to be separated before either can be used. names_pattern in R and the regex extract in Python are doing that separation. Get the pattern right against the real column names before you write anything else; a silent NA here becomes a member who disappears.
Slide 8 / 24
The empty slots, and the ones that mean something — In Python
```
print(members["age"].isna().sum(), "empty slots")

members = members[members["age"].notna() | members["sex"].notna()]
```
Speaker notes
A form with room for eight members and a household of three exports five empty slots. Most of them are nothing. One of them is not.

Slide 9 / 24

The empty slots, and the ones that mean something — In R

members |> summarise(empty = sum(is.na(age) & is.na(sex)))

members <- members |> filter(!is.na(age) | !is.na(sex))

Slide 10 / 24
The empty slots, and the ones that mean something — In Python
```
counted = members.groupby("household_id").size().rename("members_found")
check = households.join(counted, on="household_id")
mismatch = check[check["members_found"] != check["household_size"]]
print(len(mismatch), "households where the roster does not match household_size")
```
Speaker notes
The distinction: a slot where every attribute is missing is padding and should go. A slot where the age is missing but the sex is recorded is a real member with a missing age, and dropping it removes a person from the household. Filter on "the whole row is empty", never on one column. Check the result against something the file already knows:
Slide 11 / 24
The empty slots, and the ones that mean something — In R
```
check <- households |>
  left_join(count(members, household_id, name = "members_found"), by = "household_id") |>
  filter(members_found != household_size)
```
Speaker notes
The reported household size and the number of member rows should agree. Where they do not, the unflattening dropped someone or the interview did.
Slide 12 / 24
Long to wide: the DHIS2 pivot — In Python
```
wide = vax.pivot_table(
    index=["facility_id", "period"],
    columns="antigen",
    values="doses_administered",
    aggfunc="sum",
).reset_index()

print(len(vax), "->", len(wide))
```
Speaker notes
The vaccination extract arrives long — one row per facility, period and antigen — and that is the right shape to compute on. To put it in front of a district health officer, pivot:

Slide 13 / 24

Long to wide: the DHIS2 pivot — In R

wide <- vax |>
  tidyr::pivot_wider(
    id_cols = c(facility_id, period),
    names_from = antigen,
    values_from = doses_administered
  )

cat(nrow(vax), "->", nrow(wide), "\n")

Slide 14 / 24
Long to wide: the DHIS2 pivot
- pandas pivot raises on duplicates; pivot_table aggregates them — Reach for pivot first, precisely because you…
Speaker notes
2,736 rows become 456 — 38 facilities across 12 months — with six antigen columns. The arithmetic is worth checking every time: 2,736 divided by 6 antigens is 456, so nothing was aggregated away. When the two do not divide cleanly, the long table had duplicates on the pivot key, and pivot_table will have silently summed them. pandas pivot raises on duplicates; pivot_table aggregates them. Reach for pivot first, precisely because you want the error. tidyr's pivot_wider warns and produces list-columns, which is uglier and equally informative.

Slide 15 / 24

The fill value that invents data — In Python

wide = vax.pivot_table(
    index=["facility_id", "period"], columns="antigen",
    values="doses_administered", aggfunc="sum", fill_value=0,
)

Slide 16 / 24

The fill value that invents data — In R

wide <- vax |>
  tidyr::pivot_wider(names_from = antigen, values_from = doses_administered,
                     values_fill = 0)

Slide 17 / 24
The fill value that invents data — In Python
```
wide = wide.assign(reported=wide.notna().sum(axis=1))
```
Speaker notes
fill_value=0 fills combinations that did not exist in the long data. For doses administered that is often right — a facility with no penta3 row genuinely gave no penta3 doses. For a rate it is always wrong. A missing coverage cell means "not computed", and filling it with zero publishes a facility at 0% coverage that simply did not report. The cleaning course made this point about reporting; here it arrives through a different door, as a pivot argument nobody thinks of as a decision. The safe default is to leave it missing and handle it explicitly:

Slide 18 / 24

The fill value that invents data — In R

wide <- wide |> mutate(reported = rowSums(!is.na(across(bcg:penta3))))

Slide 19 / 24
The wide table you cannot filter — In Python
```
low = vax[vax["doses_administered"] < 10]
```
Speaker notes
Once wide, a question like "which facility-months had fewer than ten doses of any antigen" needs six comparisons joined by or, and adding a seventh antigen means editing every one of them. The same question on long data is one line:
Slide 20 / 24
The wide table you cannot filter — In R
```
low <- vax |> filter(doses_administered < 10)
```
Speaker notes
That is the test for whether you pivoted too early. If your next operation names more than one column that holds the same kind of value, go back to long.

Slide 21 / 24

Round-trip, and check it — In Python

back = wide.melt(id_vars=["facility_id", "period"],
                 var_name="antigen", value_name="doses_administered").dropna()

assert len(back) == len(vax)
assert back["doses_administered"].sum() == vax["doses_administered"].sum()

Speaker notes

Any reshape worth trusting is reversible. Assert it once while you are writing the code:

Slide 22 / 24

Round-trip, and check it — In R

back <- wide |>
  tidyr::pivot_longer(-c(facility_id, period),
                      names_to = "antigen", values_to = "doses_administered") |>
  filter(!is.na(doses_administered))

stopifnot(nrow(back) == nrow(vax),
          sum(back$doses_administered) == sum(vax$doses_administered))

Speaker notes

Row count and total. Two lines, and they catch a silently dropped antigen, a duplicate that got summed, and a fill value that invented rows.

Slide 23 / 24
What comes next
- You can now put a table into whatever shape the question needs.
Speaker notes
You can now put a table into whatever shape the question needs. The next unit is about the tables you have to bring in from outside — the population frame that supplies a coverage denominator, and the calendar that says which periods should have existed at all.
Slide 24 / 24
Where this goes next
Read the full lesson, with runnable code Back to the lesson

What this lesson covers

Two shapes, and what each is for

Two shapes, and what each is for

The flattened repeat group — Example

The flattened repeat group — In Python

The flattened repeat group — In R

The flattened repeat group

The empty slots, and the ones that mean something — In Python

The empty slots, and the ones that mean something — In R

The empty slots, and the ones that mean something — In Python

The empty slots, and the ones that mean something — In R

Long to wide: the DHIS2 pivot — In Python

Long to wide: the DHIS2 pivot — In R

Long to wide: the DHIS2 pivot

The fill value that invents data — In Python

The fill value that invents data — In R

The fill value that invents data — In Python

The fill value that invents data — In R

The wide table you cannot filter — In Python

The wide table you cannot filter — In R

Round-trip, and check it — In Python

Round-trip, and check it — In R

What comes next

Where this goes next