Handling missing data

Limpeza de dados em bancos de dados PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

Missing data (an example)

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

Representations for missing values:

  • NULL (general)
  • '' - empty string (used for string columns)
Limpeza de dados em bancos de dados PostgreSQL

Causes of missing data

What causes missing data? Black puzzle with missing piece

  • An image showing human brain in a head silhouette human error
  • An image of gears systematic issues
Limpeza de dados em bancos de dados PostgreSQL

Types of missing data

Three images of the different types of data with each category and abbreviation listed

Limpeza de dados em bancos de dados PostgreSQL

Types of missing data

Three images of the different types of data with each category and abbreviation listed with the description of Missing Completely at Random added

Limpeza de dados em bancos de dados PostgreSQL

Types of missing data

Three images of the different types of data with each category and abbreviation listed with the description of Missing at Random added

Limpeza de dados em bancos de dados PostgreSQL

Types of missing data

 ... |       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        | ...
 ... | ...              | ...   | ...                                          | ...
Limpeza de dados em bancos de dados PostgreSQL

Types of missing data

Three images of the different types of data with each category and abbreviation listed with the description of Missing Not at Random added

Limpeza de dados em bancos de dados PostgreSQL

Identifying missing data

SELECT
  *
FROM
  restaurant_inspection
WHERE
  score IS NULL;

SELECT
  COUNT(*)
FROM
  restaurant_inspection
WHERE
  score IS NULL;
Limpeza de dados em bancos de dados PostgreSQL

Identifying missing data

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
Limpeza de dados em bancos de dados PostgreSQL

Rectifying missing data

  • Best option: locate and add missing values

    • May not be feasible
    • May not be worthwhile

  • Provide a value (average, median, etc)

  • Exclude records

Limpeza de dados em bancos de dados PostgreSQL

Replacing missing values with COALESCE()

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

SELECT
  name,
  COALESCE(score, -1),
  inspection_type
FROM
  restaurant_inspection;
Limpeza de dados em bancos de dados PostgreSQL

Replacing missing values with 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        | ...
 ... | ...              | ...   | ...                                          | ...
Limpeza de dados em bancos de dados PostgreSQL

Let's practice!

Limpeza de dados em bancos de dados PostgreSQL

Preparing Video For Download...