The join that decides a prevalence

A prevalence figure from a SMART survey rests on a join. Every child’s weight-for-height z-score comes from looking their length or height up in the WHO 2006 reference table, and the lookup key has three parts, one of which is a rounded decimal and another of which is derived from a different column entirely.

Get any part of that key wrong and children silently fail to match. They then drop out of the denominator, and the prevalence you publish is computed on the children the join happened to find.

The files

smart-nutrition-survey-2024.v1.csv — 930 children aged 6 to 59 months from a 30-cluster survey, measured by four teams. Raw weight and height; no z-scores are shipped, because computing them is the exercise.

public/datasets/reference/who-2006-weight-for-lenhei.csv — the WHO 2006 weight-for-length and weight-for-height LMS parameters, exported from the official anthro R package. 2,404 rows: sex, lorh (L for length, H for height), lenhei in 0.1 cm steps, and the three LMS parameters.

This is a reference table, not a dataset. It exists because R has the official WHO package and Python does not have a maintained equivalent that installs cleanly. Read the table; do not reimplement the LMS interpolation from a textbook.

Set up first

A project folder, both files read-only, an outputs/ directory, and a script that runs top to bottom from a clean interpreter.

The task

Produce global and severe acute malnutrition prevalence for this survey, with the number of children in the denominator stated, and a reconciliation table showing what happened to every one of the 930.

Six things stand in the way.

The key has three parts. sex, the standard (L or H), and lenhei rounded to one decimal. All three have to be built on both sides before you can join, and the standard is not a column in the survey — it is derived from measured_lying.

Length and height are not the same measurement. A child measured lying is about 0.7 cm longer than the same child standing. WHO’s convention is that children under 24 months are assessed against the length standard and children 24 months and over against the height standard, so a child measured in the position that does not match their age standard needs the 0.7 cm adjustment applied before the lookup. Get the sign right; getting it backwards biases every z-score for the youngest half of the sample.

A float is a bad join key. lenhei is stored to one decimal on both sides and you must round rather than trust equality — 86.3 computed from an adjustment is not necessarily the same float as 86.3 read from the reference. Round once, to one decimal, on both sides, in the same function.

Four children will not match. Do not drop them and do not investigate them one at a time. Anti-join, look at the four rows together, and you will see what they have in common immediately. They are recoverable, and the dataset’s known issues say how many such records exist in total.

The reference does not cover everything. The length standard runs 45.0 to 110.0 cm and the height standard 65.0 to 120.0 cm. A child outside their standard’s range has no reference value and is genuinely unclassifiable, which is different from a child whose measurement was mistyped.

Two flagging rules, two answers. WHO flags exclude z-scores outside -5 to +5. SMART flags exclude observations more than 3 SD from the survey mean. They remove different children. Report which you used.

What to hand in

A Python script, run from a clean interpreter, producing:

• a reconciliation table accounting for all 930 children — matched, missing a measurement, out of reference range, flagged, analysed — where the column sums to 930
• GAM and SAM prevalence with the analysed denominator stated beside each
• the same prevalence computed with WHO flags and with SMART flags, side by side
• prevalence by measurement team, with the number of children per team
• an assertion for every join in the script

Check your numbers

The dataset notes state what a correct analysis finds.

	Expected
Children analysed after range checks and flagging	about 874
Global acute malnutrition	about 14.9%
Severe acute malnutrition	about 3.9%
Team 3 GAM	about 22%
Other teams GAM	10% to 16%

Oedema overrides anthropometry: a child with bilateral pitting oedema is severely acutely malnourished whatever their weight-for-height, so the SAM numerator is not simply the count below -3 z-scores.

If your denominator is 915, you dropped the unmatched children silently. If GAM is nearer 12%, check the sign of the length-height adjustment.

The questions to answer in prose

Three sentences each.

1. Four children failed the join. Say what they have in common, how an anti-join found them in one line where a row-by-row search would not have, and what an inner join would have done to the published prevalence.

2. This survey sits just under the 15% emergency threshold. Name two join-level decisions in your script that could move it across, and say which direction each one moves it.

3. Team 3’s GAM is about 22% against 10 to 16% for the other teams. Explain why this is a measurement finding rather than a nutrition finding, and what you would put in the survey report about it.

How to know you are done

Delete outputs/, restart the interpreter, run once, and every table is regenerated identically. Your reconciliation table sums to exactly 930, and every join in the script either passes a validate= argument or is followed by a row count assertion.

What this lab is not

It is not nutrition survey analysis. What GAM means, why the IPC thresholds sit where they do, and how a SMART plausibility report is judged belong with the nutrition content. This lab is about the join: a composite key you have to construct, a derived key part, a float that must be rounded before it can be compared, and four rows whose failure to match is worth more than the 915 that succeeded.