How to deduplicate a customer list in Python when no two rows are exactly the same

Removing duplicate rows sounds like set(rows). On a real customer list it rarely is, because the same person signed up twice almost never produces two identical rows. The order date differs, the city changed, the email was typed in capitals the second time. Whether those rows count as duplicates is not something the data can tell you. It depends on the key you compare, and that choice decides everything else: how many rows go, which one stays, and what you lose.

Below, the decisions are made one at a time in plain Python on a 412-row example list, and checked against the Remove Duplicates command in the Spreadsheet Editor of the osFoundry desktop app, which exposes the same choice as a single text field.

The list

The list is generated for the demo: four columns (Email, Name, City, Last order), 374 different customers, and 38 extra rows that repeat an earlier customer's email. Every repeat has its own order date, and for every third one the city was drawn again, the way a second sign-up after a move would look. Counted by email, 31 customers appear more than once: 25 of them twice, 5 three times, 1 four times.

import openpyxl

ws = openpyxl.load_workbook("en-customers.xlsx").active
header, *rows = [tuple(str(c) for c in r) for r in ws.iter_rows(values_only=True)]
len(rows)  # 412

One function, the key is the argument

Every variant below is the same loop with a different key. It keeps the first row it sees for each key, or, with keep="last", overwrites it in place:

def dedupe(rows, key, keep="first"):
    seen, out = {}, []
    for r in rows:
        k = key(r)
        if k in seen:
            if keep == "last":
                out[seen[k]] = r
            continue
        seen[k] = len(out)
        out.append(r)
    return out

A dict lookup per row, so it's linear and keeps the original order of the rows it keeps. pandas does the same with df.drop_duplicates(subset=[...], keep="first").

Whole row or a key

Three keys on the same 412 rows:

The whole-row key removes nothing. That is the default of most "remove duplicates" buttons, and on a list like this one it silently does no work, because no repeat shares its order date with the original. Email alone removes all 38. Email plus City removes 25 and keeps 13 rows that are the same person in a different city, which is right for a list of delivery addresses and wrong for a list of people.

The Spreadsheet Editor puts this choice into one field of its Remove Duplicates dialog, "Columns (blank = all, e.g. A,C)". Blank is the whole-row key. A is the email key.

Run with A, the status bar count on column A goes from 413 to 375, which is 374 emails plus the heading, the same number the Python version keeps.

Which copy survives

Keeping the first row is the convention in Excel, in pandas and in this editor, and it is arbitrary as far as your data is concerned. On this list the first row is not the customer's most recent order for 20 of the 31 repeated customers, and 12 of the 31 have copies that disagree on the city. After a first-row dedupe, those 12 keep whichever city was typed first.

If the newest row should win, sort before deduplicating. Python's sort is stable and ISO dates sort correctly as strings, so this is all it takes:

latest = dedupe(sorted(rows, key=lambda r: r[3], reverse=True), key=lambda r: r[0])
len(latest)  # 374, each with its latest order

In a spreadsheet the equivalent is sorting by the date column, newest first, before running Remove Duplicates.

Normalize the key, not the data

The example list has clean emails. Lists filled by people don't. To see what an exact comparison misses, the 38 repeat rows were rewritten in a copy of the list, cycling through five ways a second sign-up gets typed: all capitals, a leading space, a trailing space, a capitalized first letter, and the @ and . typed in full width by an input method (＠, ．). The first copy of each customer was left as it was.

import unicodedata

def email_key(e):
    return unicodedata.normalize("NFKC", e).strip().lower()

The exact key finds 3, and those are only later copies of the same customer that happened to be typed the same way as each other. strip().lower() catches everything except the full-width punctuation, which looks almost identical on screen. NFKC folds the full-width forms into ASCII and gets all 38.

The normalized value is only the key. The row that stays keeps the email exactly as it was written, which matters if the list goes back into a system that stores it.

Two cautions. The part before the @ is case-sensitive by the letter of RFC 5321, so lowercasing it is a practical assumption that holds for the big providers, not a rule. And provider-specific tricks, such as ignoring dots in Gmail addresses, merge addresses that other providers treat as different people; they belong in a separate, deliberate step.

What normalization costs

Building a set of keys from 1,000,000 emails (the example list repeated), Python 3.12 on an Apple M1, median of 5 runs:

The normalization is where the time goes, and it is still a tenth of a second per million rows. There is no reason to skip it for speed.

The order that works

Highlight first, then remove. Highlighting duplicates on the key column (in the editor, Conditional Formatting → Highlight Cells Rules → Duplicate Values, which marks the first copy too) shows you the groups before anything is deleted, and that is where you catch the shared family address that shouldn't be merged. Then pick the key, decide which copy wins and sort for it, normalize the key if people typed the data, and check the count afterwards.

The scripts behind every number here use only openpyxl and the standard library. The Spreadsheet Editor runs on your own machine, as the page on osFoundry's local-first design explains, and the app is free from the osFoundry download page.