Clean the data with a script, and show the flow
In this exercise, you implement the agreed data cleaning protocol as a standalone, non-destructive script. Running this script generates the cleaned dataset alongside a formal record-flow table accounting for every altered or dropped row. This approach transforms cleaning from an ephemeral conversation into a deterministic, reproducible computational pipeline.
Before you start: A documented data cleaning protocol (Plan the cleaning) or a restored project checkpoint (Setup).
Background
Rigorous data curation adheres to three foundational computational principles:
- Programmatic implementation: Cleaning logic is executed via an inspectable script (e.g., Python using
pandasorpolars) rather than manual spreadsheet edits, allowing identical transformations on future dataset releases. - Non-destructive workflows: The raw input data remains strictly read-only and immutable. Cleaned outputs and intermediate tables are written to a separate directory (such as
data/clean/). - Conservation of records (Flow accounting): A record-flow table tracks total counts at each processing stage, showing the starting record count, records affected by each specific rule, and the final retained count. Reconciling counts proves that no records were silently lost or unintentionally duplicated (Verification).
General methodology
Direct the agent to translate the agreed cleaning decisions into a standalone script, mandating an accompanying flow table to verify record counts:
Implement the rules in <decisions file> as an executable script (<scripts/clean.py>) that reads <raw file> and writes <clean file> without modifying the original input. Generate a record-flow table (<out/cleaning_flow.md>) documenting the initial record count (rows, images or files), the records modified or excluded by each rule, and the final record count. Run the script and verify that initial records minus excluded records strictly equals the final record count.
The resulting script functions as a permanent, reusable tool in your computational pipeline (Tools).
Workshop example · Clinical: clinic network
Start from: the data kit plus notes/cleaning-decisions.md (restore the cleaned checkpoint and delete scripts/, data/clean/ and out/ if you want to rebuild from scratch) (how to restore)
Produces: scripts/clean_visits.py, data/clean/visits_clean.csv, out/cleaning_flow.md
Prompt
Implement the decisions in notes/cleaning-decisions.md as scripts/clean_visits.py. It must read clinic_visits.csv, write data/clean/visits_clean.csv without modifying the original, and write out/cleaning_flow.md with the starting row count, the rows affected by each rule, and the final row count. Run it and confirm the numbers add up.
Files used: clinic_visits.csv, notes/cleaning-decisions.md.
Reference flow table (with the reference decisions)
| Step | Rows |
|---|---|
| Raw rows | 7465 |
| US-style dates converted | 6 |
| Exact duplicate rows removed | 15 |
| Rows sharing a visit_id (flagged, kept) | 2 |
| Systolic BP of 0 set to missing | 4 |
| HbA1c converted from mmol/mol to % | 10 |
| Impossible ages repaired from other visits | 1 |
| Final rows | 7450 |
Check: 7,465 − 15 duplicates = 7,450. The other rules change values, not row counts. If your decisions differ (for example you drop the unit-mismatch rows), your final count and flow table will differ; the check is that your table matches your decisions.
The reference script is scenarios/toy/checkpoints/cleaned/scripts/clean_visits.py. Compare yours to it only after you have checked yours.
Verification criteria
- Raw data immutability: The raw dataset file remains completely unaltered (verified by inspecting file checksums or timestamps before and after execution).
- Exact flow reconciliation: Initial record count minus all documented exclusions strictly equals the final cleaned record count in
out/cleaning_flow.md. - Consistency with data profiling: Exclusion counts match the anomaly counts identified during initial profiling.
- Auditable script logic: Each transformation in
scripts/clean.pymaps cleanly to an approved rule in the cleaning protocol.
Common pitfalls
| Symptom | Underlying cause | Resolution |
|---|---|---|
| Flow table counts do not reconcile | Overlapping filtering rules or unhandled nulls silently dropped rows | Instruct the agent: "Reconcile record counts step-by-step and report any overlapping exclusions" |
| Script overwrote the raw input file | Output file path was set identical to the input path | Restore the raw file from backup or checkpoint; configure the script to write exclusively to an isolated data/clean/ directory |
Concepts behind this
- Verification. Language models can be confidently incorrect. Require empirical evidence, verify mathematical invariants, and use clean-context peer review to audit findings. Go deeper →
- Tools. Turning interactive prompts into parameterized scripts creates deterministic, testable tools that run identically on every execution. Go deeper →