欠損データの扱い

PostgreSQLデータベースで学ぶデータクレンジング

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

欠損データ（例）

 ... |       name       | score |               inspection_type                | ... 
 ----+------------------+-------+----------------------------------------------+-----
 ... | ...              | ...   | ...                                          | ...
 ... | SCHNIPPERS       | 27    | Cycle Inspection / Initial Inspection        | ...
 ... | ATOMIC WINGS     |       | Administrative Miscellaneous / Re-inspection | ...
 ... | WING LING        | 44    | Cycle Inspection / Initial Inspection        | ...
 ... | JUAN VALDEZ CAFE | 24    | Cycle Inspection / Initial Inspection        | ...
 ... | FULTON GRAND     | 22    | Cycle Inspection / Initial Inspection        | ...
 ... | ...              | ...   | ...                                          | ...

欠損値の表現:

NULL（一般）
'' - 空文字列（文字列列で使用）

欠損の原因

欠損はなぜ起こるか？

人為的ミス
システム的問題

欠損データの種類

欠損データの各タイプと略語を示す3つの図

欠損データの種類

欠損データの各タイプと略語を示し、「完全に無作為な欠損（MCAR）」の説明を追加した3つの図

欠損データの種類

欠損データの各タイプと略語を示し、「無作為な欠損（MAR）」の説明を追加した3つの図

欠損データの種類

 ... |       name       | score |               inspection_type                | ... 
 ----+------------------+-------+----------------------------------------------+-----
 ... | ...              | ...   | ...                                          | ...
 ... | SCHNIPPERS       | 27    | Cycle Inspection / Initial Inspection        | ...
 ... | ATOMIC WINGS     |       | Administrative Miscellaneous / Re-inspection | ...
 ... | WING LING        | 44    | Cycle Inspection / Initial Inspection        | ...
 ... | JUAN VALDEZ CAFE | 24    | Cycle Inspection / Initial Inspection        | ...
 ... | FULTON GRAND     | 22    | Cycle Inspection / Initial Inspection        | ...
 ... | ...              | ...   | ...                                          | ...

欠損データの種類

欠損データの各タイプと略語を示し、「非無作為欠損（MNAR）」の説明を追加した3つの図

欠損データの特定

SELECT
  *
FROM
  restaurant_inspection
WHERE
  score IS NULL;

SELECT
  COUNT(*)
FROM
  restaurant_inspection
WHERE
  score IS NULL;

欠損データの特定

SELECT
  inspection_type,
  COUNT(*) as count
FROM
  restaurant_inspection
WHERE
  score IS NULL
GROUP BY
  inspection_type
ORDER BY
  count DESC;

                  inspection_type                  | count 
 --------------------------------------------------+-------
 Administrative Miscellaneous / Initial Inspection |   104
 Smoke-Free Air Act / Initial Inspection           |    29
 Calorie Posting / Initial Inspection              |    22
 Administrative Miscellaneous / Re-inspection      |    22
 Trans Fat / Initial Inspection                    |    18
 Smoke-Free Air Act / Re-inspection                |     7
 Trans Fat / Re-inspection                         |     3

欠損データの補正

最善: 欠損値を特定して追加
- できない場合がある
- 見合わない場合がある
代表値で補完（平均・中央値など）
レコードを除外

COALESCE()で欠損値を置換

COALESCE(arg1, [arg2, ...])

SELECT
  name,
  COALESCE(score, -1),
  inspection_type
FROM
  restaurant_inspection;

COALESCE()で欠損値を置換

 ... |       name       | score |               inspection_type                | ... 
 ----+------------------+-------+----------------------------------------------+-----
 ... | ...              | ...   | ...                                          | ...
 ... | SCHNIPPERS       | 27    | Cycle Inspection / Initial Inspection        | ...
 ... | ATOMIC WINGS     | -1    | Administrative Miscellaneous / Re-inspection | ...
 ... | WING LING        | 44    | Cycle Inspection / Initial Inspection        | ...
 ... | JUAN VALDEZ CAFE | 24    | Cycle Inspection / Initial Inspection        | ...
 ... | FULTON GRAND     | 22    | Cycle Inspection / Initial Inspection        | ...
 ... | ...              | ...   | ...                                          | ...

Ayo berlatih!

PostgreSQLデータベースで学ぶデータクレンジング