Where programme data comes from

The three exports you will meet

Most M&E officers inherit data from one of three systems. Each has a house style, and knowing it saves an afternoon.

CommCare exports one row per form submission, with repeat groups flattened into numbered columns (child_1_muac, child_2_muac, and so on up to whatever the widest form in the dataset needed). The flattening is the first thing you undo. It also means the column count changes between exports: a month where one household had nine children produces nine sets of columns, and next month’s file has seven.

KoboToolbox exports close to the XLSForm structure, with repeats in separate sheets joined by _index and _parent_index. This is more honest than the CommCare shape but it means an analysis that ignores the second sheet silently analyses households instead of people.

DHIS2 exports aggregate data — already summed by period and org unit — so the question is rarely “how do I clean this” and usually “what exactly was counted”. You cannot recover an individual from a DHIS2 pivot, and you cannot recompute a denominator that was fixed upstream of you.

The unit of observation decides everything

Before anything else, answer one question: what is one row?

Export	One row is	The trap
CommCare form dump	One form submission	A form may cover several children
KoboToolbox main sheet	One interview	Members live in the repeat sheet
KoboToolbox repeat sheet	One member	No household context until you join
DHIS2 pivot	One org unit × period × data element	Individuals are gone for good

Getting this wrong produces an analysis that is internally consistent and completely wrong. A screening register where one row is a submission rather than a child will report a caseload that is too low by exactly the number of multi-child households, and nothing in the output will look odd.

Ask the question out loud before you open the file, and write the answer at the top of your script as a comment. Most of the disputes later in this course trace back to two people holding different answers to it.

What a real export looks like

This course works against a synthetic MUAC screening register from Artibonite, Haiti. One row is one child screened. It looks like this:

child_id,commune,screening_date,age_months,sex,muac_mm,oedema,outcome
CH00854,Terre-Neuve,2024-01-15,22,m,133,false,no-action
CH01439,Verrettes,2024-01-15,16,f,143,false,no-action

Eight columns, 4,218 rows, one year of community mass screening across twelve communes. It is small enough to read and messy enough to teach — it carries missing values coded as -99, measurements left in centimetres, duplicate registrations, and referral decisions that contradict the measurement recorded next to them.

None of that is decoration. Every defect in this file is one that appears in real campaign registers, put there deliberately so you meet it here rather than the week before a report is due.

Mid-upper arm circumference, in one paragraph

Mid-upper arm circumference (MUAC) is a screening measurement taken with a colour-coded tape around a child’s upper arm. It is used because it needs no scales, no height board and about forty seconds of training. For children from 6 to 59 months the WHO thresholds are:

• Below 115 mm — severe acute malnutrition (SAM)
• 115 mm to under 125 mm — moderate acute malnutrition (MAM)
• 125 mm and above — no acute malnutrition by this measure

Bilateral pitting oedema is SAM regardless of the measurement. That is why the oedema column exists and why an analysis that filters on muac_mm alone understates the caseload.

Global acute malnutrition (GAM) is SAM plus MAM. You will compute it in lesson 7, and the argument about its denominator is lesson 8.

The three questions to ask the person who sent you the file

Routine data arrives without a codebook far more often than with one. These three questions recover most of what a codebook would have told you.

1. What does a blank mean here? Not measured, measured as zero, refused, or the tablet crashed? These are four different things and they are frequently all stored as an empty cell.
2. Has anything already been cleaned or dropped? If someone removed the rows they thought were wrong, your row count is not the caseload and your completeness rate is a fiction.
3. What period does this cover, and by which date? Date of screening, date of form submission and date of sync can differ by weeks on a poor connection. A monthly report built on the wrong one moves cases between months.

You will not always get answers. Writing down that you asked, and what you assumed in the absence of an answer, is what separates an analysis that can be defended from one that cannot.

What comes next

The next lesson sets up a working environment in Python and in R — deliberately one that works offline, because much of this audience is on a connection that cannot be relied on to install a package at the moment it is needed.