AI Data Cleaning in Practice: Duplicates, Gaps, Formats, Outliers

By the end you can take a messy spreadsheet and make it computable: the rule behind each action, what to hand to AI, and how to leave a trail.

1. Answer three questions first

• What counts as a duplicate? Two orders from one customer, duplicate or legitimate? The basis is a business key such as order id, never visual similarity.
• Can missing values be filled? A phone number short one digit cannot be guessed. A derived amount must be flagged as derived.
• Is the outlier real or a typo? An order of 500,000 is a large account or an extra zero. That goes to the source document, not an algorithm.

Without these answers, cleaning becomes endless rework.

2. Five actions and their rules

Deduplication. Sort by the business key, then check whether the whole row repeats. Exact duplicates: drop the later one. Same key with an updated status: keep the latest timestamp. Never use one-click remove duplicates; it can delete the row you wanted.

Missing values. Numeric columns affect sums, so imputation needs a flag column. Categorical columns can take an explicit "unknown". Rows missing order id, amount or date are usually dropped, with the count recorded.

Formats. Dates need one convention and a real date type, or sorting breaks. Amounts need one currency, unit and tax basis. Strip full-width characters and invisible characters in one pass.

Outliers. Flag with percentiles; do not delete. Split into entry errors such as negative amounts, genuine extremes such as a large account, and unit mismatches such as ten-thousand values in a yuan column. Only the first gets corrected.

Text. Normalizing "Co., Ltd." matters only if you aggregate by company. Splitting specification out of a product name matters only if you analyze by it. Aim for one canonical spelling per entity.

3. AI writes the rule, you keep the judgment

Ask AI to judge which records are wrong and it pattern-matches on surface features, expensively. Ask it to code the rules you already decided and it rarely fails.

DataFrame df with order_id, order_date (string, formats 2026/10/5, 2026-10-05, 20261005), amount (string, some with currency words), phone. Write pandas code to parse order_date, routing unparseable rows to bad_rows; convert amount to float; strip non-digits from phone and validate eleven digits. Keep originals, add _clean columns.

In Excel only, ask for "a column formula" instead.

4. Five situations you will hit

• Mixed date formats: have AI write a parser and export failures. Ten thousand rows by hand always has misses.
• Mixed units: if 12.5 and 125000 share a column, part of the data is probably in ten-thousands. Confirm by sampling, then convert.
• Two facts in one column: 2026-10-East-001 splits on a delimiter. Otherwise validate a regex on twenty rows first.
• Full-width characters: full-width and half-width digits form separate pivot groups, so convert every time.
• Splitting and merging: address into city and district; name plus phone into a key. Both create new columns.

5. Leave a trail

1. Keep originals, with results in new _clean columns.
2. Add dup_flag, missing_flag, outlier_flag and source.
3. Version filenames from raw_20261103 to clean_v1_20261103. Never "final2".

If cleaning ran in code, keep the script and document the rules in its header.

6. Red lines around personal data

Before sending a sheet anywhere online: replace customer names with codes, mask phones and ids, scale amounts. Headers can hold internal codes, and company data needs a version that can turn off model training.

7. The three-minute self-check

1. Row counts before, after, removed, and why.
2. Null rate on key columns near zero, or the rules missed something.
3. Sample ten rows by eye, confirming nothing was collapsed.
4. Recompute one total against the business number; a gap above one percent needs a look.

With cleaning done, analysis moves twice as fast. Continue with spreadsheet analysis with AI, see data work in WorkBuddy, and browse tools in the resource library. As of November 2026, upload limits vary.