Data Cleaning Tips: Fix Messy Spreadsheets Fast — csv-x.com

先週の火曜日、私はジュニアアナリストが5万行の顧客データベースを手動で修正するのに4時間もかけているのを見ました。彼女は個々のセルをコピー＆ペーストし、不一致のある日付形式を目を細めて見つめ、「この混乱をエクスポートした人は誰だろう」と呟いていました。私はデータオペレーションのコンサルタントとして12年の経験があり、これと似たような状況が私が関わるフォーチュン500企業で週に少なくとも2回は見られます。真実は？ほとんどの専門家は適切なデータクリー二ング技術を教わっておらず、その結果、汚れたデータに基づいた悪い決定や生産性の喪失で、企業は年間約3.1兆ドルを失っています。

私はサラ・チェンです。10年以上にわたり、企業のデータ災害を清掃する仕事をしてきました。私の専門は、混沌としたスプレッドシートを迅速に分析可能なデータセットに変換することです。私が学んだことは、データクリー二ングは完璧さではなく、効率とどの戦いを戦うべきかを知ることであるということです。今日は、私が混乱したCSVファイルをほとんどの人が苦労する時間のほんの一部でクリーンなデータに変換するために使用する正確なフレームワークを共有します。

乱雑なデータの真のコスト（そして、なぜ速度が重要か）

技術に入る前に、なぜこれが重要なのかについて話しましょう。2019年、私は中規模のeコマース企業と仕事をしており、重複したエントリーがある販売スプレッドシートに基づいて在庫の決定を行っていました。彼らは「トップセラー」とされる商品が、実際にはわずかに異なるSKU形式でデータに3回も現れていることに気づいていませんでした。その結果、彼らは340％も在庫を過剰に持ち、800万ドルの資本を8ヶ月間倉庫に留めた商品に結びつけました。

これは孤立した事件ではありません。IBMの調査によると、質の悪いデータは米国経済に年間約3.1兆ドルのコストをかけています。しかし、ほとんどの文章では言わないことがあります。最も大きなコストは悪い決定ではなく、無駄に費やされる時間です。私は企業のデータワークフローを監査すると、アナリストが実際の分析ではなくデータ準備に60-80％の時間を費やしていることがよくあります。これはデータが非常に複雑であるからではなく、彼らが非効率的な方法を使っているからです。

速度は重要です。なぜなら、クリーンなデータには賞味期限があるからです。手動で5万行を修正するまでに、ビジネスの状況が変わるかもしれません。分析していたマーケティングキャンペーンは終わっているかもしれません。四半期報告の締切が過ぎているかもしれません。迅速なデータクリーニングは、手を抜くことではなく、関連性を維持し、実際に決定を促すインサイトを得ることなのです。

私が関わる企業の中で、体系的でツールを使ったデータクリーニングアプローチを採用したところは、日常的なデータ準備タスクで70-85％の時間を節約していると報告しています。さらに重要なことに、彼らは競合他社よりも3-4週早く決定を下していると報告しています。素早く動く業界では、そのタイミングの優位性は市場シェアに直接繋がります。

治療前の診断：データランドスケープのスキャン

私が人々に見られる最大の間違いは、何に取り組んでいるのかを理解せずにクリーニングに飛び込むことです。それは、患者を検査せずに薬を処方する医者のようなものです。私はコンサルタントとしての3年目に、この教訓を痛感しました。データセットの日付形式を修正するのに6時間を費やした後、本当の問題が重複レコードであり、私のデータクリーニングが完全に無意味であったことを発見しました。

「データクリー二ングは完璧さではなく、効率とどの戦いを戦うかを知ることです。目標はフローレスデータではなく、分析可能なデータです。」

今では、私は常に体系的なスキャンから始めます。CSVファイルを開いて、正確に5分間の構造的評価を行います。まず、行数をチェックします。これは500行ですか、それとも500,000行ですか？アプローチは大きく異なります。そして、列のヘッダーをスキャンします。それらは記述的ですか？一貫していますか？「Column1」やマージされたヘッダーロウのような明らかな問題が見えますか？

次に、データタイプを見ます。スクロールして、どの列がテキストで、どれが数字で、どれが日付であるべきかを特定します。混合型に見える列—例えば、いくつかのエントリーが数字で、他のエントリーがフォーマット付きのテキストである「電話番号」列—を思い出します。これらの混合型列は、下流で問題を引き起こす赤信号です。

私が「エッジスキャン」と呼ぶこともします—最初の10行、最後の10行、真ん中のランダムサンプルを見ます。なぜなら、データ品質の問題はしばしばクラスター化するからです。私は一度、最初の5000行が完全にクリーンだったデータセットを見つけましたが、5001行目以降は異なるソースシステムからの異なる形式でした。上部だけを確認していたら、大きな問題を見逃していたでしょう。

最後に、分析が機能するために必ずクリーンでなければならない「クリティカルパス」列を特定します。顧客データベースでは、顧客ID、メール、購入日がこれに当たるかもしれません。製品カタログでは、SKU、価格、カテゴリーかもしれません。私はまずこれらの列にクリー二ング作業を集中させます。すべての列を完璧にする必要は滅多にありません。必要なのは、正しい列が完璧であることです。

実際に機能する重複検出フレームワーク

重複はデータ分析の静かな殺し屋です。それらはカウントを膨らませ、平均を歪め、幻影のトレンドを作り出します。私はマーケティングチームが「新規顧客の40%の増加」を祝っているのを見たことがありますが、それは実際にはシステム移行からの重複エントリーでした。問題は、重複はまず自らを示すことは稀であり、微妙なバリエーションに隠れていることです。

真の重複は簡単です：2行が100％同一です。ほとんどのスプレッドシートツールはこれを自動的に見つけることができます。しかし、12年間の経験で、重複がそれほど明白なデータセットに遭遇したことはほとんどありません。その代わり、私は「ファジー重複」と呼ぶものに対処します—同じエンティティを表すが、わずかな違いを持つレコードです。

顧客記録を考えてみてください。「ジョン・スミス」と「ジョン・スミス」は同じ人かもしれませんが、「ジョン・スミス」と「J.スミス」、「スミス、ジョン」、「john smith」（小文字）も同じかもしれません。単純な重複チェックではこれらすべてを見逃します。私のフレームワークは「マッチングキー」を作成することを含みます—比較のためだけに使用されるデータの標準化されたバージョンです。

名前の場合、私は小文字に変換し、すべての句読点と余分なスペースを削除し、単語をアルファベット順に並べ替えることによってマッチングキーを作成します。「スミス、ジョン」は「john smith」になり、「ジョン・スミス」も「john smith」になります—これで一致しました。住所の場合、アパート番号を削除し、街の略語を完全な単語に変換し、句読点を取り除きます。電話番号の場合、すべてのフォーマットを削除し、数字のみを保持します。

重要な見解は、元のデータを修正しないことです—一時的なマッチング列を作成し、その列に基づいて重複を見つけ、重複を特定して処理した後にマッチング列を削除します。これにより、元のデータを保持しながら、隠れた重複を見つける能力を得ることができます。

私はまた、大規模データセットに対して「確率的マッチング」と呼ばれる方法を使用します。2つのレコードが完全に一致する場合のみ重複と見なすのではなく、類似性スコアを割り当てます。2つの顧客レコードがメールと電話で一致するが、名前にわずかな違いがある場合、彼らは90％の類似性スコアを得ます。その後、80％の類似性を超えるものを手動でレビューします—これは通常、95％の重複をキャッチしますが、手動でレビューする必要があるレコードはわずか2-3％です。

日付形式の獣を手なずける

日付形式を修正するのに費やした時間ごとに1ドルもらっていたら、私は引退できるでしょう。日付は一見シンプルに見えるため、欺瞞的に複雑です。しかし、「01/02/2023」はアメリカでは1月2日であり、ヨーロッパでは2月1日を意味します。「2023-01-02」はあいまいではありませんが、日付としてではなくテキストとして保存されているかもしれません。Excelが日付をシリアル番号に変換する習慣については話を始めないでください。

「質の悪いデータは、米国経済に年間3.1兆ドルをコストしますが、真の殺人者は隠れた税です：アナリストが分析ではなくクリーニングに60-80％の時間を費やすことです。」

日付クリーニングの第一ルールは、どのフォーマットから始めるかを特定することです。私は小さなサンプルを作成します—おそらく20行—パターンを探します。すべての日付が同じフォーマットですか？「N/A」や「TBD」のような明らかな外れ値は混入していますか？「01/01/1900」のように明らかに間違っている日付（しばしばデフォルト値）や、過去のはずなのに未来の日付はありますか？