データクレンジング入門
PostgreSQLデータベースで学ぶデータクレンジング
Darryl Reeves, Ph.D.
Industry Assistant Professor, New York University
なぜデータクレンジングが重要か
- データは乱雑
- 分析前に多くの場合クレンジングが必要
- 列型の制約を活用すると有効
- 本コースは防御的手法が使えない/不適用な場合に焦点
文字列データのクレンジング
- 文字列データ: 豊富・柔軟・しばしば乱雑
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 | 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の大文字化を制限inspection_typeの余分な区切りスペースを削除census_tractを一定の桁数に統一
文字列データのクレンジング
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 | ...
... | ... | ... | ... | ...
INITCAP() の使用
INITCAP(input_string) - 大文字小文字を修正
SELECT INITCAP('HELLO FRIEND!');
Hello Friend!
REPLACE() の使用
REPLACE(input_string, to_replace, replacement) - 文字列内のテキストを置換
SELECT REPLACE('180 Main Street', 'Street', 'St');
180 Main St
LPAD() の使用
LPAD(input_string, length [, fill_value]) - 文字列の先頭に値を追加
SELECT LPAD('123', 7, 'X');
XXXX123
文字列クレンジングのクエリを構築
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!
PostgreSQLデータベースで学ぶデータクレンジング
