Ask AI for CSV cleaning rules and review every changed cell

01Start with a tiny synthetic CSV

This example uses synthetic people and addresses; none of the records are real. The goal is not to ask AI to clean the file directly. Instead, ask it to describe rules that you can inspect, approve, and implement deterministically.

Save the following as sample_people.csv. Notice the inconsistent spaces, email capitalization, department capitalization, missing-value marker, and numeric formatting.

row_id,name,email,department,hours
1, alice kim ,ALICE.KIM@example.com, sales ,8
2,Bob Lee,bob.lee@example.com,Engineering,7.5
3,Carol Park, carol.park@example.com ,SALES, 8.0
4,Dan Choi,,engineering,NA
5,Eve Han,eve.han@example.com, Sales,9

02Ask AI for rules, not a rewritten file

Give the AI the column meanings and a few representative values. Ask it to separate safe formatting changes from transformations that could change meaning.

Do not approve a rule merely because it sounds reasonable. A recommendation such as converting every name to title case could corrupt valid capitalization. A rule such as replacing every blank hours value with zero would add information that the source did not contain.

03Turn the AI answer into an explicit rule table

A conservative AI answer for this example might suggest rules like these. Review them before writing code.

These rules are intentionally narrow. Unknown department values should remain unchanged rather than being guessed. Blank email addresses remain blank. No rows are deleted or deduplicated.

04Predict the changes before running anything

For a tiny example, work out the expected result manually. This gives you a reference against which to review the script output.

There should therefore be 9 changed cells. Row 2 should be unchanged, and the script should still output all 5 rows in their original order.

05Apply only the approved rules to a copy

The following standard-library script validates the expected columns, cleans each row, and records every cell whose value changes. It refuses to run if outputs already exists, reducing the chance of silently replacing an earlier review.

import csv
import sys
from decimal import Decimal, InvalidOperation
from pathlib import Path

INPUT = Path("sample_people.csv")
OUTPUT_DIR = Path("outputs")
CLEANED = OUTPUT_DIR / "sample_people_cleaned.csv"
CHANGES = OUTPUT_DIR / "changes.csv"

EXPECTED_FIELDS = ["row_id", "name", "email", "department", "hours"]
DEPARTMENT_MAP = {
    "sales": "Sales",
    "engineering": "Engineering",
}


def clean_hours(value):
    trimmed = value.strip()
    if trimmed == "NA":
        return "", "NA converted to blank"
    if trimmed == "":
        return "", "trim whitespace"

    try:
        number = Decimal(trimmed)
    except InvalidOperation:
        return trimmed, "trim whitespace"

    if number == number.to_integral():
        normalized = str(number.quantize(Decimal("1")))
    else:
        normalized = format(number.normalize(), "f")
    return normalized, "normalize numeric notation"


def clean_cell(column, value):
    if column == "row_id":
        return value, "preserve row_id"

    if column == "name":
        return value.strip(), "trim name whitespace"

    if column == "email":
        trimmed = value.strip()
        return trimmed.lower() if trimmed else "", "trim and lowercase email"

    if column == "department":
        trimmed = value.strip()
        normalized = DEPARTMENT_MAP.get(trimmed.lower(), trimmed)
        return normalized, "trim and normalize known department"

    if column == "hours":
        return clean_hours(value)

    return value, "no rule"


def main():
    if not INPUT.is_file():
        sys.exit(f"Input file not found: {INPUT}")

    if OUTPUT_DIR.exists():
        sys.exit(f"Stop: {OUTPUT_DIR} already exists. Review or rename it first.")

    OUTPUT_DIR.mkdir()

    cleaned_rows = []
    changes = []

    with INPUT.open("r", encoding="utf-8", newline="") as source:
        reader = csv.DictReader(source)
        if reader.fieldnames != EXPECTED_FIELDS:
            sys.exit(
                f"Unexpected columns. Expected {EXPECTED_FIELDS}, got {reader.fieldnames}"
            )

        for row in reader:
            cleaned = {}
            for column in EXPECTED_FIELDS:
                before = row[column]
                after, rule = clean_cell(column, before)
                cleaned[column] = after

                if before != after:
                    changes.append(
                        {
                            "row_id": row["row_id"],
                            "column": column,
                            "before": before,
                            "after": after,
                            "rule": rule,
                        }
                    )

            cleaned_rows.append(cleaned)

    with CLEANED.open("x", encoding="utf-8", newline="") as target:
        writer = csv.DictWriter(target, fieldnames=EXPECTED_FIELDS)
        writer.writeheader()
        writer.writerows(cleaned_rows)

    with CHANGES.open("x", encoding="utf-8", newline="") as target:
        writer = csv.DictWriter(
            target,
            fieldnames=["row_id", "column", "before", "after", "rule"],
        )
        writer.writeheader()
        writer.writerows(changes)

    print(f"Rows written: {len(cleaned_rows)}")
    print(f"Changed cells: {len(changes)}")
    print(f"Cleaned copy: {CLEANED}")
    print(f"Change log: {CHANGES}")


if __name__ == "__main__":
    main()

Run it with python clean_csv.py after placing clean_csv.py and sample_people.csv in the same folder. For this example, the expected summary is 5 rows written and 9 changed cells.

06Review every changed cell

Open outputs/changes.csv before treating the cleaned file as usable. Each line should explain one transformation using row_id, column, original value, replacement value, and the rule that caused it.

1. Confirm that the change log contains exactly 9 rows for this synthetic example.
2. Check that row_id values themselves never appear as changed cells.
3. Compare the 9 entries with the manually predicted table above.
4. Confirm that row 2 does not appear in the change log.
5. Open outputs/sample_people_cleaned.csv and confirm that it still contains 5 data rows in the original order.
6. Investigate any additional change rather than assuming the AI-generated rule was correct.

07Common mistakes and limits

• Do not ask the AI to infer missing hours, departments, or email addresses unless you have a separate justified method for doing so.
• Do not use broad text transformations such as title-casing every name without checking whether legitimate values would be changed.
• Do not delete duplicates merely because two rows look similar. Deduplication requires an explicit record-matching rule.
• Do not overwrite the source CSV. Keep both the untouched input and the reviewed output.
• Do not rely only on a total such as 9 changes. Review which cells changed and why.
• For large or sensitive datasets, test rules on a representative sample and add domain-specific validation before applying them widely.

AI is useful here as a rule-drafting assistant, not as the final authority on what your data means. Column definitions, valid categories, missing-value conventions, and acceptable transformations should come from the dataset owner or another authoritative source.

Execution and verification record

2026-09-21 · hand-checked example · Python 3.12

• I checked the synthetic CSV and the approved transformations by hand.
• The expected cell-level diff contains 9 changes: 3 in row 1, 3 in row 3, 2 in row 4, and 1 in row 5.
• Row 2 has no expected changes.
• The expected cleaned dataset retains all 5 rows and preserves their order and row_id values.
• The script writes only to outputs/sample_people_cleaned.csv and outputs/changes.csv and does not overwrite sample_people.csv.
• The script stops if the outputs folder already exists.

Site-wide writing and verification principles

References

The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.

• Python documentation: csv — CSV File Reading and Writing ↗ (New tab)
• Python documentation: pathlib — Object-oriented filesystem paths ↗ (New tab)
• Python documentation: decimal — Decimal fixed-point and floating-point arithmetic ↗ (New tab)