Remove Duplicate CSV Rows by Column in Python, with an Audit File

Download the scripts and tests (ZIP)
Python scripts, tests, and ready-to-run merge, join, and date practice files with expected results. No signup required.

When two exports overlap, the same order can appear twice. Deleting every repeated customer ID is a different operation: one customer may have several legitimate orders. Before removing anything, choose the columns that identify a single record.

This guide keeps the first record in file order for each exact key and writes later occurrences to an audit file. It never edits the input. If a later record has the same key but different values, it is still a duplicate under this rule; the audit file flags that difference so you can review it.

Decide what counts as a duplicate

Your intended rule	Key to use	What to review
One row per order	`order_id`	Later versions of an order may contain corrections.
One row per order line	`order_id line_id`	An order ID alone would discard legitimate lines.
Only identical records	Every column name	Different spelling or whitespace remains different.

Keys are case-sensitive strings. 001 and 1 are different. A and A are different. Blank or whitespace-only keys stop the run; silently combining all unknown IDs would make the result misleading. The script does not decide which version is newest and does not compare timestamps.

Try a small example first

Save this invented data as orders.csv in a new working folder. Keep the leading zeros.

order_id,line_id,item,quantity
001,1,Notebook,2
001,2,Pen,3
001,1,Notebook,2
002,1,Folder,1
001,1,Notebook,4

Save the complete script below as dedupe_csv.py next to the CSV. With Python 3.10 or later installed, run:

python dedupe_csv.py orders.csv result --key order_id line_id

The result folder must not exist. The console prints (5, 3, 2): five input records, three kept, two duplicates. result/COMPLETE.txt contains:

records=5
kept=3
duplicates=2

The kept file contains:

order_id,line_id,item,quantity
001,1,Notebook,2
001,2,Pen,3
002,1,Folder,1

The duplicate audit contains:

record_number,first_record_number,row_matches_first,order_id,line_id,item,quantity
3,1,true,001,1,Notebook,2
5,1,false,001,1,Notebook,4

Record 5 is a conflict: the key matches record 1, but quantity changed. Do not import the kept file into a business system until you decide whether first-wins is appropriate for these conflicts. The record numbers count parsed data records, excluding the header; a quoted field containing a newline can span several physical lines.

Verify the sample outputs

Save this as check_result.py beside the script and run python check_result.py result. It checks this specific five-record fixture, including both audit decisions. It is not a general validator for other exports.

import csv
import sys
from pathlib import Path

folder = Path(sys.argv[1])

def rows(name):
    with (folder / name).open(encoding="utf-8", newline="") as handle:
        return list(csv.reader(handle))

expected_kept = [
    ["order_id", "line_id", "item", "quantity"],
    ["001", "1", "Notebook", "2"],
    ["001", "2", "Pen", "3"],
    ["002", "1", "Folder", "1"],
]
expected_audit = [
    ["record_number", "first_record_number", "row_matches_first",
     "order_id", "line_id", "item", "quantity"],
    ["3", "1", "true", "001", "1", "Notebook", "2"],
    ["5", "1", "false", "001", "1", "Notebook", "4"],
]
if rows("kept.csv") != expected_kept:
    raise SystemExit("Unexpected kept records")
if rows("duplicates.csv") != expected_audit:
    raise SystemExit("Unexpected duplicate audit")
if (folder / "COMPLETE.txt").read_text(encoding="utf-8").splitlines() != [
    "records=5", "kept=3", "duplicates=2"
]:
    raise SystemExit("Unexpected completion counts")
print("PASS: 3 kept records, 2 audited duplicates, completion counts match")

Expected output is PASS: 3 kept records, 2 audited duplicates, completion counts match. Quantity 2 is retained even though the later value is 4. Success does not certify that the retained business value is correct.

Reproduce an interrupted run and recover

Save a separate orders-missing-key.csv in the practice folder:

order_id,line_id,item,quantity
001,1,Notebook,2
,2,Pen,3

Run:

python dedupe_csv.py orders-missing-key.csv failed-result --key order_id line_id

The command exits with status 1 and reports Record 2: empty key. The new failed-result folder contains a partial kept.csv with the Notebook record and a header-only duplicates.csv; it has no COMPLETE.txt. Do not use those files as a finished result. Reusing failed-result is refused even after you fix the input.

For this invented exercise, the verified replacement is the original five-record orders.csv above. Keep the failing input and partial output for inspection, then run with a new destination:

python dedupe_csv.py orders.csv recovered-result --key order_id line_id
python check_result.py recovered-result

Expect (5, 3, 2) followed by the same PASS message. In a real export, obtain the missing key from an authoritative source and save a corrected copy; do not infer it from this example. Running the recovery command again fails because recovered-result already exists and preserves the successful output. An unknown key such as --key orderid fails before a new destination is created. Command-line syntax errors use status 2; handled file and validation failures use status 1.

Complete script

"""Keep the first CSV record for each exact key; write into a new folder."""
import argparse
import csv
from pathlib import Path


def dedupe(source, destination, keys):
    source, destination = Path(source), Path(destination)
    if not keys or len(set(keys)) != len(keys):
        raise ValueError('Choose one or more distinct key columns')
    with source.open(encoding='utf-8-sig', newline='') as handle:
        reader = csv.reader(handle, strict=True)
        header = next(reader, None)
        if not header or any(not name.strip() for name in header):
            raise ValueError('Missing or blank header')
        if len(set(header)) != len(header):
            raise ValueError('Duplicate header names')
        if any(key not in header for key in keys):
            raise ValueError('Unknown key column')
        positions = [header.index(key) for key in keys]
        # mkdir without exist_ok refuses every existing destination.
        destination.mkdir()
        seen = {}
        total = duplicates = 0
        with (destination / 'kept.csv').open('x', encoding='utf-8', newline='') as kept, \
             (destination / 'duplicates.csv').open('x', encoding='utf-8', newline='') as removed:
            out, audit = csv.writer(kept), csv.writer(removed)
            out.writerow(header)
            audit.writerow(['record_number', 'first_record_number', 'row_matches_first', *header])
            for number, row in enumerate(reader, 1):
                if len(row) != len(header):
                    raise ValueError(f'Record {number}: wrong field count')
                key = tuple(row[i] for i in positions)
                if any(not value.strip() for value in key):
                    raise ValueError(f'Record {number}: empty key')
                total += 1
                if key in seen:
                    first_number, first_row = seen[key]
                    audit.writerow([number, first_number, str(row == first_row).lower(), *row])
                    duplicates += 1
                else:
                    seen[key] = (number, row)
                    out.writerow(row)
        (destination / 'COMPLETE.txt').write_text(
            f'records={total}\nkept={total - duplicates}\nduplicates={duplicates}\n',
            encoding='utf-8')
    return total, total - duplicates, duplicates


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument('source')
    parser.add_argument('destination')
    parser.add_argument('--key', nargs='+', required=True)
    args = parser.parse_args()
    try:
        print(dedupe(args.source, args.destination, args.key))
    except (OSError, ValueError, csv.Error) as error:
        parser.exit(1, f'Failed: {error}. Outputs without COMPLETE.txt are incomplete.\n')

Read the result before using it

Require COMPLETE.txt. It is written only after both CSV outputs close successfully. An exception may leave partial files in the newly created folder.
Read duplicates.csv, especially rows marked false in row_matches_first. All original fields from each excluded row are retained after the three audit columns.
Check that kept plus duplicates equals the input record count.
Retain the original export. Use a different new destination folder for another run.

The completion marker is an application-level success signal, not a guarantee against power loss or disk corruption. The script deliberately leaves partial output for inspection instead of automatically deleting files.

Common failures and choices

The destination already exists. Choose another folder name. Even an empty existing folder is refused, preventing an accidental overwrite of a previous result.

Unknown key column. Match the header exactly. For a column with spaces, quote it: --key "Order ID" "Line ID". Duplicate or blank header names are rejected before output is created.

Wrong field count or empty key. Check the reported data record. A blank line is not silently skipped; it has the wrong field count. Do not replace missing IDs with one shared placeholder just to make validation pass.

The file is not UTF-8. This version accepts UTF-8 with or without a leading BOM and writes UTF-8. It does not guess encodings or separators. Re-export a copy as comma-delimited UTF-8 if your source supports that. Semicolon and tab formats need an explicit dialect change before using this example.

There are too many unique records for memory. The script reads one record at a time but retains each first unique row in a dictionary for conflict comparison. Memory therefore grows with the number and width of unique rows. This is not a constant-memory solution or a benchmarked large-data tool; use a database-backed workflow for files that exceed available memory.

What was tested

Validation date: September 10, 2026. The accompanying tests exercise composite keys, an identical duplicate and a conflicting duplicate, leading-zero IDs, quoted commas and newlines, UTF-8 BOM input, invalid row widths, blank keys, header errors, and existing output refusal. Test execution results are recorded with this publication. Samples use invented data; no customer dataset or production-scale performance claim is involved.

CSV output preserves field values, not the original byte layout or quoting style. Opening a CSV in a spreadsheet can reinterpret IDs or formula-like text. This script does not sanitize formulas or control spreadsheet import settings. Inspect unfamiliar data as text and configure column types when importing it.

Continue the workflow

If the records are spread across several exports, merge CSV files with matching headers first. To inspect changes between two snapshots instead of discarding repeated keys, use compare CSV files by ID. For upload limits after cleaning, split a CSV by record count.

References

The Python CSV documentation explains string-valued records, quoting and newline=''. The built-in open documentation describes exclusive creation mode x. The duplicate-selection and audit rules above are this guide’s own implementation choices.