Очистка набора данных FIFA 21 с использованием SQL

Очистка набора данных FIFA 21 с использованием SQL

Введение

Популярная видеоигра FIFA 21 имеет большую базу данных статистики игроков и команд, известную как FIFA 21 dataset. Хотя этот набор данных может быть полезным инструментом для изучения производительности игроков и командной тактики, часто возникают проблемы с качеством данных, которые необходимо решить, прежде чем можно будет получить какую-либо полезную информацию.

Действие по обнаружению и устранению недостатков, несоответствий и неточностей в наборе данных известно как «очистка данных». Любой проект анализа данных должен включать его, поскольку он гарантирует точность, полноту и надёжность данных. Но при работе с большими наборами данных, такими как FIFA 21 dataset, очистка данных может занять много времени и стать сложной операцией.

К счастью, SQL (язык структурированных запросов) может быть эффективным инструментом для очистки и преобразования данных. В дополнение к множеству функций и операций, которые можно использовать для очистки и изменения данных, SQL является широко используемым компьютерным языком для поддержки и управления реляционными базами данных.

В этой статье мы рассмотрим, как можно использовать SQL для повышения качества набора данных FIFA 21. В этом разделе мы рассмотрим некоторые типичные проблемы с качеством данных, возникающие в наборе данных FIFA 21, и продемонстрируем, как можно использовать SQL-запросы для их решения. Чтобы проиллюстрировать методы очистки и рекомендации по очистке данных SQL, мы также представим примеры кода. К концу этой статьи у вас должно быть чёткое представление о том, как использовать SQL для эффективной очистки и преобразования данных.

Понимание набора данных FIFA 21

Набор данных FIFA 21 представляет собой тщательную подборку информации о лучших футболистах мира, включая их биографические данные, показатели производительности и игровые характеристики. Этот датасет можно использовать для оценки игроков, изучения эффективности игроков и команд и прогнозирования будущих результатов.

Однако набор данных FIFA 21 имеет значительные проблемы с качеством, что может затруднить точный анализ. Например, в наборе данных есть повторяющиеся записи, отсутствующие данные, несогласованные данные или выбросы. Кроме того, некоторые атрибуты были выражены в нескольких единицах, что затрудняет их анализ и интерпретацию.

Очистка набора данных FIFA 21 может оказаться непростой задачей, поскольку она требует эффективного выявления и устранения проблем с качеством данных. Кроме того, поскольку набор данных содержит более 18 000 записей, ручная очистка может оказаться невозможной. Таким образом, использование SQL для автоматизации процессов очистки данных и преобразования данных является эффективным способом очистки датасета и подготовки его к анализу.

Вы можете найти набор данных здесь .

Инструмент, используемый для этой очистки — Microsoft SQL Server 2022.

Поскольку это было бы важно для нашего анализа, мы сначала попытаемся понять наш набор данных, чтобы получить представление о нем и увидеть его форму:

--View of Raw Data
SELECT *
FROM [dbo].[fifa21 raw data v2];

--Shape and Content of the Dataset
EXEC sp_help '[dbo].[fifa21 raw data v2]';

-- OR
SELECT c.name AS Column_Name, t.name AS Data_Type
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id AND t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('[dbo].[fifa21 raw data v2]');

Наш набор данных содержит всего 77 столбцов и различные типы данных:

Мы также можем просмотреть количество строк, используя другой запрос:

--Checking for accurate row count
SELECT COUNT(*) AS Row_Count
FROM [dbo].[fifa21 raw data v2];

Как показывает наш запрос, у нас есть 18 979 строк в нашем наборе данных, что говорит нам о том, что это довольно большой датасет. Теперь у нас есть представление о том, как выглядит наш набор данных, поэтому мы можем правильно приступить к процессу очистки.

Очистка данных

1. Дубликаты

Проверка на наличие дубликатов в наших данных — это первый этап процесса очистки данных, потому что дублирование значений может исказить результаты, вызвать ошибки и, в конечном итоге, повредить точности и надёжности наших данных.

--Check for Duplicates
SELECT LongName, Age, Club, Nationality, COUNT(*) AS Count
FROM [dbo].[fifa21 raw data v2]
GROUP BY LongName, Age, Club, Nationality
HAVING COUNT(*) > 1;

Судя по результатам нашего запроса, наш набор данных имеет повторяющееся значение. Как аналитики данных, мы обязаны тщательно изучить данные, чтобы чётко понять сценарий. Чтобы определить, действительно ли это дубликат с идентичными значениями или игрок в аренде, мы должны провести дополнительный анализ. Мы не можем решить, как обращаться с повторяющимся значением, пока не выясним основную причину.

--There was a duplicate but check further to confirm if it is a loan or an outright duplicate
SELECT LongName, Age, Club, Nationality, Contract
FROM [dbo].[fifa21 raw data v2]
WHERE LongName = 'Peng Wang' AND Age = 27;

Запрос данных с использованием предложения WHERE также показывает, что это действительно тот же игрок, но он был отправлен в аренду на определённый период времени.

2. НУЛЕВЫЕ значения

Мы ищем нулевые значения, потому что они также могут повлиять на наши данные.

--Checking for NULL values in the most relevant Columns
SELECT COUNT(*) AS COUNT
FROM [dbo].[fifa21 raw data v2]
WHERE Name IS NULL
  OR LongName IS NULL
  OR Age IS NULL
  OR Nationality IS NULL
  OR Club IS NULL
  OR photoUrl IS NULL
  OR playerUrl IS NULL;

Наш запрос показывает, что в наиболее релевантных столбцах нашего набора данных нет нулевых данных.

3. Удаление нерелевантных столбцов

Мы можем продолжить удаление столбцов, которые не нужны для нашего анализа.

Поскольку ссылка photoUrl не открывается, столбец Name также не имеет значения, поскольку у нас уже есть LongName, а столбец 3 содержит в основном нулевые значения. Было бы разумно просто отказаться от обоих, чтобы облегчить нашу работу.

--Drop unnecessary columns and those with excessive NULL values
ALTER TABLE [dbo].[fifa21 raw data v2]
DROP COLUMN photoUrl;

ALTER TABLE [dbo].[fifa21 raw data v2]
DROP COLUMN column3;

ALTER TABLE [dbo].[fifa21 raw data v2]
DROP COLUMN Name;

4. Переименование столбцов LongName и oOVA

Мы бы переименовали наш столбец LongName в Full_Name, а oOVA — в OVA, используя хранимую процедуру SP_RENAME.

Я использовал хранимые процедуры для переименования столбцов в наборе данных FIFA 21, потому что это обеспечивает более простой и эффективный способ изменения схемы базы данных. Хранимые процедуры — это предварительно скомпилированные блоки кода, которые можно выполнять в базе данных, и их можно многократно использовать повторно без необходимости переписывать код.

В этом случае с помощью хранимой процедуры SP_RENAME я смог переименовать столбцы «LongName» и «oOVA» с помощью всего одной инструкции SQL вместо того, чтобы писать отдельные инструкции ALTER TABLE для каждого столбца. Такой подход снижает риск ошибок, упрощает код и экономит время.

Кроме того, хранимые процедуры также могут повысить безопасность базы данных, предоставляя контролируемый доступ к определённым операциям. Это также может повысить производительность за счёт уменьшения объёма сетевого трафика, необходимого для выполнения операторов SQL. В целом хранимые процедуры — это мощный инструмент, который может повысить функциональность и производительность баз данных.

--Rename important columns
SP_RENAME '[dbo].[fifa21 raw data v2].LongName', 'Full_Name', 'COLUMN';

SP_RENAME '[dbo].[fifa21 raw data v2].oOVA', 'OVA', 'COLUMN';

5. Удаление спецсимволов и очистка столбца Full_Name

Мы очистим специальные символы, найденные в столбце Full_Name, извлекая имена без специальных символов из playerUrl в столбец Full_Name:

--Extracting Full names from the playerUrl Column
UPDATE [dbo].[fifa21 raw data v2]
SET Full_Name = SUBSTRING(playerUrl, CHARINDEX('/', playerUrl, 30) + 1, 
 CHARINDEX('/', playerUrl, CHARINDEX('/', playerUrl, 30) + 1) - CHARINDEX('/', playerUrl, 30) - 1);

После того, как мы выполнили запрос, он вернул имена с «-» и все в нижнем регистре, поэтому мы напишем ещё один запрос для устранения этих несоответствий:

--Replace "-" with sapce 
UPDATE [dbo].[fifa21 raw data v2]
SET Full_Name = REPLACE(Full_Name, '-', ' ');

--Replace first letter of each name with a capital letter
UPDATE [dbo].[fifa21 raw data v2]
SET Full_Name = UPPER(LEFT(Full_Name, 1)) + SUBSTRING(Full_Name, 2, CHARINDEX(' ', Full_Name + ' ', 2) - 2) +
           ' ' + UPPER(LEFT(SUBSTRING(Full_Name, CHARINDEX(' ', Full_Name + ' ', 2) + 1, LEN(Full_Name)), 1)) +
           SUBSTRING(SUBSTRING(Full_Name, CHARINDEX(' ', Full_Name + ' ', 2) + 1, LEN(Full_Name)), 2, LEN(Full_Name));

6. Очистка начального, конечного и лишнего пространства в колонке Club

Во-первых, мы просматриваем отдельные клубы в столбце.

SELECT DISTINCT Club
FROM [dbo].[fifa21 raw data v2]
ORDER BY Club ASC;

--Trailing spaces
UPDATE [dbo].[fifa21 raw data v2]
SET Club = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(Club, CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ')));

--Replace any consecutive spaces with a single space
UPDATE [dbo].[fifa21 raw data v2]
SET Club = REPLACE(Club, SPACE(2), SPACE(1))

7. Очистка столбца Contract

--Cleaning the Contract column
SELECT DISTINCT(Contract)
FROM [dbo].[fifa21 raw data v2];

Столбец содержит некоторые неродные символы, как видно из приведённого выше примера. Они будут удалены, а их место будет занято дефисом. Кроме того, выполнив следующий запрос, мы создадим два новых столбца с именами «contract start» и «contract end», используя информацию, предоставленную в столбце Contract:

--Cleaning the Contract column
SELECT DISTINCT(Contract)
FROM [dbo].[fifa21 raw data v2];

UPDATE [dbo].[fifa21 raw data v2]
SET Contract = REPLACE(Contract, '~', '-');

UPDATE [dbo].[fifa21 raw data v2]
SET Contract = SUBSTRING(Contract, 9, 4)
WHERE Contract LIKE '%on%';

--Creating new contract_start and contract_end columns

ALTER TABLE [dbo].[fifa21 raw data v2]
ADD Contract_Start VARCHAR(20);

ALTER TABLE [dbo].[fifa21 raw data v2]
ADD Contract_End VARCHAR(20);

--Get Contract start year
UPDATE [dbo].[fifa21 raw data v2]
SET Contract_Start = SUBSTRING(Contract, 1, 4);

--Get Contract end year
UPDATE [dbo].[fifa21 raw data v2]
SET Contract_End = RIGHT(Contract, 4);

--View Output
SELECT Contract, Contract_Start, Contract_End
FROM [dbo].[fifa21 raw data v2];

8. Очистка столбцов Height и Weight

Столбец Height содержит несколько противоречивых значений; в то время как некоторые футболисты измерялись в «см», другие измерялись в «футах-дюймах», поэтому нам нужно было бы преобразовать их в сантиметры:

--Cleaning the Height column
UPDATE [dbo].[fifa21 raw data v2]
SET [Height] =
 CONVERT(FLOAT, SUBSTRING([Height], 1, CHARINDEX('''', [Height]) - 1)) * 30.48 +
 CONVERT(FLOAT, SUBSTRING([Height], CHARINDEX('''', [Height]) + 1, CHARINDEX('"', [Height]) - CHARINDEX('''', [Height]) - 1)) * 2.54 
 + CONVERT(FLOAT, SUBSTRING([Height], CHARINDEX('.', [Height]) + 1, LEN([Height]) - CHARINDEX('.', [Height]))) * 0.0254
 WHERE [Height] LIKE '[0-9]''[0-9]%"' AND CHARINDEX('"', [Height]) > 0;

Этот запрос сначала проверяет, соответствует ли значение столбца Height шаблону из двух чисел, разделённых знаком (‘) и необязательной двойной кавычкой («) в конце. Затем он использует функцию CHARINDEX, чтобы найти позиции знака фута и двойной кавычки, и функцию SUBSTRING, чтобы извлечь значения футов и дюймов в виде подстрок. Наконец, функция CONVERT используется для преобразования извлеченных значений из футов и дюймов в сантиметры. Она умножает футовую часть на 30,48 (поскольку в футе 30,48 см), дюймовую часть на 2,54 (поскольку в дюйме 2,54 см) и десятичную часть (если есть) на 0,0254 (для преобразования дюймов в дюймы). см).

В нашей колонке Weight одни измерения выражены в килограммах (кг), а другие — в фунтах. Мы должны сделать некоторые преобразования и изменить все значения на килограммы, чтобы сохранить согласованность наших данных.

--Cleaning the Weight column

SELECT DISTINCT(Weight)
FROM [dbo].[fifa21 raw data v2]

UPDATE [dbo].[fifa21 raw data v2]
SET [Weight] = 
 CASE 
  WHEN RIGHT([Weight], 2) = 'lbs' THEN CAST(SUBSTRING([Weight], 1, LEN([Weight]) - 3) AS FLOAT) / 2.20462 
  ELSE CAST(SUBSTRING([Weight], 1, LEN([Weight]) - 2) AS FLOAT) 
 END 
WHERE RIGHT([Weight], 2) IN ('kg', 'lbs')

Этот запрос сначала проверяет, является ли единица веса килограммами или фунтами, проверяя, являются ли последние два символа в столбце «Weight» «кг» или «фунты». Затем он преобразует значение веса в число с плавающей запятой и при необходимости применяет коэффициент преобразования. Если единицей веса являются фунты, значение преобразуется в килограммы путем деления на 2,20462. Если единица веса указана в килограммах, преобразование не выполняется.

9. Очистка столбцов Value, Wage и Release_clause

Все три столбца имеют проблемы, как видно выше. Следовательно, чтобы разрешить агрегацию, мы должны преобразовать значения в число с плавающей запятой, удалить знак валюты и преобразовать буквы «М» и «К» в их эквиваленты в миллионах и тысячах.

Сначала удаляются десятичные точки во всех строках и столбцах:

--Removing decimal places

UPDATE [dbo].[fifa21 raw data v2]
SET Value = REPLACE(Value, '.', ' ');

UPDATE [dbo].[fifa21 raw data v2]
SET Wage = REPLACE(Wage, '.', ' ');

UPDATE [dbo].[fifa21 raw data v2]
SET Release_Clause = REPLACE(Release_Clause, '.', ' ');

Затем мы заменим «K» и «M» соответствующими нулями, используя оператор CASE WHEN:

UPDATE [dbo].[fifa21 raw data v2]
SET Value = 
 (SELECT CASE
   WHEN Value LIKE '% %' THEN REPLACE( Value, 'M', '00000')
   WHEN Value LIKE '%K' THEN REPLACE( Value, 'K', '000')
   WHEN Value LIKE '%M' THEN REPLACE( Value, 'M', '000000')
   ELSE Value END AS Player_Value);

UPDATE [dbo].[fifa21 raw data v2]
SET Wage = 
 (SELECT CASE
   WHEN Wage LIKE '% %' THEN REPLACE( Value, 'M', '00000')
   WHEN Wage LIKE '%K' THEN REPLACE( Value, 'K', '000')
   WHEN Wage LIKE '%M' THEN REPLACE( Value, 'M', '000000')
   ELSE Wage END AS Player_Wage);

UPDATE [dbo].[fifa21 raw data v2]
SET Release_Clause = 
 (SELECT CASE
   WHEN Release_Clause LIKE '% %' THEN REPLACE( Value, 'M', '00000')
   WHEN Release_Clause LIKE '%K' THEN REPLACE( Value, 'K', '000')
   WHEN Release_Clause LIKE '%M' THEN REPLACE( Value, 'M', '000000')
   ELSE Release_Clause END AS Release_Clause);

Следующий шаг — убедиться, что после удаления десятичных точек не осталось пробелов:

--Removing spaces left when we removed decimal points

UPDATE [dbo].[fifa21 raw data v2]
SET Value = REPLACE(Value, ' ', '');

UPDATE [dbo].[fifa21 raw data v2]
SET Wage = REPLACE(Wage, ' ', '');

UPDATE [dbo].[fifa21 raw data v2]
SET Release_Clause = REPLACE(Release_Clause, ' ', '');

Наконец, мы удаляем знак валюты и преобразуем данные в более подходящий тип, используя приведённый ниже запрос:

--Remove currency type 

SELECT Value, Wage, Release_Clause
FROM [dbo].[fifa21 raw data v2];

UPDATE [dbo].[fifa21 raw data v2]
SET Value = SUBSTRING(Value, 2, LEN(Value)-1);

UPDATE [dbo].[fifa21 raw data v2]
SET Wage = SUBSTRING(Wage, 2, LEN(Wage)-1);

UPDATE [dbo].[fifa21 raw data v2]
SET Release_Clause = SUBSTRING(Release_Clause, 2, LEN(Release_Clause)-1);

--Convert columns to bigint datatype

ALTER TABLE [dbo].[fifa21 raw data v2]
ALTER COLUMN Value BIGINT;

ALTER TABLE [dbo].[fifa21 raw data v2]
ALTER COLUMN Wage BIGINT;

ALTER TABLE [dbo].[fifa21 raw data v2]
ALTER COLUMN Release_Clause BIGINT;

10. Очистка колонок W_F, SM и IR

В этом сценарии наша задача довольно проста: нам просто нужно изменить типы данных на целые числа и удалить все специальные символы, которые могли присутствовать в любых столбцах:

--Cleaning the W_F, SM and IR columns.

SELECT W_F, SM, A_W, D_W, IR
FROM [dbo].[fifa21 raw data v2]

UPDATE [dbo].[fifa21 raw data v2]
SET W_F = SUBSTRING(W_F, 1, 1);

UPDATE [dbo].[fifa21 raw data v2]
SET SM = SUBSTRING(SM, 1, 1);

UPDATE [dbo].[fifa21 raw data v2]
SET IR = SUBSTRING(IR, 1, 1);

--Convert columns to INT

ALTER TABLE [dbo].[fifa21 raw data v2]
ALTER COLUMN W_F INT;

ALTER TABLE [dbo].[fifa21 raw data v2]
ALTER COLUMN SM INT;

ALTER TABLE [dbo].[fifa21 raw data v2]
ALTER COLUMN IR INT;

Заключение

Процесс очистки данных является важным шагом в любом проекте анализа данных, поскольку он помогает обеспечить точность, полноту и согласованность данных. В этом процессе очистки набора данных FIFA 21 мы использовали различные методы очистки данных для решения таких проблем, как отсутствующие значения, несогласованные форматы, неправильные типы данных и специальные символы.

После выполнения этих методов очистки мы смогли получить чистый и структурированный датасет, который можно использовать для дальнейшего анализа. Очищенный набор данных предоставляет достоверную информацию, которую можно использовать для получения информации об атрибутах игроков, результатах команд и других факторах, связанных с игрой в футбол.

В заключение, очистка данных является важным шагом в любом проекте анализа данных, и важно обеспечить точность и согласованность данных для получения значимой информации.

Спасибо за прочтение!