Deduplicate a Large CSV with a Hash Index

Remove duplicate rows from a huge CSV using an in-memory hash set of key hashes so it runs in a single streaming pass.

~35 mindatapythoncsv

Task

Deduplicate a multi-gigabyte CSV by hashing keys and keeping one row per key.

When to use this

A merged export contains millions of duplicate rows and cannot be loaded into memory at once as a dataframe.

Prerequisites

Python 3 with the standard library
A CSV with a natural key
Disk space for the output

Steps

1. Stream the file and keep a set of seen key hashes.

import csv, hashlib, sys
seen = set()
with open('in.csv', newline='') as fin, open('out.csv', 'w', newline='') as fout:
    reader = csv.reader(fin)
    writer = csv.writer(fout)
    header = next(reader); writer.writerow(header)
    for row in reader:
        key = hashlib.sha1('\x1f'.join(row[:4]).encode()).digest()
        if key in seen: continue
        seen.add(key); writer.writerow(row)

Expected output: Only the first occurrence of each key is written, using constant memory per unique key.

2. Report how many rows were dropped.

wc -l in.csv out.csv

Expected output: The output line count is lower than the input by the number of duplicates.

Troubleshooting

Problem

Hash collisions

Solution

Use a strong hash such as sha256 and join key fields with an unlikely delimiter.

Problem

Memory grows too large

Solution

Shard by hash prefix or use a disk-backed set if unique keys exceed memory.

Limitations

Memory is proportional to unique keys, not rows.
Not suitable if key fields are inconsistent in case.
Single pass means no cross-file reconciliation.

What this recipe does

Deduplicate a multi-gigabyte CSV by hashing keys and keeping one row per key.

A merged export contains millions of duplicate rows and cannot be loaded into memory at once as a dataframe.

Prerequisites

Python 3 with the standard library
A CSV with a natural key
Disk space for the output

Procedure

Stream the file and keep a set of seen key hashes.

import csv, hashlib, sys
seen = set()
with open('in.csv', newline='') as fin, open('out.csv', 'w', newline='') as fout:
    reader = csv.reader(fin)
    writer = csv.writer(fout)
    header = next(reader); writer.writerow(header)
    for row in reader:
        key = hashlib.sha1('\x1f'.join(row[:4]).encode()).digest()
        if key in seen: continue
        seen.add(key); writer.writerow(row)

Expected result: Only the first occurrence of each key is written, using constant memory per unique key.

Report how many rows were dropped.

wc -l in.csv out.csv

Expected result: The output line count is lower than the input by the number of duplicates.

How to confirm it worked

Run the commands above and confirm every expected result in order. If a step does not produce the stated output, use the troubleshooting notes before continuing. A passing run shows the output has one row per key and the duplicate count matches the difference in line counts.

Troubleshooting

Hash collisions — Use a strong hash such as sha256 and join key fields with an unlikely delimiter.

Memory grows too large — Shard by hash prefix or use a disk-backed set if unique keys exceed memory.

Limitations

Memory is proportional to unique keys, not rows.
Not suitable if key fields are inconsistent in case.
Single pass means no cross-file reconciliation.