Data cleaning plan for a messy dataset

Describe your messy data and get a prioritized cleaning plan with rules, code and a record of every change you make.

Change note: Initial version

Prompt

I have a messy dataset to clean before analysis. Build me a cleaning plan.

What the data is: {{data_description}}
Problems I've noticed: {{problems}}
What the cleaned data will be used for: {{purpose}}
Tool: {{tool}}

Create the plan:
1. Triage: rank the issues by how much they could distort the analysis, and note issues I may not have spotted that typically appear in this kind of data.
2. For each issue, give: how to detect it (with a quick query or formula), the recommended fix, the alternative fix, and the risk of each (information lost, bias introduced).
3. Decisions I must make: choices that depend on the business meaning (for example, is a blank "zero" or "unknown"? which duplicate record wins?). Ask me these as short questions rather than deciding silently.
4. A step order that avoids undoing earlier work (for example, standardize text before deduplicating).
5. Code or steps in {{tool}} for the main steps, with comments.
6. A change log template: for each step, record rule, rows affected before and after, and who approved it. Never delete the raw data; keep an untouched copy and keep cleaning reproducible.
7. Final validation checklist to confirm the cleaned data is fit for the stated purpose.

Be concise and practical.

Variables

• {{data_description}}Datasetlong

  Default: A CSV export from our CRM with 25,000 contact records: name, email, company, country, phone, signup date, lead source, deal value

  What it contains, row count, where it came from.

• {{problems}}Known problemslong

  Default: Duplicate contacts with different capitalization, country typed many ways (USA, U.S., United States), dates in two formats, deal value blank for many rows, some emails missing the domain

  List whatever you've noticed.

• {{purpose}}Purposetext

  Default: Analyze lead source performance and deal value by country

• {{tool}}Toolselect

  Options: Python (pandas), R, Excel, Google Sheets, SQL, OpenRefine

  Default: Python (pandas)

Example output

**Triage (highest impact first)**
1. Duplicate contacts: inflates counts and lead-source totals.
2. Blank deal value: biases averages if blanks are not real zeros.
3. Country variants: splits one country across several groups, wrecking the by-country analysis.
4. Mixed date formats: breaks time trends; ambiguous dates like 03/04/24 are risky.
5. Malformed emails: matters for dedup keys. Likely unspotted: leading/trailing spaces, test records like "test@test.com".

**Decisions for you**
- Is a blank deal value "no deal yet" or "unknown"? Excluding vs zero changes your averages.
- When duplicates differ, which record wins: newest, most complete, or one with a deal value?
- Is 03/04/24 day-first or month-first in the source system?

**Step order**: trim and lowercase text -> standardize country -> parse dates -> flag invalid emails -> deduplicate -> handle blanks.

**Code (pandas)**
df["email"] = df["email"].str.strip().str.lower()
country_map = {"usa": "United States", "u.s.": "United States", "united states": "United States"}
df["country"] = df["country"].str.strip().str.lower().map(country_map).fillna(df["country"])

**Change log**: Rule 4, "lowercase emails", 25,000 in, 25,000 out, 3,112 changed, approved by: ____.

**Validation**: No duplicate emails; every country appears in a reference list; no future signup dates; row count reconciles with log.

Notes

Surfacing business-meaning decisions as questions stops the model from quietly making choices that bias your analysis. The change log keeps the cleaning auditable and repeatable.