You get a 100K-row operations export, open it up, and find: the date column has three formats ("2024/1/1", "2024-01-01", "Jan 1"); phone numbers mix "+86 138...", "138-xxxx-xxxx", and blanks; the city field is half "Beijing" and half "Beijing City"; a few GMV rows are negative or zero from test data. Cleaning this by hand in Excel takes all night. Throwing it all at an LLM in one shot is expensive, slow, and non-reproducible. This SOP gives you a 6-step method where the LLM acts as scout and judge while Python/pandas does the heavy lifting--turning a dirty table into analysis-ready data, with rerunnable scripts and traceable lineage at every step.
1. Why "LLM + Python Script" on Two Tracks, Not One
Two extremes both fail. Pure-LLM full-data rewrite: feeding 100K rows to the model line by line costs enough for a half-year subscription, runs all night, and worst of all can't be reproduced--next time the data updates you start over, rules depending on the model's mood that session. Pure Python: you hand-write every rule, and fuzzy calls like whether "Beijing XX Tech Co." and "Beijing XX Technology Co., Ltd." are the same entity defeat regex no matter how hard you try.
The right split: the LLM handles what it's good at--understanding and judgment--profiling dirty points, drafting rule proposals, fuzzy matching and semantic dedup; pandas handles batch execution--codifying deterministic rules once, running them forever. The LLM provides the brain, the script provides the muscle, and the two interlock into a reproducible pipeline. The 6 steps below are that interlock.
2. Step 1 Profile: Let the LLM Be Your Dirty-Data Scout
Don't start writing cleaning code. First, sample the data and feed it to the LLM to tell you where it's dirty. Sampling instead of full-data keeps it fast and cheap--200 random rows usually expose the main problems. Convert the sample to CSV or JSON, pair it with this profiling prompt:
You are a data quality auditor. Below is a random sample of tabular data (CSV).
Inspect each column and report the following dirty points:
1. Missing values: which columns have empty strings, NULL, or NaN? Estimate the ratio.
2. Format inconsistency: do date/phone/amount/enum fields have multiple formats? Give one example each.
3. Outliers: do numeric columns have obvious outliers (negatives, extremes, all-zero)? List examples.
4. Duplicates: are there suspected duplicate rows? Which fields are the basis?
Output per-column JSON: {"column": {"issue_type": ["concrete_sample"]}}.
Only report issues that actually exist in the sample. Do not fabricate or generalize.
Data sample:
{paste 200 rows of CSV}You get a structured dirty-point report, e.g., "date column has 3 formats", "phone column mixes +86 prefix and nulls", "GMV column has 2 negative rows". This report is the input for the next step. Note: the LLM gives you "dirty-point descriptions," not "cleaning actions"--don't let it rewrite the data in the same pass, that's uncontrollable.
3. Step 2 Define Rules: LLM Drafts, Human Decides
With the dirty-point report, have the LLM draft executable cleaning rules, but you make the final call. The LLM doesn't know your business definitions--whether GMV of 0 is test data to delete or a real refund to keep, it can't judge.
Cleaning rule generation prompt:
Based on the dirty-point report and business context below, generate an executable cleaning rule list.
Requirements:
- Each rule specifies: target column, condition, action (fill/delete/standardize/split), fallback for anomalies.
- Number rules in execution order; they must not conflict.
- Output format: seq | column | condition | action | fallback
- Do not modify data directly; only output rules.
Business context: e-commerce livestream daily report; GMV<=0 is test data to be deleted.
Dirty-point report:
{paste the JSON from Step 1}The LLM returns a list like "Rule 1 | date | multiple formats | to_datetime standardize | rows failing parse set to NaT then deleted." Review each rule: is it correct for your business? Is it over-cleaning? Finalize after edits--this rule sheet is the blueprint for the next step. Keep this document; it's your cleaning contract.
4. Step 3 Script Execution: Codify Rules in pandas, Reproducibly
Translate the finalized rules into pandas code. The core principle: each rule maps to one code block, and the code is the single source of truth for that rule. Next time data updates, run the script and you're done--no need to re-chat with the LLM.
import pandas as pd
df = pd.read_csv("dirty.csv")
raw_count = len(df) # keep for validation
# Rule 1: drop GMV<=0 test rows
df = df[df['GMV'] > 0]
# Rule 2: standardize dates, set parse failures to NaT then drop
df['date'] = pd.to_datetime(df['date'], errors='coerce')
df = df.dropna(subset=['date'])
# Rule 3: keep digits only in phone
df['phone'] = df['phone'].astype(str).str.replace(r'\D', '', regex=True)
df.loc[df['phone'] == '', 'phone'] = None
# Rule 4: strip "市"/"City" suffix for uniformity
df['city'] = df['city'].astype(str).str.replace('市$', '', regex=True)
df.to_csv("cleaned.csv", index=False)
print(f"Raw {raw_count} rows, cleaned {len(df)} rows")Each rule is annotated with a number matching the Step 2 rule sheet. pandas to_datetime with errors='coerce' turns parse failures into NaT instead of crashing--standard practice for dirty data. Don't let one bad row blow up the whole script.
5. Step 4 LLM for Hard Rules: Fuzzy Match / Semantic Dedup / Field Extraction
At this point, everything expressible as a deterministic rule is clean. The remaining hard cases--fuzzy matching, semantic dedup, unstructured field extraction--regex can't cover, so the LLM judges. But the approach isn't "let the LLM modify all data"; it's "LLM judges pair by pair, script executes in batch."
Take "suspected duplicate company names" as an example: first use a script to filter candidate pairs (high name similarity), then let the LLM judge each pair:
Fuzzy match judgment prompt:
You are a data dedup judge. Below are candidate record pairs to adjudicate (fields aligned).
For each pair, determine whether they are the same entity. Output a JSON array:
[{"pair_id": 1, "same": true/false, "confidence": 0.0-1.0, "reason": "which fields were the basis"}]
Criteria: similar names (tolerate spelling differences, abbreviations, aliases) + corroborating address/phone/ID.
Pairs with confidence below 0.6 must have reason set to "needs human review."
Don't just check for exact name equality; handle variants like "Beijing XX Co." vs. "Beijing XX Co., Ltd."
Record pairs:
{paste candidate pairs JSON}The LLM outputs JSON; you parse with a script: high-confidence pairs merge automatically, low-confidence ones go to a human review queue. Field extraction works the same way--feed the LLM mixed province/city/district values buried in an "address" column, batch in, batch out, script writes to DB. Key discipline: the LLM only outputs judgment results and never touches the raw data; all DB writes are done by the script.
6. Step 5 Validate: Before/After Comparison + Anomaly Review
Cleaning without validation is no cleaning at all. Validation has two parts: quantitative reconciliation and quality spot-checking.
Quantitative reconciliation--compare row counts, key-field non-null rates, and enum-value distributions before and after. For example, the city column with 50 enum values pre-clean should converge to under 30 post-clean; the GMV total delta should equal the sum of GMV in deleted anomalous rows. Write a comparison script:
raw = pd.read_csv("dirty.csv")
clean = pd.read_csv("cleaned.csv")
print(f"Rows: {len(raw)} -> {len(clean)} (deleted {len(raw)-len(clean)})")
print(f"GMV total: {raw['GMV'].sum():.2f} -> {clean['GMV'].sum():.2f}")
print(f"City enum count: {raw['city'].nunique()} -> {clean['city'].nunique()}")Quality spot-check--randomly pull 50 cleaned rows and eyeball them, focusing on over-cleaning: deleted rows that shouldn't have been, standardized fields that became null, fuzzy merges that combined two different companies into one. Log anomalies, trace which rule caused them, fix and rerun. This is the last gate against over-cleaning.
7. Step 6 Audit Trail: Rerunnable Rules, Data Lineage
After cleaning, you need to be able to reproduce the entire process with one command when a new batch arrives a month later. Two things must stay:
First, a rerunnable script repo. Put profiling, cleaning, and validation scripts in numbered order (01_profile.py, 02_clean.py, 03_validate.py), with a run_all.sh to chain them. When a rule changes, edit the script--don't hand-tune in Excel.
Second, data lineage records. Every cleaning run preserves three versions: raw/ (original data archived by date), cleaned/ (post-clean), and log/ (cleaning log with row-count changes, which rows were deleted, rule version number). When anyone asks "where did this row come from," you can trace it to the original row and the rule version that ran. A before/after comparison table goes in the log:
Clean time: 2026-07-29
Rule version: v1.2
Raw rows: 10234 -> Cleaned rows: 9876 (deleted 358)
Deletion breakdown: GMV<=0 deleted 210, date parse fail deleted 89, duplicates deleted 598. Five Pitfalls from the Trenches
1. Letting the LLM rewrite all data directly. To save effort, feeding 100K rows to the model for a full rewrite costs triple-digit tokens, runs 4 hours, and can't be reproduced. Correct approach: the LLM profiles and judges; the script executes. If a rule is deterministic, never hand it to the model.
2. Unstable LLM fuzzy judgment without checks. The same company-name pair judged twice by the LLM gives different answers (same once, different once). Fix: confidence below 0.6 forces human review, and even high-confidence pairs get a 10% spot check. Don't fully trust the model.
3. Sending sensitive data out without desensitization. Customer names, phone numbers, and IDs fed directly to a cloud LLM crosses compliance lines. Desensitize sensitive fields (hash or mask) in a script before sending to the LLM, or run a local model--Ollama with qwen2.5 handles fuzzy judgment with data never leaving your domain.
4. Cleaning rules become a black box. Rules scattered across a dozen LLM chat sessions, no one remembers them all. Next data batch arrives and no one knows how to clean. Fix: all finalized rules must live in scripts and docs. The script is the documentation; chat logs are not the source of truth.
5. Over-cleaning loses information. To get things "clean," deleting all rows with any missing value,一刀切 all outliers--wiping out real refunds, test orders, and edge cases. Analysis time, the numbers don't add up. Fix: always keep the original data, store the cleaned version separately, and make deletion records traceable. Better to keep too much than to cut blindly.
References