Undoing a flattened repeat group

What a form platform does to a repeat group

A household questionnaire asks about each member. In CommCare, KoboToolbox or ODK the answers come back flattened — one row per household, with the repeat group spread across numbered columns:

hh    member_1_age  member_1_sex  member_2_age  member_2_sex
H1              34  f                        8  m

Nothing about that shape is analysable. You cannot compute a mean age, filter to children, or join to anything, because a person is not a row.

pivot_longer() with .value

library(tidyr)

long <- wide |>
  pivot_longer(
    starts_with("member_"),
    names_to = c("member", ".value"),
    names_pattern = "member_(\\d+)_(.*)"
  )

# A tibble: 2 × 4
  hh    member   age sex
  <chr> <chr>  <dbl> <chr>
1 H1    1         34 f
2 H1    2          8 m

One row per person, and age and sex are separate columns with their own types. That is the shape everything downstream expects.

The .value sentinel is the piece worth understanding. In names_to, an ordinary name — "member" — becomes a new column holding that part of the old column name. ".value" is different: it says this part of the name is the name of the output column. So member_1_age splits into member = "1" and a value belonging to a column called age.

Without .value you would get a single value column mixing numbers and text, and a name column you then have to split and pivot again.

The pattern

names_pattern is a regular expression with one capture group per entry in names_to, in order. "member_(\\d+)_(.*)" captures the number and then everything after the second underscore.

Two alternatives when the names are simpler:

# Fixed separator, no regex needed.
pivot_longer(wide, starts_with("member_"),
             names_to = c("member", ".value"), names_sep = "_(?=[a-z]+$)")

# One value column, name kept whole.
pivot_longer(wide, starts_with("crop_"),
             names_to = "slot", names_prefix = "crop_", values_to = "crop",
             values_drop_na = TRUE)

values_drop_na = TRUE on that last one matters: a household with three crop slots and two crops has an empty third, and a NA row per unused slot is noise that changes every count.

Check the row count against what you expect

A pivot that produces the wrong number of rows is the easiest error to make and the hardest to see, because the result looks correct.

stopifnot(nrow(long) == sum(!is.na(wide$member_1_age)) + sum(!is.na(wide$member_2_age)))

More practically, count people per household and compare against the household size the form recorded:

long |>
  count(hh, name = "members_found") |>
  left_join(households |> select(hh, hh_size), by = "hh") |>
  filter(members_found != hh_size)

An empty result is the check passing. A non-empty one is a list of households where the roster and the reported size disagree — which is a data-quality finding before it is a pivot problem.

pivot_wider() and the warning that means something

The reverse operation builds the wide table a report wants:

by_commune_month |>
  pivot_wider(names_from = month, values_from = cases, values_fill = 0)

If more than one row shares the same key, tidyr does not error:

Warning: Values from `v` are not uniquely identified; output will contain
list-cols.

That warning means your key is not a key. The cell now holds a list of the several values that collided, and every downstream operation on it either fails strangely or silently operates on a list.

Do not add values_fn = sum to make the warning go away without first finding out why there were duplicates. It is usually one of three things: a re-submitted form, a facility reporting twice in a month, or a grouping variable you forgot to include. Only the first is safe to sum.

by_commune_month |> count(commune, month) |> filter(n > 1)

That is the diagnostic, and it should come before the pivot rather than after the warning.

values_fill

pivot_wider(..., values_fill = 0)

Safe for a count, where an absent combination genuinely had zero. Wrong for a rate, where the absence means “not computed” rather than “zero percent” — a commune with no screening in March has an undefined GAM rate, not a GAM rate of 0%, and filling it with zero drags every average down.

Separating and uniting columns

tibble(x = c("2024-01", "2024-02")) |>
  separate_wider_delim(x, delim = "-", names = c("year", "month"))

# A tibble: 2 × 2
  year  month
  <chr> <chr>
1 2024  01
2 2024  02

separate_wider_delim() replaced the older separate(), and the improvement is that it is strict: a row with the wrong number of pieces is an error rather than a silent truncation. Where ragged input is expected, say so:

separate_wider_delim(x, "-", names = c("year", "month"), too_few = "align_start")

The union is unite(), useful for building a composite key before a join:

unite(df, "key", district, community, sep = "-", remove = FALSE)

remove = FALSE keeps the source columns, which you almost always still want.

Nesting, briefly

A repeat group can also stay nested rather than being flattened:

by_commune <- muac |>
  tidyr::nest(.by = commune)

by_commune$data[[1]]      # the full tibble for the first commune

That is the shape for running the same model or the same summary per commune without a loop. It is worth knowing it exists; for most programme reporting, .by in summarise() gets you there with less machinery.

Where this fits

The Python course covers the same reshaping under melt and pivot. The vocabulary differs, the failure modes do not: a repeat group that must become rows, a key that turns out not to be unique, and a fill value that is right for a count and wrong for a rate.

What comes next

The data has the right shape. The last lesson turns everything so far into a function another officer can run on next quarter’s export without editing anything but its arguments.