deploy notes
Automation

Remove duplicate CSV records with Python using a clear matching rule

Remove exact repeated records from a small CSV, keep conflicting versions in a separate review file, and verify the result without overwriting your export.

Beginner6 min read
In this guide

Two rows with the same order ID are not always duplicates. They might be identical exports, or they might disagree about the quantity. Deleting the second row in both cases could hide a real correction.

Here you will keep one copy of an identical record and put every row with a conflicting ID into a separate review file. Your original CSV stays unchanged. The result is a cleaned file you can inspect and a short, explicit list of decisions still needed.

Decide what identifies one record

Choose a column that should identify exactly one record, such as order_id in a report with one row per order. This is the matching key. Do not use a person’s name, and do not use an order ID alone if each order legitimately has several product rows. In that case, obtain an export with a unique line-item ID and use that column instead.

This guide uses exact text matching. It does not guess that ABC, abc, and ABC mean the same thing, or recognize people from similar names. All fields must match for rows sharing a key to be safely collapsed.

You need Python 3.12 or newer, a terminal, and a plain-text editor. Check with py --version in Windows PowerShell or python3 --version in macOS/Linux Terminal. If needed, install Python from the official downloads page, reopen the terminal, and retry. No extra packages or server are needed.

Prepare a small CSV

Create a folder named csv-clean. Put a copy of your UTF-8, comma-separated CSV with a header in it, or save this fictional example as orders.csv:

order_id,item,quantity
1001,Notebook,2
1001,Notebook,2
1002,Mug,1
1002,Mug,3
1003,Pen,4
1004,Folder,1

Use UTF-8 plain text; in Notepad, choose All files to avoid an added .txt extension. The example has six records: one safe duplicate of order 1001, two conflicting versions of 1002, and two other orders. The cleaned file should have three records; the review file should retain both versions of 1002.

Save the script

Save this as dedupe_csv.py in the same folder:

import argparse
import csv
import sys
from pathlib import Path

sys.stdout.reconfigure(errors="backslashreplace")

parser = argparse.ArgumentParser(description="Separate duplicates from conflicts.")
parser.add_argument("file")
parser.add_argument("--key", required=True)
parser.add_argument("--output-dir", required=True)
args = parser.parse_args()

try:
    groups = {}
    total = 0
    with open(args.file, encoding="utf-8-sig", newline="") as source:
        reader = csv.reader(source, strict=True)
        header = next(reader, None)
        if not header or any(not name.strip() for name in header):
            raise ValueError("Missing column names.")
        if len(header) != len(set(header)):
            raise ValueError("Column names must be unique.")
        if args.key not in header:
            raise ValueError(f"Key column not found: {args.key}")
        key_index = header.index(args.key)
        for row in reader:
            if not row:
                continue
            if len(row) != len(header):
                raise ValueError(f"Wrong cell count near line {reader.line_num}.")
            key = row[key_index]
            if not key.strip():
                raise ValueError(f"Blank key near line {reader.line_num}.")
            groups.setdefault(key, []).append(row)
            total += 1

    clean = []
    review = []
    for rows in groups.values():
        if all(row == rows[0] for row in rows):
            clean.append(rows[0])
        else:
            review.extend(rows)  # Keep every version for a human decision.

    folder = Path(args.output_dir)
    folder.mkdir()  # Refuse an existing folder, even if it is empty.
    for name, rows in [("clean.csv", clean), ("review.csv", review)]:
        with (folder / name).open("x", encoding="utf-8", newline="") as output:
            writer = csv.writer(output)
            writer.writerow(header)
            writer.writerows(rows)
    removed = total - len(clean) - len(review)
    print(f"Input: {total}; clean: {len(clean)}; review: {len(review)}; removed: {removed}")
    print(f"Saved clean.csv and review.csv in {folder}")
except (OSError, UnicodeError, csv.Error, ValueError) as error:
    sys.exit(f"Cleanup failed: {error}")

The script holds the input in memory. Use small exports that fit comfortably in available memory. It ignores completely blank lines but refuses records with a missing key or the wrong number of cells.

Run it and account for every row

In your terminal, enter cd "your folder path", replacing the quoted text with the full path to csv-clean. Run:

python3 dedupe_csv.py orders.csv --key order_id --output-dir checked-orders

On Windows, replace python3 with py. For your own export, replace the filename and order_id with its exact key column name. Quote names containing spaces. checked-orders must be a new folder name; let the script create it.

The example should report:

Input: 6; clean: 3; review: 2; removed: 1
Saved clean.csv and review.csv in checked-orders

Open both output files in a text editor. clean.csv should contain orders 1001, 1003, and 1004. review.csv should contain both quantities for 1002, with none of that order in the cleaned file. Each output has one header, which is not included in the counts.

Check the arithmetic: 3 clean + 2 review + 1 removed = 6 input records. The removed count includes only redundant copies from non-conflicting groups. Conflicting groups retain all their rows, including any repeated versions, for review.

Before importing the cleaned file as a complete dataset, resolve the review rows against your original system. For the example, find the correct quantity for order 1002, then add one verified record to a new copy of clean.csv. The script cannot decide whether 1 or 3 is correct. If there are no conflicts, review.csv contains only its header.

If the result is not what you expected

  • The output folder exists: use a new name such as checked-orders-v2; previous results will not be overwritten.
  • Blank key or key column not found: inspect your header and records. Fix the export or choose the actual unique record column; do not group all blank IDs together.
  • Many conflicts: check the matching rule before cleaning data by hand. You may have used an order-level ID for a line-item report.
  • Wrong cell count or encoding error: re-export a correctly quoted, UTF-8 CSV. The CSV reading guide explains these checks.

A header-only input creates two header-only outputs and reports zero records. That is valid structure, but not a successful export if you expected data. If writing fails or is interrupted, do not use the partial output folder; fix the cause and rerun with a new folder name. The original remains your recovery copy.

← Back to automation guides