Build the dataset

Before we begin

ARRIVAL PROMPT

If two analysts download “the same” public indicator one year apart, should their files be identical?

Today

1. Reconstruct the data pipeline.
2. Use dplyr verbs with a purpose.
3. Test keys, joins, types, ranges, and missingness.
4. Document provenance.
5. Write and review an agent update contract.

By 5 PM

• Explain raw, derived, analysis, and output layers.
• Build a small analysis table with readable R.
• Test the iso3c + year key.
• Diagnose a join that changes the row count.
• Compare a frozen file with a later release.
• Record source, indicator code, unit, years, and retrieval date.

Yesterday’s evidence inventory

Your inventory named:

• the unit,
• the variables,
• the years,
• the missingness,
• one limitation.

Today we ask a harder question: How did those columns get into this file?

A dataset is a chain of decisions

Chain of custody

Imagine the raw download as evidence arriving in a sealed bag.

• Raw: preserve exactly.
• Script: record each examination.
• Derived file: show the result of a reproducible step.
• Key and range checks: confirm nothing silently multiplied, disappeared, or changed meaning.
• Analysis table: use only after the path can be reconstructed.

Reconstruct the pipeline

Frozen does not mean permanent truth

The teaching file ends in 2022 so everyone begins from one reference point.

A later World Bank release may:

• add new years,
• revise older values,
• improve country coverage,
• change metadata,
• leave different indicators ending in different years.

Wide and long

Tidy data

• Each variable is a column.
• Each observation is a row.
• Each type of observational unit is a table.

Tidy is not the same as complete, correct, or analysis-ready.

Types carry meaning

Inspect before transforming

wdi |>
  summarise(
    rows = n(),
    economies = n_distinct(iso3c),
    first_year = min(year),
    last_year = max(year)
  )

wdi |>
  count(income_level, sort = TRUE)

Six verbs, six questions

Choose the verb

Build the health table

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

Every line should correspond to a sentence you can defend.

Predict the row count

The source file has 4,991 rows.

The key test

stopifnot(
  !anyDuplicated(health[c("iso3c", "year")])
)

This assertion says: no economy appears more than once in the same year.

If it fails, stop before joining or summarizing.

Why duplicate keys are dangerous

What happens when we join by iso3c?

Join contract

Before a join, state:

1. The key on the left.
2. The key on the right.
3. The expected relationship: one-to-one, many-to-one, or one-to-many.
4. The expected row count after the join.
5. How unmatched rows will be inspected.

Diagnose before continuing

duplicates <- country_metadata |>
  count(iso3c) |>
  filter(n > 1)

unmatched <- health |>
  anti_join(country_metadata, by = "iso3c")

anti_join() is a question: which rows have no match?

Missingness is part of the data

health |>
  group_by(year) |>
  summarise(
    observed = sum(!is.na(under5_mortality)),
    missing = sum(is.na(under5_mortality)),
    .groups = "drop"
  )

Coverage can change across time and indicators.

Silent row loss

fit <- lm(
  under5_mortality ~ log_income,
  data = health
)

The model uses only rows observed on both variables.

A named analysis sample

health <- health |>
  mutate(
    analysis_sample =
      !is.na(under5_mortality) &
      !is.na(log_income)
  )

count(health, analysis_sample)

Named sample rules are easier to inspect than hidden filters.

Five-minute build

Provenance answers five questions

1. Who published the data?
2. What exact indicator code and definition were used?
3. When was the file retrieved?
4. How was it transformed?
5. Which version produced the analysis?

Indicator codes are evidence

A friendly label can hide a different unit or denominator.

Write a provenance sentence

The agent as data engineer

An agent can help:

• read API documentation,
• draft a download function,
• reshape JSON into a table,
• add tests,
• compare file versions,
• document retrieval steps.

It must not silently overwrite the frozen baseline.

Give agency one rung at a time

Weak update prompt

Update the dataset to 2025.

Problems:

• Not every indicator may reach 2025.
• The official source is unspecified.
• Historical revisions are not addressed.
• The output location is unclear.
• No checks define success.

Update contract

Goal: Extend one named WDI indicator beyond 2022.
Context: Read the build script and indicator dictionary.
Constraints: Use the official API. Do not overwrite the frozen files.
Preserve the indicator code and unit. Record the retrieval date.
Done when: A new dated file exists, country-year keys are unique,
and a comparison reports new rows, latest year, missingness,
and changed historical values.

Review before run

When the agent proposes code, inspect:

• the API base URL,
• indicator code,
• requested years,
• retry and error behavior,
• country metadata filters,
• output filename,
• uniqueness checks.

Do not confuse syntactic readability with source validity.

Compare versions

Human-agent-evidence pass

Keep a workshop receipt

For each agent round, record:

1. Context: what the agent actually read.
2. Permission: read, plan, edit, run, or network.
3. Action: exact file change, request, or command.
4. Observation: diff, status, error, row count, or path.
5. Decision: continue, revise, reject, or stop.

Lab 2 tomorrow

THU AUG 20 · 4-5 PM

You will supervise an update for one indicator and leave with:

• a dated output or documented failure,
• a version comparison,
• a provenance note,
• one check that caught a problem,
• one unresolved question.

Exit ticket