Clean a Messy Dataset With AI

Steps

1. 01Profile before you touch anything

   Paste twenty representative rows, including the ugly ones, and ask what is wrong. You want an inventory: inconsistent formats, missing values, likely duplicates, impossible values, mixed units. Include the ugly rows deliberately — a clean sample produces a clean report and a false sense of security.

2. 02Turn problems into explicit rules

   For each problem, get a rule precise enough to apply mechanically, and force the ambiguous cases into the open.

   Prompt Copy

   Here is a data quality report for a dataset. For each issue, write a cleaning rule precise enough that two people applying it independently would get the same result. State explicitly what to do with edge cases and what should be flagged for a human rather than auto-corrected.
   
   ISSUES:
   [paste]
   SAMPLE ROWS:
   [paste]

3. 03Decide what must never be auto-corrected

   Some fields carry meaning that a plausible guess destroys — identifiers, financial amounts, anything legally significant. Write these down as a do-not-touch list before you start. A model asked to clean data will happily normalise an account number.

4. 04Apply the rules where you can see them

   Use formulas, find-and-replace, or a short script — anything where the transformation is visible and undoable. Keep the original file untouched. If you do use AI to generate a script, read it before running it against real data.

5. 05Verify by sampling, not by feel

   Pull twenty random rows from the cleaned file and check them against the original by hand. Count how many are wrong. That number, not a general impression, is your error rate, and it tells you whether the cleaned data is usable.

If you want to go further

• Turn the rule list into a reusable script for next time
• Add a validation step that rejects rows failing the rules rather than fixing them

Try next