डेटा क्लीनिंग परिचय
PostgreSQL डेटाबेस में डेटा क्लीनिंग
Darryl Reeves, Ph.D.
Industry Assistant Professor, New York University
डेटा साफ करना क्यों ज़रूरी है?
- डेटा गड़बड़ होता है
- विश्लेषण से पहले, अक्सर क्लीनिंग ज़रूरी होती है
- कॉलम टाइप constraints उपयोगी हैं
- जब defensive तरीके उपलब्ध/लागू न हों, उसी पर कोर्स केंद्रित है
स्ट्रिंग डेटा की क्लीनिंग
- स्ट्रिंग डेटा: बहुत, लचीला, और अक्सर गड़बड़
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में capitalization सीमित करेंinspection_typeमें अतिरिक्त divider space हटाएँ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) - capitalization ठीक करना
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 | ...
... | ... | ... | ... | ...
अभ्यास करते हैं!
PostgreSQL डेटाबेस में डेटा क्लीनिंग
