cassionData Analysis

Back to the lessonLesson 4 of 8Getting the data in

Getting to a tidy table

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 / 21

    What this lesson covers

    • The rule
    • What untidy looks like in this sector
    • Long and wide
    • Joining a roster to its parent
    • Prove the join did what you said
    • The register is already tidy
    • What comes next
    Speaker notes
    Reshape flattened repeat groups into one row per observation, join a member roster to its household, and prove the join did not silently change your caseload.

  2. Slide 2 / 21

    The rule

    • One row per observation, one column per variable, one table per kind of thing.
    Speaker notes
    One row per observation, one column per variable, one table per kind of thing. The rule is old and it still does most of the work. It is worth stating why, because "tidy" sounds like tidiness rather than engineering. In a tidy table, every operation you want — filter, group, join, plot — is the same operation regardless of which variable you are working on. In an untidy one, each variable needs bespoke code, and the bespoke code is where the errors live.

  3. Slide 3 / 21

    What untidy looks like in this sector

    • Flattened repeat groups — CommCare exports one row per form submission, so a household visit that screened three…
    Speaker notes
    Three shapes account for nearly everything you will meet. Flattened repeat groups. CommCare exports one row per form submission, so a household visit that screened three children becomes:

  4. Slide 4 / 21

    What untidy looks like in this sector — Example

    form_id,commune,child_1_id,child_1_muac,child_2_id,child_2_muac,child_3_id,child_3_muac
F0012,Gonaives,CH00854,133,CH00855,118,,
F0013,Verrettes,CH01439,143,,,,

  5. Slide 5 / 21

    What untidy looks like in this sector

    • Values in column headers — A DHIS2 pivot puts periods across the top:
    Speaker notes
    One row is a form. You want one row per child. The column count also changes between exports, which means any code that names the columns explicitly breaks next month. Values in column headers. A DHIS2 pivot puts periods across the top:

  6. Slide 6 / 21

    What untidy looks like in this sector — Example

    org_unit,Jan-2024,Feb-2024,Mar-2024
Gonaives,412,388,401

  7. Slide 7 / 21

    What untidy looks like in this sector

    • Multiple things in one table — A survey export with household characteristics repeated on every member row
    Speaker notes
    The month is data — it is a value of a variable called period — but it is living in a header. Multiple things in one table. A survey export with household characteristics repeated on every member row. Nothing is wrong until someone computes a mean household size and gets a number weighted by household size.

  8. Slide 8 / 21

    Long and wide — In Python (cont.)

    import pandas as pd

wide = pd.read_csv("data/raw/commcare-screening-export.csv")

long = wide.melt(
    id_vars=["form_id", "commune"],
    var_name="field",
    value_name="value",
)

# child_1_muac -> child 1, field muac
parts = long["field"].str.extract(r"^child_(?P<slot>\d+)_(?P<attribute>.+)$")
long = pd.concat([long, parts], axis=1).dropna(subset=["slot"])

children = (
    long.pivot(index=["form_id", "commune", "slot"], columns="attribute", values="value")
    Speaker notes
    Reshaping between the two is the single most useful mechanical skill in this course.

  9. Slide 9 / 21

    Long and wide — In Python (cont.)

        .reset_index()
    .dropna(subset=["id"])
)

print(children.head())

  10. Slide 10 / 21

    Long and wide — In R (cont.)

    library(dplyr)
library(tidyr)
library(readr)

wide <- read_csv("data/raw/commcare-screening-export.csv")

children <- wide |>
  pivot_longer(
    cols = starts_with("child_"),
    names_to = c("slot", "attribute"),
    names_pattern = "child_(\\d+)_(.+)",
    values_to = "value",
    values_transform = as.character
  ) |>
  pivot_wider(names_from = attribute, values_from = value) |>
  filter(!is.na(id))

  11. Slide 11 / 21

    Long and wide — In R (cont.)

    
children

  12. Slide 12 / 21

    Long and wide

    • Match the slot number with a pattern, never by listing columns. The list changes between exports; the pattern does…
    • Drop the empty slots. A form that screened one child still produces columns for slots two and three, filled with…
    Speaker notes
    Two details carry the weight: Check the arithmetic afterwards. If the export had 1,600 forms and the widest had three children, the melt produces 4,800 candidate rows, of which only the real ones survive the drop. That surviving count is your caseload, and it should match what the programme thinks it screened.

  13. Slide 13 / 21

    Joining a roster to its parent — In Python

    households = pd.read_excel("data/raw/kobo-export.xlsx", sheet_name="household")
members = pd.read_excel("data/raw/kobo-export.xlsx", sheet_name="member")

merged = members.merge(
    households[["_index", "commune", "interview_date"]],
    left_on="_parent_index",
    right_on="_index",
    how="left",
    validate="many_to_one",
    indicator=True,
)

print(merged["_merge"].value_counts())
    Speaker notes
    KoboToolbox splits repeats into their own sheet, joined on _parent_index.

  14. Slide 14 / 21

    Joining a roster to its parent — In R

    library(readxl)

households <- read_excel("data/raw/kobo-export.xlsx", sheet = "household")
members     <- read_excel("data/raw/kobo-export.xlsx", sheet = "member")

merged <- members |>
  left_join(
    households |> select(`_index`, commune, interview_date),
    by = join_by(`_parent_index` == `_index`),
    relationship = "many-to-one",
    unmatched = "error"
  )

nrow(merged) == nrow(members)
    Speaker notes
    validate="many_to_one" in pandas and relationship = "many-to-one" in dplyr are the important arguments, and almost nobody uses them. They raise if the relationship you asserted is not the one in the data — which is exactly the failure that otherwise multiplies your row count and is discovered three steps later as a caseload that is 14% too high.

  15. Slide 15 / 21

    Prove the join did what you said — In Python

    before = len(members)
after = len(merged)

assert after == before, f"join changed the row count: {before} -> {after}"

unmatched = (merged["_merge"] == "left_only").sum()
assert unmatched == 0, f"{unmatched} members have no household"

assert merged["child_id"].is_unique, "duplicate identifiers after join"
    Speaker notes
    Three checks, every time. They take a minute and they have caught more errors than any technique in this course.

  16. Slide 16 / 21

    Prove the join did what you said — In R

    stopifnot(nrow(merged) == nrow(members))
stopifnot(!any(is.na(merged$commune)))
stopifnot(!any(duplicated(merged$child_id)))

  17. Slide 17 / 21

    Prove the join did what you said

    A join that changes your row count is not a data problem you fix downstream. It is a signal that you misunderstood one of the two tables, and the fix is upstream of the join.
    Speaker notes
    The row count check is the one to internalise. A left join can only leave the row count unchanged or increase it. If it increased, the right-hand table had duplicate keys, and every duplicated key has quietly multiplied its rows. In a caseload table, that is an overcount you will report to a donor.

  18. Slide 18 / 21

    The register is already tidy — In Python

    muac.head()
    Speaker notes
    The MUAC screening register this course uses is one row per child, which is why it can be worked directly:

  19. Slide 19 / 21

    The register is already tidy — In R

    head(muac)
    Speaker notes
    That is deliberate. Reshaping is a real skill and you will need it, but it is not where the interesting decisions are. The interesting decisions start in the next lesson, when you look at what is actually in those 4,218 rows.

  20. Slide 20 / 21

    What comes next

    • The table is the right shape.
    Speaker notes
    The table is the right shape. The next lesson finds out what is wrong with it — systematically, and with the missingness broken down by site and week, because missingness that clusters is a completely different problem from missingness that does not.

  21. Slide 21 / 21

    Where this goes next

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