Getting to a tidy table

The rule

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.

What untidy looks like in this sector

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:

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

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:

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

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.

Long and wide

Reshaping between the two is the single most useful mechanical skill in this course.

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")
    .reset_index()
    .dropna(subset=["id"])
)

print(children.head())

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

children

Two details carry the weight:

• Match the slot number with a pattern, never by listing columns. The list changes between exports; the pattern does not.
• Drop the empty slots. A form that screened one child still produces columns for slots two and three, filled with nothing. Keeping them inflates your caseload with rows that are not children.

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.

Joining a roster to its parent

KoboToolbox splits repeats into their own sheet, joined on _parent_index.

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

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)

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.

Prove the join did what you said

Three checks, every time. They take a minute and they have caught more errors than any technique in this course.

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"

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

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.

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.

The register is already tidy

The MUAC screening register this course uses is one row per child, which is why it can be worked directly:

muac.head()

head(muac)

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.

What comes next

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.