Write a Data Cleaning Script for Messy Data

This prompt generates a structured pandas cleaning script that detects each data-quality issue you describe, applies a fix, and prints a before/after summary, after backing up the original data. It's aimed at analysts and data engineers staring at a messy CSV who want a systematic, auditable cleaning pass rather than ad-hoc one-off edits. The detect/fix/summary loop is the key design choice — it makes every transformation visible and reversible, so you can trust what the script actually changed instead of crossing your fingers.

What you can expect back

import pandas as pd

df = pd.read_csv("customers.csv")
df_backup = df.copy()  # preserve original before any mutation

# 1. Duplicate rows
dupes = df.duplicated().sum()
print(f"[Duplicates] found: {dupes}")
df = df.drop_duplicates()
print(f"[Duplicates] rows after: {len(df)}")

# 2. Inconsistent date formats
print("[Dates] unique sample before:", df['signup_date'].unique()[:5])
df['signup_date'] = pd.to_datetime(df['signup_date'], errors='coerce')
print(f"[Dates] unparseable (now NaT): {df['signup_date'].isna().sum()}")

# 3. Mixed casing in country
print("[Country] before:", df['country'].unique())
df['country'] = df['country'].str.strip().str.upper()
print("[Country] after:", df['country'].unique())

# 4. Negative ages
bad_age = (df['age'] < 0).sum()
print(f"[Age] negative values: {bad_age}")
df.loc[df['age'] < 0, 'age'] = pd.NA
print(f"[Age] nulls after fix: {df['age'].isna().sum()}")

print("\nCleaning complete. Original preserved in df_backup.")

Illustrative example — your results will vary by tool and inputs.

Get sharper results

• 01Paste real (anonymized) sample rows including the actual messy values — the model writes far better detection logic when it can see the exact formats it's dealing with.
• 02Tell it how to handle ambiguous fixes: should negative ages become null, zero, or be dropped? If you don't decide, the script picks for you and may not match your intent.
• 03Ask it to write the cleaned result to a new file rather than overwriting the source, so a buggy fix can never destroy your raw data.
• 04Request a final data-quality report (null counts, row count, dtype summary) at the end so you can confirm the cleaning did what you expected.
• 05For large files, ask it to add chunked reading or dtype specification so the script doesn't blow up your memory on load.

Adapt it for your case