Bei der Datenbereinigung geht es darum, beschädigte oder ungenaue Datensätze in einem Datensatz zu erkennen und zu korrigieren. Dieser Prozess umfasst die Identifizierung unvollständiger, falscher, ungenauer oder irrelevanter Teile der Daten und das anschließende Ersetzen, Ändern oder Löschen der verschmutzten oder groben Daten.
Untersuchungen zeigen, dass Unternehmen im Durchschnitt fast 30 % ihrer Daten als ungenau einschätzen. Solche nicht aufbereiteten oder „schmutzigen“ Daten verursachen den Unternehmen erhebliche Kosten, die etwa 12 % ihres Gesamtumsatzes ausmachen. Die Auswirkungen gehen über bloße finanzielle Verluste hinaus und unterstreichen, wie wichtig saubere und zuverlässige Daten sind. Daher kann die Bedeutung sauberer Daten gar nicht hoch genug eingeschätzt werden.
Die Bedeutung des Datenbereinigungsprozesses kann gar nicht hoch genug eingeschätzt werden, da er es uns ermöglicht, konsistente, strukturierte und genaue Daten zu produzieren und damit die Grundlage für fundierte Entscheidungen zu schaffen, die wertvolle Zeit und finanzielle Ressourcen sparen.
Im Bereich der Datenanalyse stehen zahlreiche Tools für die Datenbereinigung zur Verfügung, wie Excel, SQL, Python, R usw. Im Rahmen dieses Tutorials werden wir uns jedoch auf die Verwendung von SQL konzentrieren.
SQL ist ein schnelles und effizientes Werkzeug für die Durchführung von Datenbereinigungsaufgaben in unseren Datenbeständen. Seine Fähigkeiten umfassen verschiedene wesentliche Funktionen, einschließlich der Fähigkeit, irrelevante Daten herauszufiltern, doppelte Werte zu verwalten, Datensatzformate zu standardisieren und fehlende Werte zu behandeln, neben anderen Funktionalitäten.
In diesem Projekt werden wir grundlegende Datenbereinigungsaufgaben an einem Datensatz durchführen, der aus dem Internet entnommene Stellenanzeigen von Glassdoor für Jobs in den Datenwissenschaften enthält. Sie finden den Datensatz hier.
Als Haupttool wird SQL (SQLite) verwendet.
Das Projekt
Hier finden Sie einen Überblick über das Projekt.
SELECT *
FROM Uncleaned_DS_jobs
LIMIT 10;
Unser Hauptziel in diesem Projekt ist es, den Datensatz zu untersuchen, irrelevante und doppelte Daten zu entfernen, strukturelle Fehler zu korrigieren, Typkonvertierungen durchzuführen, fehlende Daten zu behandeln und unseren Datensatz zu standardisieren und zu validieren.
Schritt 1. Erstellen einer neuen Tabelle für die Arbeit.
Unser erster Schritt besteht darin, eine neue Kopie der Tabelle/des Datensatzes zu erstellen, mit der/dem wir arbeiten, da wir die Originaldaten nicht verändern wollen.
CREATE TABLE jobs AS
SELECT *
FROM Uncleaned_DS_jobs;
Die obige Abfrage erstellt eine neue Tabelle „jobs“, die im Wesentlichen eine Kopie unserer ursprünglichen Daten ist.
Schritt 2. Untersuchen Sie die Tabellenstruktur.
Der nächste Schritt besteht darin, die Struktur unserer Tabelle zu untersuchen, um Informationen über die Spalten in der Tabelle zu erhalten, z. B. die Spaltennamen und Datentypen usw.
PRAGMA table_info(jobs);
Schritt 3: Standardisierung der Spaltennamen
Wie man an den Spaltennamen sehen kann, könnte man sie etwas bereinigen. Ich bevorzuge Spaltennamen in Groß- und Kleinschreibung. Die Großschreibung ist eine Namenskonvention, bei der jedes Leerzeichen durch einen Unterstrich (_) ersetzt wird und die Wörter klein geschrieben werden.
In SQL können wir dies mit den folgenden Abfragen erreichen.
ALTER TABLE jobs
RENAME 'Job Title' TO 'job_title';
ALTER TABLE jobs
RENAME 'Salary Estimate' TO 'salary_estimate';
ALTER TABLE jobs
RENAME COLUMN 'Job Description' TO 'job_description';
ALTER TABLE jobs
RENAME COLUMN 'Rating' TO 'rating';
ALTER TABLE jobs
RENAME COLUMN 'Company Name' TO 'company_name';
ALTER TABLE jobs
RENAME COLUMN 'Location' TO 'location';
ALTER TABLE jobs
RENAME COLUMN 'Headquarters' TO 'headquarters';
ALTER TABLE jobs
RENAME COLUMN 'Size' TO 'size';
ALTER TABLE jobs
RENAME COLUMN 'Founded' TO 'founded';
ALTER TABLE jobs
RENAME COLUMN 'Type of ownership' TO 'type_of_ownership';
ALTER TABLE jobs
RENAME COLUMN 'Industry' TO 'industry';
ALTER TABLE jobs
RENAME COLUMN 'Sector' TO 'sector';
ALTER TABLE jobs
RENAME COLUMN 'Revenue' TO 'revenue';
ALTER TABLE jobs
RENAME COLUMN 'Competitors' TO 'competitors';
SCHRITT 4: Bereinigung der Spalte job_title
Bei genauer Betrachtung der Spalte job_title zeigt sich, dass sie mehrere Variationen des Wortes „senior“ und „junior“ enthält, die wir harmonisieren müssen, um sie alle in einer Form zu halten.
Mit dieser Abfrage können wir die verschiedenen Berufsbezeichnungen untersuchen.
SELECT DISTINCT(job_title), COUNT(*) AS job_count
FROM jobs
GROUP BY job_title
ORDER BY job_count DESC;
Wir können eine Reihe von UPDATE
-Anweisungen verwenden, um bestimmte Stellenbezeichnungen in der Tabelle jobs
zu standardisieren und zu ändern. Der Zweck dieser Aktualisierungen ist die Vereinheitlichung der Stellenbezeichnungen, um die Konsistenz und Lesbarkeit der Spalte job_title zu gewährleisten.
Jede UPDATE
-Anweisung würde die Funktion REPLACE()
verwenden, um bestimmte Instanzen von Stellenbezeichnungen in der Spalte job_title
zu finden und sie durch unsere standardisierten Versionen oder geänderten Bezeichnungen zu ersetzen.
-- standardize the data
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr Data Scientist', 'Senior Data Scientist');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr Data Engineer (Sr BI Developer)', 'Senior Data Engineer (Senior BI Developer');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Jr. Business Data Analyst (position added 6/12/2020)', 'Junior Business Data Analyst');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Jr. Data Engineer', 'Junior Data Engineer');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Data Scientist 3 (718)', 'Data Scientist 3');
UPDATE jobs
SET job_title = REPLACE(job_title, '(Sr.) Data Scientist -', 'Senior Data Scientist');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr. ML/Data Scientist - AI/NLP/Chatbot', 'Senior ML/Data Scientist - AI/NLP/Chatbot');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr Scientist - Extractables & Leachables', 'Senior Scientist - Extractables & Leachables');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr Data Analyst', 'Senior Data Analyst');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Machine Learning Scientist - Bay Area, CA', 'Machine Learning Scientist');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr. Data Scientist II', 'Senior Data Scientist II');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Data Scientist - TS/SCI FSP or CI Required', 'Data Scientist');
UPDATE jobs
SET job_title = REPLACE(job_title, 'ENGINEER - COMPUTER SCIENTIST - RESEARCH COMPUTER SCIENTIST - SIGNAL PROCESSING - SAN ANTONIO OR', 'Research Computing Scientist');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr. Research Associate/ Scientist, NGS prep & Molecular Genomics', 'Senior Research Associate / Scientist, NGS prep & Molecular Genomics');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Sr. Data Analyst', 'Senior Data Analyst');
UPDATE jobs
SET job_title = REPLACE(job_title, 'COMPUTER SCIENTIST - ENGINEER - RESEARCH COMPUTER SCIENTIST - TRANSPORTATION TECHNOLOGY', 'Research Computer Scientist');
UPDATE jobs
SET job_title = REPLACE(job_title, 'ELISA RESEARCH SCIENTIST (CV-15)', 'ELISA Research Scientist (CV-15');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Health Plan Data Analyst, Sr', 'Health Plan Data Analyst, Senior');
UPDATE jobs
SET job_title = REPLACE(job_title, 'Machine Learning Engineer, Sr.', 'Machine Learning Engineer, Senior');
Jetzt haben wir eine sauberere Version.
SCHRITT 5: Extrahieren unnötiger Zeichen aus der Spalte salary_estimate
Im nächsten Schritt extrahieren wir die Werte „(Glassdoor est.)“ aus der Spalte, wobei wir nur die Zahlen behalten und den Datentyp in VARCHAR konvertieren.
-- examine salary_estimate column
SELECT salary_estimate
FROM jobs
LIMIT 5;
--- Add a cleaned salary_estimate column to the table
ALTER TABLE ds_jobs
ADD COLUMN cleaned_salary_estimate VARCHAR(11);
-- Update the new column with values based on the SUBSTRING() function
UPDATE ds_jobs
SET cleaned_salary_estimate = SUBSTRING(salary_estimate, 1, 11);
-- Delete the salary_estimate column as it is no longer useful.
ALTER TABLE ds_jobs
DROP COLUMN salary_estimate;
Step 6: Bereinigen Sie die Spalte company_name.
Wie oben zu sehen ist, enthält diese Spalte einige unnötige Zeichen, die entfernt werden müssen. Wir können dies mit der folgenden Abfrage tun:
-- clean out unnecessary characters
SELECT
company_name,
SUBSTR(company_name, 1, LENGTH(company_name) - 4) AS cleaned_company_name
FROM jobs;
-- update our table
UPDATE jobs
SET company_name = SUBSTR(company_name, 1, LENGTH(company_name) - 4);
Diese Abfrage verwendet die Funktion SUBSTR()
, um die Zeichen aus der Spalte company_name
abzurufen, beginnend mit dem ersten Zeichen (Index 1) und unter Ausschluss der letzten 3 Zeichen (-3).
Schritt 7: Fixieren Sie die Spalte size.
Die Größenspalte kann ein wenig gesäubert werden, damit sie ansprechender aussieht.
SELECT size,
CASE
WHEN size = '-1' THEN '1'
ELSE REPLACE(size, ' to ', '-')
END AS cleaned_size
FROM jobs;
Schritt 8: Bereinigen Sie die Spalte type_of_ownership.
-- clean type of ownership column
SELECT type_of_ownership
FROM jobs
GROUP BY type_of_ownership;
Wie oben zu sehen ist, enthält diese Spalte Daten, die wir standardisieren müssen, d. h. wir ersetzen die „-1“-Werte durch „unbekannt“ und ändern die Werte „Unternehmen – privat“ und „Unternehmen – öffentlich“, damit sie leichter zu lesen sind.
Dies können wir mit CASE WHEN-Anweisungen erreichen.
UPDATE jobs
SET type_of_ownership =
CASE
WHEN type_of_ownership = 'Company - Private' THEN 'Private Company'
WHEN type_of_ownership = 'Company - Public' THEN 'Public Company'
WHEN type_of_ownership = -1 THEN 'Unknown'
ELSE type_of_ownership -- Keeps other values unchanged
END;
Schritt 9: Bereinigen Sie die Spalte der Mitbewerber.
Zum Schluss können wir die Spalte der Mitbewerber bereinigen, da die meisten Werte als ‑1 angezeigt werden. Auch hier würden wir sie als „unbekannt“ anzeigen.
SELECT competitors, COUNT(competitors)
FROM ds_jobs
GROUP BY competitors;
--- most of the values show -1, replace with unknown
SELECT competitors,
CASE WHEN competitors = -1 THEN 'Unknown'
ELSE competitors
END AS cleaned_competition
FROM jobs;
--- update TABLE
UPDATE jobs
SET competitors =
CASE
WHEN competitors = -1 THEN 'Unknown'
ELSE competitors
END;
Voilà!
Jetzt haben wir einen sauberen Datensatz, mit dem wir EDA oder weitere Analysen durchführen können.
Quelle: medium.com