Skip to content

Clean the data with a script, and show the flow

Full step  about 7 min

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 pandas or polars) 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 · Imaging: chest radiographs

Start from: the imaging kit plus notes/cleaning-decisions.md and out/review.csv (restore the cleaned checkpoint and delete scripts/, data/clean/ and out/cleaning_flow.md if you want to rebuild from scratch) (how to restore)
Produces: scripts/clean_images.py, data/clean/ (cleaned images, manifest.csv, labels_clean.csv, scores_clean.csv), out/cleaning_flow.md

Prompt

Implement the decisions in notes/cleaning-decisions.md as scripts/clean_images.py. It must read images/, labels.csv, model_scores.csv and my out/review.csv, and write cleaned copies to data/clean/images/ without modifying any original. When an image's pixels change, write it uncompressed rather than re-encoding the JPEG. Write data/clean/manifest.csv (one row per input file: kept, fixed or excluded, and why), labels_clean.csv and scores_clean.csv, and out/cleaning_flow.md with the starting count, the files removed or changed by each rule, and the final count. Run it and confirm the numbers add up.

Files used: images/*.dcm, labels.csv, model_scores.csv, notes/cleaning-decisions.md, out/review.csv.

Reference flow table (with the reference decisions)

Step Files
Image files received 283
Unreadable (2), not a chest radiograph (3), wrong matrix (4), mirrored (4) removed 270
Duplicates removed: 6 identical, 2 same-pixel copies, 2 conflicting pairs (4 files) 258
Cleaned images 258

Of the 283 files in the manifest, 194 are kept unchanged, 64 are fixed (pixels, header or both), and 25 are excluded. Check: 283 − 13 − 12 = 258. All 258 images have a normalized label, and 247 have all four scores. Five images still have a blank sex, which cannot be recovered.

Check the work. Count the files in data/clean/images/; compare with the flow table. Open a few cleaned images and the ones listed as fixed (the viewer can show them). Confirm that the files in images/ are unchanged. If your decisions differ from the reference, your table will differ; the check is that it matches your decisions.

The reference script is scenarios/imaging/checkpoints/cleaned/scripts/clean_images.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.py maps 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 →

Builds on: Plan the cleaning and make the judgement calls