Articles / agentspythonprojects
An AI agent team for data cleaning: plans as JSON, pandas does the work
Four agents clean a messy CSV. A profiler, a model that plans in JSON, a whitelisted pandas executor and a validator that rejects bad plans. Real runs shown.
Handing a CSV to a language model with the instruction "clean this" fails in predictable ways. The model invents values for blanks, drops rows it finds odd, renames columns, and gives you no record of what it changed. The fix is to stop letting the model touch the data at all. It writes a cleaning plan as JSON, and plain pandas code decides whether that plan is allowed, runs it, and checks the result.
This post builds that as a team of four parts around a synthetic orders file with every common defect: mixed date formats, duplicate rows, misspelt city names, negative and absurd amounts, and missing values. The planner runs two ways. A scripted fake makes the walk-through repeatable, and a real local model (Qwen3 4B through Ollama) shows what a small model does with the same job. The validator rejected every plan the local model wrote, each for a specific, checkable reason.
If you want a model to explore a dataset and explain it, see the EDA agent post. For orchestration frameworks, parallel agents and human approval, see multi-agent workflow automation in Python. This post stays on one narrow job: changing data safely.
The shape of the team#
orders_messy.csv
|
v
+---------------+ profile (JSON: counts, value lists, IQR bounds)
| Profiler |-------------------------------+
| pandas only | |
+---------------+ v
+-----------------+
+----------------------------->| Planner | model, or a scripted fake
| feedback: what failed | returns a JSON |
| | list of steps |
| +-----------------+
| |
| v
| +-----------------+
|<------- rejected ------------| Plan check | op on whitelist? column exists?
| +-----------------+
| | ok
| v
| +-----------------+
| | Executor | named pandas functions only,
| | | works on a copy, quarantines drops
| +-----------------+
| |
| v
| +-----------------+
+<------- FAIL ----------------| Validator |---- PASS ---> clean frame + report
+-----------------+
Only the planner is probabilistic. Everything else is ordinary code that gives the same answer every time, so any result can be replayed and audited. In the terms of multi-agent architecture patterns, this is a pipeline with a critic loop, where the critic is code rather than a second model.
The code below ran on Python 3.15 with pandas 3.1.1 and pytest 9.2.0, in a fresh venv. Only pandas is needed for the main pipeline; the Ollama call uses the standard library.
A dataset with known defects#
Generating the mess yourself has one big advantage over a public dataset: you know the right answer, so you can tell whether the cleaning was correct and not only whether it looked tidy.
import numpy as np
import pandas as pd
rng = np.random.default_rng(7)
n = 500
cities = ["Bengaluru", "Mumbai", "Delhi", "Pune"]
typos = {"Bengaluru": ["bengaluru", "Bangalore", "Banglore", " Bengaluru "],
"Mumbai": ["mumbai", "Bombay", "Mumbai "],
"Delhi": ["delhi", "New Delhi", "Dehli"],
"Pune": ["pune", "PUNE"]}
dates = pd.Timestamp("2026-01-01") + pd.to_timedelta(rng.integers(0, 300, n), unit="D")
fmts = ["%Y-%m-%d", "%d/%m/%Y", "%d %b %Y", "%Y/%m/%d"]
city = rng.choice(cities, n)
df = pd.DataFrame({
"order_id": np.arange(1001, 1001 + n),
"order_date": [d.strftime(fmts[i % 4]) for i, d in enumerate(dates)],
"city": [rng.choice(typos[c]) if rng.random() < 0.25 else c for c in city],
"amount": rng.lognormal(7, 0.5, n).round(2),
"quantity": rng.integers(1, 6, n).astype(float),
})
df.loc[rng.choice(n, 6, replace=False), "amount"] = [250000.0, 189000.0, -500.0, 0.0, 999999.0, -120.0]
df.loc[rng.choice(n, 40, replace=False), "quantity"] = np.nan
df.loc[rng.choice(n, 15, replace=False), "city"] = np.nan
df.loc[rng.choice(n, 5, replace=False), "order_date"] = "not a date"
df = pd.concat([df, df.sample(20, random_state=1)], ignore_index=True) # exact duplicates
df.to_csv("orders_messy.csv", index=False)
(520, 5)
order_id order_date city amount quantity
1001 2026-10-11 Mumbai 550.06 4.0
1002 07/07/2026 Mumbai 1163.84 2.0
1003 25 Jul 2026 NaN 3715.36 4.0
1004 2026/09/27 Delhi 1944.02 1.0
So the file has 520 rows, 20 of them exact copies, four date formats plus five junk strings, 16 spellings of four cities, six bad amounts and 40 missing quantities.
Agent 1: the profiler#
The profiler is pandas and nothing else. Its output is the only view of the data the planner gets, so what it leaves out, the planner cannot fix.
def profile(df: pd.DataFrame, max_values: int = 50) -> dict:
out = {"rows": len(df), "duplicate_rows": int(df.duplicated().sum()), "columns": {}}
for col in df.columns:
s = df[col]
info = {"dtype": str(s.dtype), "nulls": int(s.isna().sum()), "unique": int(s.nunique())}
if pd.api.types.is_numeric_dtype(s):
q1, q3 = s.quantile([0.25, 0.75])
iqr = q3 - q1
lo, hi = q1 - 1.5 * iqr, q3 + 1.5 * iqr
info.update(min=float(s.min()), median=float(s.median()), max=float(s.max()),
iqr_bounds=[round(lo, 2), round(hi, 2)],
iqr_outliers=int(((s < lo) | (s > hi)).sum()),
negatives=int((s < 0).sum()))
else:
vc = s.value_counts()
info["top_values"] = {str(k): int(v) for k, v in vc.head(max_values).items()}
info["values_shown"] = f"{min(len(vc), max_values)} of {len(vc)}"
info["sample_raw"] = [str(v) for v in s.dropna().drop_duplicates().head(8)]
out["columns"][col] = info
return out
Part of the output for amount:
"amount": {"dtype": "float64", "nulls": 0, "unique": 500, "min": -500.0,
"median": 1078.955, "max": 999999.0,
"iqr_bounds": [-507.69, 2871.33], "iqr_outliers": 24, "negatives": 2}
The IQR rule flags 24 rows. Only three are planted errors (250000, 189000 and 999999). The lower fence sits at -507.69, so the planted -500, -120 and 0 are not flagged at all, and the other 21 flagged rows are ordinary large orders from a skewed distribution. A plan that drops "IQR outliers" would throw away real revenue. That is why the plan format below uses a business threshold (amount above 50,000) and a sign check, not a statistical fence.
The first version of this profiler kept only the top 12 values per column. "Bangalore" appeared 8 times and fell outside the top 12, so the local model never saw it and never mapped it. The validator caught the leftover value on every attempt. Showing every value for low-cardinality columns, plus the values_shown count so the model knows when a list is cut, fixed it.
Agent 2: the planner, behind one method#
The planner gets the profile and, on a retry, the list of checks the last plan failed. It returns text that should be JSON. Nothing else in the system knows whether a model is involved.
class Planner:
"""Returns a JSON string: {"steps": [{"op": ..., "column": ..., ...}]}"""
def propose(self, profile: dict, feedback: list[str] | None = None) -> str:
raise NotImplementedError
For a repeatable walk-through, a scripted fake replays three fixed plans: one that tries to run arbitrary code, one that is valid but too aggressive, and a good one.
import json
CITY_MAP = {"bangalore": "bengaluru", "banglore": "bengaluru", "bombay": "mumbai",
"new delhi": "delhi", "dehli": "delhi"}
DATES = ["%Y-%m-%d", "%Y/%m/%d", "%d/%m/%Y", "%d %b %Y"]
EVIL_PLAN = {"steps": [{"op": "run_python", "code": "df = df[df.amount < 1e4]"}]}
BAD_PLAN = {"steps": [
{"op": "drop_duplicates"},
{"op": "parse_dates", "column": "order_date", "formats": DATES},
{"op": "drop_rows_where", "column": "quantity", "condition": "null"},
{"op": "drop_rows_where", "column": "city", "condition": "null"},
{"op": "drop_rows_where", "column": "order_date", "condition": "null"},
{"op": "strip_lower", "column": "city"},
]}
GOOD_PLAN = {"steps": [
{"op": "drop_duplicates"},
{"op": "parse_dates", "column": "order_date", "formats": DATES},
{"op": "drop_rows_where", "column": "order_date", "condition": "null"},
{"op": "strip_lower", "column": "city"},
{"op": "map_values", "column": "city", "mapping": CITY_MAP},
{"op": "fill_missing", "column": "city", "strategy": "constant", "value": "unknown"},
{"op": "drop_rows_where", "column": "amount", "condition": "zero_or_negative"},
{"op": "drop_rows_where", "column": "amount", "condition": "greater_than", "value": 50000},
{"op": "fill_missing", "column": "quantity", "strategy": "median"},
]}
class ScriptedPlanner(Planner):
"""Stands in for the model. Replays fixed plans in order, one per call."""
def __init__(self, plans):
self.plans, self.calls = list(plans), 0
def propose(self, profile, feedback=None):
plan = self.plans[min(self.calls, len(self.plans) - 1)]
self.calls += 1
return json.dumps(plan)
The real model call comes later. Swapping it in changes one constructor argument.
Agent 3: the executor and its whitelist#
The executor never calls exec or eval on anything the model wrote. Each allowed operation is a named Python function, and the plan can only pick among them and fill in arguments.
def _drop_duplicates(df, step):
return df.drop_duplicates(subset=step.get("subset"), keep="first")
def _strip_lower(df, step):
c = step["column"]
df[c] = df[c].str.strip().str.lower()
return df
def _map_values(df, step):
c = step["column"]
norm = lambda v: v.strip().lower() if isinstance(v, str) else v
mapping = {norm(k): norm(v) for k, v in step["mapping"].items()} # "Bangalore " == "bangalore"
df[c] = df[c].map(lambda v: mapping.get(norm(v), norm(v)) if isinstance(v, str) else v)
return df
def _parse_dates(df, step):
c = step["column"]
raw = df[c]
parsed = pd.Series(pd.NaT, index=df.index, dtype="datetime64[ns]")
for fmt in step["formats"]: # first format that matches wins, per row
todo = parsed.isna()
parsed[todo] = pd.to_datetime(raw[todo], format=fmt, errors="coerce")
df[c] = parsed
return df
def _fill_missing(df, step):
c, how = step["column"], step["strategy"]
value = {"median": lambda s: s.median(), "mode": lambda s: s.mode().iloc[0],
"constant": lambda s: step["value"]}[how](df[c])
df[c] = df[c].fillna(value)
return df
CONDITIONS = {"null": lambda s, v: s.isna(), "zero_or_negative": lambda s, v: s <= 0,
"greater_than": lambda s, v: s > v}
def _drop_rows_where(df, step):
return df[~CONDITIONS[step["condition"]](df[step["column"]], step.get("value"))]
OPS = {"drop_duplicates": _drop_duplicates, "strip_lower": _strip_lower,
"map_values": _map_values, "parse_dates": _parse_dates,
"fill_missing": _fill_missing, "drop_rows_where": _drop_rows_where}
REQUIRED = {"strip_lower": {"column"}, "map_values": {"column", "mapping"},
"parse_dates": {"column", "formats"}, "fill_missing": {"column", "strategy"},
"drop_rows_where": {"column", "condition"}}
ALLOWED_FORMATS = {"%Y-%m-%d", "%d/%m/%Y", "%d %b %Y", "%Y/%m/%d", "%m/%d/%Y"}
Two details came from failures. _map_values normalises the mapping keys itself, because the local model wrote keys like "Bangalore" after asking for a lowercase step, so nothing matched. And CONDITIONS holds lambdas instead of precomputed masks: the first version built all three masks eagerly and crashed when s < 0 ran on a string column.
Dates get explicit formats tried in order, not format="mixed". Here is why, on pandas 3.1.1:
s = pd.Series(['2026-10-11', '07/08/2026', '25 Jul 2026', '2026/09/27', 'not a date'])
pd.to_datetime(s, format='mixed', errors='coerce')
pd.to_datetime(s, format='mixed', dayfirst=True, errors='coerce')
[2026-10-11, 2026-07-08, 2026-07-25, 2026-09-27, NaT]
[2026-11-10, 2026-08-07, 2026-07-25, 2026-09-27, NaT]
Without dayfirst, the Indian date 07/08/2026 becomes 8 July. With it, the ISO date 2026-10-11 becomes 10 November. Neither setting is right for this file. The pandas docs warn that dayfirst is a preference, not a strict rule. A fixed list of strftime formats tried in a known order removes the guessing.
Before any step runs, a static check reads the plan:
def check_plan(plan_text: str, df: pd.DataFrame) -> tuple[list[dict], list[str]]:
"""Static checks before anything touches the data."""
errors = []
try:
steps = json.loads(plan_text)["steps"]
except (json.JSONDecodeError, KeyError, TypeError) as exc:
return [], [f"plan is not valid JSON with a 'steps' list: {exc}"]
for i, step in enumerate(steps):
op = step.get("op")
if op not in OPS:
errors.append(f"step {i}: op '{op}' is not on the whitelist")
continue
missing = REQUIRED.get(op, set()) - step.keys()
if missing:
errors.append(f"step {i}: {op} needs {sorted(missing)}")
col = step.get("column")
if col is not None and col not in df.columns:
errors.append(f"step {i}: unknown column '{col}'")
bad_fmt = set(step.get("formats", [])) - ALLOWED_FORMATS
if op == "parse_dates" and bad_fmt:
errors.append(f"step {i}: date formats not allowed: {sorted(bad_fmt)}")
if op == "drop_rows_where" and step.get("condition") not in CONDITIONS:
errors.append(f"step {i}: unknown condition")
if op == "fill_missing" and step.get("strategy") not in {"median", "mode", "constant"}:
errors.append(f"step {i}: unknown fill strategy")
return steps, errors
def execute(df: pd.DataFrame, steps: list[dict]):
out, log, removed = df.copy(), [], []
for step in steps:
before = out
out = OPS[step["op"]](out.copy(), step)
gone = before.loc[before.index.difference(out.index)]
if len(gone):
removed.append(gone.assign(_reason=f"{step['op']}:{step.get('column', '*')}"))
log.append(f"{step['op']:<16} {step.get('column', '*'):<11} rows {len(before)} -> {len(out)}")
quarantine = pd.concat(removed) if removed else df.iloc[0:0]
return out, log, quarantine
execute works on copies and never deletes a row outright. Every dropped row lands in a quarantine frame tagged with the step that removed it, so a human can review it later.
Agent 4: the validator#
The validator does not read the plan. It compares the raw frame with the cleaned one against rules you write once per dataset. A plan can look sensible and still do damage, so the validator judges only the output.
RULES = {
"max_row_loss": 0.08, # beyond exact duplicates
"no_nulls": ["order_id", "order_date", "city", "amount", "quantity"],
"allowed": {"city": {"bengaluru", "mumbai", "delhi", "pune", "unknown"}},
"ranges": {"amount": (0.01, 50000), "quantity": (1, 100)},
"dtypes": {"order_date": "datetime64", "amount": "float", "quantity": "float"},
"date_range": ("2025-01-01", "2026-11-22"),
}
def validate(raw: pd.DataFrame, clean: pd.DataFrame, rules=RULES) -> list[str]:
problems = []
if list(clean.columns) != list(raw.columns):
problems.append(f"schema changed: {list(raw.columns)} -> {list(clean.columns)}")
expected = len(raw) - int(raw.duplicated().sum())
loss = 1 - len(clean) / expected
if loss > rules["max_row_loss"]:
problems.append(f"dropped {loss:.1%} of non-duplicate rows (limit {rules['max_row_loss']:.0%})")
if clean["order_id"].duplicated().any():
problems.append("order_id is not unique")
for c in rules["no_nulls"]:
if c in clean and clean[c].isna().any():
problems.append(f"{c} still has {int(clean[c].isna().sum())} nulls")
for c, allowed in rules["allowed"].items():
bad = set(clean[c].dropna().unique()) - allowed
if bad:
problems.append(f"{c} has values outside the allowed set: {sorted(bad)}")
for c, (lo, hi) in rules["ranges"].items():
n = int(((clean[c] < lo) | (clean[c] > hi)).sum())
if n:
problems.append(f"{c}: {n} values outside [{lo}, {hi}]")
lo, hi = (pd.Timestamp(d) for d in rules["date_range"])
if pd.api.types.is_datetime64_any_dtype(clean["order_date"]):
n = int(((clean["order_date"] < lo) | (clean["order_date"] > hi)).sum())
if n:
problems.append(f"order_date: {n} dates outside {rules['date_range']}")
for c, kind in rules["dtypes"].items():
if not str(clean[c].dtype).startswith(kind):
problems.append(f"{c} dtype is {clean[c].dtype}, expected {kind}")
return problems
The 8% row-loss budget is a choice for this file. Pick yours from how much data the downstream report can afford to lose, and write it down before you see any plan.
The loop that ties them together#
def run(df, planner, max_attempts=3, verbose=False):
prof = profile(df)
feedback = None
for attempt in range(1, max_attempts + 1):
print(f"\n=== attempt {attempt} ===")
plan_text = planner.propose(prof, feedback)
if verbose:
print("plan:", plan_text)
steps, errors = check_plan(plan_text, df)
if errors:
print("plan rejected before execution:", *errors, sep="\n ")
feedback = errors
continue
try:
clean, log, quarantine = execute(df, steps)
except Exception as exc: # a step that crashes is a bad plan, not a crash
print("execution failed:", repr(exc))
feedback = [f"execution failed: {exc!r}"]
continue
print(*log, sep="\n")
problems = validate(df, clean)
if not problems:
print("validator: PASS")
print(report(df, clean))
print("quarantined rows by reason:", quarantine["_reason"].value_counts().to_dict())
return clean
print("validator: FAIL", *problems, sep="\n ")
feedback = problems
print("giving up; raw data left untouched")
return None
The before/after report is a small helper:
def report(raw, clean):
rows = []
for c in raw.columns:
rows.append({"column": c, "nulls_before": int(raw[c].isna().sum()),
"nulls_after": int(clean[c].isna().sum()),
"unique_before": int(raw[c].nunique()), "unique_after": int(clean[c].nunique())})
return pd.DataFrame(rows).to_string(index=False)
Run 1: scripted planner#
=== attempt 1 ===
plan rejected before execution:
step 0: op 'run_python' is not on the whitelist
=== attempt 2 ===
drop_duplicates * rows 520 -> 500
parse_dates order_date rows 500 -> 500
drop_rows_where quantity rows 500 -> 460
drop_rows_where city rows 460 -> 447
drop_rows_where order_date rows 447 -> 443
strip_lower city rows 443 -> 443
validator: FAIL
dropped 11.4% of non-duplicate rows (limit 8%)
city has values outside the allowed set: ['bangalore', 'banglore', 'bombay', 'dehli', 'new delhi']
amount: 6 values outside [0.01, 50000]
=== attempt 3 ===
drop_duplicates * rows 520 -> 500
parse_dates order_date rows 500 -> 500
drop_rows_where order_date rows 500 -> 495
strip_lower city rows 495 -> 495
map_values city rows 495 -> 495
fill_missing city rows 495 -> 495
drop_rows_where amount rows 495 -> 492
drop_rows_where amount rows 492 -> 489
fill_missing quantity rows 489 -> 489
validator: PASS
column nulls_before nulls_after unique_before unique_after
order_id 0 0 500 489
order_date 0 0 410 246
city 16 0 16 5
amount 0 0 500 489
quantity 40 0 5 5
quarantined rows by reason: {'drop_duplicates:*': 20, 'drop_rows_where:amount': 6, 'drop_rows_where:order_date': 5}
The code-execution plan never reached the data. The aggressive plan ran on a copy and failed on three separate rules. The good plan kept 489 of 500 unique orders (2.2% loss) and every removed row is in quarantine with its reason. order_date shows 0 nulls before cleaning because the five junk values were strings, not blanks; they only became visible as nulls after parsing.
Run 2: a real local model#
The same loop with Qwen3 4B Instruct, served by Ollama 0.40.16 on a laptop, temperature 0. Ollama's /api/chat accepts a JSON schema in the format field and constrains decoding to it (structured outputs docs, chat API).
import json, urllib.request
from team import Planner, ALLOWED_FORMATS
OP_GUIDE = """Allowed ops (anything else is rejected):
- drop_duplicates {}
- strip_lower {column}
- map_values {column, mapping: {old: new}} applied after strip_lower, so keys are lowercase
- parse_dates {column, formats: [strftime strings]} tried in order per row.
Allowed formats: %Y-%m-%d, %Y/%m/%d, %d/%m/%Y, %d %b %Y, %m/%d/%Y
- fill_missing {column, strategy: median|mode|constant, value?}
- drop_rows_where {column, condition: null|zero_or_negative|greater_than, value?}
Target: city values in {bengaluru, mumbai, delhi, pune, unknown}; amount in (0, 50000];
quantity has no nulls; drop at most 8% of rows beyond exact duplicates. Dates are Indian (day first)."""
SCHEMA = {"type": "object", "required": ["steps"], "properties": {"steps": {
"type": "array", "items": {"type": "object", "required": ["op"], "properties": {
"op": {"enum": ["drop_duplicates", "strip_lower", "map_values", "parse_dates",
"fill_missing", "drop_rows_where"]},
"column": {"enum": ["order_id", "order_date", "city", "amount", "quantity"]},
"formats": {"type": "array", "items": {"enum": sorted(ALLOWED_FORMATS)}},
"mapping": {"type": "object"}, "strategy": {"enum": ["median", "mode", "constant"]},
"condition": {"enum": ["null", "zero_or_negative", "greater_than"]},
"value": {}}}}}}
class OllamaPlanner(Planner):
def __init__(self, model="qwen3:4b-instruct", url="http://localhost:11434/api/chat"):
self.model, self.url = model, url
def propose(self, profile, feedback=None):
msg = f"{OP_GUIDE}\n\nProfile:\n{json.dumps(profile)}\n\nReturn a cleaning plan as JSON."
if feedback:
msg += "\n\nYour previous plan failed these checks; fix them:\n- " + "\n- ".join(feedback)
body = {"model": self.model, "stream": False, "format": SCHEMA,
"options": {"temperature": 0},
"messages": [{"role": "user", "content": msg}]}
req = urllib.request.Request(self.url, json.dumps(body).encode(),
{"Content-Type": "application/json"})
with urllib.request.urlopen(req, timeout=300) as r:
return json.loads(r.read())["message"]["content"]
To use a hosted model instead, keep the class shape and replace the body of propose with your provider's call. No API key is needed for anything in this post.
The first attempt used a loose schema ("type": "string" for every field). All three plans were rejected before execution, each for the same invented format:
=== attempt 1 ===
plan rejected before execution:
step 3: date formats not allowed: ['%d/%m/%y']
=== attempt 2 ===
plan rejected before execution:
step 3: date formats not allowed: ['%d-%m-%Y', '%d/%m/%y']
=== attempt 3 ===
plan rejected before execution:
step 3: date formats not allowed: ['%d/%m/%y']
giving up; raw data left untouched
The feedback named the bad format and the model wrote it again. Moving the whitelist into the schema as enum values made that error impossible to produce. After that change, plus the full value list in the profile and key normalisation in the executor, the run looked like this (step logs and plans trimmed; the validator lines are verbatim):
=== attempt 1 ===
... 9 steps, rows 520 -> 479
validator: FAIL
order_date still has 5 nulls
quantity still has 38 nulls
=== attempt 2 ===
... 8 steps, rows 520 -> 457
validator: FAIL
dropped 8.6% of non-duplicate rows (limit 8%)
order_date still has 4 nulls
city still has 13 nulls
city has values outside the allowed set: ['bombay']
amount: 3 values outside [0.01, 50000]
=== attempt 3 ===
... 11 steps, rows 520 -> 450
validator: FAIL
dropped 10.0% of non-duplicate rows (limit 8%)
giving up; raw data left untouched
Attempts did not improve steadily. Attempt 2 cleared the quantity nulls from attempt 1 by deleting those rows, then left the city nulls in place, dropped bombay from its mapping and stopped removing amounts above 50,000. The model's mapping in attempt 3 was correct for every city, but it chose to drop the 39 rows with missing quantity instead of filling them, which alone blew the budget. And it filled missing cities with "strategy": "mode", which turns 15 unknown cities into "pune", the most common value. The validator passed that part, which leads to the next section.
A 4B model on a laptop is a weak planner for this job. That is useful to know: the safety of the system did not depend on the model being good, and the raw file was never modified.
What the validator cannot catch#
The rules check the shape of the output. They cannot tell a correct value from a plausible wrong one, and this run had both kinds:
- Mode imputation on
citywrites a valid value that is wrong for most of those rows. Every rule passes. If the city split matters downstream, add a rule that the share of each city may move by at most a point or two after cleaning. - Of the 124 unique rows in
dd/mm/yyyyform, 64 have a day of 12 or less, so%m/%d/%Ywould also parse them, silently, to a different date. Both are in the allowed list. A plan that puts the US format first passes every check. The only defence is knowing the source: remove%m/%d/%Yfrom the whitelist for Indian data, or check parsed dates against a second field such as a ship date. - Six bad amounts were planted, and the good plan quarantines exactly six. On real data you will not know the true count, which is why the quarantine table exists.
Testing the team#
Every bad plan you meet in production becomes a regression test. Three tests cover the main promises: known plans keep their verdicts, unknown columns are refused, and the raw frame is never mutated.
def outcome(plan):
steps, errors = check_plan(json.dumps(plan), df)
if errors:
return "rejected"
clean, _, _ = execute(df, steps)
return "pass" if not validate(df, clean) else "fail"
def test_known_plans():
assert outcome(GOOD_PLAN) == "pass"
assert outcome(BAD_PLAN) == "fail"
assert outcome(EVIL_PLAN) == "rejected"
def test_unknown_column_is_rejected():
assert outcome({"steps": [{"op": "strip_lower", "column": "town"}]}) == "rejected"
def test_raw_frame_is_never_modified():
before = df.copy()
execute(df, check_plan(json.dumps(GOOD_PLAN), df)[0])
pd.testing.assert_frame_equal(df, before)
... [100%]
3 passed in 0.76s
For the model side, save each real plan the local model writes, with the profile it saw, and score them against the validator as a fixed set. That gives a pass rate you can compare across models and prompt changes instead of judging one run by eye.
Failure modes, in order of how often they showed up here#
- Invented arguments (date formats, conditions). Fixed with
enumin the decoding schema, kept as a check in code too. - Missing context. A truncated value list hid "Bangalore". Fixed in the profiler.
- Format mismatch between steps. The model wrote mixed-case keys for a lowercase column. Fixed by normalising in the executor.
- Over-deletion. Dropping rows is the easiest way to make nulls go away. Caught by the row-loss budget.
- Plausible wrong values (mode fill, ambiguous dates). Not caught by shape rules; needs distribution checks or domain rules.
- Executor crashes on a bad combination of arguments. Caught by the
tryaroundexecuteand returned to the planner as feedback.
The next step for this design is to raise the planner's pass rate with a bigger model or a few example plans in the prompt, and to add a distribution rule for city. The whitelist, quarantine and validator stay as they are.