Clean a CSV with Python without guessing which duplicate to keep

A weekly export arrives with spaces around IDs, a price entered as “unknown,” and two rows with the same order number but different quantities. Deleting one duplicate makes the file look cleaner. It does not tell you which quantity is right.

This walkthrough uses our existing 12-record synthetic order export to demonstrate a repeatable alternative: accept rows that meet explicit rules and put the others on a review list. You will finish with five accepted records, seven records needing review, and the original file intact. This is an educational example from VentureBench, the human-owned workshop behind this business experiment; it is not a customer case study.

1. Check whether these rules fit your data

The example expects a comma-delimited UTF-8 CSV with this exact header, in this order:

order_id,item,quantity,unit_price,notes

• Trim spaces at the edges of every field.
• Require an order ID and item name.
• Flag every row sharing an order ID, rather than selecting a winner.
• Require quantity to be a positive whole number.
• Accept nonnegative prices with at most two decimal places; write accepted prices with two places.

These are deliberate example rules, not universal accounting rules. One order ID represents one record here. If a real order contains several line items, repeating the order ID may be perfectly legitimate. You would need a different key and acceptance rule before using this approach. Returns, negative adjustments, localized prices and currencies also need separate decisions.

2. Unzip and check Python

Extract the download into a folder you can find. Open a terminal in that extracted folder. You need Python 3; this example was executed with Python 3.12. It uses the standard library, so there is no package-install step. Review clean_orders.py before running it. It reads the specified local input and writes three local result files; it makes no network requests.

On Windows, check the Python launcher:

py -3 --version

If your installation uses python or python3, use that command instead in the following steps. If no Python command exists, use the official Python download page and its installation guidance. You can still inspect the included expected outputs without installing anything.

3. Run the included sample

From the extracted folder, run:

py -3 clean_orders.py sample.csv demo-result

demo-result must be a new folder. The script refuses an existing output directory so a second run cannot silently replace an earlier result. For another run, choose a new name such as demo-result-2. Keep the input unchanged while working through the example.

The console report should show total_records: 12, accepted: 5 and rejected: 7. Here “rejected” means held for review, not deleted from the source.

4. Read all three outputs

• clean.csv: accepted rows A001, A002, A006, A007 and A010, with normalized spacing and prices.
• exceptions.csv: CSV record references and reasons for the seven records needing a decision. It does not copy the full original rows; match each reference back to the preserved source.
• report.json: counts, rule summary and the input file’s SHA-256 fingerprint, which identifies the input bytes used for the run.

For example, A001 changes from padded text and price 5.5 to trimmed text and 5.50. Both A003 records are flagged because their quantities disagree. A005 has an invalid price, the unnamed-ID record lacks a required value, and A009 has more price precision than the example permits. Nothing invents a replacement price or rounds a questionable input into acceptance.

Record references start at 2 because the header counts as the first CSV record. They are not necessarily physical text-editor line numbers when a quoted field contains a line break.

5. Verify before adapting it

py -3 verify_demo.py

The included check asserts the expected IDs and counts, spacing and price normalization, duplicate quarantine, wrong-header refusal, existing-folder refusal and original-file preservation. A passing sample check establishes this example’s behavior; it does not validate a different business dataset.

The script uses Python’s CSV parser rather than splitting each line at commas. The official CSV documentation explains dictionary-based reading and writing, quoting and newline handling. That matters when a field itself contains a comma or line break.

Common errors and boundaries

• “Expected exact header”: check spelling, field order and delimiter. A semicolon-delimited or different-schema export requires an intentional adaptation.
• “File exists”: choose a fresh destination folder. Do not delete a result you still need.
• Encoding or parser error: keep the original and confirm its actual export format. This example reads UTF-8 with or without its byte-order mark; it is not an automatic encoding detector.
• Unexpected duplicate flags: confirm the business key. Do not turn off conflict checks merely to make the count look better.

The demo limits input to 5 MB and 10,000 records and does not process Excel workbooks, merge folders or schedule runs. Its output prefixes text beginning with a spreadsheet formula marker with an apostrophe. That is a precaution for common imports, not a guarantee across spreadsheet applications. Review unfamiliar CSVs as text and control column types when importing; opening and saving a CSV in a spreadsheet can change values. The original export remains the reference.

An original MeasureGrove feature prepared with AI assistance. General information and inspiration, not individual professional advice. Terms & disclaimers.