LEARNING OUTCOMES
Make transformations traceable.
By the end of the lesson, you should be able to reshape and join data while preserving a clean path from the downloaded source to the analysis table.
- 01Use
select(),filter(),mutate(), andsummarise(). - 02Explain why
iso3c + yearmust be unique. - 03Separate raw, derived, and analysis-ready files.
- 04Join tables and diagnose unmatched rows.
- 05Review an agent-built update against the frozen 2022 file.
Why this matters for policy work
An analysis dataset is not merely a cleaner copy of a download. It is a record of decisions about population, time, definitions, missing observations, and comparability. A filter changes who is represented. A join can change how often an economy appears. A transformation can make a pattern easier to see while also changing the meaning of a coefficient. Those choices belong in the research argument.
Imagine the raw download as evidence arriving in a sealed bag. We do not rewrite it. Each derived file is a documented examination of that evidence; each script records what happened; each key check confirms that no observation was silently duplicated or lost. The analysis table is trustworthy because its path is inspectable, not because it looks clean.
Public indicators also change after publication. A later release may add a year, revise an earlier value, or alter coverage without announcing the change in the table itself. Preserving raw and frozen layers gives the analyst a stable point of comparison. It also gives a coding agent a safer contract: build forward, report differences, and never erase the evidence needed to understand what changed.
PROJECT STRUCTURE
A dataset is also a chain of decisions.
| Layer | Purpose | Rule |
|---|---|---|
| Raw | Exact download or API response. | Never edit by hand. |
| Derived | Cleaned and joined tables. | Rebuild with code. |
| Analysis | Variables for one policy question. | Keep definitions explicit. |
| Output | Figures, tables, and reports. | Never treat as source data. |
The key test. If a country appears twice in the same year, a later join can silently duplicate rows. Test the key before and after every join.
The agent receives a permission ladder
Do not begin by granting the broadest task. Increase agency only after the previous rung is understood: first ask the agent to read and explain the build script; then ask for a plan; then permit a new dated output; finally permit execution and comparison. At every rung, inspect the proposed source, target path, and completion check.
No edits; name inputs, outputs, and assumptions.
List files, source endpoints, risks, and checks.
Write a new dated file; never overwrite the baseline.
Report keys, coverage, new years, revisions, and missingness.
DECISION CHECKS
Every verb changes the evidence in a particular way.
| Verb | Question before running | Check after running |
|---|---|---|
filter() | Which observations will leave the sample? | Compare row counts, countries, and year coverage. |
select() | Which identifiers must remain? | Confirm the key and required metadata survived. |
mutate() | What does the new variable mean and in what units? | Inspect missingness and plausible ranges. |
summarise() | What is the new unit of observation? | Confirm the grouping and number of rows. |
left_join() | Is the right-side key unique? | Check duplicates and unmatched identifiers. |
Class interactions
You will predict row counts before transformations, diagnose a deliberately broken join, and compare two possible project structures. These are not speed tests. The purpose is to make invisible data decisions discussable.
Rows, columns, values, or unit.
Use a visible check, not intuition.
Connect the result to the verb.
Keep the decision in code or documentation.
LIVE CODING
Build one analysis table in public.
Keep the identifiers and variables needed for one question.
Filter years, name variables, and create a justified transformation.
Add country metadata and inspect unmatched keys.
Check uniqueness, ranges, missingness, and row counts.
Write the source, indicator, year, unit, and limitations.
health <- wdi |>
select(iso3c, country, year, income_level,
gdp_per_capita_ppp, under5_mortality) |>
filter(year >= 2000) |>
mutate(log_income = log(gdp_per_capita_ppp))
stopifnot(!anyDuplicated(health[c("iso3c", "year")]))select(...) keeps the country and year identifiers, the income classification, and the two indicators required for the health question. Naming the columns makes the analytical scope visible.
filter(year >= 2000) restricts the sample to the period used in the camp. Because this removes observations, we compare the row count and year range before and after the filter.
mutate(log_income = log(...)) creates a new variable while preserving the original income measure. The logarithm compresses the long upper tail of income; it also changes how a later coefficient must be interpreted.
stopifnot(!anyDuplicated(...)) turns the country-year key into an executable test. If a duplicate exists, the script stops at the point where the structure becomes unsafe rather than carrying the error into a join or model.
AGENT UPDATE PASS
Update forward, never overwrite backward.
Here the agent moves from explainer to supervised data engineer. The work order is still not “get the latest data.” It names the source, defines the protected baseline, specifies how historical revisions should be separated from new country-years, and requires a comparison receipt that can be checked without trusting the agent's prose.
Goal: Extend the World Bank indicator data beyond 2022 using the official API. Context: Read data/build_wdi.R, data/README.md, and the indicator dictionary. Constraints: Do not overwrite the frozen 2000-2022 files. Use the latest available year for each indicator, preserve indicator codes, and record the retrieval date. Stop if a key is duplicated. Done when: A new dated file exists, the build is reproducible, and a comparison reports row counts, year coverage, missingness, and changed historical values.
- □Did the agent use the official source and preserve the retrieval date?
- □Are historical revisions separated from newly added years?
- □Is each country-year still unique?
- □Can the entire file be rebuilt from the script?
SLIDES
Lesson deck.
Replace or extend slides/lesson-2/slides.qmd, then render it. The URL and this embedded slot remain unchanged.