Remove Duplicate Rows Across Many Large Excel Files

Weekly exports often overlap. The system emits "the last 30 days" every week, so each record appears in four or five files; someone re-sends a file with a different name; two branches export the same shared customers. Combining a year of such files with pd.concat and drop_duplicates works until the combined data no longer fits comfortably in memory — or until you need to know which file each duplicate came from. A streaming approach handles any number of files with a small, fixed memory cost: read one row at a time, compute a fingerprint from the fields that define "the same record", keep only the set of fingerprints seen so far, and write each first-seen row straight to the output. This page builds that, logs every duplicate, and handles the "keep the latest version" case. It belongs to Working with Large Excel Files in Python.

Prerequisites

pip install openpyxl xlsxwriter pandas

The exports/ folder holds eight exports of up to 15,000 orders each — 119,500 rows in total — covering overlapping windows, so most orders appear in two or three files. Some exports write order IDs in lower case, and later exports carry updated statuses for some orders. The task: one workbook with each order exactly once.

Step 1 — Decide what makes two rows "the same"

Duplicates are defined by a key, not by entire rows. Here an order is identified by its order ID — but the same order exported twice may differ in other fields (an updated status, a different export date), and the ID may be written in different case. Normalise the key fields and fingerprint them:

import hashlib

KEY = ["Order ID"]

def fingerprint(record):
    parts = [str(record[k]).strip().upper() for k in KEY]
    return hashlib.blake2b("\x1f".join(parts).encode(), digest_size=8).digest()

print(fingerprint({"Order ID": "SO-100042"}) == fingerprint({"Order ID": " so-100042 "}))

The fingerprint is an 8-byte BLAKE2 hash of the normalised key fields, joined with a separator character that cannot appear in the data. Normalising first — trimming and upper-casing — means so-100042 and SO-100042 count as the same order. Storing 8-byte hashes instead of the key strings keeps memory small: a million fingerprints take a few tens of megabytes in a Python set, regardless of how many columns the rows have. With 64 bits, the chance of two different keys colliding is negligible even for billions of rows. If the key is several columns, list them all in KEY.

Step 2 — Stream every file, write unique rows as you go

Read each workbook in openpyxl's read-only mode, one row at a time, and write rows whose fingerprint has not been seen to an XlsxWriter workbook in constant-memory mode. Duplicates go to a log instead:

from pathlib import Path
from openpyxl import load_workbook
import xlsxwriter

files = sorted(Path("exports").glob("orders_week*.xlsx"))
seen = set()
dupes = []

out = xlsxwriter.Workbook("orders_unique.xlsx", {"constant_memory": True})
ws_out = out.add_worksheet("Orders")
date_fmt = out.add_format({"num_format": "yyyy-mm-dd"})
out_row = 0

for f in files:
    wb = load_workbook(f, read_only=True)
    rows = wb.active.iter_rows(values_only=True)
    header = list(next(rows))
    if out_row == 0:
        ws_out.write_row(0, 0, header + ["Source file"])
        out_row = 1
    for excel_row, values in enumerate(rows, start=2):
        record = dict(zip(header, values))
        fp = fingerprint(record)
        if fp in seen:
            dupes.append((record["Order ID"], f.name, excel_row))
            continue
        seen.add(fp)
        for c, v in enumerate(values):
            if hasattr(v, "year"):
                ws_out.write_datetime(out_row, c, v, date_fmt)
            else:
                ws_out.write(out_row, c, v)
        ws_out.write(out_row, len(values), f.name)
        out_row += 1
    wb.close()
out.close()
print(f"read {sum(1 for _ in dupes) + out_row - 1:,} rows from {len(files)} files: "
      f"{out_row - 1:,} unique, {len(dupes):,} duplicates")

Only one row of one file is in memory at a time, plus the set of fingerprints. Each unique row is written immediately with a source-file column, so the output says where every record came from; dates are written as real dates. The duplicate log keeps the order ID, file and Excel row number of every skipped row — the evidence anyone needs to check a surprising count. The result: 119,500 rows read, 60,000 unique orders written and 59,500 duplicates logged. Files are processed in name order, which here is also date order, so each order is kept from the first export it appeared in.

Step 3 — Keep the latest copy instead of the first

Keeping the first copy is wrong when later exports carry corrections — here, later exports have updated statuses. To keep the latest copy without loading everything, make two passes: first find which file holds the newest copy of each key, then write only those rows:

latest = {}                                      # fingerprint -> index of the newest file containing it
for i, f in enumerate(files):                    # files are in export order
    wb = load_workbook(f, read_only=True)
    rows = wb.active.iter_rows(values_only=True)
    header = list(next(rows))
    key_cols = [header.index(k) for k in KEY]
    for values in rows:
        latest[fingerprint({k: values[c] for k, c in zip(KEY, key_cols)})] = i
    wb.close()

out = xlsxwriter.Workbook("orders_latest.xlsx", {"constant_memory": True})
ws_out = out.add_worksheet("Orders")
r, written = 1, set()
for i, f in enumerate(files):
    wb = load_workbook(f, read_only=True)
    rows = wb.active.iter_rows(values_only=True)
    header = list(next(rows))
    if i == 0:
        ws_out.write_row(0, 0, header + ["Source file"])
    for values in rows:
        fp = fingerprint(dict(zip(header, values)))
        if latest[fp] == i and fp not in written:
            written.add(fp)
            ws_out.write_row(r, 0, [v.date().isoformat() if hasattr(v, "year") else v for v in values] + [f.name])
            r += 1
    wb.close()
out.close()
print(f"{r - 1:,} unique orders, each from its latest export")

The first pass stores one small integer per fingerprint — the index of the newest file that contains it. The second pass writes each row only from that file. Memory is still proportional to the number of unique keys, not to the data. The cost is reading every file twice; with fast readers that is usually acceptable, and still far cheaper than loading all the files together. (For brevity this pass writes dates as ISO text; use write_datetime as in Step 2 in real code.) The written set guards against a key that appears twice within one file.

Step 4 — Check the result

Verify both outputs with pandas — which is fine at this stage, because each output holds only unique rows:

import pandas as pd

first = pd.read_excel("orders_unique.xlsx")
last = pd.read_excel("orders_latest.xlsx")
for name, df in (("first", first), ("latest", last)):
    ids = df["Order ID"].str.upper()
    print(f"{name:6} rows {len(df):,}  duplicate ids {ids.duplicated().sum()}  "
          f"shipped {int((df['Status'] == 'shipped').sum()):,}")

log = pd.DataFrame(dupes, columns=["Order ID", "File", "Row"])
print(log["File"].value_counts().sort_index().to_dict())

Both outputs contain 60,000 orders with no duplicate IDs. But they disagree sharply on status: keeping the first copy leaves 12,880 orders marked shipped, keeping the latest gives 54,050, because each order is taken from its most recent export, after its status had moved on. Which is right depends on the question — but for a current order book, keeping the first copy would report tens of thousands of shipped orders as still open. The duplicate log, summarised by file — 8,500 repeats in each export after the first — shows how much each export overlapped with the ones before it — a useful sanity check that the files really are the overlapping windows you think they are. Writing the log to its own sheet, or to a CSV for very large runs, keeps the evidence with the result.

When pandas is the better tool

If the combined data fits comfortably in memory — say, a few hundred thousand rows on a normal laptop — pd.concat followed by drop_duplicates(subset=..., keep="last") is simpler, and normalising keys first works the same way: add a column with the normalised key and de-duplicate on that. The streaming version earns its place when the files keep growing, when memory is limited (a container, a shared server), or when you need a per-row duplicate log. Both give the same answer; the test in Step 4 is the same either way. Duplicate detection within a single file is covered in Find Duplicate Rows in Excel with Python.

Common pitfalls

• Duplicates defined on whole rows. Updated fields make true duplicates look different; define a key.
• Unnormalised keys. Case and spaces make the same key look different.
• Keeping the first copy when later files correct earlier ones. Use the two-pass latest approach.
• No record of what was dropped. Log every duplicate with file and row.
• Processing files in an arbitrary order. Sort by export date so "first" and "latest" mean what you intend.

Conclusion

To remove duplicates across many large Excel files, stream them: read each workbook row by row in read-only mode, fingerprint every record from its normalised key fields, keep only the set of fingerprints in memory, and write first-seen rows straight to an XlsxWriter workbook in constant-memory mode while logging every duplicate with its file and row. When later exports correct earlier ones, a two-pass variant keeps each record's latest copy. Memory then depends on the number of unique keys, not on the size or number of files.

Frequently asked questions

How do I remove duplicates across several Excel files in Python? Read the files in order and keep a set of keys already seen; write a row only if its key is new. For data that fits in memory, pd.concat followed by drop_duplicates on the key columns does the same.

How do I de-duplicate Excel data that is too big for memory? Stream rows with openpyxl read-only mode, keep a set of compact hashes of the key fields, and write unique rows directly to the output with XlsxWriter constant_memory mode.

How do I keep the latest version of a duplicated record? Make a first pass recording, for each key, the newest file that contains it, then a second pass writing each row only from that file.

Why are obvious duplicates not detected? Usually because the key differs in case or spacing, or because duplicates are compared on whole rows where some fields changed. Normalise the key and compare on the key only.

Related

• Up one level: Working with Large Excel Files in Python — big files, fast code.
• Keep the Latest Record per Key with pandas — the in-memory version of Step 3.
• Append New Rows to a Master Excel File Without Duplicates — de-duplicating incrementally.
• Write Large DataFrames to Excel with Write-Only Mode — streaming output with openpyxl.