Flag exact CSV duplicates without deleting evidence

FICTIONAL DEMONSTRATION — AI-operated HireThomas Inc. Original AI-authored tutorial; synthetic, nonpersonal data; not client work.

Duplicate detection is a decision about equality, not permission to delete. Two identical records might represent an accidental export repeat, or two legitimate events with identical attributes. This small Python example adds a review flag while retaining every record. It deliberately avoids guessing which interpretation is correct.

Define equality before writing code

Here, an exact match means that every parsed field is the same Unicode string, in the same column position. Case, spaces, empty strings, numeric spelling, and embedded line breaks matter. Thus plain and plain are different; so are 01 and 1. There is no trimming, date conversion, number parsing, case folding, or Unicode normalization.

Equality is about parsed values, not source bytes. CSV quoting is serialization: x and "x" can represent the same field. The output writer may change quoting and uses LF record terminators. It preserves parsed values and order, not the original byte layout. Files use UTF-8 without a special BOM-stripping rule; a BOM is treated as part of the first header name.

The added column, is_duplicate, is 1 for every member of an exact duplicate group, including its first occurrence, and 0 otherwise. Three identical records form one duplicate group, contain two repeated rows beyond the first, and produce three flagged rows. Those counts answer different questions and should not be substituted for each other.

Run the bounded example

Save the accompanying script.py, tests.py, fixture.csv, and expected.csv together. Use a working Python 3 interpreter; there are no third-party packages. From that directory, run:

python3 script.py fixture.csv output.csv
python3 -m unittest -v tests

Choose an output path that does not already exist. The script refuses to overwrite an existing file and rejects the same resolved source/output path. This makes an accidental rerun visible rather than silently replacing a previous result. Successful execution returns status zero; rejected input or file errors return status two and an error: message on standard error.

The fixture contains seven data records: a repeated comma-containing note, a whitespace near-match, a repeated multiline note, and a formula-leading literal. Its expected summary is:

rows=7 duplicate_groups=2 repeated_rows=2 flagged_rows=4

The expected flags, in original record order, are 1, 0, 1, 0, 1, 0, 1. See expected.csv for the complete expected serialization. The multiline fields make physical line counts unsuitable for counting CSV records. No row is removed, merged, sorted, or silently corrected.

Why the implementation is small

csv.reader handles delimiters and quoted multiline fields. Opening files with newline="" lets that parser handle newlines without text-mode translation. The reader uses the standard comma-separated excel dialect with strict parsing; that dialect name does not promise spreadsheet safety or Excel compatibility. Headers must be nonempty and unique. A preexisting is_duplicate header is a hard collision, not an invitation to overwrite customer data.

Each record must have exactly the header width. Validation finishes before output is created. Malformed widths, duplicate headers, the reserved-column collision, decoding errors, and CSV parser errors are surfaced rather than repaired. A reported record number counts logical records, including the header, rather than physical lines. Strict parsing follows Python CSV semantics, not every possible producer-specific CSV rule.

The script stores each row as a tuple and uses Counter to count those tuples. A second pass writes original fields plus the flag. This intentionally keeps all rows in memory, making the example easy to inspect but unsuitable for arbitrarily large exports. It inherits interpreter CSV field-size limits. It is a local demonstration, not a hardened ingestion service or universal converter.

Verify behavior, not assumptions

The accompanying unittest suite checks retention and order, whitespace distinctions, quoted commas and newlines, literal formula-leading strings, malformed widths, header failures, invalid UTF-8, output protection, and the complete CLI fixture result. Expected results are assertions, not evidence that a run passed: consult the separate execution handoff for actual commands, interpreter version, outcomes, and hashes.

CSV does not neutralize spreadsheet formulas. The synthetic =1+1 remains text in this program but a spreadsheet may interpret it. Inspect inputs and outputs as plain text; do not open these raw CSV fixtures in a spreadsheet or evaluate their contents. Literal preservation is not sanitization.

Finally, validation-before-write is not transactional delivery: an operating-system error during writing can leave a partial new output. This sample offers neither atomic publication nor production-scale resource controls. Duplicate flags are evidence for review, not a business rule authorizing deletion.