CSV Data Cleaning Techniques Every Analyst Should Know - CSV-X.com

3年前、私はフォーチュン500企業が隠されたUnicode文字を含むCSVファイルをインポートしたために230万ドルを失うのを目撃しました。そのせいで彼らの顧客データベース全体が壊れてしまいました。私はサラ・チェンで、過去12年間、データオペレーションコンサルタントとして活動し、悪いCSV処理が引き起こす混乱を片付けてきました。目に見えない文字がSQLクエリを壊すことから、1月を12月に変えてしまう日付形式まで、私はあらゆることを見てきました。そして、これらの災害の90％は完全に防ぐことができるとお伝えしたいのです。

真実は、CSVファイルは見かけほど単純ではないということです。無害に見えますが—テキストの行と列だけ—実際にはデータ腐敗の危険が潜んでいます。200以上の組織での経験から、平均的なアナリストはデータを分析するのではなく、60％の時間をデータのクリーンアップに費やしていることがわかりました。それはただ非効率的なだけでなく、才能とリソースの大きな無駄です。しかし、良いニュースがあります。これから共有する基本的なCSVクリーンアップ技術を習得すれば、その時間を半分に減らし、データ品質を劇的に改善できます。

この記事は理論についてではありません。混乱した現実のCSVファイルをクリーンで分析可能なデータセットに変換するために、私が毎日使用している実践的な技術についてです。顧客データ、財務記録、科学的測定のいずれに関しても、これらの方法は数え切れないほどの時間を節約し、高価なミスを防ぐことができます。

CSVファイルの隠れた複雑さの理解

クリーンアップ技術に dive する前に、CSVファイルがなぜそれほど問題を引き起こすのかを理解する必要があります。ほとんどのアナリストはCSVを、値を区切るためのコンマを持つ単純なテキストファイルだと考えていますが、実際にははるかに複雑です。データアナリストとしての最初の年に、3日間も失敗し続けるパイプラインをデバッグしていたとき、そのCSVファイルがコロンではなくセミコロンを区切り文字として使用していることを発見したことが例です。

CSV形式には公式の標準がありません。RFC 4180がガイドラインを提供していますが、普遍的に守られているわけではありません。これは異なるシステムがCSVを非常に異なる方法でエクスポートすることを意味します。タブ区切りやパイプ区切り、さらにはカスタムのマルチキャラクター区切りを持つファイルに出くわしたことがあります。あるシステムはすべてのフィールドを引用符で囲み、他のシステムは特殊文字を含むフィールドのみを引用し、また別のものは何も引用しないことがあります。

文字エンコーディングは別の大きな問題です。私はかつて、患者の名前が完全に混乱してしまった医療提供者と働いていました。彼らのシステムはUTF-8でエクスポートしていましたが、分析ツールはWindows-1252エンコーディングを予期していました。その結果、「José García」は「José García」となり、患者の照合には全く使えなくなってしまいました。500以上の異なるソースからのCSVファイルを分析した結果、約35％が適切に処理されないとデータ腐敗を引き起こすエンコーディングの問題を抱えていることがわかりました。

行の終わりもまた隠れた複雑さの一つです。WindowsはCRLF（キャリッジリターン + ラインフィード）を使用し、UnixはLF、古いMacシステムはCRを使用します。これらが混ざってしまうと（あなたが思うよりも頻繁に発生します）、行数が完全に間違ってしまうことがあります。私は、一つの論理行が不一致な行の終わりのせいで複数の物理行に分かれてしまったデータセットを見たことがあります。これにより、すべての計算が downstream で狂ってしまいました。

ここでの教訓は簡単です。CSVファイルについて何かを仮定しないことです。処理する前に必ず徹底的に点検してください。私は、実際のデータをクリーンアップすることを考える前に、区切り文字、エンコーディング、行の終わり、引用スタイルをチェックするという体系的なアプローチを用いています。この5分の投資で、無数のデバッグ時間を節約できています。

エンコーディング問題の検出と処理

エンコーディングの問題は、データ品質の静かな殺し屋です。多くのテキストエディタでは目に見えず、微妙な方法でデータを壊し、あなたの分析パイプライン全体を失敗させる可能性があります。私の12年の経験から、エンコーディングの問題は私が遭遇したすべてのCSV関連データ問題の約40％を占めると見積もっています。

"平均的なアナリストは、データを分析するのではなく、60％の時間をデータのクリーンアップに費やします。それは非効率的なだけでなく、適切なCSV処理技術で半分にも削減できる大きな無駄です."

最初のステップは検出です。私は常にファイルが実際に使用するエンコーディングをチェックすることから始め、仮定はしません。合理的な精度でエンコーディングを検出するツールがありますが、完璧ではありません。私は微妙な兆候を探す習慣を身につけました。例えば、アポストロフィの代わりにâ、またはéの代わりにéのような奇妙な文字が見られた場合、エンコーディングの不一致があるということです。これらの特定パターンは、UTF-8データがWindows-1252またはISO-8859-1として解釈されたことを示しています。

私の標準的なエンコーディング検出ワークフローは次の通りです。まず、UTF-8でファイルを開こうとします。モジバケ（文字化け）が見られた場合、問題があると判断します。そして、ファイルの最初にバイトオーダーマーク（BOM）があるかどうかを確認します。これはエンコーディングを示す特殊なバイトシーケンスです。UTF-8ファイルは時折、バイトEF BB BFで始まります。これはUTF-8 BOMです。しかし、多くのシステムではBOMが含まれていないため、それに頼るわけにはいきません。

エンコーディングを特定したら、すべてをUTF-8に変換して処理します。UTF-8は現代のデータ作業の事実上の標準です—任意のUnicode文字を表すことができ、ASCIIとの後方互換性があり、ほぼすべてのツールやプログラミング言語によってサポートされています。私は個人的なルールを設けています。私がクリーンアップしたすべてのデータセットはUTF-8であるべきで、例外はありません。

しかし、多くのアナリストが見落とす重要な点があります。それは、元のエンコーディング情報を保持する必要があるということです。私は常に、クリーンデータにメタデータファイルを作成して、元のエンコーディング、変換日、遭遇した問題を文書化しています。これにより、利害関係者が特定の文字が元のシステムと異なって見える理由を質問したときに、何度も助けられました。

特に問題のあるファイルに対しては、「エンコーディング考古学」という技術を使用します。私は系統的に異なるエンコーディングを試し、結果を既知の良いデータと照合します。たとえば、顧客名を扱っていて「José」がデータセットに表示されるべきことがわかっている場合、「José」が正しく表示されるまで異なるエンコーディングを試すことができます。これは面倒に聞こえますが、私はこのプロセスを自動化するスクリプトを構築し、既知の値のリストに対してテストを行い、生じた一致の数に基づいて各エンコーディングにスコアを付けています。

区切り文字と引用スタイルの標準化

CSVファイルを扱う際に最もイライラする点の一つは、CSVの「C」が必ずしも「カンマ」を意味するわけではないということです。私は、タブ、セミコロン、パイプ、コロン、さらにはカスタムのマルチキャラクターシーケンスを区切り文字として使用するファイルに遭遇したことがあります。私が遭遇した最悪のケースは、データにカンマとシングルパイプの両方が含まれていたため、「||」（ダブルパイプ）を区切り文字として使用していた金融サービス会社でした。私のパーサーが失敗し続ける理由を理解するのに2時間かかりました。

区切り文字のバリエーションを処理する鍵は、決して仮定をハードコードしないことです。私は常にファイルの最初の数行を分析して、実際の区切り文字を特定することから始めます。私のアプローチは、最初の10-20行で潜在的な区切り文字（カンマ、タブ、セミコロン、パイプ）の出現回数をカウントし、どれが最も一貫して現れるかを確認することです。区切り文字は、各行で同じ回数出現するべきです—それがあなたの信号です。

しかし、ここでトリッキーなのは、データが区切り文字を含んでいる場合です。ここで引用が登場します。適切にフォーマットされたCSVファイルは、特殊文字を含むフィールドを引用符で囲みます。たとえば、区切り文字がカンマで、住所が「123 Main St, Apt 4」の場合、次のように引用されるべきです：「123 Main St, Apt 4」。引用がなければ、パーサーは住所内のカンマをフィールドの区切りと誤解し、一つのフィールドを二つに分割してしまいます。

私は、区切り文字と引用の問題を処理するための三段階アプローチを開発しました。まず、標準設定（カンマ区切り、引用文字はダブルクオーテーション）でファイルを解析しようとします。それが失敗したり、各行のフィールド数が不一致だった場合は、次の段階に進みます：区切り文字の検出。ファイル構造を分析し、異なる方法を試していきます。