Course · Intermediate · Data Preparation
Joining and Reshaping Programme Data
Household and member rosters, repeat groups, admissions and discharges, and monthly aggregates that have to line up with a population denominator.
What you will be able to do
- Choose a join type from what you want to be true of the result, and prove afterwards that it was
- State the grain of every table you touch, and aggregate to the grain before joining rather than after
- Move a table between long and wide deliberately, and undo the flattened repeat group a form platform exports
- Attach a population denominator from an administrative frame, and defend the denominator you chose
- Build a complete period grid so a month that was never reported is visible rather than absent
- Reconcile a line-level register against the aggregate that was reported upward, and publish the gap
Standards and methodologies
Almost no programme question is answerable from one file. The children are in the screening register and the population is in the census projection. The households are in one export and their members in another. The daily register is in the tablet and the monthly figure is in DHIS2, and the two disagree.
This course is about putting those together. It is shorter than the cleaning course and harder, because the failure mode is different: a cleaning defect makes a value wrong, and you can usually see it. A join defect makes a row count wrong, and there is nothing to see — no error, no warning, no implausible value. A left join that adds 120 rows and a many-to-many that turns 70,245 rows into 3.5 million both produce a table that looks exactly like a table.
So the discipline here is stated up front and repeated in every lesson: say what the join should do before you run it, and prove afterwards that it did. Both languages will check the relationship for you if you ask, and asking costs one argument.
Every technique is shown in Python and in R, against platform datasets where the problem is genuine — a student roster that is not unique on its own identifier, a DHIS2-shaped extract whose complete twelve-month grid hides which facilities were silent, and a WASH survey where the analysis is per person and the data is per household.
You should have done Data Cleaning and Validation, or at least be in the habit of asserting a key before you use it. That course ends where this one starts.
Practice
Reading the lessons is not the same as having done the work. Each of these applies the course to a dataset it did not teach on.
- Lab · 120 minThe join that decides a prevalenceJoin 930 SMART survey children to the WHO 2006 reference table on a three-part key where one part is a rounded float and another is derived from a third column. Four children fail to match, and those four are the finding.
- Exercise · 45 minThe three schools at the bottomA ranking sends a support team to the three worst-attending schools. All three are there because of a join decision, and the school that genuinely has the worst attendance is fourth.
Progress
Take it offline
The whole course as a typeset PDF — every lesson, every code example, the data dictionary and the indicator definitions. Generated from the same source as this page.
The LaTeX source ships alongside each PDF, so an organisation can rebrand the handout or fold a lesson into its own training pack.