Cara menghapus duplikat di daftar pelanggan dengan Python saat tidak ada dua baris yang persis sama

Menghapus baris duplikat kedengarannya cukup dengan set(rows). Di daftar pelanggan sungguhan, jarang semudah itu, karena orang yang sama mendaftar dua kali hampir tidak pernah menghasilkan dua baris yang identik. Tanggal pesanannya beda, kotanya sudah pindah, atau email-nya diketik pakai huruf kapital di pendaftaran kedua. Apakah baris-baris itu dihitung sebagai duplikat tidak bisa dijawab oleh datanya sendiri. Semuanya tergantung pada key yang dibandingkan, dan pilihan itu menentukan semua hal lainnya: berapa baris yang terhapus, baris mana yang tersisa, dan apa yang hilang.

Di bawah ini keputusannya diambil satu per satu dengan Python biasa pada daftar contoh berisi 412 baris, lalu dicocokkan dengan perintah Remove Duplicates di Spreadsheet Editor aplikasi desktop osFoundry, yang menyediakan pilihan yang sama dalam satu kolom teks.

Daftarnya

Daftar ini dibuat khusus untuk demo: empat kolom (Email, Name, City, Last order), 374 pelanggan berbeda, dan 38 baris tambahan yang mengulang email pelanggan sebelumnya. Setiap baris ulangan punya tanggal pesanan sendiri, dan untuk setiap baris ulangan ketiga, kotanya diacak ulang, seperti tampilan pendaftaran kedua setelah pindah rumah. Kalau dihitung berdasarkan email, ada 31 pelanggan yang muncul lebih dari sekali: 25 di antaranya dua kali, 5 tiga kali, dan 1 empat kali.

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

Satu fungsi, key-nya jadi argumen

Semua varian di bawah memakai loop yang sama dengan key yang berbeda. Fungsi ini menyimpan baris pertama yang ditemui untuk setiap key, atau, dengan keep="last", menimpanya di posisi yang sama:

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

Satu lookup dict per baris, jadi waktunya linear dan urutan asli baris yang disimpan tetap terjaga. pandas melakukan hal yang sama dengan df.drop_duplicates(subset=[...], keep="first").

Seluruh baris atau sebuah key

Tiga key pada 412 baris yang sama:

key	baris disimpan	baris dihapus
seluruh baris	412	0
Email	374	38
Email + City	387	25

Key seluruh baris tidak menghapus apa pun. Itulah default kebanyakan tombol "hapus duplikat", dan di daftar seperti ini tombol itu diam-diam tidak melakukan apa-apa, karena tidak ada baris ulangan yang tanggal pesanannya sama dengan baris aslinya. Email saja menghapus semua 38 baris. Email ditambah City menghapus 25 dan menyisakan 13 baris yang sebenarnya orang yang sama di kota yang berbeda. Itu benar untuk daftar alamat pengiriman, tapi salah untuk daftar orang.

Spreadsheet Editor menaruh pilihan ini di satu kolom pada dialog Remove Duplicates, yaitu "Columns (blank = all, e.g. A,C)". Kosong berarti key seluruh baris. A berarti key email.

Setelah dijalankan dengan A, hitungan di status bar untuk kolom A turun dari 413 menjadi 375, yaitu 374 email ditambah baris judul, sama dengan jumlah yang disimpan versi Python.

Salinan mana yang bertahan

Menyimpan baris pertama adalah konvensi di Excel, di pandas, dan di editor ini, dan dari sudut pandang data Anda, itu pilihan yang sembarang. Di daftar ini, baris pertama bukan pesanan terbaru untuk 20 dari 31 pelanggan yang berulang, dan 12 dari 31 pelanggan itu punya salinan dengan kota yang berbeda. Setelah dedupe berdasarkan baris pertama, 12 pelanggan itu memakai kota mana pun yang kebetulan diketik lebih dulu.

Kalau baris terbaru yang harus menang, urutkan dulu sebelum menghapus duplikat. Sort di Python bersifat stable dan tanggal ISO terurut dengan benar sebagai string, jadi cukup begini:

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

Di spreadsheet, padanannya adalah mengurutkan kolom tanggal dari yang terbaru sebelum menjalankan Remove Duplicates.

Normalisasi key-nya, bukan datanya

Email di daftar contoh ini bersih. Daftar yang diisi manusia tidak begitu. Untuk melihat apa yang terlewat oleh perbandingan persis, 38 baris ulangan ditulis ulang di salinan daftar, bergantian dengan lima cara pendaftaran kedua biasanya diketik: semua huruf kapital, spasi di depan, spasi di belakang, huruf pertama kapital, serta @ dan . yang diketik dalam bentuk full-width oleh input method (＠, ．). Salinan pertama setiap pelanggan dibiarkan apa adanya.

import unicodedata

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

key	baris disimpan	baris dihapus
persis	409	3
strip().lower()	381	31
NFKC + strip().lower()	374	38

Key persis hanya menemukan 3, dan itu pun cuma salinan berikutnya dari pelanggan yang sama yang kebetulan diketik dengan cara yang sama satu sama lain. strip().lower() menangkap semuanya kecuali tanda baca full-width, yang di layar terlihat hampir sama. NFKC mengubah bentuk full-width menjadi ASCII dan menangkap semua 38.

Nilai yang dinormalisasi hanya dipakai sebagai key. Baris yang tersisa tetap menyimpan email persis seperti yang ditulis, dan ini penting kalau daftarnya dikembalikan ke sistem yang menyimpannya.

Ada dua catatan. Bagian sebelum @ secara harfiah bersifat case-sensitive menurut RFC 5321, jadi mengubahnya ke huruf kecil adalah asumsi praktis yang berlaku untuk provider besar, bukan aturan. Lalu trik khusus provider, seperti mengabaikan titik di alamat Gmail, bisa menggabungkan alamat yang oleh provider lain dianggap orang berbeda; trik seperti itu sebaiknya jadi langkah terpisah yang memang disengaja.

Berapa biaya normalisasi

Membuat set key dari 1,000,000 email (daftar contoh yang diulang), Python 3.12 di Apple M1, median dari 5 kali run:

key	waktu
string mentah	5 ms
strip().lower()	80 ms
NFKC + strip().lower()	102 ms

Waktunya memang habis di normalisasi, tapi tetap hanya sepersepuluh detik per sejuta baris. Tidak ada alasan untuk melewatkannya demi kecepatan.

Urutan yang berhasil

Tandai dulu, baru hapus. Menandai duplikat di kolom key (di editor: Conditional Formatting → Highlight Cells Rules → Duplicate Values, yang juga menandai salinan pertama) memperlihatkan kelompok-kelompoknya sebelum ada yang dihapus, dan di situlah Anda bisa melihat alamat keluarga bersama yang seharusnya tidak digabung. Setelah itu pilih key, tentukan salinan mana yang menang dan urutkan sesuai itu, normalisasi key kalau datanya diketik manusia, lalu cek hitungannya sesudahnya.

Skrip di balik semua angka di sini hanya memakai openpyxl dan standard library. Spreadsheet Editor berjalan di komputer Anda sendiri, seperti dijelaskan di halaman desain local-first osFoundry, dan aplikasinya gratis dari halaman download osFoundry.