--- title: "Cleaning and preparing messy data with AI" description: "Why cleaning matters most Most analysis errors start in the data, not the math. Duplicated rows, inconsistent spellings, mixed date formats and currency…" url: https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making/cleaning-data-with-ai updated: 2026-10-05 --- AI for Data Analysis & Decision Making · Foundations: AI as your analysis partner · lesson 3 of 16 · 12 min # Cleaning and preparing messy data with AI ## Why cleaning matters most Most analysis errors start in the data, not the math. Duplicated rows, inconsistent spellings, mixed date formats and currency confusion quietly distort every result downstream. AI assistants are excellent cleaning partners, because cleaning is repetitive, pattern-based work, as long as every change is visible and reversible. ## Start with a data quality audit Before changing anything, ask for an audit: ```text Profile this dataset with Python and report: - row count, column types, date range - missing values per column (count and %) - duplicate rows and duplicate IDs - distinct values for categorical columns (top 20 each) - numeric columns: min, max, mean, and suspicious values (negatives, zeros, extreme outliers) Do not modify the data yet. ``` This surfaces issues like "country" containing UK, U.K., United Kingdom and GB; revenue with negative values (refunds?); dates spanning an unexpected year. ## Common cleaning tasks AI handles well - **Standardizing categories:** mapping spelling variants to canonical values. Ask for the mapping table first, review it, then apply it. - **Parsing dates** in mixed formats. Be careful with ambiguous formats: is 03/04/2026 the 3rd of April or March 4th? Tell the AI which convention your source uses; if mixed, flag ambiguous rows rather than guessing. - **Splitting and combining fields:** full names, addresses, product codes. - **Fixing types:** numbers stored as text, currency symbols and thousands separators. - **Deduplicating:** exact duplicates, and fuzzy duplicates (for example "Acme Ltd" and "ACME Limited"), where AI can propose candidate matches for you to confirm. - **Categorizing free text:** tagging support tickets or survey comments into themes, with a defined category list. ## The review-then-apply pattern Never let an assistant silently "clean" your data. Use a two-step pattern: 1. **Propose:** "List every transformation you would apply and show a mapping table for category changes. Don't apply yet." 2. **Apply with a log:** "Apply the approved transformations in code. Output the cleaned file and a change log with row counts before and after each step." ```text Step Rows before Rows after Notes Remove exact duplicates 12,480 12,391 89 duplicates Drop test orders (email @ourco) 12,391 12,352 39 test orders Standardize country 12,352 12,352 14 variants -> 6 values Flag ambiguous dates 12,352 12,352 57 flagged, not dropped ``` The log makes your cleaning reproducible and defensible. ## Missing data: decisions, not defaults How you handle missing values can change conclusions. Options include leaving them as missing, excluding rows, filling with a default, or imputing. AI will happily fill blanks with averages if asked, but that can hide real patterns (for example, missing income data may be concentrated in one customer group). Ask the AI to show you *where* data is missing (by segment and over time) before deciding, and document the decision. ## Outliers: investigate before removing An order worth 100 times the average might be an error, a test, a bulk B2B purchase or your best customer. Ask the AI to list outliers with their full rows, then decide with business context. Removing outliers because they are inconvenient is a classic way to fool yourself. ## Worked example: a lead list from three sources A marketing team merges leads from an events spreadsheet, website forms and a purchased list. Issues found by the audit: company names in several variants, phone numbers in local and international formats (Pakistan, UAE and UK numbers), job titles in free text, and 6% duplicate emails. The AI proposes standard formats (international phone format, a job-level category from titles, a company matching table). The team reviews the fuzzy company matches, rejecting a few wrong merges (two different companies with similar names). The final log shows each step and count. Also noted: the purchased list's consent status is unclear, so it is kept separate until the team confirms it can be used lawfully for marketing under relevant data protection and e-marketing rules. ## Failure modes - Silent changes with no log. - Over-aggressive fuzzy matching merging different entities. - Guessing ambiguous dates. - Filling missing values without understanding why they are missing. ## Hands-on: a cleaning script with a built-in change log Ask the assistant for code in this shape, so every step records row counts: ```python import pandas as pd log = [] def step(df, name, fn): before = len(df) out = fn(df) log.append({"step": name, "rows_before": before, "rows_after": len(out)}) return out df = pd.read_csv("leads_merged.csv", dtype=str) df = step(df, "drop exact duplicates", lambda d: d.drop_duplicates()) df = step(df, "drop internal test leads", lambda d: d[~d["email"].str.endswith("@ourco.com", na=False)]) COUNTRY_MAP = {"uk": "GB", "u.k.": "GB", "united kingdom": "GB", "gb": "GB", "uae": "AE", "u.a.e": "AE", "united arab emirates": "AE", "ksa": "SA", "saudi arabia": "SA", "pakistan": "PK", "pk": "PK"} df = step(df, "standardize country", lambda d: d.assign( country=d["country"].str.strip().str.lower().map(COUNTRY_MAP).fillna("UNMAPPED"))) # Ambiguous dates: parse with the source's convention, flag failures, never guess df["created"] = pd.to_datetime(df["created_raw"], format="%d/%m/%Y", errors="coerce") df["date_flag"] = df["created"].isna() pd.DataFrame(log).to_csv("cleaning_log.csv", index=False) print(pd.DataFrame(log)) print("unmapped countries:", (df["country"] == "UNMAPPED").sum(), "| unparsed dates:", df["date_flag"].sum()) ``` The mapping table is written out for review, unmapped values are counted rather than silently dropped, and ambiguous dates are flagged, not guessed. ## Second worked example: a Dubai clinic's appointment data A clinic group merges appointment exports from three branches. The audit finds Arabic and English spellings of the same doctor, times recorded in two formats, and "no-show" coded as three different values. The analyst asks for a mapping table for doctors and statuses, reviews it with the operations manager (who spots two doctors with similar names who are different people), then applies it in code with a log. The no-show rate by branch changes noticeably after cleaning, because one branch had been coding late cancellations as no-shows. ## Going further Turn your cleaning steps into a reusable script with tests: for example, assert no duplicate IDs remain, all countries are in the approved list, and totals reconcile to the source system within a tolerance. AI can write these tests too; you review them once and run them every time new data arrives. ## Video lecture: Cleaning and preparing messy data with AI Lecture coming soon · 15 chapters · about 8 minutes. Read the full transcript below. 1. Cleaning data with AI 2. Why it matters 3. Audit first 4. What AI does well 5. Review, then apply 6. Decisions, not defaults 7. Example 1: merged lead lists 8. Example 2: Dubai clinic data 9. Watch me do it, part 1 10. Watch me do it, part 2 11. Categorizing free text 12. Common mistakes 13. Trustworthy cleaning 14. Recap 15. Try this now ## Lecture transcript ### Cleaning data with AI Here's a sentence that should make every analyst nervous: I asked the AI to clean the data, and it did. Cleaned how? Which rows were dropped? Which values were changed, and to what? If you can't answer, you don't have clean data. You have mystery data. In this lecture you'll learn why cleaning matters most, how to audit data quality before touching anything, the review-then-apply pattern, how to treat missing values and outliers as decisions, and a cleaning script with a built-in change log. ### Why it matters Why does cleaning matter most? Because most analysis errors start in the data, not the math. Duplicated rows, inconsistent spellings, mixed date formats and currency confusion quietly distort every result downstream. AI assistants are excellent cleaning partners, because cleaning is repetitive, pattern-based work. But only if every change is visible and reversible. Think of it like editing a legal contract. You'd accept tracked changes from a clever assistant. You'd never accept a silently rewritten document. ### Audit first Start with an audit, and change nothing yet. Ask the assistant to profile the dataset with code: row count, column types, date range, missing values per column with counts and percentages, duplicate rows and duplicate ids, distinct values for categorical columns, and for numeric columns the minimum, maximum and mean, with suspicious values like negatives, zeros and extreme outliers. This surfaces things like country containing UK, U K with dots, United Kingdom and G B, negative revenue that might be refunds, and dates in an unexpected year. ### What AI does well What does AI handle well? Standardizing categories, by mapping spelling variants to canonical values. Parsing dates in mixed formats, with care: is zero three slash zero four the third of April or March the fourth? Tell the AI your source's convention, and if it's mixed, flag ambiguous rows instead of guessing. Splitting and combining fields. Fixing types, like numbers stored as text with currency symbols. Deduplicating, including fuzzy matches like Acme Limited and ACME Ltd, where AI proposes candidates for you to confirm. And categorizing free text into a defined list of themes. ### Review, then apply Here's the core pattern: review, then apply. Never let an assistant silently clean your data. Step one, propose: list every transformation you'd apply, show a mapping table for category changes, and don't apply anything yet. Step two, apply with a log: apply only the approved transformations in code, output the cleaned file, and a change log with row counts before and after each step. The log makes your cleaning reproducible and defensible, and it's the first thing you'll check when a number looks wrong later. ### Decisions, not defaults Missing data and outliers are decisions, not defaults. AI will happily fill blanks with averages if you ask, but that can hide real patterns. Missing income data might be concentrated in one customer group. So ask the AI to show where data is missing, by segment and over time, before you decide, and document the decision. And outliers: an order worth a hundred times the average might be an error, a test, a bulk business purchase, or your best customer. List them with their full rows, then decide with business context. ### Example 1: merged lead lists First example, a simple one. A marketing team merges leads from an events spreadsheet, website forms and a purchased list. The audit finds company names in several variants, phone numbers in local and international formats from Pakistan, the UAE and the UK, free-text job titles, and six percent duplicate emails. The AI proposes standard formats and a company matching table. The team reviews the fuzzy matches and rejects a few wrong merges of different companies with similar names. And they keep the purchased list separate until they confirm its consent status for marketing. ### Example 2: Dubai clinic data Second example, a business case. A Dubai clinic group merges appointment exports from three branches. The audit finds Arabic and English spellings of the same doctor, times in two formats, and no-show coded three different ways. The analyst asks for mapping tables for doctors and statuses and reviews them with the operations manager, who spots two doctors with similar names who are actually different people. After cleaning in code with a log, the no-show rate by branch changes noticeably, because one branch had been coding late cancellations as no-shows. ### Watch me do it, part 1 Watch me build a cleaning script with a log. I ask the assistant for code in a specific shape: a small helper called step, which records rows before and after each transformation. Then the steps. Drop exact duplicates. Drop internal test leads, identified by our own email domain. Standardize country with an explicit mapping table, and anything not in the table becomes unmapped, not silently dropped. And parse dates using the source's day, month, year convention, flagging failures instead of guessing. ### Watch me do it, part 2 I run it, and it prints the log: rows before and after for each step. Eighty-nine duplicates removed. Thirty-nine test leads removed. Country standardized, with fourteen variants mapped to six values, and three unmapped, which I review by hand: two are typos, one is a new market. Fifty-seven dates flagged, not dropped. The log goes into the project folder next to the cleaned file. Anyone can now see exactly what happened to the data, and rerun it when next month's export arrives. ### Categorizing free text A special word on free text, like survey comments or support tickets, because AI is genuinely great at categorizing them. The trick is to define the category list yourself first, for example delivery, price, quality, app bug, other, and ask the model to assign exactly one category per row with a confidence score. Then hand-check a random sample of fifty rows. If agreement is high, trust the rest, and report the other bucket's size. If it's low, refine the definitions and rerun. Never let the model invent categories on the fly for a report. ### Common mistakes Common mistakes. Silent changes with no log. Over-aggressive fuzzy matching that merges different entities. Guessing ambiguous dates. Filling missing values without understanding why they're missing. And removing outliers because they're inconvenient, which is a classic way to fool yourself. Each of these is easy to prevent with the checks you've just seen, and each one, left unchecked, eventually reaches a decision-maker. ### Trustworthy cleaning How do you know your cleaning is trustworthy? Totals reconcile to the source system within a known tolerance. The log explains every change in row count. Unmapped and flagged values are counted and reviewed. And the script includes tests, like asserting no duplicate ids remain and every country is in the approved list. AI can write those tests too; you review them once and run them every time new data arrives. ### Recap Recap. Most analysis errors start in the data. Audit before changing anything. AI excels at standardizing, parsing, fixing types, deduplicating and categorizing, as long as you use review then apply, with a change log. Treat missing data and outliers as decisions. And turn cleaning into a tested, rerunnable script. ### Try this now Try this now. Run a data quality audit on one real dataset with an AI assistant. Produce a proposed cleaning plan with mapping tables, review it, then apply it with a script that writes a change log. Note one decision about missing data or outliers you made deliberately, and why. ## Key takeaways - Most analysis errors start in the data; audit quality before changing anything. - AI excels at standardizing categories, parsing dates, fixing types, deduplicating and categorizing text. - Use review-then-apply with a change log showing row counts at each step. - Treat missing data and outliers as decisions to investigate, not defaults to apply. ## Try it Run a data quality audit on one real dataset with an AI assistant. Produce a proposed cleaning plan and a change log, and note one decision about missing data you made deliberately. - [Previous: The AI analysis tool landscape in 2026: assistants, spreadsheets, notebooks and MCP](https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making/ai-analysis-tools-2026) - [Next: Exploratory analysis with AI](https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making/exploratory-analysis) - [All lessons of AI for Data Analysis & Decision Making](https://optimizeall.com/learn/ai-for-data-analysis-and-decision-making)