LLM CSV Data Cleaning With Pandas: Validation Checks That Catch What the Model Misses

An LLM can speed up CSV cleaning a lot, as long as it writes the rules and your code applies them. If the model rewrites the rows itself, you get tidy-looking data with no record of what changed. The workable split: the model proposes cleaning rules and validation checks, pandas applies them, and a short report tells you which rows were repaired, which were rejected and why.

This post builds that loop on an invented 11-row orders export with the usual damage: duplicate rows, rupee symbols, Indian digit grouping, mixed date formats, state abbreviations, a negative quantity. I ran the script on 2026-10-09 with pandas 3.0.6 on Python 3.13 and pasted the real output. The prompts are untested templates, since no live model call was part of this session, and the "model suggestions" are labelled as illustrative.

If you want the SQL side of data work, see AI SQL assistant schema prompts. For the general habit of asking for decisions rather than descriptions, prompting for data analysis is the companion.

Why shouldn't the LLM clean the CSV directly?

Three reasons, all practical.

No audit trail. If the model returns 400 cleaned rows, you can't tell which of its 400 edits were right. A rule like "strip the rupee sign and commas from amount" can be read in ten seconds and applied to row 40,000 identically.

It can't be repeated. Next month's export has the same problems in slightly different shapes. A script re-runs. A chat transcript doesn't.

Silent repairs hide real problems. A model that "fixes" an amount of 1,50,000 to 1500.00 because the neighbours look like 450 to 2,100 has made a business decision. Maybe that order really was 1,50,000 rupees. You want that row in front of a person, not quietly repaired.

There's also a data-handling point. Send the model column names, a profile (distinct values, null counts, min and max) and five to ten representative rows, not the file. For personal data, check the policy of whatever service you use before sending even those, and mask emails and phone numbers in the sample.

What does a messy CSV look like, and what should you flag?

Here's the invented file, saved as orders_messy.csv. Every problem is deliberate.

order_id,order_date,customer_email,state,qty,amount,status
1001,2026-09-01,Asha@Example.com,Karnataka,2,"₹1,299.00",Delivered
1002,02/09/2026,ravi@example.com,KA,1,Rs. 450,delivered
1003,2026-09-03,meena@example.com,maharashtra ,3,"2,100",DELIVERED
1003,2026-09-03,meena@example.com,maharashtra ,3,"2,100",DELIVERED
1004,04/09/2026,,Tamil Nadu,1,899,Returned
1005,2026-09-05,sam@example.com,TN,-1,899,Delivered
1006,not a date,lee@example.com,Delhi,2,N/A,Delivered
1007,2026-09-07,Asha@example.com,Karnataka ,1,"₹1,299.00",Cancelled
1008,09/10/2026,ravi@example.com,KA,2,"1,50,000",Delivered
1009,2026-09-09,neha@example.com,Gujarat,1,450,Shipped
1010,2026-09-10,neha@example,Gujarat,1,450,Shipped

Sort the problems into three bins before writing any code, because each bin gets different treatment.

The second bin is the dangerous one. All three slash dates have two valid readings. The pandas documentation for to_datetime says dayfirst=True "is not strict" and only prefers day-first, so passing it and moving on isn't a safe plan. The script below tries ISO first, then day-first, then month-first, and accepts a reading only if it falls inside the month the export is supposed to cover.

How do you get the LLM to propose rules instead of rewriting rows?

Give it the profile, ask for rules as a table, and forbid it from returning data. This is an untested template; adapt the column list.

I have a CSV export of orders (Indian ecommerce store, one month of data,
September 2026). Columns: order_id, order_date, customer_email, state, qty,
amount, status.

Profile (computed with pandas, not guessed):
- order_date: 7 values like 2026-09-03, 3 like 04/09/2026, 1 non-date text
- amount: strings including "₹1,299.00", "Rs. 450", "1,50,000", "N/A"
- state: 8 distinct spellings for 5 states, some with trailing spaces
- status: mixed case
- 1 exact duplicate row

Do NOT return cleaned data. Return a table with these columns:
rule | column | pandas approach | repair or quarantine | risk if the rule is wrong

Then list 8 validation checks that should run AFTER cleaning, each with the
exact condition as a pandas boolean expression. Mark any rule where you are
guessing the business meaning (for example what a blank email means).

A response in the right shape would include rows like "strip currency symbols and commas, then pd.to_numeric(errors='coerce'), repair, risk: Indian grouping 1,50,000 becomes 150000 and may be a typo", and a validation list with an amount ceiling. That is illustrative, written by hand to show the shape, not a captured model reply. The useful part is the last column. Making the model state the risk of each rule gives you a review list.

Review every proposed rule against the three bins above. Models tend to put "handle missing email" in the repair bin. Whether a blank email is an error or a legitimate guest checkout is your call.

What does the cleaning and validation script look like?

This is the script I ran. It reads everything as strings (so pandas can't convert anything behind your back), applies the rules, runs the checks, and quarantines failures instead of fixing them. The window is the export month; the state map is the kind of lookup an LLM drafts well and you must review.

import re
import sys
import pandas as pd

SRC = sys.argv[1]
WINDOW = (pd.Timestamp("2026-09-01"), pd.Timestamp("2026-09-30"))
STATE_MAP = {"ka": "Karnataka", "karnataka": "Karnataka", "maharashtra": "Maharashtra",
             "tn": "Tamil Nadu", "tamil nadu": "Tamil Nadu", "delhi": "Delhi", "gujarat": "Gujarat"}
STATUSES = {"delivered", "returned", "cancelled", "shipped"}
EMAIL_RE = re.compile(r"^[^@\s]+@[^@\s]+\.[^@\s]+$")

raw = pd.read_csv(SRC, dtype=str, keep_default_na=False)
print("rows read:", len(raw))
df = raw.copy()

# 1. exact duplicates
dup = df.duplicated(keep="first")
print("exact duplicate rows dropped:", int(dup.sum()))
df = df[~dup].copy()

# 2. text normalisation
df["customer_email"] = df["customer_email"].str.strip().str.lower()
df["state"] = df["state"].str.strip().str.lower().map(STATE_MAP)
df["status"] = df["status"].str.strip().str.lower()

# 3. money: strip symbols and commas, keep NaN when not a number
amt = df["amount"].str.replace(r"[₹,]|Rs\.?", "", regex=True).str.strip()
df["amount_num"] = pd.to_numeric(amt, errors="coerce")
df["qty_num"] = pd.to_numeric(df["qty"], errors="coerce")

# 4. dates: ISO first, then day-first, then month-first, accept only inside the window
iso = pd.to_datetime(df["order_date"], format="%Y-%m-%d", errors="coerce")
dmy = pd.to_datetime(df["order_date"], format="%d/%m/%Y", errors="coerce")
mdy = pd.to_datetime(df["order_date"], format="%m/%d/%Y", errors="coerce")
in_win = lambda s: s.between(*WINDOW)
date = iso.copy()
for cand in (dmy, mdy):
    pick = date.isna() & in_win(cand)
    date[pick] = cand[pick]
df["order_date_clean"] = date

# 5. validation: nothing is silently fixed, failures are listed
warn_ambiguous = df["order_id"][dmy.notna() & mdy.notna() & (dmy != mdy)].tolist()
print("slash dates with two valid readings, resolved by the window rule (confirm with the source system):", warn_ambiguous)
checks = {
    "unparseable_or_out_of_window_date": df["order_date_clean"].isna() | ~in_win(df["order_date_clean"]),
    "missing_or_invalid_email": ~df["customer_email"].fillna("").apply(lambda e: bool(EMAIL_RE.match(e))),
    "unknown_state": df["state"].isna(),
    "unknown_status": ~df["status"].isin(STATUSES),
    "non_positive_qty": ~(df["qty_num"] > 0),
    "missing_amount": df["amount_num"].isna(),
    "amount_over_10000": df["amount_num"] > 10000,
}
print("\nvalidation failures by check:")
for name, mask in checks.items():
    ids = df.loc[mask, "order_id"].tolist()
    print(f"  {name:36s} {len(ids)}  {ids}")

bad = pd.concat(checks, axis=1).any(axis=1)
clean = df[~bad]
print(f"\nrows kept: {len(clean)}  quarantined for review: {int(bad.sum())}  (from {len(raw)} read)")
assert len(clean) + int(bad.sum()) + int(dup.sum()) == len(raw), "row accounting broke"
assert clean["order_id"].is_unique
print("row accounting OK, order_id unique in clean set")
print()
print(clean[["order_id", "order_date_clean", "state", "qty_num", "amount_num", "status"]].to_string(index=False))

Real output from python clean_orders.py orders_messy.csv:

rows read: 11
exact duplicate rows dropped: 1
slash dates with two valid readings, resolved by the window rule (confirm with the source system): ['1002', '1004', '1008']

validation failures by check:
  unparseable_or_out_of_window_date    1  ['1006']
  missing_or_invalid_email             2  ['1004', '1010']
  unknown_state                        0  []
  unknown_status                       0  []
  non_positive_qty                     1  ['1005']
  missing_amount                       1  ['1006']
  amount_over_10000                    1  ['1008']

rows kept: 5  quarantined for review: 5  (from 11 read)
row accounting OK, order_id unique in clean set

order_id order_date_clean       state  qty_num  amount_num    status
    1001       2026-09-01   Karnataka        2      1299.0 delivered
    1002       2026-09-02   Karnataka        1       450.0 delivered
    1003       2026-09-03 Maharashtra        3      2100.0 delivered
    1007       2026-09-07   Karnataka        1      1299.0 cancelled
    1009       2026-09-09     Gujarat        1       450.0   shipped

What did the checks catch that a quick glance would miss?

Start with the accounting line. 11 rows read, 1 duplicate dropped, 5 quarantined, 5 kept: 1 + 5 + 5 = 11. The assertion makes the script fail loudly if a future rule ever loses a row without saying so. That one line is the cheapest defence against a cleaning step that "works" and quietly eats data.

Row 1008 is the instructive one. Its date 09/10/2026 read day-first is 9 October, outside the September window, so the script picked the month-first reading, 10 September. That is a guess, and the guess is flagged in the ambiguity list. Without the amount check (1,50,000 becomes 150000, over the 10,000 ceiling) this row would have gone through with a guessed date and a suspicious amount. Two weak signals on one row is a good reason to quarantine it.

Rows 1002 and 1004 are resolved by the window, not by knowledge. 02/09/2026 as month-first is 9 February, so day-first wins. That is fine when the export covers one month. It breaks the day you load a quarter of data, where both readings fall in range. The ambiguity list exists for that day: if it's long, stop and ask the source system for ISO dates.

Row 1003's duplicate was exact, so dropping it is safe. Be more careful with near-duplicates (same order_id, different amount). They indicate a conflict, not a copy. Add a check that order_id is unique after dedup. The script asserts that on the clean set.

Row 1004 has a blank email and was quarantined. Maybe that's right and maybe it's a guest checkout. The script can't know, which is the point. Move the decision into a named rule when you learn the answer.

How do you test the checks themselves?

Validation code has bugs too. Two habits catch most of them.

Keep a file of known-bad rows and assert each one trips the expected check. The invented CSV above plays that role. When you change a regex or threshold, rerun it and confirm the same IDs show up under the same checks. A check that never fires on a file built to trip it is broken.

Compare before and after on aggregates you can verify independently. Sum amount_num over the clean set and compare it with a figure from the source system or a finance report. A mismatch tells you more than any per-row check. If you don't have an independent figure, record the total in the cleaning log and compare month to month.

The same discipline applies to anything an LLM writes for you, which is why the avoiding hallucinations lesson treats checkable output as the safe case. A cleaning rule you can test on known-bad rows is checkable. A rewritten file is not.

A checklist you can paste into your next cleaning job

1. Read every column as text (dtype=str, keep_default_na=False) so nothing converts silently.
2. Write down the expected window, units and allowed values before looking at the data.
3. Sort each problem into repair by rule, repair but confirm, or quarantine.
4. Parse dates with explicit formats; list any slash date with two valid readings.
5. Convert money and quantities with errors='coerce', then check for NaN, zero, negatives and ceilings.
6. Map categories through an explicit dictionary and fail on anything unmapped. Unknown means unknown.
7. Dedupe exact duplicates only; flag repeated keys with different values as conflicts.
8. Assert row accounting: kept + quarantined + dropped equals read.
9. Write the quarantine file with the failed check name beside each row.
10. Save the script, the input checksum and the summary output together so the run can be reproduced.

If you later turn this into an agent that cleans files unattended, put the checks in code the agent cannot edit. The data analyst agent walkthrough shows how to give an agent tools rather than free rein, and AI prompts for data analysts in India has a pandas prompt set for the exploratory step that comes before this one.

Limits of this example

The file has 11 rows. Real exports have millions, mixed encodings and columns nobody remembers adding. Nothing here has been measured for speed or tested on large inputs. The state map covers five states and is the sort of table you'll extend by hand. The thresholds, such as the 10,000 ceiling, are arbitrary for this invented store and have no meaning for yours. Treat the structure (rules, checks, quarantine, accounting) as the reusable part and everything else as placeholder.