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.

LLazyPrompt(@lazyprompt)Data & AnalysisPlanWorks with any model
No ratings yetSign in to rate
Save 0 Fork Report
Prompt template
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.
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.

Why it works

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.

Licensed CC0 (public domain) Version 1 · history