Clean Spreadsheets Without Losing the Original Truth

Make records usable while preserving the evidence behind every change.

A spreadsheet contains two identical purchase amounts on the same date. An AI cleanup removes one as a duplicate. Unfortunately, they were separate purchases with different transaction IDs.

Another row contains a negative amount. The model changes it to positive because it looks unusual. That row was a refund.

Data cleaning is not the process of making a table look normal. It is the process of applying justified, documented transformations while preserving what the source actually says. Some unusual values are errors. Others are precisely the events the analysis needs to retain.

Preserve the input before transforming it

Keep an unchanged source file and record its origin, export date, version, and import settings. Assign stable source-row identifiers if the file does not already provide them.

A cleaned table should be a separate output. The change log should let a reviewer trace each transformed value to its original text and the rule used.

Spreadsheet imports can change information before any explicit cleanup. Identifiers with leading zeros may be converted to numbers. Dates may be interpreted using a machine’s locale. Long numeric-looking codes may be displayed or stored differently from the source.

Define column types before importing where the tool permits it. An identifier is often text even when it contains only digits. Record the intended date format and currency convention rather than relying on visual appearance.

Profile the dataset before proposing repairs

Count rows, inspect column types, identify missing values, examine category spellings, and check candidate keys. Summarize unusual ranges without assuming they are invalid.

Our fictional transaction packet has eight imported rows. Amounts use one teaching currency. The source owner confirms that transaction IDs identify distinct events and that row 3 is an accidental reimport of row 2. No equivalent confirmation exists for other rows.

The dictionary permits negative refund amounts and zero-value no-charge issues. It does not establish whether row 4 uses month/day or day/month ordering. Row 7 is not a valid calendar date. Row 6 lacks an amount.

The correct next step is a rule proposal, not an instruction to make every cell look consistent.

Define duplicates using identity and evidence

Rows 1 and 2 share a date and amount but have different transaction IDs. Keep both. Row 3 repeats row 2’s transaction ID and fields, and the source owner confirms the reimport. Exclude row 3 from the canonical transaction output while preserving it in the source and duplicate log.

A repeated ID with conflicting values would require investigation. It could represent a revision, an export problem, or a violation of the source’s identity rule. Do not keep the first row automatically unless that behavior is justified by the source process.

The pandas duplicated method can flag repeated rows using chosen columns and a specified treatment of repeated occurrences. Those options implement your definition; they do not decide which business events are legitimate duplicates. See pandas’ duplicate-detection documentation.

Separate detection from disposition. “Potential duplicate” is a finding. “Excluded as confirmed reimport” is a decision supported by additional evidence.

Normalize only what the source supports

Dates written as 2026-07-01 can be parsed using the specified year-month-day format. The ambiguous 07/08/2026 could mean July 8 or August 7. Guessing from neighboring rows may produce a plausible but unsupported date.

Preserve the original date text, leave the canonical date unresolved, and record a reason code such as AMBIGUOUS_DATE_ORDER. For the impossible February date, use a different reason such as INVALID_CALENDAR_DATE.

In pandas, to_datetime can raise an error for invalid parsing or convert invalid values to its missing-date marker when configured to coerce. Coercion does not repair the date. If used, it needs an accompanying record of which values failed and why. See pandas’ date-conversion documentation.

Category normalization also needs a mapping. Converting an approved variant such as “sale” to “Sale” may be straightforward. Merging “Refund” and “Sale adjustment” requires knowing whether they mean the same event in the source system.

Keep missing, zero, and not applicable distinct

Row 6’s missing amount is not zero. Replacing it with zero would invent an amount and make the output look more complete than the evidence allows.

Row 8’s zero is a supplied valid value under the dictionary. It belongs in the appropriate analysis and should not be removed because an automated rule treats zeros as blank.

Some datasets also use “not applicable,” “not collected,” and “withheld” for different reasons. Preserve those distinctions when they matter to interpretation.

Imputation can be appropriate in some modeling workflows, but it is an explicit analytical method with assumptions. It should not overwrite the raw truth or masquerade as a recovered observation. If later modeling uses imputed values, retain indicators and document how the imputation was fitted.

Quarantine ambiguity without losing it

Create three outputs for this exercise: ready records, unresolved records, and confirmed duplicate records. Every source row must appear in exactly one disposition category.

The ready output contains rows 1, 2, 5, and 8. The unresolved output contains rows 4, 6, and 7. Row 3 belongs in the confirmed duplicate log.

Quarantine means “not ready for this defined analysis,” not “unimportant” or “deleted.” Some unresolved records may contain valid amounts that matter to total reconciliation even when their dates remain uncertain.

Assign an owner and a question to each unresolved record. For row 4, ask for the source date convention or original record. For row 6, ask for the missing amount. For row 7, ask for the correct date from the source. Do not ask AI to choose whichever correction makes the monthly total look reasonable.

Reconcile counts and known amounts

The row accounting is:

8 source rows = 4 ready + 3 unresolved + 1 confirmed duplicate.

The seven known source amounts sum to 100. There is also one missing amount. After excluding the confirmed duplicate amount of 20, known amounts for distinct transactions total 80.

The ready records total:

20 + 20 − 5 + 0 = 35.

The unresolved records contain known amounts of 30 and 15, plus one unknown amount. Their known subtotal is 45. Thus:

100 known source amount = 35 ready + 45 unresolved known + 20 duplicate.

This is a reconciliation of known recorded amounts. It is not a complete transaction total because one amount is missing. Nor is 35 the total of all business activity; it is the total of the ready subset.

A model that reports “cleaned revenue: 35” has hidden both the unresolved amounts and the definition of the source field.

Write a change log that supports review

A useful log includes source-row ID, field, original value, output value, rule identifier, disposition, evidence, and reviewer status. Not every entry changes a value. Keeping a negative refund is itself worth explaining when it was flagged as unusual.

For example: “Row 5, amount -5 retained; permitted refund sign under dictionary.” For row 3: “Excluded from canonical output; confirmed reimport of T02 by source owner.” For row 4: “Canonical date unresolved; original text retained.”

Record the rule version and execution date for scripted transformations. If a later source clarification resolves row 4, create a documented update rather than silently replacing the earlier output.

In shared workflows, preserve the relationship between source version, cleaning rules, and downstream analysis. A chart should not quietly switch to a newly cleaned dataset without its accompanying version and reconciliation notes.

A reusable prompt

Profile this dataset and propose cleaning rules before changing it. Use the supplied dictionary and source-owner confirmations. Separate definite errors from unusual valid values and unresolved cases. Preserve original values and source-row IDs. Define duplicate identity, date parsing, category mappings, and missing-value handling explicitly. Produce ready records, unresolved records, and an exclusion log with reasons. Reconcile row counts and known amounts, and do not treat missing amounts as zero or the ready subset as the complete population.

For students: defend the rows you keep

Use the eight-row packet to explain why rows 1 and 2 both remain, why row 5 stays negative, and why row 8’s zero is valid. The assignment is as much about preserving unusual truth as finding errors.

Spreadsheet students can build the three disposition sheets and a change log. Computing students can implement the rules as a script. Research students can discuss how unresolved records might bias a later analysis if their exclusion is ignored.

Do not use real classmates’ or customers’ records for the exercise. The synthetic packet contains the same reasoning challenges without private data.

Practice: produce a traceable cleaned packet

Create the three outputs and reconciliation above. Then imagine the source owner confirms that row 4 means August 7, 2026. Update only that resolved field and move the record to ready status, preserving the prior decision in the log.

Explain why row 7 still needs source clarification and why row 6’s amount cannot be reconstructed from the subtotal.

Completion check: Before clarification, the outputs contain four ready rows, three unresolved rows, and one duplicate; known amounts reconcile as 35 + 45 + 20 = 100; the missing amount remains explicit; and every transformation or exclusion has a reason.

Stretch: Implement the same rules in a reproducible script. Add checks that a repeated transaction ID with a conflicting amount is quarantined, a legitimate zero is preserved, and ambiguous date ordering never becomes a guessed canonical date.