Если у вас не установлена СУБД, то можно использоваться SQLize
Оригинал статьи: https://www.kdnuggets.com/data-cleaning-in-sql-how-to-prepare-messy-data-for-analysis
Не терпится приступить к анализу данных с помощью SQL? Что ж, возможно, вам придется немного подождать. Но почему?
Данные в таблицах БД часто могут быть неупорядоченными. Ваши данные могут содержать пропущенные значения, дублирующиеся записи, выбросы, противоречивые записи данных и многое другое. Поэтому очистка данных перед тем, как вы сможете проанализировать их с помощью SQL, очень важна.
Когда вы изучаете SQL, вы можете разворачивать таблицы базы данных, изменять их, обновлять и удалять записи по своему усмотрению. Но на практике это почти никогда не происходит. У вас может не быть разрешения изменять таблицы, обновлять и удалять записи. Но у вас будет доступ на чтение к базе данных и вы сможете запускать множество запросов SELECT.
В этом руководстве мы создадим таблицу базы данных, наполним ее и посмотрим, как мы можем очистить данные с помощью SQL. Давайте начнем!
Создание таблицы базы данных с записями
Для этого урока давайте создадим таблицу employees следующим образом:
-- Create the employees table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employe_name VARCHAR(50),
salary DECIMAL(10,2),
hire_date VARCHAR(20),
department VARCHAR(50)
);
Далее вставим в таблицу несколько вымышленных записей:
-- Insert 20 sample records
INSERT INTO employees (employee_id, employee_name, salary, hire_date, department) VALUES
(1, 'Amy West', 60000.00, '2021-01-15', 'HR'),
(2, 'Ivy Lee', 75000.50, '2020-05-22', 'Sales'),
(3, 'joe smith', 80000.75, '2019-08-10', 'Marketing'),
(4, 'John White', 90000.00, '2020-11-05', 'Finance'),
(5, 'Jane Hill', 55000.25, '2022-02-28', 'IT'),
(6, 'Dave West', 72000.00, '2020-03-12', 'Marketing'),
(7, 'Fanny Lee', 85000.50, '2018-06-25', 'Sales'),
(8, 'Amy Smith', 95000.25, '2019-11-30', 'Finance'),
(9, 'Ivy Hill', 62000.75, '2021-07-18', 'IT'),
(10, 'Joe White', 78000.00, '2022-04-05', 'Marketing'),
(11, 'John Lee', 68000.50, '2018-12-10', 'HR'),
(12, 'Jane West', 89000.25, '2017-09-15', 'Sales'),
(13, 'Dave Smith', 60000.75, '2022-01-08', NULL),
(14, 'Fanny White', 72000.00, '2019-04-22', 'IT'),
(15, 'Amy Hill', 84000.50, '2020-08-17', 'Marketing'),
(16, 'Ivy West', 92000.25, '2021-02-03', 'Finance'),
(17, 'Joe Lee', 58000.75, '2018-05-28', 'IT'),
(18, 'John Smith', 77000.00, '2019-10-10', 'HR'),
(19, 'Jane Hill', 81000.50, '2022-03-15', 'Sales'),
(20, 'Dave White', 70000.25, '2017-12-20', 'Marketing');
Я использовал небольшой набор имен и фамилий. Однако вы можете более творчески подойти к этому.
Примечание: Все запросы в этом руководстве предназначены для MySQL. Но вы можете использовать другие СУБД.
1. Отсутствующие значения
Отсутствующие значения в записях данных всегда являются проблемой. Поэтому вы должны обращаться с ними соответствующим образом.
Наивный подход заключается в удалении всех записей, содержащих пропущенные значения для одного или нескольких полей. Однако не следует этого делать, если вы не уверены, что нет другого способа обработки пропущенных значений.
В таблице employees мы видим, что в столбце ‘department’ (см. Строку employee_id 13) имеется нулевое значение, указывающее на отсутствие поля:
SELECT * FROM employees;
Вы можете использовать функцию COALESCE(), чтобы использовать строку ‘Unknown’ в качестве нулевого значения:
SELECT
employee_id,
employee_name,
salary,
hire_date,
COALESCE(department, 'Unknown') AS department
FROM employees;
Выполнение приведенного выше запроса должно дать вам следующий результат:
2. Повторяющиеся записи
Повторяющиеся записи в таблице базы данных могут искажать результаты анализа. Мы выбрали employee_id в качестве первичного ключа. Таким образом, у нас не будет повторяющихся записей о сотрудниках в таблице employee_data.
Вы также можете использовать оператор SELECT DISTINCT:
SELECT DISTINCT * FROM employees;
Как и ожидалось, результирующий набор содержит все 20 записей:
3. Преобразование типов данных
Если вы заметили, столбец ‘hire_date’ в настоящее время имеет тип VARCHAR, а не date. Чтобы упростить работу с датами, полезно использовать функцию STR_TO_DATE() следующим образом:
SELECT
employee_id,
employee_name,
salary,
STR_TO_DATE(hire_date, '%Y-%m-%d') AS hire_date,
department
FROM employees;
Здесь мы выбрали только столбец ‘hire_date’ среди прочих и не выполняли никаких операций со значениями даты. Таким образом, выходные данные запроса должны быть такими же, как и в предыдущем запросе.
Но если вы хотите выполнить какие-либо операции с имеющимся значениями дат, эта функция может быть полезна.
4. Выпадающие значения
Выпадающие значения в одном или нескольких числовых полях могут исказить анализ. Поэтому мы должны проверять и удалять их, чтобы отфильтровать нерелевантные данные.
Но для определения того, какие значения представляют собой искажения, требуются знание предметной области.
В нашем примере, допустим, мы знаем, что верхний предел столбца ‘зарплата‘ равен 100000. Таким образом, любая запись в столбце ‘зарплата‘ может быть не более 100000. И записи, превышающие это значение, являются искажением.
Мы можем проверить наличие таких записей, выполнив следующий запрос:
SELECT *
FROM employees
WHERE salary > 100000;
Как видно, все записи в столбце ‘зарплата’ релевантные. И результирующий набор пуст:
5. Разнородный ввод данных
Разнородные форматирование записи данных довольно распространено, особенно в столбцах дат и строк.
В таблице employees мы видим, что запись, соответствующая сотруднику "joe smith" написано строчными буквами без прописных.
Но для согласованности давайте выделим все имена, отформатированные неправильно. Вы можете использовать функцию CONCAT() в сочетании с UPPER() и SUBSTRING() вот так:
SELECT
employee_id,
CONCAT(
UPPER(SUBSTRING(employee_name, 1, 1)), -- Capitalize the first letter of the first name
LOWER(SUBSTRING(employee_name, 2, LOCATE(' ', employee_name) - 2)), -- Make the rest of the first name lowercase
' ',
UPPER(SUBSTRING(employee_name, LOCATE(' ', employee_name) + 1, 1)), -- Capitalize the first letter of the last name
LOWER(SUBSTRING(employee_name, LOCATE(' ', employee_name) + 2)) -- Make the rest of the last name lowercase
) AS employee_name_title_case,
salary,
hire_date,
department
FROM employees;
6. Проверка диапазонов
Говоря о выпадающих значениях, мы упомянули, что хотели бы, чтобы верхний предел в столбце "зарплата" был равен 100000, и рассматривали любую запись о зарплате выше 100000 как искажение.
Но также верно и то, что вам не нужны отрицательные значения в столбце ‘зарплата’. Таким образом, вы можете выполнить следующий запрос, чтобы проверить, что все записи о сотрудниках содержат значения от 0 до 100000:
SELECT
employee_id,
employee_name,
salary,
hire_date,
department
FROM employees
WHERE salary < 0 OR salary > 100000;
Как видно, результирующий набор пуст:
7. Вывод новых столбцов
Вывод новых столбцов, по сути, не является этапом очистки данных. Однако на практике вам может потребоваться использовать существующие столбцы для получения новых, которые будут полезны при анализе.
Например, таблица employees содержит столбец ‘hire_date’. Возможно, более полезным полем является столбец ‘years_of_service‘, который указывает, как долго сотрудник работает в компании.
Следующий запрос находит разницу между текущим годом и значением year в ‘hire_date’ для вычисления ‘years_of_service’:
SELECT
employee_id,
employee_name,
salary,
hire_date,
department,
YEAR(CURDATE()) - YEAR(hire_date) AS years_of_service
FROM employees;
Вы должны получить следующее:
Как и в случае с другими запросами, этот не изменяет исходную таблицу. Чтобы добавить новые столбцы в исходную таблицу, вам необходимо иметь разрешения на ИЗМЕНЕНИЕ таблицы базы данных.
Подведем итоги
Я надеюсь, вы понимаете, как соответствующие задачи по очистке данных могут улучшить их качество и облегчить анализ. Вы узнали, как проверять пропущенные значения, дублирующиеся записи, неправильное форматирование, искаженияи многое другое.
Попробуйте создать собственную таблицу реляционной базы данных и запустите несколько запросов для выполнения обычных задач очистки данных. Затем познакомьтесь с тем, как использовать SQL для визуализации данных.
Попробовать работу с SQL можно онлайн на сайте SQLize.