How to Clean Data With ChatGPT

Start with a copy of the source

Download the six-row source CSV and keep an untouched copy. Its row_id column identifies R1 through R6. If your own export has no stable row identifier, add one to a working copy and retain the original file. You will need that identifier to explain where a cleaned, merged, or rejected row came from.

Open ChatGPT and attach the working CSV, or paste the small practice table into the chat. The retained editorial example used pasted fictional text; attaching a CSV is a supported option when file analysis is available in your account. Check that ChatGPT can see all six rows and the five column names before asking it to clean anything. OpenAI's data-analysis guidance explains supported files and recommends clear headers with one record per row.

The source has two Aster rows that might refer to one record. R3 writes revenue as $950. R4 has a blank region but a real revenue value of 0; R5 has an N/A revenue value; and R6 has 2026-13-01, which cannot be parsed as a calendar date. None of those facts alone authorizes ChatGPT to delete a row or invent a value.

Find the problems before changing values

Ask for a profile that names the affected rows. For this file, the expected starting count is six, the columns are row_id, name, region, revenue, and date, and the row IDs should be unique. If ChatGPT reports a different count or skips a column, stop and fix the input before accepting its analysis. A successful upload message does not prove it read the full file.

Inspect the attached or pasted CSV without editing it. Report the number of data rows, column names, unique row IDs, candidate data types, blank and N/A values, format differences, possible duplicates, and invalid dates. Name the affected row IDs and show the original values. Separate observations from rules that need my approval. If you cannot inspect every row, say where the read stopped.

Compare the reported count and examples with the source CSV. In particular, R4's 0 is known revenue; R5's N/A is unknown revenue. A profile that groups them together has already lost a distinction the clean output must preserve.

Choose a rule for each kind of problem

Formatting changes can be straightforward; changes to meaning need an explicit decision. In this practice file, the proposed rules trim surrounding spaces, normalize the case of names and regions, parse the shown currency formats, and write approved dates as YYYY-MM-DD. They leave missing values missing and send invalid dates to a separate quarantine file. A duplicate is merged only after the record identity rule and all relevant values agree. The R1/R2 merge is an approved assumption for this fictional exercise, not a general rule for customer or transaction data.

Dates require more care than uniform formatting suggests. The practice file's R2 date, 08/01/2026, matches R1 as August 1 only under the approved month-first interpretation. In a day-first export it would mean January 8. If you cannot establish the source locale from the data owner or export specification, preserve the original string and hold that row for review. The same restraint applies when 1,200 could use a different decimal convention.

Your file may also have problems this six-row exercise does not test. Preserve leading zeros in identifiers such as 0012 by treating the column as text. Keep rows that look similar when a reliable record key is missing. Ask for an approved mapping before combining labels such as NE and Northeast; they may have different meanings in the source system. Separate pasted totals or notes from actual records before counting rows. These are decision points to inspect in your data, not additional outcomes proven by the practice files.

Propose cleaning rules for this CSV, but do not apply them yet. For each rule, show the affected columns, accepted input formats, output format, rows that would change, and what would be held for review. Preserve 0 separately from blank and N/A. Do not infer a date locale, duplicate identity, category mapping, or missing value. Keep the original file unchanged and retain source row IDs.

Approve the date locale, duplicate key, and missing-value policy before moving on. If R1 and R2 had different revenue after parsing, the proposed merge would hide a disagreement; keep both until someone who knows the records resolves it. The retained ChatGPT rule proposal for the fictional file is shown below.

Reject changes that would hide a problem

The editorial audit deliberately proposed five unsafe changes: deleting rows with blanks, converting N/A to zero, turning R6's impossible date into January 13, merging R1/R2 without both source IDs, and overwriting the raw file. ChatGPT rejected those proposals in the retained example. That screenshot shows how the model responded to planted mistakes; it does not validate a future cleaning run on your own file.

Review the actual rule proposal and output for the same failures. If a missing value became 0, restore the missing state and rerun from the original. If a date was guessed, place the source row in quarantine with the original string. If a merge lost a source ID or combines unequal records, undo the merge. A warning that merely says “check the data” is too vague to establish which rows are safe.

Create separate outputs, then reconcile them

After approving the rules, ask ChatGPT for a cleaned candidate, quarantined rows, and a field-level change log. If file creation is available, request separate CSV files; otherwise review the tables before saving them as CSV. The retained editorial run produced a preview, and the downloadable practice files were prepared from the reviewed result. Do not treat a generated success label as evidence that the rows are correct.

Apply only the rules I approved to the working CSV. Return three separate outputs: a cleaned candidate with original row IDs and review states, a quarantine table with original values and reasons, and a field-level change log showing before and after values. Preserve the raw file. Do not merge uncertain duplicates or fill missing values. List any rule you could not apply and any row that still needs a decision.

In the candidate CSV, C1 traces to R1 and R2, C2 to R3, C3 to R4, and C4 to R5. C3 and C4 are still marked REVIEW_MISSING_REGION and REVIEW_MISSING_REVENUE. Their MISSING_* strings are review markers, not replacement values for the downstream system. The quarantine CSV retains R6 and its invalid date. The change log lists ten field changes or review decisions. “Clean” in the filename means normalized candidate data here; it does not mean all four rows are approved for import.

Reconcile source IDs before checking individual values. Six source rows must be accounted for: R1 and R2 both appear in C1's lineage, R3 in C2, R4 in C3, R5 in C4, and R6 in quarantine. That is six source IDs represented by four candidate records and one quarantined row. Any missing ID, unexplained duplicate ID, or output row with no source ID is a stop sign. Then compare every changed field with the original and the approved rule. Check the date and amount conversions, the R1/R2 match, R4's zero, R5's missing value, and R6's untouched invalid date.

Run the practice checker

Download and extract the practice kit, then run this command in the extracted folder:

python3 clean-data-evidence-gate.py

The Python checker compares the fixed practice files with the approved six-row fixture. It checks the raw-file fingerprint, expected output rows, source-ID coverage, quarantine values, and the change log. The saved result is a baseline for those files. A pass does not approve the duplicate definition, missing-value policy, or downstream import for a real dataset; adapt the checks to your own schema and have the relevant owner decide what to do with unresolved rows.

If a check fails, keep the original CSV and inspect the named row or field before regenerating the candidate. Do not import C3 or C4 until the missing region and revenue are resolved or explicitly accepted by the receiving system. Keep R6 out of the import until its date is corrected from a reliable source.