cassionData Analysis

Back to the lessonLesson 7 of 8Reshaping and repeating

Undoing a flattened repeat group

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 · PDFSlides · PowerPoint

  1. Slide 1 / 22

    What this lesson covers

    • What a form platform does to a repeat group
    • pivot_longer() with .value
    • Check the row count against what you expect
    • pivot_wider() and the warning that means something
    • Separating and uniting columns
    • Nesting, briefly
    • Where this fits
    • What comes next
    Speaker notes
    pivot_longer() with .value, the one call that turns a CommCare or Kobo export back into one row per person — plus the pivot_wider() warning that means your key is not a key.

  2. Slide 2 / 22

    What a form platform does to a repeat group — Example

    hh    member_1_age  member_1_sex  member_2_age  member_2_sex
H1              34  f                        8  m
    Speaker notes
    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: 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.

  3. Slide 3 / 22

    pivot_longer() with .value — In R

    library(tidyr)

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

  4. Slide 4 / 22

    pivot_longer() with .value — Example

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

  5. Slide 5 / 22

    pivot_longer() with .value

    • The pattern
    Speaker notes
    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. 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:

  6. Slide 6 / 22

    pivot_longer() with .value — In R

    # 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)
    Speaker notes
    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.

  7. Slide 7 / 22

    Check the row count against what you expect — In R

    stopifnot(nrow(long) == sum(!is.na(wide$member_1_age)) + sum(!is.na(wide$member_2_age)))
    Speaker notes
    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.

  8. Slide 8 / 22

    Check the row count against what you expect — In R

    long |>
  count(hh, name = "members_found") |>
  left_join(households |> select(hh, hh_size), by = "hh") |>
  filter(members_found != hh_size)
    Speaker notes
    More practically, count people per household and compare against the household size the form recorded: 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.

  9. Slide 9 / 22

    pivot_wider() and the warning that means something — In R

    by_commune_month |>
  pivot_wider(names_from = month, values_from = cases, values_fill = 0)
    Speaker notes
    The reverse operation builds the wide table a report wants:

  10. Slide 10 / 22

    pivot_wider() and the warning that means something — Example

    Warning: Values from `v` are not uniquely identified; output will contain
list-cols.
    Speaker notes
    If more than one row shares the same key, tidyr does not error:

  11. Slide 11 / 22

    pivot_wider() and the warning that means something

    • That warning means your key is not a key — The cell now holds a list of the several values that collided, and every…
    Speaker notes
    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.

  12. Slide 12 / 22

    pivot_wider() and the warning that means something — In R

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

  13. Slide 13 / 22

    pivot_wider() and the warning that means something

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

  14. Slide 14 / 22

    pivot_wider() and the warning that means something — In R

    pivot_wider(..., values_fill = 0)
    Speaker notes
    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.

  15. Slide 15 / 22

    Separating and uniting columns — In R

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

  16. Slide 16 / 22

    Separating and uniting columns — Example

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

  17. Slide 17 / 22

    Separating and uniting columns — In R

    separate_wider_delim(x, "-", names = c("year", "month"), too_few = "align_start")
    Speaker notes
    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:

  18. Slide 18 / 22

    Separating and uniting columns — In R

    unite(df, "key", district, community, sep = "-", remove = FALSE)
    Speaker notes
    The union is unite(), useful for building a composite key before a join: remove = FALSE keeps the source columns, which you almost always still want.

  19. Slide 19 / 22

    Nesting, briefly — In R

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

by_commune$data[[1]]      # the full tibble for the first commune
    Speaker notes
    A repeat group can also stay nested rather than being flattened: 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.

  20. Slide 20 / 22

    Where this fits

    • The Python course covers the same reshaping under melt and pivot.
    Speaker notes
    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.

  21. Slide 21 / 22

    What comes next

    • The data has the right shape.
    Speaker notes
    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.

  22. Slide 22 / 22

    Where this goes next

    Read the full lesson, with runnable code Back to the lesson