CSVファイルの重複行を削除する方法

完全に同じ行だけを、ヘッダーと元の順序を保ったまま削除するには次のようにします。

awk 'NR==1 || !seen[$0]++' input.csv > deduped.csv

これは簡単なケースの対処法ですが、その簡単なケースは見た目より少ないものです。実際の作業ファイルにある「重複」の多くは、同じ行ではなく、同じ人物の2件のレコードです。大文字小文字が違っていたり、末尾に空白があったり、電話番号が更新されていたりします。間違った方を消すとデータを失うので、手順の順番が大切です。まず何をもって同じ行とするかを決め、次に違いを確認し、最後に削除します。

ステップ1：キーを決める

重複とは、同じものを表す2行のことです。どの列でそれを判断するかは、ファイルの性質ではなく、データについての判断です。

• 行全体の重複は、すべてのフィールドが同じで、ほぼ間違いなくミスです。二重インポート、再実行されたジョブ、2回走った追記などが原因です。安全に削除できます。
• キーの重複は、email や order_id が同じで、ほかが全部違うものです。これはデータの問題です。片方はおそらく新しいか、より完全で、どちらを残すか選ぶ必要があります。

これを取り違えるのが、レコードを失う典型的なパターンです。顧客リストで name を基準に重複を除くと、同姓同名の別人2人を平然と消してしまいます。

ステップ2：削除する前に数える

重複削除は、中身を見ずに実行してはいけません。消そうとしているものを確認します。

duckdb -c "SELECT email, count(*) n FROM read_csv('input.csv') GROUP BY email HAVING n > 1 ORDER BY n DESC LIMIT 20"

キーが正しいかどうかは、2つの数字でわかります。重複が数件なら、本物の二重入力でしょう。ファイルの3分の1が「重複」なら、キーが間違っています。もともと一意ではなかった列で照合しているのです。

次に、具体的な1件をすべての列で確認します。ここは多くの人が飛ばす手順です。

duckdb -c "SELECT * FROM read_csv('input.csv') WHERE email = '[email protected]'"

2行が、まさに気にしていた列で食い違っていることが、ここでわかります。

ステップ3：残す行を選ぶ

行が違うとわかった時点で、「重複を削除する」は「正しい方を残す」に変わります。ルールは3つあり、正しい頻度のおおよその順に並べています。

最新のものを残す。 タイムスタンプがあれば、たいていこれが答えです。

duckdb -c "COPY (SELECT * FROM (SELECT *, row_number() OVER (PARTITION BY email ORDER BY updated_at DESC) rn FROM read_csv('input.csv')) WHERE rn = 1) TO 'deduped.csv' (HEADER)"

最も完全なものを残す。 タイムスタンプがないときは、空でないフィールドの数で並べると、まずまずの代用になります。埋まっているレコードの方が、たいてい後のものだからです。(col_a IS NOT NULL)::INT + (col_b IS NOT NULL)::INT + … で並べ、先頭を取ります。

2つを統合する。 互いに相手にない情報を持っていることもあります。よくあることで、近道はありません。列ごとの coalesce になります。件数が少なければ、大量のデータで下手に自動化するより、手作業の方が確実です。

どれを選ぶにしても、削除する前に、捨てる行を別のファイルに書き出してください。元に戻せない重複削除は、この先2週間のどこかで必ず後悔します。

ほぼ重複（実はこれが大半）

完全一致では、現実の重複の大半を見逃します。先に正規化し、そのあとで比較し、元の値は残しておきます。

duckdb -c "SELECT lower(trim(email)) k, count(*) n FROM read_csv('input.csv') GROUP BY k HAVING n > 1"

効果の大きい順に、適用する正規化は次のとおりです。

• 大文字小文字。 [email protected] と [email protected] は同じメールボックスです。
• 空白。 コピー＆ペーストで入る末尾の空白は見えないうえ、非常によくあります。trim() は先頭と末尾を除去します。Webフォームから入る non-breaking space（不可分スペース）は replace で処理します。
• 識別子の記号。 +44 20 7946 0958、02079460958、(020) 7946 0958 は同じ電話番号です。比較する前に、数字以外をすべて取り除きます。
• Gmailのドットとプラスアドレス。 [email protected] は [email protected] と同じ受信箱に届きます。これを重複とみなすかは、人を重複排除するのか、アカウントを重複排除するのかで変わります。

自動でやってはいけないのは、名前のあいまい一致です。「Jon Smith」と「John Smith」は同一人物かもしれないし、同僚2人かもしれません。あるファイルに合う類似度のしきい値は、次のファイルでは合いません。候補ペアを出し、目で確認してから処理してください。

CSV File Viewer - Smart CSViPhone・iPad · Android

SQLエディタとビジュアルフィルタエディタが、この作業の両輪です。GROUP BY で重複キーを見つけ、フィルタでそのキーを持つ全行を読めます。事前にデータベースへ読み込む必要はありません。

できること

スプレッドシートで行う

ExcelとGoogleスプレッドシートには、どちらも「重複を削除」（Remove Duplicates）コマンドがあります。使う前に知っておくべき性質があります。最初に出てきた行を残して残りを削除しますが、どれが最初かは指定できません。 シートがたまたまどの順に並んでいたかで、どのレコードが残るかが決まります。

そのため、実行する前にあえて並べ替えます。タイムスタンプの降順にすれば、「最初を残す」が「最新を残す」になり、たいていはそれが意図どおりです。

もう1つの方法は、COUNTIF の作業列を使うやり方です。遅いものの非破壊で、重複を削除せずに見せてくれます。大きなファイルでは、Excelが固まる原因にもなります。50万行にわたる COUNTIF は計算量が二乗で増えるからです。

スマートフォンで

スマートフォンで現実的にできるのは、本格的な重複削除ではありません。疑わしい列で並べ替えてスクロールし、繰り返しが隣り合うのを見ることです。ファイルに重複の問題があるかどうかを知る、いちばん早い方法です。

重複が見えたら、1つのキーで絞り込み、それを共有する数行を読みます。肝心な問いにはこれで答えが出ます。これは同じレコードなのか、たまたま1つのフィールドが同じ別々のレコードなのか、ということです。

CSV Editor - Smart CSViPhone・iPad

Excelのように任意の列で並べ替え、フィルタをかけたまま安全に行を削除できます。非表示の行は保護されるので、削除がフィルタ外のデータに触れることはありません。元に戻す操作がすべてをカバーするため、スマートフォンでもこの作業が現実的になります。

できること

検証する

3つの数字が一致するはずです。

wc -l input.csv deduped.csv
duckdb -c "SELECT count(*) rows, count(DISTINCT email) keys FROM read_csv('deduped.csv')"

rows と keys は、同じ値になっていなければなりません。違う場合は、すべてのグループに重複削除が効いていません。また、input − deduped は、ステップ2で数えた、削除するつもりだった行数と一致する必要があります。それより多く消えていたら、キーが想定より広かった（または狭かった）ということです。そのとき、取っておいた破棄行のファイルが午後を救ってくれます。