Clean a Messy CSV Without Inventing the Blank Cells

The question is whether a reviewer can show which cells changed and whether each missing value stayed missing. A polished table is untrustworthy if a tool converts N/A, fills a blank, or drops a quoted comma.

This tutorial uses a tiny, clearly synthetic CSV with no personal data or copied records. It is a proposed dry run, not a completed result.

Materials and the working contract

Gather the synthetic fixture, a plain-text editor, Python or another deterministic CSV parser, a timer, and a result sheet. Choose a reviewer who understands the columns and can stop the work; the prompt author cannot be the only approver.

record_key,region,orders,return_rate,note
synth-01, north ,12,0.20,steady
synth-02,South,,0.15,
synth-03,East,N/A,0.10,source check
synth-04, West ,NULL,,late line
synth-05,"Central, A",7,0.05,"contains, comma"

The allowed operation is narrow: trim surrounding spaces in region and note only when listed by the reviewer. A blank, N/A, or NULL may be labeled, never supplied, converted, or replaced. Parsed-cell values, headers, keys, and row order are protected; output must retain valid CSV quoting.

Python’s current CSV documentation says rows are returned as strings without automatic type conversion unless a special option is used, and recommends newline='' for correct newline and quoted-field handling. Python CSV documentation Use that reader as the reference parser here.

00:00–00:07 — Freeze the input

Save it as synthetic-original.csv; do not paste a live export into the exercise. Make a working copy and record the original byte hash, line-ending style, row count, header sequence, and column count. Keep the original read-only.

Read the copy once and inspect five records. The quoted comma in Central, A is intentional. If an uneven row, broken quote, unexpected encoding, or duplicate key appears, stop; do not ask AI to repair parsing.

00:07–00:15 — Ask for a plan, not a rewrite

Give the model only the working copy and an instruction such as:

Act as a transformation planner using only this CSV. Return one structured row per proposed operation with row, column, observed value, operation, replacement, and reason. Allowed: trim surrounding whitespace in region or note. For empty, N/A, or NULL, use operation=flag_missing and replacement=UNCHANGED. Never infer, fill, average, normalize, type-convert, split, merge, reorder, delete, or create rows; never change keys or headers. Outside this contract, return operation=STOP.

If available, use strict structured output with required fields and an operation enum. OpenAI’s current documentation describes schema adherence and detectable refusals, but a valid shape does not establish that a value is true or permitted. Structured model outputs Treat the response as a proposal list, never as a replacement file.

00:15–00:25 — Build the change ledger

Copy proposals into a ledger with row, column, before, allowed_operation, after, evidence, and reviewer_decision. The illustrative ledger has trims for synth-01.region and synth-04.region, plus missing flags for all five blank, N/A, and NULL cells. Never average missing orders or interpret “late line.”

Check each before against the parsed copy. Reject rows with inexact values, ambiguous locations, or meaning-changing operations. “Remove one leading and one trailing space from row 1, column region” is reviewable; “make regions consistent” is not.

00:25–00:33 — Apply only approved edits

Use a deterministic script, formula, or hand edit on the working copy. Apply only approved trims and preserve missing markers literally. If using pandas, test its policy first: current read_csv docs say empty strings and tokens such as N/A and NULL become missing by default; keep_default_na=False changes that, while na_filter=False makes those options irrelevant. pandas read_csv Here, lexical preservation beats convenient numeric types.

Save a new synthetic-cleaned.csv; never replace the frozen original.

00:33–00:37 — Run the exact preservation check

Verify the original hash still matches and that the cleaned file has five data rows, the same headers, key order, and valid quoting. Compare parsed cells: only approved trim coordinates may differ; every other string, especially each missing marker, must match exactly. This does not claim byte-identical output serialization.

import csv

def rows(path):
    with open(path, newline="", encoding="utf-8") as handle:
        return list(csv.reader(handle, strict=True))

before = rows("synthetic-original.csv")
after = rows("synthetic-cleaned.csv")
approved = {(1, 1), (4, 1)}
assert before[0] == after[0]
assert [row[0] for row in before[1:]] == [row[0] for row in after[1:]]
assert len(before) == len(after)
for r, (old_row, new_row) in enumerate(zip(before, after)):
    assert len(old_row) == len(new_row)
    for c, (old, new) in enumerate(zip(old_row, new_row)):
        if (r, c) in approved:
            assert new == old.strip(), (r, c, old, new)
        else:
            assert old == new, (r, c, old, new)

Only two region trims are approved. The loop also asserts all five missing markers remain literal.

00:37–00:40 — Review, fail safely, choose the next decision

The reviewer signs when changed cells have ledger rows, others match, the original is intact, and missing markers remain. Fail and discard if a new value appears, a marker changes, a row or key moves, a quote is lost, an unapproved cell changes, or a proposal lacks source explanation.

If it passes, the next decision is a limited dry run on a larger synthetic or explicitly de-identified file with the same allowlist and independent readback. If it fails, revise one boundary at a time or stay manual. This never authorizes automatic writes to a source dataset.

Sources and limitations

All sources below were checked September 2, 2026.

1. Python Software Foundation, “csv — CSV File Reading and Writing” — supports string-oriented CSV reads, dialect and quoting differences, and newline='' handling. Limitation: the standard-library reader does not decide which values are missing or whether a proposed edit is semantically correct.
2. pandas, “pandas.read_csv” — supports the documented defaults and controls for missing-value parsing, including keep_default_na and na_filter. Limitation: parser options can change representation; they do not establish a business-approved cleaning policy.
3. OpenAI, “Structured model outputs” — supports strict schema-shaped responses and detectable refusals. Limitation: structural adherence is not evidence that a proposed value is supported, safe, or authorized.