Menangani data hilang

Membersihkan Data di Database PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

Data hilang (contoh)

 ... |       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        | ...
 ... | ...              | ...   | ...                                          | ...

Representasi nilai hilang:

  • NULL (umum)
  • '' - string kosong (untuk kolom string)
Membersihkan Data di Database PostgreSQL

Penyebab data hilang

Apa penyebab data hilang? Teka-teki hitam dengan potongan yang hilang

  • Gambar otak manusia dalam siluet kepala kesalahan manusia
  • Gambar roda gigi masalah sistematis
Membersihkan Data di Database PostgreSQL

Jenis data hilang

Tiga gambar jenis data dengan tiap kategori dan singkatannya

Membersihkan Data di Database PostgreSQL

Jenis data hilang

Tiga gambar jenis data dengan tiap kategori dan singkatannya, plus deskripsi Missing Completely at Random

Membersihkan Data di Database PostgreSQL

Jenis data hilang

Tiga gambar jenis data dengan tiap kategori dan singkatannya, plus deskripsi Missing at Random

Membersihkan Data di Database PostgreSQL

Jenis data hilang

 ... |       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        | ...
 ... | ...              | ...   | ...                                          | ...
Membersihkan Data di Database PostgreSQL

Jenis data hilang

Tiga gambar jenis data dengan tiap kategori dan singkatannya, plus deskripsi Missing Not at Random

Membersihkan Data di Database PostgreSQL

Mengidentifikasi data hilang

SELECT
  *
FROM
  restaurant_inspection
WHERE
  score IS NULL;

SELECT
  COUNT(*)
FROM
  restaurant_inspection
WHERE
  score IS NULL;
Membersihkan Data di Database PostgreSQL

Mengidentifikasi data hilang

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
Membersihkan Data di Database PostgreSQL

Memperbaiki data hilang

  • Opsi terbaik: cari dan tambahkan nilai yang hilang

    • Mungkin tidak memungkinkan
    • Mungkin tidak sepadan

  • Beri nilai (rata-rata, median, dll.)

  • Keluarkan rekaman

Membersihkan Data di Database PostgreSQL

Mengganti nilai hilang dengan COALESCE()

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

SELECT
  name,
  COALESCE(score, -1),
  inspection_type
FROM
  restaurant_inspection;
Membersihkan Data di Database PostgreSQL

Mengganti nilai hilang dengan 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        | ...
 ... | ...              | ...   | ...                                          | ...
Membersihkan Data di Database PostgreSQL

Ayo berlatih!

Membersihkan Data di Database PostgreSQL

Preparing Video For Download...