Wie man eine Kundenliste in Python dedupliziert, wenn keine zwei Zeilen exakt gleich sind

Doppelte Zeilen entfernen klingt nach set(rows). Bei einer echten Kundenliste ist es das selten, denn wenn sich dieselbe Person zweimal anmeldet, entstehen fast nie zwei identische Zeilen. Das Bestelldatum ist ein anderes, die Stadt hat sich geändert, die E-Mail wurde beim zweiten Mal in Großbuchstaben getippt. Ob diese Zeilen als Duplikate zählen, können einem die Daten nicht sagen. Es hängt vom Schlüssel ab, den man vergleicht, und diese Wahl entscheidet alles Weitere: wie viele Zeilen wegfallen, welche bleibt und was dabei verloren geht.

Unten werden diese Entscheidungen einzeln in reinem Python an einer Beispielliste mit 412 Zeilen getroffen und mit dem Befehl Remove Duplicates im Spreadsheet Editor der Desktop-App von osFoundry abgeglichen, der dieselbe Wahl auf ein einziges Textfeld reduziert.

Die Liste

Die Liste ist für die Demo generiert: vier Spalten (Email, Name, City, Last order), 374 verschiedene Kunden und 38 zusätzliche Zeilen, die die E-Mail eines früheren Kunden wiederholen. Jede Wiederholung hat ihr eigenes Bestelldatum, und bei jeder dritten wurde die Stadt neu gezogen, so wie eine zweite Anmeldung nach einem Umzug aussehen würde. Nach E-Mail gezählt kommen 31 Kunden mehr als einmal vor: 25 davon zweimal, 5 dreimal, 1 viermal.

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

Eine Funktion, der Schlüssel ist das Argument

Jede Variante unten ist dieselbe Schleife mit einem anderen Schlüssel. Sie behält die erste Zeile, die sie für einen Schlüssel sieht, oder überschreibt sie mit keep="last" an Ort und Stelle:

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

Ein Dict-Lookup pro Zeile, also linear, und die behaltenen Zeilen bleiben in ihrer ursprünglichen Reihenfolge. pandas macht dasselbe mit df.drop_duplicates(subset=[...], keep="first").

Ganze Zeile oder ein Schlüssel

Drei Schlüssel auf denselben 412 Zeilen:

Schlüssel	Zeilen behalten	Zeilen entfernt
ganze Zeile	412	0
Email	374	38
Email + City	387	25

Der Schlüssel „ganze Zeile“ entfernt nichts. Das ist die Voreinstellung der meisten „Duplikate entfernen“-Buttons, und bei einer Liste wie dieser tut er stillschweigend gar nichts, weil keine Wiederholung dasselbe Bestelldatum wie das Original hat. Die E-Mail allein entfernt alle 38. E-Mail plus City entfernt 25 und behält 13 Zeilen, die dieselbe Person in einer anderen Stadt sind. Für eine Liste von Lieferadressen ist das richtig, für eine Liste von Personen falsch.

Der Spreadsheet Editor packt diese Wahl in ein einziges Feld seines Remove-Duplicates-Dialogs, "Columns (blank = all, e.g. A,C)". Leer ist der Schlüssel „ganze Zeile“. A ist der E-Mail-Schlüssel.

Mit A ausgeführt, springt die Anzahl in der Statusleiste für Spalte A von 413 auf 375, also 374 E-Mails plus die Überschrift, genau die Zahl, die auch die Python-Version behält.

Welche Kopie überlebt

Die erste Zeile zu behalten ist die Konvention in Excel, in pandas und in diesem Editor, und aus Sicht der eigenen Daten ist das willkürlich. In dieser Liste ist die erste Zeile bei 20 der 31 mehrfach vorkommenden Kunden nicht die neueste Bestellung, und bei 12 der 31 widersprechen sich die Kopien bei der Stadt. Nach einer Deduplizierung, die die erste Zeile behält, bleibt bei diesen 12 die Stadt stehen, die zuerst eingetippt wurde.

Wenn die neueste Zeile gewinnen soll, vor dem Deduplizieren sortieren. Pythons Sortierung ist stabil, und ISO-Datumswerte sortieren sich als Strings korrekt, also reicht das hier:

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 einer Tabellenkalkulation entspricht das dem Sortieren nach der Datumsspalte, neueste zuerst, bevor man Remove Duplicates ausführt.

Den Schlüssel normalisieren, nicht die Daten

Die Beispielliste hat saubere E-Mails. Listen, die Menschen ausfüllen, haben das nicht. Um zu sehen, was ein exakter Vergleich übersieht, wurden die 38 Wiederholungszeilen in einer Kopie der Liste umgeschrieben, reihum in fünf Varianten, wie eine zweite Anmeldung typischerweise eingetippt wird: alles in Großbuchstaben, ein Leerzeichen am Anfang, ein Leerzeichen am Ende, ein großer Anfangsbuchstabe sowie @ und . in voller Breite, wie sie eine Eingabemethode erzeugt (＠, ．). Die erste Kopie jedes Kunden blieb unverändert.

import unicodedata

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

Schlüssel	Zeilen behalten	Zeilen entfernt
exakt	409	3
strip().lower()	381	31
NFKC + strip().lower()	374	38

Der exakte Schlüssel findet 3, und das sind nur spätere Kopien desselben Kunden, die zufällig untereinander gleich getippt wurden. strip().lower() erwischt alles außer den Satzzeichen in voller Breite, die auf dem Bildschirm fast identisch aussehen. NFKC faltet die vollbreiten Formen auf ASCII und findet alle 38.

Der normalisierte Wert ist nur der Schlüssel. Die Zeile, die bleibt, behält die E-Mail genau so, wie sie geschrieben wurde. Das ist wichtig, wenn die Liste zurück in ein System geht, das sie speichert.

Zwei Einschränkungen. Der Teil vor dem @ unterscheidet laut Wortlaut von RFC 5321 zwischen Groß- und Kleinschreibung. Ihn kleinzuschreiben ist also eine praktische Annahme, die bei den großen Anbietern hält, keine Regel. Und anbieterspezifische Tricks, etwa das Ignorieren von Punkten in Gmail-Adressen, führen Adressen zusammen, die andere Anbieter als verschiedene Personen behandeln; die gehören in einen eigenen, bewussten Schritt.

Was die Normalisierung kostet

Ein Set von Schlüsseln aus 1.000.000 E-Mails aufbauen (die Beispielliste wiederholt), Python 3.12 auf einem Apple M1, Median aus 5 Läufen:

Schlüssel	Zeit
roher String	5 ms
strip().lower()	80 ms
NFKC + strip().lower()	102 ms

Die Zeit geht in die Normalisierung, und es bleibt trotzdem eine Zehntelsekunde pro Million Zeilen. Es gibt keinen Grund, sie aus Geschwindigkeitsgründen wegzulassen.

Die Reihenfolge, die funktioniert

Erst markieren, dann entfernen. Duplikate in der Schlüsselspalte hervorheben (im Editor Conditional Formatting → Highlight Cells Rules → Duplicate Values, das auch die erste Kopie markiert) zeigt die Gruppen, bevor irgendetwas gelöscht wird, und genau dort fällt die gemeinsame Familienadresse auf, die nicht zusammengeführt werden sollte. Danach den Schlüssel wählen, festlegen, welche Kopie gewinnt, und entsprechend sortieren, den Schlüssel normalisieren, wenn Menschen die Daten eingetippt haben, und hinterher die Anzahl prüfen.

Die Skripte hinter allen Zahlen hier verwenden nur openpyxl und die Standardbibliothek. Der Spreadsheet Editor läuft auf dem eigenen Rechner, wie die Seite zum Local-first-Design von osFoundry erklärt, und die App gibt es kostenlos auf der Download-Seite von osFoundry.