Long, wide, and the repeat group in between

Two shapes, and what each is for

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:

Long is for computing. Wide is for reading.

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.

The flattened repeat group

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:

household_id, member_1_age, member_1_sex, member_2_age, member_2_sex, member_3_age, ...

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.

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

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)

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.

The empty slots, and the ones that mean something

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.

print(members["age"].isna().sum(), "empty slots")

members = members[members["age"].notna() | members["sex"].notna()]

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

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

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:

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

check <- households |>
  left_join(count(members, household_id, name = "members_found"), by = "household_id") |>
  filter(members_found != household_size)

The reported household size and the number of member rows should agree. Where they do not, the unflattening dropped someone or the interview did.

Long to wide: the DHIS2 pivot

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:

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

print(len(vax), "->", len(wide))

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

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

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.

The fill value that invents data

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

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

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:

wide = wide.assign(reported=wide.notna().sum(axis=1))

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

The wide table you cannot filter

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:

low = vax[vax["doses_administered"] < 10]

low <- vax |> filter(doses_administered < 10)

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.

Round-trip, and check it

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

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

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

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.

What comes next

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.