Data cleaning plan for a messy CSV

You are a careful data engineer. I need to clean a CSV file before analysis.

What the data is: [SHORT DESCRIPTION, e.g. "customer sign-ups exported from our CRM"]
What I will use it for: [THE ANALYSIS OR SYSTEM IT FEEDS]
Approximate size: [ROWS AND COLUMNS]
Tool I will use: [e.g. Python with pandas, R, Excel, SQL]

Here are the header and a sample of rows (including some problem rows):
```
[PASTE HEADER + 20-50 ROWS]
```

Step 1 - Profile: for each column give the likely meaning, expected type, and problems you can see (missing values, mixed formats, inconsistent categories, outliers, duplicates, encoding issues, leading/trailing spaces).
Step 2 - Plan: a numbered cleaning plan ordered by impact. For each step say what changes, why, and how many rows it might affect.
Step 3 - Decisions for me: list choices that depend on business rules (e.g. drop or impute missing values) with your recommended default.
Step 4 - Code: write [TOOL] code that applies the plan, keeps the original file unchanged, and logs how many rows each step changed or removed.
Step 5 - Checks: list validation checks to run after cleaning.

Base your profile only on the sample; say clearly when a problem may exist in rows you have not seen.

Example (excerpt):
Column signup_date — expected type: date. Problems: three formats seen ("2026-03-04", "04/03/2026", "March 4"); 2 empty values. Note: "04/03/2026" is ambiguous (day/month order).

Decision for you: country has "UK", "United Kingdom" and "GB". Recommended default: map all to ISO code "GB".

Plan step 1: Strip whitespace from all text columns (affects most rows; no information lost).

When to use it

Use this prompt when you receive an export from a CRM, a survey tool, a spreadsheet maintained by several people or a public dataset, and you need to make it usable. It helps you see the problems before you start editing, so you clean the data on purpose instead of by trial and error.

How to customize it

• [SHORT DESCRIPTION] gives the model context to infer what each column means.
• [WHAT I WILL USE IT FOR] decides what “clean” means. A dashboard needs consistent categories; a machine learning model also needs careful handling of missing values and outliers.
• [ROWS AND COLUMNS] helps the model judge whether its code needs to be memory-efficient.
• [TOOL] sets the language of the code in step 4.
• [HEADER + ROWS] is the evidence. Include deliberately messy rows, not only the first 20, which are often the cleanest.

Why it works

The prompt separates diagnosis from treatment. Profiling first, then planning, then coding mirrors how experienced data engineers work and makes each stage reviewable. The ordered plan with an estimate of affected rows makes the consequences of each step visible.

The “Decisions for me” step is a deliberate constraint: many cleaning choices are business decisions, not technical ones, and a model should not make them silently. Logging rows changed per step and keeping the original file are safeguards that make the cleaning reproducible and auditable. The final instruction about unseen rows reminds both you and the model that a sample cannot reveal every problem.

Tips and variations

• Remove or mask personal data before pasting rows. Fake but realistic values are enough to show format problems.
• If your assistant can run code and accepts file uploads, upload the full file and ask it to run the profile on all rows, then compare with the sample-based plan.
• Ask for the checks in step 5 as automated assertions so you can rerun them on next month’s export.
• For recurring exports, finish with: “Turn this into a reusable function with parameters.”
• See our guide on preparing a dataset for fine-tuning if the cleaned data will be used to train a model.