How to Build an AI Agent That Cleans and Deduplicates Business Data
Most data cleaning does not need an LLM. This guide uses rules for the easy 90%, a model for the ambiguous cases, and a change log so every edit can be reviewed and reverted.
By OnChain Team8 min read
A good AI data-cleaning agent uses the model sparingly. Deterministic code profiles the data, normalises formats and finds candidate duplicates; a language model is asked only about the ambiguous cases, such as whether "ACME corp." and "Acme Corporation Ltd" at the same address are one customer; and every change is written to a log that a person can review, approve or revert. That keeps the agent cheap, fast and auditable. This guide builds it in Python with pandas, RapidFuzz and the Claude API, using a customer table as the example.
Why not just ask a model to clean the file?
You could paste rows into a chat and ask for a cleaned version. It breaks down quickly:
- Scale. Real tables have tens of thousands of rows, far more than you want to send through a model one by one.
- Silent changes. A model rewriting rows can alter values you did not ask it to touch, and you cannot easily tell which.
- No audit trail. For customer, financial or compliance data, you need to know why each change was made.
The design below fixes all three: the model never edits the table directly, it only answers narrow questions, and your code applies the answers and logs them.
The pipeline
Main flowSecondarySwipe to see the whole diagram →
Step 1: Set up
pip install pandas rapidfuzz anthropic pydantic
export ANTHROPIC_API_KEY=...The example table has id, name, email, phone, country and billing_address columns. Swap in your own.
Step 2: Profile before you change anything
Profiling tells you what is actually wrong, so you fix that rather than what you assume is wrong. It is also the "before" number you will compare against.
import pandas as pd
def profile(df: pd.DataFrame) -> pd.DataFrame:
rows = []
for col in df.columns:
s = df[col]
rows.append(
{
"column": col,
"missing": int(
s.isna().sum() + (s.astype(str).str.strip() == "").sum()
),
"distinct": int(s.nunique(dropna=True)),
"example": s.dropna().astype(str).head(3).tolist(),
}
)
return pd.DataFrame(rows)
df = pd.read_csv("customers.csv", dtype=str)
print(f"{len(df)} rows, {df.duplicated().sum()} exact duplicate rows")
print(profile(df).to_string(index=False))Step 3: Normalise with plain rules
Most "duplicates" are formatting differences. Fix those deterministically and many problems disappear before any model is involved. Keep the original columns and add normalised ones, so nothing is lost.
import re
import pandas as pd
LEGAL_SUFFIXES = (
r"\b(inc|incorporated|ltd|limited|llc|gmbh|srl|sa|bv|plc|corp|corporation|co)\b\.?"
)
def norm_name(value: str | None) -> str:
if not isinstance(value, str):
return ""
v = value.casefold()
v = re.sub(LEGAL_SUFFIXES, " ", v)
v = re.sub(r"[^\w\s]", " ", v)
return re.sub(r"\s+", " ", v).strip()
def norm_email(value: str | None) -> str:
return value.strip().casefold() if isinstance(value, str) else ""
def norm_phone(value: str | None) -> str:
return re.sub(r"\D", "", value) if isinstance(value, str) else ""
def normalise(df: pd.DataFrame) -> pd.DataFrame:
out = df.copy()
out["name_n"] = out["name"].map(norm_name)
out["email_n"] = out["email"].map(norm_email)
out["phone_n"] = out["phone"].map(norm_phone)
out["domain_n"] = out["email_n"].str.split("@").str[-1].fillna("")
return outStep 4: Find candidate duplicates with blocking and fuzzy matching
Comparing every row with every other row is slow: 50,000 rows means over a billion pairs. Blocking compares only rows that share something cheap to check, such as the email domain or the first letters of the name. RapidFuzz then scores each pair quickly.
from itertools import combinations
import pandas as pd
from rapidfuzz import fuzz
FREE_MAIL = {"gmail.com", "yahoo.com", "outlook.com", "hotmail.com", "icloud.com"}
def blocks(df: pd.DataFrame):
keys = df.apply(
lambda r: (
r["domain_n"]
if r["domain_n"] and r["domain_n"] not in FREE_MAIL
else r["name_n"][:4]
),
axis=1,
)
for _, idx in df.groupby(keys).groups.items():
if (
1 < len(idx) <= 500
): # skip giant blocks; split them with a second key instead
yield list(idx)
def score(a: pd.Series, b: pd.Series) -> float:
name = fuzz.token_sort_ratio(a["name_n"], b["name_n"])
same_email = a["email_n"] and a["email_n"] == b["email_n"]
same_phone = a["phone_n"] and a["phone_n"] == b["phone_n"]
return min(100.0, name + (10 if same_email else 0) + (5 if same_phone else 0))
def candidate_pairs(df: pd.DataFrame) -> pd.DataFrame:
pairs = []
for idx in blocks(df):
for i, j in combinations(idx, 2):
s = score(df.loc[i], df.loc[j])
if s >= 80:
pairs.append({"left": i, "right": j, "score": s})
return pd.DataFrame(pairs, columns=["left", "right", "score"])Tune the weights and thresholds on a sample you have labelled by hand. Fifty labelled pairs are enough to tell whether 80 and 95 are sensible cut-offs for your data.
Step 5: Ask the model only about the grey zone
Pairs above 95 are merged by rule. Pairs between 80 and 95 go to the model with the full records and a narrow question. Structured outputs guarantee the answer fits a schema your code can act on.
import json
from typing import Literal
import anthropic
from pydantic import BaseModel
client = anthropic.Anthropic()
class MatchDecision(BaseModel):
same_entity: bool
confidence: Literal["high", "medium", "low"]
reason: str # one sentence, shown to the reviewer
SYSTEM = (
"You decide whether two customer records refer to the same company. "
"Use only the fields provided. "
"Different legal entities in the same group are NOT the same entity. "
"If the evidence is weak or conflicting, answer same_entity=false with low confidence."
)
def judge(left: dict, right: dict) -> MatchDecision | None:
response = client.messages.parse(
model="claude-opus-5-5",
max_tokens=1000,
system=SYSTEM,
messages=[
{
"role": "user",
"content": f"Record A: {json.dumps(left)}\nRecord B: {json.dumps(right)}",
}
],
output_format=MatchDecision,
)
if response.stop_reason == "refusal":
return None # treat as "needs human review"
return response.parsed_outputSend only the columns the decision needs. If the table holds personal data, check your data processing agreements before sending any of it to an external API, and consider masking fields the model does not need, such as full phone numbers.
For large volumes, submit these questions through the Message Batches API, which is cheaper and does not need answers in real time.
Step 6: Fill missing values only from evidence
Filling gaps is where an unsupervised agent does the most damage, by inventing plausible values. Use rules first, and accept a model's answer only if it comes from another field in the same row and passes validation.
import pandas as pd
from pydantic import BaseModel
COUNTRIES = {"RO", "DE", "NL", "FR", "US", "GB"} # your allowed values
PHONE_PREFIX = {"40": "RO", "49": "DE", "31": "NL", "33": "FR", "1": "US", "44": "GB"}
def country_by_rule(row: pd.Series) -> tuple[str | None, str]:
phone = row.get("phone_n", "")
for prefix, code in sorted(PHONE_PREFIX.items(), key=lambda kv: -len(kv[0])):
if phone.startswith(prefix):
return code, f"phone prefix +{prefix}"
return None, ""
class CountryGuess(BaseModel):
country: str | None # ISO code, or null if the row does not say
evidence_field: str | None # which field the answer comes from
def country_by_model(row: pd.Series, client) -> tuple[str | None, str]:
response = client.messages.parse(
model="claude-opus-5-5",
max_tokens=500,
system=(
"Return the ISO 3166-1 alpha-2 country code only if a field "
"in the record states it. "
"Otherwise return null. Never guess from a company name."
),
messages=[
{
"role": "user",
"content": str(row[["billing_address", "email"]].to_dict()),
}
],
output_format=CountryGuess,
)
guess = response.parsed_output
if response.stop_reason == "refusal" or not guess or guess.country not in COUNTRIES:
return None, ""
return guess.country, f"model, from {guess.evidence_field}"If neither rule nor model produces a validated answer, leave the value empty and flag the row. An honest gap is better than a confident wrong value.
Step 7: Log every change and apply only what is approved
The agent produces proposals, not edits. Each proposal records the row, the column, the old and new value, who or what proposed it, and why.
from dataclasses import dataclass, asdict
import pandas as pd
@dataclass
class Change:
row_id: str
column: str
old: str | None
new: str | None
reason: str
source: str # "rule" or "model"
approved: bool = False
def propose_merge(
df: pd.DataFrame, keep: int, drop: int, reason: str, source: str
) -> list[Change]:
# Keep the older record; mark the other as merged into it, so nothing is deleted
return [
Change(
df.loc[drop, "id"], "merged_into", None, df.loc[keep, "id"], reason, source
)
]
def apply(df: pd.DataFrame, changes: list[Change]) -> pd.DataFrame:
out = df.set_index("id").copy()
for c in changes:
if c.approved:
out.loc[c.row_id, c.column] = c.new
return out.reset_index()
def revert(df: pd.DataFrame, changes: list[Change]) -> pd.DataFrame:
out = df.set_index("id").copy()
for c in reversed(changes):
if c.approved:
out.loc[c.row_id, c.column] = c.old
return out.reset_index()
# Save proposals for review:
# pd.DataFrame([asdict(c) for c in changes]).to_csv("proposed_changes.csv")A reviewer can approve rule-based changes in bulk and look at model-based ones individually. Merging by marking rows (merged_into) instead of deleting them means any merge can be undone later.
Step 8: Measure the result
Run the profile again after applying changes and report the difference: duplicates removed, values filled, rows flagged for a human. Keep the labelled sample from step 4 and re-check precision whenever you change thresholds, prompts or the model. If merges start going wrong, you want to find out from that sample, not from a customer.
What we would do
Put most of the effort into normalisation and blocking. They are cheap, fast and fully predictable, and they shrink the number of questions the model has to answer. Use the model for judgement on genuinely ambiguous cases, require it to point to evidence, and never let it write to the table directly. The change log is not an extra; it is what makes the agent safe to use on data that matters.
We build AI agents and data platforms like this in our AI development work.