Під час створення моделі машинного навчання або участі в проекті з аналізу даних важливо переконатися, що використовувані дані мають найвищу якість. Пам’ятайте: якщо сміття на вході, тоді сміття на виході (garbage in, garbage out). Очищення даних – це важливий етап в будь-якому проекті з обробки даних; це процес виявлення та видалення неузгоджень та помилок в даних. Приєднуйтеся, і давайте поглибимося в очищення даних за допомогою SQL.
Для цього навчального посібника ми будемо використовувати просту базу даних управління персоналом з двома таблицями: employees та department. Перш ніж ми розпочнемо процес очищення, ми повинні розуміти наші дані.
1.Розуміння даних
По-перше, нам потрібно зрозуміти, яка структура база даних. Це дозволить нам отримати уявлення про активні взаємозв’язки між таблицями, і використані типи даних будуть направляти нас у проведенні подальших етапів очищення та аналізу.
1.2 Показати таблиці
show tables;+-----------------+
| Tables_in_hr_db |
+-----------------+
| departments |
| employees |
+-----------------+Ми маємо лише дві таблиці в базі даних – щасливчики ми! Ну, насправді ні. Це лише для навчальних цілей. У реальній виробничій базі даних ви знайдете набагато більше ніж дві таблиці.
1.3 Опис таблиць
DESCRIBE departments;
DESCRIBE employees;+-----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id | int | NO | PRI | NULL | |
| department_name | varchar(50) | YES | | NULL | |
+-----------------+-------------+------+-----+---------+-------+
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| employee_id | int | NO | PRI | NULL | |
| employee_name | varchar(100) | YES | | NULL | |
| department_id | int | YES | MUL | NULL | |
| gender | varchar(10) | YES | | NULL | |
| salary | varchar(15) | YES | | NULL | |
| hire_date | date | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+У вищенаведеному скрипті ми описали дві таблиці, і тепер ми можемо побачити типи даних стовпців та наявність відношення “один до багатьох” між таблицями employees та department.
1.4 Отримання даних
Ми відобразимо перші 5 рядків, щоб отримати уявлення про дані, які зберігаються в таблицях.
SELECT * FROM departments LIMIT 5;
SELECT * FROM employees LIMIT 5;+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
| 1 | Human Resources |
| 2 | Marketing |
| 3 | Finance |
+---------------+-----------------+
+-------------+---------------+---------------+--------+------------+------------+
| employee_id | employee_name | department_id | gender | salary | hire_date |
+-------------+---------------+---------------+--------+------------+------------+
| 1 | John Doe | 1 | M | $60,000.00 | 2022-01-15 |
| 2 | Jane Smith | 2 | Female | $75,000.00 | 2022-02-20 |
| 3 | Bob Johnson | 1 | m | $55,000.00 | 2022-03-10 |
| 4 | Alice Brown | 3 | f | $80,000.00 | 2022-04-05 |
| 5 | Charlie White | 2 | male | $70,000.00 | 2022-05-12 |
+-------------+---------------+---------------+--------+------------+------------+З цього вже можна вбачити деякі невідповідності в наших даних. Наприклад, стовпець “gender” не має однорідності, оскільки він містить різні значення для того самого статевого ознаки, а стовпець “salary” включає знак долара, що може створювати проблеми, якщо ми маємо намір проводити обчислення з ним.
У наступному розділі ми виправимо ці проблеми разом із іншими, які можуть бути ще не виявлені.
2.Очищення даних
2.1 Робота з дублікатами
Дублікати можуть негативно впливати на наш аналіз чи модель, тому найкраще виявляти їх і вирішувати це питання на початковому етапі.
У скрипті нижче ми будемо шукати дублікати. Пропустимо стовпець “employee_id”, оскільки ми знаємо, що це первинний ключ (primary key) з попереднього етапу.
Простий підхід за допомогою ORDER BY.
SELECT employee_name, department_id, gender, salary, hire_date
FROM employees
ORDER BY 1,2,3,4,5;+-----------------+---------------+--------+------------+------------+
| employee_name | department_id | gender | salary | hire_date |
+-----------------+---------------+--------+------------+------------+
| Alice Brown | 3 | f | $80,000.00 | 2022-04-05 |
| Bob Johnson | 1 | m | $55,000.00 | 2022-03-10 |
| Bob Johnson | 1 | M | $55,000.00 | 2022-03-10 |
| Carlos da Costa | 3 | m | $75,000.00 | 2050-05-10 |
| Charlie White | 2 | male | $70,000.00 | 2022-05-12 |
| David Black | 1 | F | $68,000.00 | 2022-07-25 |
| Eva Green | 3 | | $72,000.00 | 2022-06-18 |
| Frank Red | NULL | F | $60,000.00 | 2022-09-05 |
| Jane Smith | 2 | Female | $75,000.00 | 2022-02-20 |
| Jane Smith | 2 | female | $75,000.00 | 2022-02-20 |
| John Doe | 1 | M | $60,000.00 | 2022-01-15 |
| John Doe | 1 | m | $60,000.00 | 2022-01-15 |
| Sophie Grey | 2 | NULL | $65,000.00 | 2022-08-30 |
+-----------------+---------------+--------+------------+------------+Цей підхід працює тільки для таблиці з невеликою кількістю записів! Тепер давайте перевіримо більш надійний підхід, використовуючи підзапит і функцію агрегування COUNT для підрахунку кількості дублікатів.
SELECT COUNT(*)
FROM(
SELECT employee_name, department_id, gender, salary, hire_date, count(*) as quantity
FROM employees
GROUP BY 1,2,3,4,5) suquery
WHERE quantity > 1;+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+У вищенаведеному скрипті відбувається підрахунок кількості дубльованих записів, вибираючи всіх співробітників, групуючи їх за схожістю і рахуючи кількість, а потім фільтруючи лише рядки з кількістю більше 1.
Для більшої ясності зараз ми можемо перерахувати фактичні дубльовані записи та їх кількість входжень.
SELECT employee_name, department_id, gender, salary, hire_date, count(*) as quantity
FROM employees
GROUP BY 1,2,3,4,5
HAVING quantity > 1;+---------------+---------------+--------+------------+------------+----------+
| employee_name | department_id | gender | salary | hire_date | quantity |
+---------------+---------------+--------+------------+------------+----------+
| John Doe | 1 | M | $60,000.00 | 2022-01-15 | 2 |
| Jane Smith | 2 | Female | $75,000.00 | 2022-02-20 | 2 |
| Bob Johnson | 1 | m | $55,000.00 | 2022-03-10 | 2 |
+---------------+---------------+--------+------------+------------+----------+Тепер, коли ви виявили дублікати, ви можете вирішити видаляти, оновлювати або залишати їх відповідно до ваших вимог.
2.2 Робота з відсутніми даними та значеннями Null
Ми використовуватимемо два скрипти: один для пошуку порожніх рядків і інший для пошуку значень NULL.
-- display record with empty strings
SELECT * FROM employees WHERE gender = "";
-- display records with null
SELECT * FROM employees
WHERE
employee_name IS NULL OR
department_id IS NULL OR
gender IS NULL OR
salary IS NULL OR
hire_date IS NULL;+-------------+---------------+---------------+--------+------------+------------+
| employee_id | employee_name | department_id | gender | salary | hire_date |
+-------------+---------------+---------------+--------+------------+------------+
| 6 | Eva Green | 3 | | $72,000.00 | 2022-06-18 |
+-------------+---------------+---------------+--------+------------+------------+
+-------------+---------------+---------------+--------+------------+------------+
| employee_id | employee_name | department_id | gender | salary | hire_date |
+-------------+---------------+---------------+--------+------------+------------+
| 8 | Sophie Grey | 2 | NULL | $65,000.00 | 2022-08-30 |
| 9 | Frank Red | NULL | F | $60,000.00 | 2022-09-05 |
+-------------+---------------+---------------+--------+------------+------------+
Зі скрипту вище ми бачимо один запис із порожньою стрічкою в стовпці “gender” і два із значеннями NULL.
Залежно від набору даних та вимог, ви можете обробляти відсутні дані, заповнюючи їх середнім, медіанним, модальним значенням, використовуючи заповнення вперед чи назад, або замінюючи константою.
Допустимо, ми знаємо, що Sophie Grey – це жінка, ми можемо використовувати оператор CASE, щоб замінити значення NULL реальною статевою ознакою.
SELECT employee_id, employee_name, department_id, salary, hire_date,
CASE WHEN gender IS NULL then "F" END AS gender
FROM employees
WHERE employee_id = 8;У випадку, якщо очікується наявність значень NULL, можна використовувати функцію COALESCE для відображення значення за замовчуванням.
2.3 Робота з невірними категорійними даними
З попередніх розділів ми помітили, що стовпець “gender” має багато непослідовностей, зі значеннями, такими як Female, F, f і так далі. Якщо ми хочемо мати якісні дані, нам потрібно це виправити. Спочатку давайте виведемо всі унікальні значення у стовпці “gender“, а потім замінимо їх на відповідну категорію “m” для чоловіків та “f” для жінок.
Ми будемо використовувати функцію UPPER, оскільки функція DISTINCT нечутливий до регістру.
SELECT DISTINCT UPPER(gender) AS gender FROM employees;
SELECT employee_id, employee_name, department_id, salary, hire_date, CASE
WHEN UPPER(gender) IN ("M","MALE") THEN "m"
WHEN UPPER(gender) IN ("FEMALE","F") THEN "f"
ELSE "" END AS gender
FROM employees;+--------+
| gender |
+--------+
| M |
| FEMALE |
| F |
| MALE |
| |
| NULL |
+--------+
+-------------+-----------------+---------------+------------+------------+--------+
| employee_id | employee_name | department_id | salary | hire_date | gender |
+-------------+-----------------+---------------+------------+------------+--------+
| 1 | John Doe | 1 | $60,000.00 | 2022-01-15 | m |
| 2 | Jane Smith | 2 | $75,000.00 | 2022-02-20 | f |
| 3 | Bob Johnson | 1 | $55,000.00 | 2022-03-10 | m |
| 4 | Alice Brown | 3 | $80,000.00 | 2022-04-05 | f |
| 5 | Charlie White | 2 | $70,000.00 | 2022-05-12 | m |
| 6 | Eva Green | 3 | $72,000.00 | 2022-06-18 | |
| 7 | David Black | 1 | $68,000.00 | 2022-07-25 | f |
| 8 | Sophie Grey | 2 | $65,000.00 | 2022-08-30 | |
| 9 | Frank Red | NULL | $60,000.00 | 2022-09-05 | f |
| 10 | John Doe | 1 | $60,000.00 | 2022-01-15 | m |
| 11 | Jane Smith | 2 | $75,000.00 | 2022-02-20 | f |
| 12 | Bob Johnson | 1 | $55,000.00 | 2022-03-10 | m |
| 13 | Carlos da Costa | 3 | $75,000.00 | 2050-05-10 | m |
+-------------+-----------------+---------------+------------+------------+--------+Чи ви помітили, що один співробітник – мандрівник у часі?
За інформацією від відділу кадрів, співробітник Carlos da Costa був прийнятий на роботу 10 травня 2050 року; скористайтеся функцією CASE для його зміни.
2.4 Конвертація типів
У стовпці “salary” є знак долара, що заважатиме виконанню обчислень.
Допустимо, ми хочемо отримати середню зарплату за відділ, якщо ми введемо в скрипт нижче, ми отримаємо нулі для середньої зарплати.
SELECT department_name, AVG(salary) as "Average Salary"
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
GROUP BY department_name;+-----------------+----------------+
| department_name | Average Salary |
+-----------------+----------------+
| Human Resources | 0 |
| Marketing | 0 |
| Finance | 0 |
+-----------------+----------------+Щоб виправити це, спочатку замінимо знак долара і змінимо тип даних на десятковий.
SELECT department_name,
AVG(
cast(replace(salary,'$','') AS DECIMAL(10,2))
)
as "Average Salary"
FROM employees LEFT JOIN departments
ON employees.department_id = departments.department_id
GROUP BY department_name;+-----------------+----------------+
| department_name | Average Salary |
+-----------------+----------------+
| Human Resources | 59.600000 |
| Marketing | 71.250000 |
| Finance | 75.666667 |
+-----------------+----------------+Ось і все, тепер стовпець “salary” має правильний формат і може використовуватися для обчислень.
3.Заключні думки
Хоча в області очищення даних є багато ще для вивчення, цей навчальний посібник слугує вихідним пунктом для початку вашого шляху. Найбільш ефективним способом покращити свої навички в очищенні даних і знаходжені нови фішок – це активна участь у проектах з обробки даних. Я вірю, що ви отримали корисні поради з цього навчального посібника і закликаю вас поглибитися в реальні сценарії роботи з даними, щоб ще більше вдосконалити свої навички
Гарного дня та успішного кодування!
Зміст зовнішніх матеріалів не обов’язково відображає думки чи роботу Maven Analytics чи будь-якого з її членів команди.
Ми віримо в підтримку навчання протягом усього життя, і наша мета – надавати платформу для спільноти даних для обміну своєю роботою та отримання зворотного зв’язку від спільноти Maven Analytics.
Приємного навчання!
-Команда Maven
ОРИГІНАЛ СТАТТІ:Data Cleaning with SQL
АВТОР СТАТІ:Carlos da Costa
🚀Долучайтесь до нашої спільноти Telegram:
🚀Долучайтесь до нашої спільноти FaceBook: