17  The Question, the Data and the Rules

Keywords

Quarto, reproducible publishing, coding agents, Markdown, Python, EPUB, data analysis, k-means clustering

In 2023, Colombia’s national report on drinking water scored the water of its urban areas at 5.7, in the low-risk band, and the water of its rural areas at 36.5, in the high-risk band (Table 17.1). DANE’s projections put 12.6 million people, 24% of the population, in rural zones in 2023. People drink and cook with this water, so where the risk sits, and what goes with it, is worth knowing.

The question for this part is therefore: which kinds of municipal zone exist in Colombia, judged by water safety, services and context, and how do health and education outcomes differ between them? Part II’s numbers were simple counts; these are not. Every step has a way to go wrong that a clean render will not reveal: a variable that means something narrower than its name, a gap that is not random, a comparison that looks causal and is not.

In this chapter
  • Explain what the IRCA scores, and why this part’s IRCA understates risk.
  • Identify the unit of analysis, and say what an empty cell means.
  • Distinguish features from outcomes, and explain why clustering on an outcome is circular.
  • Set up the water project’s data, rules and environment.

Start from~/quarto-workshop/, cloned in Chapter 4. The London guide is not needed.

You build~/water-quality/, with data/, AGENTS.md and a uv environment.

CheckpointNone here: the catch-up at the end of Chapter 18 covers both chapters.

17.1 The question and its readers

The question splits in two, and the order matters:

  1. Which kinds of municipal zone exist, judged by water safety, services and context?
  2. How do health and education outcomes differ between those kinds?

“Kinds” will be clusters: groups of zone-years, each one municipality’s urban or rural zone in one year (Section 17.4), that are similar to each other on the chosen variables, found by an algorithm called k-means (Chapter 20). Because the unit is a zone in a year, a municipality’s two zones can belong to different kinds, and a zone can move between kinds over the years.

The answer has two readers. Officials will read it as a report, in Word or as a PDF, and a municipal council will see it as a short deck of slides (Chapter 23). Both will look for where to act, and both will be tempted to read a difference between kinds as an effect of the water. So the limits are stated now, each with its reason. The water score covers only systems run by a utility (Section 17.2). Many rural zones have no score at all, so the results describe sampled systems (Section 18.2). And the comparisons describe; they cannot establish that water causes any outcome (Chapter 22).

17.2 Scoring water risk: the IRCA

Colombia scores the safety of drinking water with the IRCA, the Índice de Riesgo de la Calidad del Agua para consumo humano (water-quality risk index for human consumption), defined in Resolución 2115 of 2007. It runs from 0, every test passed, to 100, every test failed, and article 15 sorts the scale into five bands (Table 17.1).

Table 17.1: The IRCA risk bands (Resolución 2115 de 2007, art. 15) and where the 2023 national figures fall (MinSalud, MinVivienda, INS and Superservicios, INCA 2023, Bogotá, January 2025, Tabla 4; both checked 2026-10-30).
IRCA Band National figures for 2023 that fall in it
0–5 no risk (sin riesgo)
5.1–14 low (bajo) urban areas 5.7; utility-supplied 7.4
14.1–35 medium (medio) the whole country 16.4
35.1–80 high (alto) rural areas 36.5; self-supplied 43.7
80.1–100 unfit (inviable sanitariamente)

The national average sits in the medium band and hides the urban–rural gap, which is why this part’s data keep each municipality’s two zones apart.

NoteWhy this works

Surveillance samples of tap water are tested against health limits. Each tested characteristic carries a risk score: Escherichia coli 25, total coliforms 15, turbidity 15, free residual chlorine 15, apparent colour 6 and pH 1.5, among others. A sample’s IRCA is the score of the characteristics that fail their limit, as a percentage of the score of all those tested, and a month’s IRCA is the mean over its samples (articles 13 and 14). That is why 0 means every test passed and 100 means every test failed.

WarningCommon pitfall

The IRCA in this part’s data is not the national figure. The open table it comes from (INS, datos.gov.co nxt2-39c3) holds the utility-supplied (“prestadores”) IRCA: samples from systems run by a water utility. A check on 2026-10-30, recorded in the data’s README (caveat 1), found that its 2023 department totals match INCA’s utility column in 30 of 30 departments and the overall figure in only 5. Self-supplied systems, which have no utility, score far worse (43.7 against 7.4 in 2023), so the data understate the risk wherever people rely on self-supply, and say nothing where no utility exists. Label the variable “utility-supplied IRCA” everywhere it appears.

17.3 Saber 11, and what an average covers

One of the outcomes the analysis compares (Section 17.5) is an education measure, Saber 11, the national exam taken by students finishing upper secondary school, set by ICFES. It has five tests: critical reading, mathematics, social studies and citizenship, natural sciences, and English. The global score is five times the weighted mean of the five test scores (each 0 to 100; English counts once, the others three times), from 0 to 500, and its mean was fixed at 250 in the first sitting in 2014, so that years can be compared (ICFES, Guía de orientación Saber 11.° 2017-1, pp. 101 and 104, checked 2026-10-30).

Both measures are averages, and an average describes only what it averaged over. IRCA is an average over the water samples taken, so a system that is never sampled has no IRCA at all. The Saber 11 mean in the data is an average over the students who sat the exam at schools in that municipality and zone, so it says nothing about students who had left school. Keep both facts in mind in every comparison that follows.

17.4 Where the data come from

Most of the data are Colombian open data from five public bodies; rainfall and altitude come from two scientific datasets. All are joined on one code: joining two tables matches their rows by a column they share, here the code that DANE’s list of municipalities, DIVIPOLA (División Político-Administrativa), gives each municipality. It has five digits, two for the department and three for the municipality, so 05001 is Medellín, in Antioquia (05). The sources are:

  • INS (Instituto Nacional de Salud): the IRCA surveillance results.
  • DANE (the national statistics office): the list of municipalities and their codes, population projections and municipal boundaries.
  • ICFES and MEN (the Ministry of Education): Saber 11 results and school dropout rates.
  • DNP (the National Planning Department), through its TerriData portal: water and sewer coverage, multidimensional poverty, municipal performance, transfers for water and infant mortality.
  • CHIRPS (the Climate Hazards Center’s rainfall data) and NASA SRTM (elevation data): rainfall, and a derived altitude for each head town.

They arrive as three files, in examples/water/data/ in the workshop repository: water_municipal.csv (the analysis table), municipios.geojson (municipal boundaries for maps) and README.md, which documents every column, source, licence and caveat (Appendix G lists them all).

One row is one municipality’s urban or rural zone in one year, a zone-year: this is the analysis’s unit. The urban zone is the head town (cabecera municipal); the rural zone is the smaller settlements and the dispersed countryside. The table covers every one of DANE’s 1,122 DIVIPOLA entries, both zones and every year from 2010 to 2023: 31,416 rows and 29 columns. Of those entries, 1,103 are municipalities; the rest are the island of San Andrés and areas that are not municipalities, whose rows are mostly empty. Because every municipality has all its rows, a gap is an empty cell, never a missing row.

Pause and think

A municipality’s rural zone has no IRCA in one year. Was its water safe that year?

Answer

The data cannot say. IRCA averages the samples taken from utility-run systems, so an empty cell means no utility-supplied score was published: the zone may not have been sampled, or its water may be self-supplied, which nationally scores far worse (Table 17.1). An empty IRCA is unknown, never safe and never zero.

TipIn practice

Read data/README.md before you analyse anything, and ask the agent to read it first too. Its caveats are the analysis’s traps, written down: the IRCA source above; department capitals that have no urban and rural split for coverage; a fiscal index whose method changes in 2020; infant mortality whose method changes in 2017; Saber 11 only in some years; pesos that are not adjusted for inflation. Each one turns up in the chapters that follow.

17.5 Features and outcomes

The most important design decision in the water analysis is which variables define the kinds and which only describe them.

The features, which the clustering sees, are nine variables about water, people and place, and government. The outcomes, which the analysis only compares, are infant mortality, the Saber 11 score and school dropout (Table 17.2, Table 17.3). Three features need a word. The multidimensional poverty index (IPM, Índice de Pobreza Multidimensional) is the share of a zone’s people who are poor in several respects at once, from the 2018 census. MDM (Medición de Desempeño Municipal) is DNP’s score of a municipality’s management and results. And SGP (Sistema General de Participaciones) is the system of national transfers to municipalities: the feature is its allocation for drinking water and basic sanitation, per person.

Both tables give each variable’s level and years, computed from the municipalities’ rows. A zone variable differs between a municipality’s urban and rural rows; a municipal one is the same on both; “one value” is repeated every year. Meanings, units and sources come from data/README.md (TerriData is DNP’s data portal).

Table 17.2: The nine features the clustering sees: three about water, four about people and place, two about government.
Variable Measures (unit); source Level; years
IRCA (irca) water-risk index, utility-supplied (0–100); INS zone; 2010–2023
Piped water (water_coverage) aqueduct coverage (%); TerriData zone; 2010–2023
Sewer (sewer_coverage) sewerage coverage (%); TerriData zone; 2010–2023
Poverty (ipm) people multidimensionally poor (%); 2018 census zone; one value
Rural share (rural_share) rural share of the population (0–1); DANE municipality; 2010–2023
Altitude (altitude_m) the head town’s height (m); SRTM municipality; one value
Rainfall (rainfall_mm) rain over the municipality (mm a year); CHIRPS municipality; 2010–2023
MDM (mdm) municipal performance score (0–100); TerriData municipality; 2016–2023
SGP (sgp_water_pc) water transfers (nominal pesos per person, logged); TerriData, DANE municipality; 2010–2023
Table 17.3: The three outcomes, compared between the kinds but never clustered on. Level and years as in Table 17.2.
Variable Measures (unit); source Level; years
Infant mortality (infant_mortality) deaths under one year per 1,000 live births; TerriData municipality; 2010–2023
Saber 11 (saber11_mean) mean exam score of the zone’s schools (0–500); ICFES zone; 2014–2017, 2019, 2020 and 2022
School dropout (dropout_rate) public-school pupils who leave during the year (%); MEN municipality; 2011–2023

The last column matters in every chapter that follows. A zone variable is measured separately for a municipality’s urban and rural zones; a municipal one is a single value for the whole municipality, repeated on both of its rows. Five of the nine features are municipal, so a municipality’s two rows in a year differ only in the other four. And a municipal outcome, such as infant mortality, must be counted once per municipality, not once per row.

Never cluster on an outcome. If infant mortality helped define the kinds, the kinds would differ in infant mortality by construction, and the comparison in the second question would be circular: it would find exactly what was put in. Keeping outcomes out of the clustering is what lets the comparison say anything at all.

Pause and think

A colleague wants infant mortality added to the features, “since it is what we care about”. What would the comparison of outcomes between the kinds then show?

Answer

That the kinds differ in infant mortality, which would be true by construction: the algorithm would have grouped zones partly by that very number. The comparison would find what was put in. Infant mortality stays an outcome, compared only after the kinds are found.

NoteWhy this works

The same care is why the fiscal index idf is not a feature, although it describes municipal government: its method changes in 2020 (idf to 2020, idf_new from 2020, on different scales), so it cannot form one series across the years the analysis uses. A variable that changes meaning partway through would create kinds that are artefacts of the change.

17.6 Compute once, publish many

Part II computed in each page. The water analysis computes once (Figure 17.1). One importable script, scripts/analysis.py, does all of the computation and writes its results to outputs/; every page only reads those results and draws them. A design choice, such as how many rows to drop for missing values, is made in one place, and every output follows at the next render.

The data file water_municipal.csv feeds one script, scripts/analysis.py, which Quarto runs before every render. The script prepares the features, chooses k, fits k-means, and computes cluster profiles and outcome comparisons, then writes them once to outputs/: clusters.parquet, cluster_profiles.csv, k_selection.csv, outcomes_by_year.csv, irca_infant.csv and summary.json. Every page reads those files and none recomputes them: the explore and clusters pages, the report in html, docx, pdf and epub, the council slides in revealjs and pptx, a blog post and a dashboard. Together they are one website, published on GitHub Pages.
Figure 17.1: Compute once, publish many. One script turns the data into results once; every publication reads the results, and none recomputes them.

The split solves two problems from Part II. A slow computation no longer slows every page and every format, because it runs once. And no page can disagree with another, because there is only one result to disagree with. Results are saved as Parquet, which keeps column types (a code such as 05001 stays text instead of becoming the number 5001), and as CSV where people will open them.

This book follows the same rule: every number in the water analysis is computed when the book is built, by importing the finished project’s analysis.py and running it in memory.

17.7 Setting up the project: data, rules and environment

The project lives beside the London one, so that the workshop repository’s checkpoints are always at ../quarto-workshop/checkpoints/:

~
├── quarto-workshop/
├── london-guide/
└── water-quality/

In your home folder (~), quarto-workshop/ is the clone from Chapter 4, london-guide/ is Part II’s project and water-quality/ is this part’s.

Quit the London guide’s agent first (/exit), so that the new one starts in the new folder and takes it as its world (Section 2.4). Then make the folder, start its history as you did for the guide (Section 6.2), start the agent in it, and copy the data:

You do
cd ~
mkdir -p water-quality
cd water-quality
git init -b main
claude
Ask your coding agent
Copy ../quarto-workshop/examples/water/data into this folder as data/, then list the files in data/ with their sizes.
Expect it to run

copies the folder · lists README.md, municipios.geojson and water_municipal.csv with their sizes

The data sit outside the new folder, in ../quarto-workshop/, so the agent asks before it reads there: approve it, as for the London guide (Section 6.2).

Then write the rules. If you have never clustered anything, the worry is fair: the agent can write an analysis that runs, renders and is wrong. You do not guard against that by writing the code yourself. You guard against it by checking, and a check needs something to check against.

Ask your coding agent
Read data/README.md, then create AGENTS.md for this project. Scope: describe Colombian municipal water safety and infant mortality from data/ only. Rules: keep data/ unchanged, label IRCA as utility-supplied (prestadores), never cluster on outcomes, and say comparisons are descriptive, not causal. Show me the file.
Expect it to run

reads data/README.md · writes AGENTS.md: scope, rules, the data’s caveats

You do

approve · check that AGENTS.md holds all four rules

The four rules are this chapter in miniature: the inputs are evidence and stay unchanged; the IRCA is labelled for what it is; outcomes describe the clusters but never define them; and comparisons between clusters do not establish causal effects of water safety. Asking the agent to read the README first lets it add the data’s caveats to the rules itself.

Ask your coding agent
Give this project its own Python environment with `uv`: run `uv init --bare`, then `uv add ipykernel nbclient nbformat pyyaml pandas numpy matplotlib plotly scikit-learn pyarrow itables tabulate`. Run `quarto` and `python` through `uv run`, and add that rule to AGENTS.md. Create a .gitignore for .venv/, .quarto/, _site/, outputs/ and *_files/. Show me the Python line of `uv run quarto check jupyter`.
Expect it to run

uv init --bare · uv add … · writes pyproject.toml, uv.lock and .venv/ · adds the uv run rule to AGENTS.md · writes .gitignore · uv run quarto check jupyter

You should see a Python whose path runs through this folder’s .venv, as for the London guide (Section 6.10). The recipe is the London guide’s, with the analysis’s packages added: scikit-learn for k-means, pyarrow for Parquet, itables for the dashboard’s searchable table and tabulate for tables written as Markdown. Packages shared with Part II come from uv’s cache.

ImportantTry it

Copy the data, write the rules and create the environment.

You’re done when AGENTS.md states the analysis’s scope and its rules, uv run quarto check jupyter shows this project’s .venv Python, and the first commit holds them. Open a new terminal tab and run cd ~/water-quality first (a tab left in ~/london-guide would commit there); then git status should list .gitignore, AGENTS.md, data/, pyproject.toml and uv.lock, and not .venv/; then git add . and git commit -m "Data, rules and environment" record them, and git status says the working tree is clean.

There is no catch-up here. If you get stuck, the one at the end of Chapter 18 restores this chapter’s work and the next chapter’s together.

Summary

  • First find kinds of municipal zone from water, services and context; then compare outcomes between them.
  • The data’s IRCA is utility-supplied, so it understates risk wherever people rely on self-supply.
  • One row is a zone-year and every row exists, so an empty cell means unmeasured, never safe.
  • Zone variables differ between a municipality’s two rows; municipal ones are counted once.
  • Features define the kinds and outcomes only describe them: clustering on an outcome is circular.
  • One script computes every result once, and AGENTS.md holds the limits for the agent.

What the rules cannot say is what the table holds: where it is empty, and where a series moves for reasons unrelated to water. That is the next chapter’s question.

Further reading