Pandas cleaning script built from a sample of messy rows
Writes a Python pandas script that cleans a messy export: mixed date formats, numbers with currency signs and commas, inconsistent city or product spellings, blank rows and duplicates, and saves a clean file plus a log of every change. Use it when the same export arrives every week and gets cleaned by hand.
The output this prompt produced, unedited.
The prompt
What to give
Twenty sample rows, copied with headers and all their mess: [paste]
What each column should become: [e.g. order_date as a date, amount as a number in rupees]
Known spellings to unify: [e.g. Bengaluru, Bangalore, B'lore]
What counts as a duplicate: [e.g. same order ID, or same phone and date]
Input file type and output wanted: [e.g. Excel in, CSV out]
Checklist for the script
One function per step, named for what it fixes, run in a clear order.
Dates parsed with the day-first or month-first rule stated, and failures listed rather than guessed.
Currency signs, commas and spaces stripped before numbers are converted.
Text trimmed, case fixed and spellings mapped from a dictionary at the top.
Duplicates removed by the stated rule, keeping the latest row.
Every changed or dropped row written to a change log with the reason.
Output format
The full script, runnable with one command and a file path.
The mapping dictionary, ready to extend.
A sample of the summary it prints: rows read, fixed, dropped and flagged.
Two lines on how to schedule it to run each week.
Guardrails
Never overwrite the input file.
Rows that cannot be cleaned go to a separate file, not into the clean output.
pandas data cleaningpython cleaning scriptmessy csv cleanupdate parsingdeduplicate rowsdata preparation
How to run this
Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.
The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.