Pengantar pembersihan data

Membersihkan Data di Database PostgreSQL

Darryl Reeves, Ph.D.

Industry Assistant Professor, New York University

Mengapa pembersihan data penting?

Data itu berantakan
Sebelum dianalisis, sering perlu dibersihkan
Gunakan batasan tipe kolom
Kursus fokus saat pendekatan defensif tidak tersedia/berlaku

Membersihkan data string

Data string: banyak, fleksibel, dan sering berantakan

 name                            | grade |            inspection_type              | census_tract | ... 
 --------------------------------+-------+-----------------------------------------+--------------+-----
 ...                             | ...   | ...                                     | ...          | ...
 EMPANADAS MONUMENTAL            | B     | Cycle Inspection  /  Re-inspection      | 26900        | ...
 ALPHONSO'S PIZZERIA & TRATTORIA | A     | Cycle Inspection  /  Initial Inspection | 202          | ...
 THE SPARROW TAVERN              | A     | Cycle Inspection  /  Initial Inspection | 12500        | ...
 BURGER KING                     | A     | Cycle Inspection  /  Re-inspection      | 86400        | ...
 ASTORIA PIZZA                   | B     | Cycle Inspection  /  Re-inspection      | 6300         | ...
 ...                             | ...   | ...                                     | ...          | ...

Membersihkan data string

 name                            | grade |            inspection_type              | census_tract | ... 
 --------------------------------+-------+-----------------------------------------+--------------+-----
 ...                             | ...   | ...                                     | ...          | ...
 EMPANADAS MONUMENTAL            | B     | Cycle Inspection  /  Re-inspection      | 26900        | ...
 ALPHONSO'S PIZZERIA & TRATTORIA | A     | Cycle Inspection  /  Initial Inspection | 202          | ...
 THE SPARROW TAVERN              | A     | Cycle Inspection  /  Initial Inspection | 12500        | ...
 BURGER KING                     | A     | Cycle Inspection  /  Re-inspection      | 86400        | ...
 ASTORIA PIZZA                   | B     | Cycle Inspection  /  Re-inspection      | 6300         | ...
 ...                             | ...   | ...                                     | ...          | ...

Batasi kapitalisasi di name
Hapus spasi pemisah ekstra di inspection_type
Samakan panjang nilai census_tract

Membersihkan data string

 name                            | grade |            inspection_type              | census_tract | ... 
 --------------------------------+-------+-----------------------------------------+--------------+-----
 ...                             | ...   | ...                                     | ...          | ...
 EMPANADAS MONUMENTAL            | B     | Cycle Inspection  /  Re-inspection      | 26900        | ...
 ALPHONSO'S PIZZERIA & TRATTORIA | A     | Cycle Inspection  /  Initial Inspection | 202          | ...
 THE SPARROW TAVERN              | A     | Cycle Inspection  /  Initial Inspection | 12500        | ...
 BURGER KING                     | A     | Cycle Inspection  /  Re-inspection      | 86400        | ...
 ASTORIA PIZZA                   | B     | Cycle Inspection  /  Re-inspection      | 6300         | ...
 ...                             | ...   | ...                                     | ...          | ...

 name                            | grade |           inspection_type             | census_tract | ... 
 --------------------------------+-------+---------------------------------------+--------------+-----
 ...                             | ...   | ...                                   | ...          | ...
 Empanadas Monumental            | B     | Cycle Inspection / Re-inspection      | 026900       | ...
 Alphonso'S Pizzeria & Trattoria | A     | Cycle Inspection / Initial Inspection | 000202       | ...
 The Sparrow Tavern              | A     | Cycle Inspection / Initial Inspection | 012500       | ...
 Burger King                     | A     | Cycle Inspection / Re-inspection      | 086400       | ...
 Astoria Pizza                   | B     | Cycle Inspection / Re-inspection      | 006300       | ...
 ...                             | ...   | ...                                   | ...          | ...

Menggunakan fungsi INITCAP()

INITCAP(input_string) - membetulkan kapitalisasi

SELECT INITCAP('HELLO FRIEND!');

Hello Friend!

Menggunakan fungsi REPLACE()

REPLACE(input_string, to_replace, replacement) - mengganti satu nilai teks dengan lainnya

SELECT REPLACE('180 Main Street', 'Street', 'St');

180 Main St

Menggunakan fungsi LPAD()

LPAD(input_string, length [, fill_value]) - menambahkan teks di depan string

SELECT LPAD('123', 7, 'X');

XXXX123

Membangun kueri pembersihan string

SELECT
  INITCAP(name) as name,
  grade,
  REPLACE(inspection_type, '  /  ', ' / ') as inspection_type, 
  LPAD(census_tract, 6, '0') as census_tract
FROM
  restaurant_inspection;

 name                            | grade |           inspection_type             | census_tract | ... 
 --------------------------------+-------+---------------------------------------+--------------+-----
 ...                             | ...   | ...                                   | ...          | ...
 Empanadas Monumental            | B     | Cycle Inspection / Re-inspection      | 026900       | ...
 Alphonso'S Pizzeria & Trattoria | A     | Cycle Inspection / Initial Inspection | 000202       | ...
 The Sparrow Tavern              | A     | Cycle Inspection / Initial Inspection | 012500       | ...
 Burger King                     | A     | Cycle Inspection / Re-inspection      | 086400       | ...
 Astoria Pizza                   | B     | Cycle Inspection / Re-inspection      | 006300       | ...
 ...                             | ...   | ...                                   | ...          | ...

Ayo berlatih!

Membersihkan Data di Database PostgreSQL