How to Clean Messy CSV Data (A Practical Checklist)

先週の火曜日、私はジュニアアナリストがCSVファイルを手動で修正するのに4時間を費やしているのを見ました。この作業は本来、20分で済むはずのものでした。彼女はセルを1つずつコピー＆ペーストし、3つの異なるシステムからエクスポートされた日付形式を修正しようとしていました。彼女が作業を終えたとき、目はうつろで、ファイルには彼女が見逃した不整合がまだ残っていました。私は12年間データエンジニアをしており、このような状況が常に起こっているのを目の当たりにしています—聡明な人々が不完全なCSVファイルに無駄な午後を捧げているのです。彼らには体系的なアプローチがありません。

真実は、CSVファイルは最も普遍的でありながら、最も問題のあるデータ形式であるということです。すべてのシステムがエクスポートでき、すべてのツールがインポートできる一方で、それらはエンコーディングの問題、区切り文字の混乱、構造的な不整合の地雷源です。データ品質研究所の2023年の調査によると、データ専門家はデータクリーニングタスクに週平均19.2時間を費やしており、そのうち約40%がCSVの取り扱いに費やされています。つまり、毎週ほぼ8時間—1日の労働時間全体—が予防可能な問題の修正に失われています。

私はマーカス・チェンで、最後の10年間は、精力的なスタートアップからフォーチュン500企業までのさまざまな会社にデータパイプラインを構築してきました。何十億行ものCSVデータを処理し、これらのファイルが壊れるありとあらゆる方法を見てきました。私が学んだのは、乱雑なCSVデータをクリーニングすることは賢くなることでなく、体系的にならなければならないということです。問題を分析やデータベースに流出する前に捉えるチェックリストが必要です。この記事は、そのチェックリストであり、数千時間の実世界のデータ取り扱いから抽出されたものです。

CSVファイルがなぜ問題を引き起こすのかの理解

クリーニングプロセスに入る前に、なぜCSVファイルがこれほど多くの頭痛の種を引き起こすのかを理解する必要があります。このフォーマットは一見、単純そうに見えます: 値はカンマで区切られ、一行につき一行です。しかし、その単純さがまさに問題なのです。JSONやXMLのような構造化されたフォーマットとは異なり、CSVには誰もが従う正式な仕様がありません。RFC 4180標準は存在しますが、規則というよりは提案に過ぎず、ほとんどのシステムはそれを完全に無視します。

あるとき、顧客のレガシー会計システムから受け取ったCSVファイルは、区切り文字としてセミコロンを使用し、数値の値にカンマが埋め込まれており（例えば「1,234.56」）、テキストのクオリフィケーションに単一引用符と二重引用符の両方を使用し、同じファイル内でWindowsとUnixの行末を混在させていました。それを正しく解析するだけで3時間かかり、専門的なツールを使っていました。ファイルは彼らのシステムでは「問題なく動作していた」ため、彼らのソフトウェアはすべてのこれらの癖を処理するカスタムロジックを持っていたのです。

CSVファイルの核心的な問題はいくつかのカテゴリに分かれます。まず、区切り文字の問題があります—データ内にカンマが一般的であるため、システムはタブ、パイプ、セミコロン、または他の文字を使用しますが、どれを使用しているのかを必ずしも教えてくれません。次に、テキストのクオリフィケーションがあります—値にクオートが必要なときはいつで、データにクオート文字が含まれているときはどうなるのか？第三に、エンコーディングの問題があります—これはUTF-8、Latin-1、Windows-1252、またはまったく別のものか？間違えると、アクセント付きの文字の代わりにゴミ文字が表示されます。

更に構造的な問題もあります。CSVファイルはデータタイプを表現する組み込みの方法がないため、すべてがテキストとして解析されるまでテキストのままです。日付は「2024-01-15」、「01/15/2024」、「15-Jan-24」または「January 15, 2024」となり、時には異なるユーザーがデータを異なる方法で入力したため、同じ列に4つの形式すべてが表示されることがあります。数値には通貨記号、パーセント符号、千の区切り文字が含まれている場合があります。ブール値は「true/false」、「yes/no」、「1/0」、「Y/N」、またはそれらの任意の組み合わせになる可能性があります。

最悪のことに、多くのCSVの問題は静かに発生します。インポートツールはエラーなしに成功するかもしれませんが、データは微妙に壊れています。先頭ゼロが製品コードから削除され、「00123」が「123」に変わり、在庫システムが壊れたケースを見たことがあります。システムがMM/DD/YYYYであると仮定したために日付が誤って解釈され、ファイルがDD/MM/YYYYを使用していたために、注文が2月31日のような不可能な日付にスケジュールされることがありました。これらのエラーは自らを告げることはなく、数値が合わないことに誰かが気づくまで、データの中に潜んでいます。

ステップ1: インポート前に検査する

人々がCSVファイルで犯す最大の誤りは、それらを直接Excelで開いたり、データベースに直接インポートしたりすることです。これは避けてください。最初のステップは、常にテキストエディタやコマンドラインツールでの検査です。私は、Unuxシステムではhead、tail、およびwc -lを組み合わせて使用しています、または大きなファイルを扱うことができるVS CodeやSublime Textのようなプログラマ向けのテキストエディタを使用します。

"CSVファイルはデータ形式のゴキブリです—どんなことにも生き延び、どこでも機能し、予期しなかった問題を引き起こします。"

まず、ファイルの最初の20〜30行を見てください。これにより、区切り文字、ヘッダー行があるかどうか、一般的な構造がどのように見えるかを把握できます。しかし、そこで止まらないでください—最後の20〜30行も見てください。ファイルがクリーンに始まっても、最後がゴミで終わることが何回あったか数えきれません：部分行、エクスポートプロセスからのエラーメッセージ、またはデータに丁寧に付加された要約統計。1つのクライアントのERPシステムは、エクスポートの最後に「総レコード: 45,892」というフッターを追加していたため、無邪気なインポートプロセスをクラッシュさせていました。

wc -l filename.csvで行数を確認し、予想と比較してください。ファイルに10,000件のレコードとヘッダーが必要な場合、10,001行が表示されるべきです。10,247行が表示されたら、何かがおかしい—おそらくテキストフィールドに埋め込まれた行の区切りがあります。これは、コメントや説明などのユーザー生成内容を含むCSVファイルで非常に一般的です。「この商品は素晴らしい\n非常におすすめ」と書かれた場合、突然行の途中に改行が発生します。

最初の数行を見て区切り文字を探します。各行に含まれるカンマ、タブ、パイプ、またはセミコロンを数えます。それらは一貫しているべきです。行1にカンマが12個あり、行2に15個ある場合、何かがおかしい—区切り文字が間違っているか、区切り文字が含まれている未クオリフィケーションのテキストフィールドがあるのです。私はかつて、ファイルをデバッグするのに1時間を費やしましたが、区切り文字としてパイプ文字（|）が使われていることに気づくのに時間がかかりました。私の端末のフォントではほとんど見えませんでした。

エンコーディングを確認するために、非ASCII文字を探します。アポストロフィの代わりに’が表示されたり、éの代わりにéが表示されたりする場合、エンコーディングが不一致です。ファイルはおそらくUTF-8ですが、Latin-1として読み込まれているか、その逆です。file -i filename.csvのようなツールを使ってエンコーディングを検出するか、テキストエディタのエンコーディング検出機能を使用してください。これを早期に間違えると、データのすべてのテキストフィールドが壊れます。

ステップ2: 構造を検証する

手動でファイルを検査したら、次のステップは構造の検証です。すべての行は同じ数のフィールドを持ち、その数はヘッダー行と一致するべきです。これは明白に聞こえますが、実際のデータでは常に違反されています。私はcsvモジュールやpandasを使ってこの検証をプログラム的に行います。目視で何千行も確認することは不可能だからです。

私が受け取るすべてのCSVファイルに対して実行するシンプルなPythonスクリプトがあります。これは、各行のフィールド数をカウントし、ヘッダーと一致しないものを報告します。最近のプロジェクトでは、このスクリプトが50,000行中347行に余分なフィールドがあることを明らかにしました。これはエクスポートプロセスにバグがあり、特定の条件下で最後の列を重複させてしまったためです。このチェックがなければ、これらの行はインポート中に静かに壊れ、余分なデータが切り捨てられたり間違った列に押し込まれたりしていたでしょう。

特に引用されたフィールドに注意してください。CSVはフィールド値内での区切り文字や改行を可能にするために引用符を使用しますが、引用規則は複雑で、しばしば誤って実装されます。「Smith, John」のようなフィールドは正しく引用されていますが、「彼は私に『こんにちは』と言った」の場合はどうでしょうか？正しい形式は「彼は『こんにちは』と言った」であり、引用符を二重にする必要がありますが、多くのシステムはこれを誤って「彼は『こんにちは』と言った」と生成し、パースに失敗します。

空の行をチェックしてください。これらはファイルの最後やデータセクションの間に現れることがよくあります。これらは