Project Overview

The project highlights the vital role of data cleaning, adhering to the 'garbage in, garbage out' principle. I worked with a dataset from EA Sports' FIFA21, sourced from Kaggle, which was very messy and full of formatting issues, rendering it unsuitable for analysis in its original state. Containing over 19,000 rows, this project demonstrates the essential process of transforming raw, disorganized data into a clean and refined format, crucial for any meaningful analysis.

Tools Used

I employed Excel Power Query for this project, a robust tool for data manipulation. Power Query stands out in handling large and complex datasets more efficiently than traditional Excel functions, making it an ideal choice for this extensive data cleaning task.

Project Files

The outcomes of the data cleaning process can be accessed online at this link. To further explore or modify the queries, download the .xlsx and .csv files. Note that these files require the Excel desktop version for full functionality, as Office Online does not support Power Query.

Raw Data Preview

Below is a preview of the first 10 rows of the raw data before cleaning:

ID Name LongName photoUrl playerUrl Nationality Age ↓OVA POT Club Contract Positions Height Weight Preferred Foot BOV Best Position Joined Loan Date End Value Wage Release Clause Attacking Crossing Finishing Heading Accuracy Short Passing Volleys Skill Dribbling Curve FK Accuracy Long Passing Ball Control Movement Acceleration Sprint Speed Agility Reactions Balance Power Shot Power Jumping Stamina Strength Long Shots Mentality Aggression Interceptions Positioning Vision Penalties Composure Defending Marking Standing Tackle Sliding Tackle Goalkeeping GK Diving GK Handling GK Kicking GK Positioning GK Reflexes Total Stats Base Stats W/F SM A/W D/W IR PAC SHO PAS DRI DEF PHY Hits
158023 L. Messi Lionel Messi https://cdn.sofifa.com/players/158/023/21_60.png http://sofifa.com/player/158023/lionel-messi/210006/ Argentina 33 93 93 \n\n\n\nFC Barcelona 2004 ~ 2021 RW, ST, CF 170cm 72kg Left 93 RW 1-Jul-04 NaN €103.5M €560K €138.4M 429 85 95 70 91 88 470 96 93 94 91 96 451 91 80 91 94 95 389 86 68 72 69 94 347 44 40 93 95 75 96 91 32 35 24 54 6 11 15 14 8 2231 466 4 ★ 4★ Medium Low 5 ★ 85 92 91 95 38 65 771
20801 Cristiano Ronaldo C. Ronaldo dos Santos Aveiro https://cdn.sofifa.com/players/020/801/21_60.png http://sofifa.com/player/20801/c-ronaldo-dos-santos-aveiro/210006/ Portugal 35 92 92 \n\n\n\nJuventus 2018 ~ 2022 ST, LW 187cm 83kg Right 92 ST 10-Jul-18 NaN €63M €220K €75.9M 437 84 95 90 82 86 414 88 81 76 77 92 431 87 91 87 95 71 444 94 95 84 78 93 353 63 29 95 82 84 95 84 28 32 24 58 7 11 15 14 11 2221 464 4 ★ 5★ High Low 5 ★ 89 93 81 89 35 77 562
200389 J. Oblak Jan Oblak https://cdn.sofifa.com/players/200/389/21_60.png http://sofifa.com/player/200389/jan-oblak/210006/ Slovenia 27 91 93 \n\n\n\nAtlético Madrid 2014 ~ 2023 GK 188cm 87kg Right 91 GK 16-Jul-14 NaN €120M €125K €159.4M 95 13 11 15 43 13 109 12 13 14 40 30 307 43 60 67 88 49 268 59 78 41 78 12 140 34 19 11 65 11 68 57 27 12 18 437 87 92 78 90 90 1413 489 3 ★ 1★ Medium Medium 3 ★ 87 92 78 90 52 90 150
192985 K. De Bruyne Kevin De Bruyne https://cdn.sofifa.com/players/192/985/21_60.png http://sofifa.com/player/192985/kevin-de-bruyne/210006/ Belgium 29 91 91 \n\n\n\nManchester City 2015 ~ 2023 CAM, CM 181cm 70kg Right 91 CAM 30-Aug-15 NaN €129M €370K €161M 407 94 82 55 94 82 441 88 85 83 93 92 398 77 76 78 91 76 408 91 63 89 74 91 408 76 66 88 94 84 91 186 68 65 53 56 15 13 5 10 13 2304 485 5 ★ 4★ High High 4 ★ 76 86 93 88 64 78 207
190871 Neymar Jr Neymar da Silva Santos Jr. https://cdn.sofifa.com/players/190/871/21_60.png http://sofifa.com/player/190871/neymar-da-silva-santos-jr/210006/ Brazil 28 91 91 \n\n\n\nParis Saint-Germain 2017 ~ 2022 LW, CAM 175cm 68kg Right 91 LW 3-Aug-17 NaN €132M €270K €166.5M 408 85 87 62 87 87 448 95 88 89 81 95 453 94 89 96 91 83 357 80 62 81 50 84 356 51 36 87 90 92 93 94 35 30 29 59 9 9 15 15 11 2175 451 5 ★ 5★ High Medium 5 ★ 91 85 86 94 36 59 595
188545 R. Lewandowski Robert Lewandowski https://cdn.sofifa.com/players/188/545/21_60.png http://sofifa.com/player/188545/robert-lewandowski/210006/ Poland 31 91 91 \n\n\n\nFC Bayern München 2014 ~ 2023 ST 184cm 80kg Right 91 ST 1-Jul-14 NaN €111M €240K €132M 423 71 94 85 84 89 407 85 79 85 70 88 407 77 78 77 93 82 420 89 84 76 86 85 391 81 49 94 79 88 88 96 35 42 19 51 15 6 12 8 10 2195 457 4 ★ 4★ High Medium 4 ★ 78 91 78 85 43 82 248
209331 M. Salah Mohamed Salah https://cdn.sofifa.com/players/209/331/21_60.png http://sofifa.com/player/209331/mohamed-salah/210006/ Egypt 28 90 90 \n\n\n\nLiverpool 2017 ~ 2023 RW 175cm 71kg Left 90 RW 1-Jul-17 NaN €120.5M €250K €144.3M 392 79 91 59 84 79 406 90 83 69 75 89 460 94 92 91 92 91 393 80 69 85 75 84 376 63 55 91 84 83 90 122 38 43 41 62 14 14 9 11 14 2211 470 3 ★ 4★ High Medium 3 ★ 93 86 81 90 45 75 246
212831 Alisson Alisson Ramses Becker https://cdn.sofifa.com/players/212/831/21_60.png http://sofifa.com/player/212831/alisson-ramses-becker/210006/ Brazil 27 90 91 \n\n\n\nLiverpool 2018 ~ 2024 GK 191cm 91kg Right 90 GK 19-Jul-18 NaN €102M €160K €120.3M 114 17 13 19 45 20 138 27 19 18 44 30 268 56 47 40 88 37 240 64 52 32 78 14 140 27 11 13 66 23 65 50 15 19 16 439 86 88 85 91 89 1389 490 3 ★ 1★ Medium Medium 3 ★ 86 88 85 89 51 91 120
231747 K. Mbappé Kylian Mbappé https://cdn.sofifa.com/players/231/747/21_60.png http://sofifa.com/player/231747/kylian-mbappe/210006/ France 21 90 95 \n\n\n\nParis Saint-Germain 2018 ~ 2022 ST, LW, RW 178cm 73kg Right 91 ST 1-Jul-18 NaN €185.5M €160K €203.1M 408 78 91 73 83 83 394 92 79 63 70 90 458 96 96 92 92 82 404 86 77 86 76 79 341 62 38 91 80 70 84 100 34 34 32 42 13 5 7 11 6 2147 466 4 ★ 5★ High Low 3 ★ 96 86 78 91 39 76 1.6K
192448 M. ter Stegen Marc-André ter Stegen https://cdn.sofifa.com/players/192/448/21_60.png http://sofifa.com/player/192448/marc-andre-ter-stegen/210006/ Germany 28 90 93 \n\n\n\nFC Barcelona 2014 ~ 2022 GK 187cm 85kg Right 90 GK 1-Jul-14 NaN €110M €260K €147.7M 118 18 14 11 61 14 144 21 18 12 63 30 254 38 50 37 86 43 268 66 79 35 78 10 171 43 22 11 70 25 70 48 25 13 10 439 88 85 88 88 90 1442 484 4 ★ 1★ Medium Medium 3 ★ 88 85 88 90 45 88 130

Data Cleaning Highlights

  • Numerical Conversions: Columns 'Age', 'OVA', and 'POT' were converted to numerical formats.
  • Date Formatting: Updated 'Joined' and 'Loan Date End' to a uniform date format.
  • Column Name Standardization: Renamed columns for clarity, removing special characters.
  • Contract Column Transformation: Reorganized the 'Contract' column into 'Contract Type', 'Contract Start', and 'Contract End'.
  • Numerical Data Formatting: Standardized units in height and weight columns, and converted monetary values to a numerical format.
  • Character Removal and Conversion: Removed extraneous characters from specific columns and converted them to numbers.
  • Whitespace Elimination: Cleared extra spaces in the 'Club' column.
  • Removing Unnessary Columns: Columns such as 'LongName', 'photoUrl, 'playerUrl' that are not required in future analysis will be removed to have a more consice data.
  • Transformation of the Positions column for Enhanced Analysis: To facilitate detailed analysis of players' positions, the "Positions" column, initially containing comma-separated values representing all positions a player can play (e.g., "RW, ST, CF" for L. Messi), was transformed. Instead of duplicating each player's data across multiple rows for every position, a new "Positions" table was created. This table consists of two columns: one for the player ID and another for the individual positions, effectively normalizing the dataset. This approach minimizes redundancy by maintaining a clean "Players" table with unique player entries and a separate "Positions" table linking players to their possible positions, thereby optimizing the structure for future analyses.
These steps transformed the FIFA21 dataset into a more structured and analyzable format, demonstrating the critical role of data cleaning in data analytics.

Cleaned Data Preview

Here is a preview of the Players Table and Positions table of the cleaned data:

ID Name Nationality Age OVA POT Club Contract Type Contract Start Contract End Height Weight Preferred Foot BOV Best Position Joined Loan Date End Value Wage Release Clause Attacking Crossing Finishing Heading Accuracy Short Passing Volleys Skill Dribbling Curve FK Accuracy Long Passing Ball Control Movement Acceleration Sprint Speed Agility Reactions Balance Power Shot Power Jumping Stamina Strength Long Shots Mentality Aggression Interceptions Positioning Vision Penalties Composure Defending Marking Standing Tackle Sliding Tackle Goalkeeping GK Diving GK Handling GK Kicking GK Positioning GK Reflexes Total Stats Base Stats W/F SM A/W D/W IR PAC SHO PAS DRI DEF PHY Hits
231747 K. Mbappé France 21 90 95 Paris Saint-Germain Fixed Terms 2018 2022 178 73 Right 91 ST 43282 1/0/1900 185500000 160000 203100000 408 78 91 73 83 83 394 92 79 63 70 90 458 96 96 92 92 82 404 86 77 86 76 79 341 62 38 91 80 70 84 100 34 34 32 42 13 5 7 11 6 2147 466 4 5 High Low 3 96 86 78 91 39 76 1600
212831 Alisson Brazil 27 90 91 Liverpool Fixed Terms 2018 2024 191 91 Right 90 GK 43300 1/0/1900 102000000 160000 120300000 114 17 13 19 45 20 138 27 19 18 44 30 268 56 47 40 88 37 240 64 52 32 78 14 140 27 11 13 66 23 65 50 15 19 16 439 86 88 85 91 89 1389 490 3 1 Medium Medium 3 86 88 85 89 51 91 120
209331 M. Salah Egypt 28 90 90 Liverpool Fixed Terms 2017 2023 175 71 Left 90 RW 42917 1/0/1900 120500000 250000 144300000 392 79 91 59 84 79 406 90 83 69 75 89 460 94 92 91 92 91 393 80 69 85 75 84 376 63 55 91 84 83 90 122 38 43 41 62 14 14 9 11 14 2211 470 3 4 High Medium 3 93 86 81 90 45 75 246
200389 J. Oblak Slovenia 27 91 93 Atlético Madrid Fixed Terms 2014 2023 188 87 Right 91 GK 41836 1/0/1900 120000000 125000 159400000 95 13 11 15 43 13 109 12 13 14 40 30 307 43 60 67 88 49 268 59 78 41 78 12 140 34 19 11 65 11 68 57 27 12 18 437 87 92 78 90 90 1413 489 3 1 Medium Medium 3 87 92 78 90 52 90 150
192985 K. De Bruyne Belgium 29 91 91 Manchester City Fixed Terms 2015 2023 181 70 Right 91 CAM 42246 1/0/1900 129000000 370000 161000000 407 94 82 55 94 82 441 88 85 83 93 92 398 77 76 78 91 76 408 91 63 89 74 91 408 76 66 88 94 84 91 186 68 65 53 56 15 13 5 10 13 2304 485 5 4 High High 4 76 86 93 88 64 78 207
192448 M. ter Stegen Germany 28 90 93 FC Barcelona Fixed Terms 2014 2022 187 85 Right 90 GK 41821 1/0/1900 110000000 260000 147700000 118 18 14 11 61 14 144 21 18 12 63 30 254 38 50 37 86 43 268 66 79 35 78 10 171 43 22 11 70 25 70 48 25 13 10 439 88 85 88 88 90 1442 484 4 1 Medium Medium 3 88 85 88 90 45 88 130
190871 Neymar Jr Brazil 28 91 91 Paris Saint-Germain Fixed Terms 2017 2022 175 68 Right 91 LW 42950 1/0/1900 132000000 270000 166500000 408 85 87 62 87 87 448 95 88 89 81 95 453 94 89 96 91 83 357 80 62 81 50 84 356 51 36 87 90 92 93 94 35 30 29 59 9 9 15 15 11 2175 451 5 5 High Medium 5 91 85 86 94 36 59 595
188545 R. Lewandowski Poland 31 91 91 FC Bayern München Fixed Terms 2014 2023 184 80 Right 91 ST 41821 1/0/1900 111000000 240000 132000000 423 71 94 85 84 89 407 85 79 85 70 88 407 77 78 77 93 82 420 89 84 76 86 85 391 81 49 94 79 88 88 96 35 42 19 51 15 6 12 8 10 2195 457 4 4 High Medium 4 78 91 78 85 43 82 248
158023 L. Messi Argentina 33 93 93 FC Barcelona Fixed Terms 2004 2021 170 72 Left 93 RW 38169 1/0/1900 103500000 560000 138400000 429 85 95 70 91 88 470 96 93 94 91 96 451 91 80 91 94 95 389 86 68 72 69 94 347 44 40 93 95 75 96 91 32 35 24 54 6 11 15 14 8 2231 466 4 4 Medium Low 5 85 92 91 95 38 65 771
20801 Cristiano Ronaldo Portugal 35 92 92 Juventus Fixed Terms 2018 2022 187 83 Right 92 ST 43291 1/0/1900 63000000 220000 75900000 437 84 95 90 82 86 414 88 81 76 77 92 431 87 91 87 95 71 444 94 95 84 78 93 353 63 29 95 82 84 95 84 28 32 24 58 7 11 15 14 11 2221 464 4 5 High Low 5 89 93 81 89 35 77 562
ID Position
231747 ST
231747 RW
231747 LW
212831 GK
209331 RW
200389 GK
192985 CM
192985 CAM
192448 GK
190871 CAM
190871 LW
188545 ST
158023 ST
158023 RW
158023 CF
20801 ST
20801 LW
mehdi.rezagholipour@gmail.com
(+44) 07466683765
Birmingham, UK