Cleaning Data With pandas and LLMs in October 2026: Show the Model Your Real Columns, Count What Every Step Drops, and Never Overwrite the Raw File

Asking an LLM to "clean this CSV" is one of the most common ways people use AI for data work, and it often goes wrong in quiet ways. The code runs, a tidy file comes out, and nobody notices that a third of the ZIP codes lost their leading zeros, that a merge doubled the row count, or that every unparseable date became blank. The model isn't being careless. It just can't see your data, so it guesses.

This guide shows a practical way to get pandas cleaning code from an LLM that you can trust: give it the real shape of your data, make every step prove it didn't lose or invent rows, and keep the raw file untouched. The examples use plain pandas, and the same habits carry over to Polars or SQL.

Step 1: Show the Model Your Real Columns, Not a Description

"I have a sales file with customers and dates" leaves the model to invent column names, types, and formats. Instead, run a few lines of profiling and paste the output into your prompt:

import pandas as pd

df = pd.read_csv("sales_raw.csv", dtype=str)
print(pd.__version__)
print(df.shape)
print(df.dtypes)
print(df.head(10).to_string())
print(df.isna().sum())
for col in ["order_date", "amount", "zip"]:
    print(col, df[col].dropna().sample(8, random_state=0).tolist())

This gives the model the pandas version, the row and column count, exact column names, and a sample of what the messy values actually look like, such as "$1,204.50", "03/04/2025", or "N/A". Before you paste, replace names, emails, and anything else personal with fake values of the same format. The model needs the pattern, not the person.

Mentioning the version matters. pandas 2.x and 3.x differ in some defaults, including Copy-on-Write behavior and the default string dtype, and a model trained on older examples may hand you patterns that now warn or behave differently.

A prompt that works

I'm using pandas <version>. Below is the profile of my CSV.
Write a cleaning script that:
- reads the file with dtype=str so nothing is converted silently
- parses order_date with an explicit format (it is MM/DD/YYYY)
- converts amount to a number after removing "$" and ","
- keeps zip as text, padded to 5 digits
- prints how many rows each step changed or dropped
- writes to sales_clean.csv and never overwrites sales_raw.csv
Do not drop rows unless I asked for it. Ask me if a rule is ambiguous.

Notice that the prompt states the date format. Without it, a value like 03/04/2025 could be March 4 or April 3, and the model has no way to know which one your system produced.

A pandas DataFrame: show the model real columns and values, not a description. Image: Pandas dataframe.png by Lucasadvent, CC BY-SA 4.0, via Wikimedia Commons.

Step 2: Read Everything as Text First

By default, read_csv infers types. That is convenient and dangerous: 02134 becomes the integer 2134, long account numbers can turn into floats, and a column of mostly numbers with one "unknown" stays as object type without telling you why.

Reading with dtype=str and converting columns on purpose puts every type decision in front of you. If the model's script skips this, ask it to add it. Then convert each column explicitly:

df["zip"] = df["zip"].str.strip().str.zfill(5)

df["amount"] = pd.to_numeric(
    df["amount"].str.replace(r"[$,]", "", regex=True).str.strip(),
    errors="coerce",
)

df["order_date"] = pd.to_datetime(
    df["order_date"], format="%m/%d/%Y", errors="coerce"
)

Note that zfill is only right if every ZIP should be exactly five digits. If your data includes ZIP+4 values or non-US postal codes, say so, because padding those would corrupt them.

Cleaning should filter on purpose, and you should count what each step catches. Image: Laboratory sieves BMK.jpg by User:BMK, CC BY-SA 3.0, via Wikimedia Commons.

Step 3: Count What Every Conversion Breaks

errors="coerce" is useful because it turns bad values into NaN or NaT instead of crashing. It is also how data disappears without anyone noticing. The fix is to count the new blanks after every conversion and look at what caused them:

raw = pd.read_csv("sales_raw.csv", dtype=str)

bad_amount = df["amount"].isna() & raw["amount"].notna()
print("amount failed to parse:", bad_amount.sum())
print(raw.loc[bad_amount, "amount"].value_counts().head(20))

bad_date = df["order_date"].isna() & raw["order_date"].notna()
print("date failed to parse:", bad_date.sum())
print(raw.loc[bad_date, "order_date"].value_counts().head(20))

If 3 out of 40,000 amounts fail and they are all "refunded", you can make a deliberate decision about them. If 9,000 dates fail, the format you gave was wrong for part of the file, and you just avoided shipping a broken dataset. Feed this output back to the model and ask it to handle the specific failing patterns rather than guessing at new ones.

Step 4: Make Merges Prove Their Shape

The most damaging cleaning bug is a join that silently multiplies rows. If the customer table has two rows for the same customer ID, every order for that customer appears twice after the merge, and every total downstream is wrong.

pandas has two arguments that catch this, and LLM-written merges rarely include them unless you ask:

before = len(df)
merged = df.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
)
assert len(merged) == before, "merge changed the row count"
print(merged["_merge"].value_counts())

validate="many_to_one" raises an error if customer_id is not unique in the right-hand table. indicator=True adds a _merge column showing which rows found a match, so you can see how many orders have no matching customer instead of discovering blank columns later. Ask the model to use both on every merge in the script.

Step 5: Be Explicit About Duplicates and Missing Values

"Remove duplicates" sounds simple, but it hides real choices. Are two rows duplicates if every column matches, or only if the order ID matches? If the order ID repeats with different amounts, which one wins? drop_duplicates() with no arguments only removes rows that are identical in every column, which may not be what you meant.

Tell the model the rule in words, then check the result:

dupes = df[df.duplicated(subset=["order_id"], keep=False)]
print("rows sharing an order_id:", len(dupes))
print(dupes.sort_values("order_id").head(20).to_string())

df = (df.sort_values("updated_at")
        .drop_duplicates(subset=["order_id"], keep="last"))
assert df["order_id"].is_unique

Do the same with missing values. dropna() with no arguments drops a row if any column is blank, which can wipe out most of a wide file. Name the columns that must be present, use dropna(subset=[...]), and print how many rows were removed. Filling blanks with zero is a business decision, not a cleaning step: a missing discount and a zero discount are not the same thing.

Avoid chained assignment

Models still produce code like df[df["country"] == "US"]["region"] = "NA". That pattern writes to a temporary copy, so the original frame doesn't change, and under Copy-on-Write it is guaranteed not to. Ask for .loc instead:

df.loc[df["country"] == "US", "region"] = "NA"

Step 6: Add Assertions, Then Test Against Known Answers

A cleaning script should fail loudly when the data stops matching your assumptions. Put a short block of checks at the end, and ask the model to write them from rules you state:

assert df["order_id"].is_unique
assert df["amount"].dropna().ge(0).all(), "negative amounts found"
assert df["order_date"].dropna().between("2020-01-01", "2026-12-31").all()
assert df["zip"].str.fullmatch(r"\d{5}").all()
print("final rows:", len(df), "of", len(raw))

Then check a few numbers you already know. If finance says October revenue was a certain figure, compute it from the cleaned file and compare. Pick three or four specific orders, look them up in the raw file by hand, and confirm they came through with the right date, amount, and customer. Ten minutes of spot checks catches the errors that look plausible, which are exactly the ones that slip through.

Step 7: Never Overwrite the Raw File

Keep the original export read-only and write cleaned output to a new file, ideally with a date in the name. Save the cleaning script next to it, under version control if you can. That way, when someone asks why a number changed, you can rerun the script, compare outputs, and show exactly which rule did it.

out = f"sales_clean_{pd.Timestamp.today():%Y%m%d}.csv"
df.to_csv(out, index=False)

The index=False matters. Without it, pandas writes the row index as an unnamed first column, which shows up as a mystery column the next time anyone opens the file.

A Short Checklist

Paste real column names, dtypes, null counts, and sample values (with personal data replaced) instead of describing the file.
Name your pandas version and the exact date and number formats.
Read as text, then convert each column on purpose.
Count every value that errors="coerce" turned blank and look at why.
Use validate and indicator on every merge and assert the row count.
State duplicate and missing-value rules in words, with explicit subset arguments.
Use .loc, not chained indexing.
End the script with assertions and compare against numbers you already trust.
Write to a new file and keep the raw export untouched.

An LLM is genuinely good at writing the tedious parts of a cleaning script: the regexes, the type conversions, the reshaping. What it can't do is see your data or know your business rules. Give it the first, state the second, and make the script prove every step, and you get the speed without the silent damage.

Search This Blog

Technology On the Net