Introduktion till datarensning
Datarensning i PostgreSQL-databaser
Darryl Reeves, Ph.D.
Industry Assistant Professor, New York University
Varför är datarensning viktigt?
- Data är ofta rörig
- Måste rensas innan analys
- Kolumntypsbegränsningar är ett bra hjälpmedel
- Kursen fokuserar på fall där defensiva metoder saknas
Rensa strängdata
- Strängdata: vanlig, flexibel och ofta rörig
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 | ...
... | ... | ... | ... | ...
Rensa strängdata
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 | ...
... | ... | ... | ... | ...
- Begränsa versalisering i
name - Ta bort extra avgränsarutrymme i
inspection_type - Ge
census_tract-värden en enhetlig längd
Rensa strängdata
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 | ...
... | ... | ... | ... | ...
Använda funktionen INITCAP()
INITCAP(input_string) – korrigerar versalisering
SELECT INITCAP('HELLO FRIEND!');
Hello Friend!
Använda funktionen REPLACE()
REPLACE(input_string, to_replace, replacement) – ersätter ett textvärde med ett annat
SELECT REPLACE('180 Main Street', 'Street', 'St');
180 Main St
Använda funktionen LPAD()
LPAD(input_string, length [, fill_value]) – lägger till text i början av en sträng
SELECT LPAD('123', 7, 'X');
XXXX123
Bygga rensningsfrågan för strängar
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 | ...
... | ... | ... | ... | ...
Nu kör vi en övning!
Datarensning i PostgreSQL-databaser
