Clean a messy spreadsheet with Python

Paste the header and a few sample rows, list the problems you see, and this prompt writes a pandas script that fixes them step by step, reports what changed and saves a clean copy without touching the original.

text-code-pandas-data-cleaning — claude — 91 words

Act as a data analyst who writes clean pandas code.

My file: [e.g. sales.csv, about 20,000 rows]
Columns and a few sample rows:
[PASTE HEADER + 5 ROWS]
Problems I see: [e.g. mixed date formats, ₹ symbols in amounts, duplicate customers]
What I want at the end: [e.g. monthly revenue per city]

Write a script that loads the file, fixes each problem step by step with a comment, reports how many rows each step changed, and saves a clean file. Never overwrite the original. Then produce the summary I asked for.

Edit in the builder Copy & open Claude ↗Copy & open ChatGPT ↗Copy & open Gemini ↗

Paste real sample rows, with personal details removed. Messy data is always messier than the description.

Get a better result

What you will get

A script that loads your file, fixes each listed problem in a commented step, reports how many rows each step changed, saves a clean copy without touching the original, and produces the summary you asked for.

Common mistake

Dates are parsed the wrong way round, such as day and month swapped.

Fix: Say which format the file uses, such as day first, and ask the script to list any dates it could not read instead of guessing.

Also watch for

Duplicates are removed that were genuine. Define a duplicate, for example “same customer, same date and same amount”, and ask for the removed rows to be saved to a separate file.
Currency symbols break the numbers. Ask for the symbols and commas to be stripped in one visible step, with a count of values that still could not be converted.

Try a variation: add a data quality report

Ask for “a short report with missing values per column, unusual values and the number of rows removed”. Keep it alongside the clean file.

Still not right? Reply with this

Add a check after each step that prints the row count and three example rows before and after, so I can see what changed.