Clean a messy CSV step by step without losing data

Prompt

Act as a data analyst who specializes in data quality and preparation. You work reproducibly and never delete information without recording it.

Context:
- File description (source, approximate row count, columns): {{description}}
- Sample rows (header plus 10-20 rows, anonymized): {{sample}}
- Tool I will use to clean it: {{tool}}
- What the clean data will be used for: {{end_use}}

If the sample is not enough to understand a column, ask me before deciding on a rule.

Deliverables:
1. A quick profile per column: expected type, detected type, problem examples and estimated share of blanks in the sample.
2. A list of issues found: encoding and odd characters, extra whitespace, inconsistent capitalization, mixed date formats, decimal separators, exact and likely duplicates, impossible or outlier values, categories spelled several ways.
3. An ordered cleaning plan (order matters), with one clear rule per step and the reason for it.
4. Implementation in {{tool}}: commented code, or formulas and menu steps.
5. A missing-value strategy per column (leave, flag, impute with an explicit rule, or exclude) without inventing data.
6. A final data dictionary: normalized column name, type, description and allowed values.
7. Validation checks after cleaning (row counts before and after, control totals, uniqueness per key).
8. A change log so every step can be audited.

Constraints:
- Always work on a copy and keep the original file.
- Never drop rows silently: count and justify every exclusion.
- Mark any business rule I need to confirm as [FILL IN].
- If you detect personal data, recommend anonymizing it before sharing.

Format: tables for the profile and dictionary, a numbered list for the plan and code blocks.

How to use it

Usage example

Sample output

import pandas as pd

raw = pd.read_csv("orders_raw.csv", dtype=str, encoding="utf-8")
df = raw.copy()  # never touch the original

df = df.apply(lambda s: s.str.strip())
df["customer_norm"] = (df["customer"].str.upper()
    .str.replace(r"[.,]", "", regex=True)
    .str.replace(r"\s+", " ", regex=True))

# [FILL IN] confirm 02/03/2026 means Feb 3 (US order)
df["date"] = pd.to_datetime(df["date"], format="mixed", errors="coerce")
df["amount"] = pd.to_numeric(df["amount"].str.replace(",", "", regex=False), errors="coerce")
df["status"] = df["status"].str.lower()

STATES = {"CA": "CA", "CALIFORNIA": "CA", "TEX": "TX", "TX": "TX"}  # [FILL IN] full list
df["state"] = df["state"].str.upper().str.rstrip(".").map(STATES)

df["flag_missing_date"] = df["date"].isna()
df["flag_negative"] = df["amount"] < 0  # [FILL IN] refund or error?
df["flag_likely_dupe"] = df.duplicated(["customer_norm", "date", "amount"])

assert len(df) == len(raw), "no rows dropped silently"

Key facts

Category

Data management · Data cleaning

Output type

Text

Level

Intermediate

Designed for

ChatGPT, Claude, Gemini and Microsoft Copilot

Verified in

Pending editorial verification

Variables

File description, Sample rows, Tool and End use of the data

Language

English

Price

Free, no sign-up

Updated

Nov 7, 2026

Frequently asked questions