FIFA player analysis
Tools used: Python, Db-diagram, PostgreSQL, Tableau, Excel.
Like most football fans, I spend a significant portion of my free time playing FIFA, particularly enjoying the career mode. This hobby sparked an intriguing thought: why not collect data and analyse how my favorite team, Arsenal, stacks up against some of the heavyweights in the sport? I've structured this entire project into three distinct parts:
Part 1: Database Creation and Exploratory Data Analysis.
Part 2: In-depth analysis of the Arsenals' prospects and potential.
Part 3: Developing a Mixed Integer Optimisation program to acquire players in the Transfer Market Within a Set Budget and Specific Requirements.
Database Creation:
I play FIFA 21 and chose a dataset with player information for this version from Kaggle and created a database with separate tables to store the data for efficient analysis and management.
In the interest of time efficiency and to better visualize my database structure, I used dbdiagram.io—a free online tool for drawing Entity-Relationship Diagrams through coding. It's designed specifically for developers and data analysts.
Here is the output of my database:
Once the database schema was ready, I exported it to PostgreSQL using psql in the command prompt. The command I used for this operation was as follows:
Exploratory data analysis:
Before delving into the specific analysis of my team, it was crucial to explore the dataset comprehensively and understand the variations in player stats and other dynamics. To illustrate my findings, I will include the SQL queries used along with the corresponding visuals.
1) Who are the top 10 players based on their overall rating (ova) within specific positions?
To uncover this, I executed the following SQL query to retrieve the top 10 players. By altering the 'main_position' parameter in the query, I could extract the list for each position category. Below is an example showcasing the query for the position of Striker (ST):
2) Which players have the best defensive attributes (defending, marking, standing tacking) across the database?
The query illustrates that players like Virgil van Dijk from Liverpool and Giorgio Chiellini from Juventus are at the pinnacle of defensive prowess, bringing invaluable experience and skill to their teams.
The table not only showcases the names and teams of these top defenders but also their ages and specific defensive attributes, providing a clear picture of who leads the rankings in the defensive department.
3) Who are the players with the best attacking attributes (finishing, shot power, attacking) across the league under the age 27?
This query selects the players' names, ages, teams, and their respective shooting, shot power, and finishing scores. It filters out the players over the age of 27 to focus on younger talents and orders the results by the finishing attribute, which is a crucial skill for any attacker. The LIMIT 10 clause ensures we are only viewing the top ten players, making the data set manageable and focused.
Showcasing a list of formidable strikers and forwards who are already making their mark. Players such as Harry Kane from Tottenham Hotspur and Kylian Mbappé from Paris Saint-Germain feature prominently, indicating their lethal prowess in front of the goal.
As we can see from the table, these young players are not just the future; they are actively shaping the present, with attributes that place them at the top of the game. Their shot power and finishing skills make them invaluable assets to their respective clubs.
4) How does strength & stamina correlate with their overall performance?
A player's physicality, especially their strength and stamina, can significantly influence their performance on the pitch. To investigate this relationship, I executed a SQL query that categorizes players into three groups based on their strength and stamina scores: 'high strength & stamina', 'medium strength & stamina', and 'low strength & stamina'. The query then calculates the average overall performance rating for each category. Here’s the SQL code:
The query uses a CASE statement to categorize each player based on their attributes and then rounds the average overall rating for each group to the nearest whole number. The results are sorted in descending order to see which category, on average, ranks higher in terms of overall performance.
As depicted in the visual, players with 'high strength & stamina' have an average overall performance rating of 73, suggesting a strong positive correlation between physical attributes and player ratings. Meanwhile, those with 'low strength & stamina' show a lower average rating of 63, further reinforcing the importance of physical fitness in the game. This analysis can offer valuable insights for team building, indicating that a focus on physical training could enhance player performance.
5) Dribbling vs Height: Is There a Connection?
To explore whether a player's height impacts their dribbling proficiency, I pulled the relevant data using SQL and then applied a linear regression model in Excel.
After constructing a simple linear regression with height as the independent variable (X) and dribbling ability as the dependent variable (Y), the summary output from Excel revealed a noticeable yet slight inverse relationship between height and dribbling ability. An increase in height was associated with a marginal decrease in dribbling score. However, the R Square value was low, indicating that height alone is not a strong predictor of dribbling ability.
To further my analysis, I categorized player heights into bins of 10 cm each and created a histogram to observe the distribution of players' dribbling scores. This histogram showed that players within the height range of 174 to 183 cm exhibited higher dribbling scores.
This analysis suggests that while height may have a minor influence on a player's ability to dribble, it cannot solely account for the variability in dribbling skills. The histogram provides a visual confirmation, indicating that players of average height tend to have better dribbling scores, yet exceptions abound, highlighting the multifaceted nature of football skills.
6) Players who have a high release clause with a lower overall rating?
Another intriguing aspect of football analytics is the relationship between a player's release clause and their overall rating. It's particularly interesting to identify players who have high release clauses despite having lower overall ratings. Here's the SQL query I utilized to extract such players:
This query is designed to find players whose release clauses are in the top 25th percentile, yet their overall ratings are below the league average. The results are sorted by descending release clause and ascending overall rating to prioritize players with the highest release clauses and lowest ratings.
The findings from this query shed light on an interesting dynamic:
The list includes young players with release clauses significantly higher than what their current overall ratings might suggest. However, a crucial observation is that all these players are below 20 years old. This indicates that these young talents are seen as future stars, and their clubs are keen to secure their services by setting high release clauses. It's a strategic move that underlines the potential that these clubs see in their youthful prospects.
7) Wages and Release Clauses Comparison
In the realm of football, a player's financial value is often reflected in their wage and release clause. To gain deeper insight into how these financial aspects differ between younger and more experienced players, I conducted a comparative analysis. Here's the SQL query that facilitated this investigation:
领英推荐
This query determines the average wage and release clause for both young players (aged 21 and under) and senior players (over 30 years of age). The results, as illustrated in the visuals below, offer a stark contrast:
The bar graphs reveal that senior players command significantly higher wages and release clauses on average, which is not surprising given their wealth of experience and proven track records. However, it's worth noting that young players, while lower on the wage scale, have substantial release clauses. This indicates that clubs are investing in the potential of these players, safeguarding their assets with higher release clauses to secure their promising futures.
8) Height Distribution Across Different Positions
Physical characteristics can play a significant role in a football player's suitability for certain positions on the field. To illustrate this, I gathered data on player height across various positions using the following SQL query:
After obtaining the necessary data, I created a box plot to visualize the height distribution for each position:
The box plot highlights several key insights into the typical physical profiles for different playing roles:
In addition to the box plot, I also created a pivot table in Excel to summarize the maximum, minimum, and average heights for players in each position. This table provides a concise snapshot of the height metrics across positions:
The table reveals interesting statistics, such as:
This pivot table complements the earlier visualizations, providing concrete numbers to the visual patterns observed in the box plot and offering a clear indication of the physical attributes associated with each playing position.
Comparison between the top 5 in premier league
1) Average overall rating of the players in the famous teams in premier league
To evaluate the collective talent of the top clubs in the Premier League, I conducted an analysis of the average overall player ratings. The SQL query below was designed to calculate the average overall rating (ova) for players in five renowned teams: Arsenal, Manchester City, Manchester United, Chelsea, and Liverpool.
This query retrieves the average overall ratings for each team and orders them from highest to lowest.
As seen in the table, Chelsea and Manchester United are tied at the top with an average rating of 78, closely followed by Liverpool and Manchester City with 77, and Arsenal at 76. This marginal difference underscores the highly competitive nature of the Premier League, where a small edge in player quality can be pivotal. These figures are rounded to the nearest whole number for simplicity, reflecting the broader picture of team strength as perceived in the FIFA database.
2) What is the Average Player Rating in ST Positions for Each Team?
Strikers often carry the heavy responsibility of scoring goals and securing victories for their teams. It's fascinating to compare the average ratings of players in this pivotal position among the elite clubs. To do this, I executed a SQL query to calculate the average rating for strikers (ST) within Arsenal, Manchester City, Manchester United, Liverpool, and Chelsea:
The query isolates strikers from the specified teams and computes the average overall rating for each club's ST position. Here are the compelling results:
Liverpool and Manchester United lead the ranking with the highest average ratings for their strikers at 82, suggesting a strong offensive front. Chelsea follows closely with an 81, while Manchester City's average is 78. Arsenal's strikers lag slightly behind with an average rating of 75. These figures highlight the striking power each team possesses and could potentially correlate with their goal-scoring records and league success.
3) which team has the most home grown players (England)?
A homegrown player in the Premier League is more than just a footballer; they often represent the heart and soul of the club's local culture and identity. Thus, I was curious to find out which of the top English clubs have invested heavily in local talent. To answer this question, I constructed the following SQL query:
This query categorizes players as either 'homegrown' or 'foreign' based on their nationality and aggregates them by their teams. Here's what the analysis revealed:
Manchester United stands out with the highest number of homegrown players, boasting 13 English nationals in their squad. This reflects their long-standing tradition and commitment to nurturing local talent. In contrast, both Chelsea and Liverpool have a more diverse squad with a higher count of foreign players, yet still maintaining a significant presence of English talent with 9 homegrown players each.
These figures provide an interesting perspective on the recruitment strategies of the top English clubs, showing a blend of local and international talents that make up the fabric of these renowned teams.
4) Strength & Stamina Comparison Among Premier League Teams
In football, strength and stamina are crucial attributes that contribute to a team's overall dynamism and endurance on the pitch. Curious about how these attributes vary among the top Premier League teams, I devised an SQL query to analyze the average strength and stamina of players from Arsenal, Manchester City, Manchester United, Liverpool, and Chelsea:
This query calculates the average strength and stamina for each specified team. The results are as follows:
Manchester United and Liverpool lead the pack with the highest average stamina, both scoring 71, indicative of their high-intensity playing style. In terms of strength, Manchester United again stands out alongside Arsenal, both with an average of 70, suggesting a robust physical presence on the field.
5) Which Team Has the Fastest Players on Average?
Speed can be a game-changer in football, creating the decisive moments that lead to goals. With this in mind, I wanted to find out which of the Premier League's top teams boasts the fastest players on average. To do so, I used the following SQL query:
The query aggregates the average sprint speed of players from each team and orders the teams by this average. The results give us a clear indication of which team's players have the edge in pace:
The table reveals that Chelsea leads the pack with the highest average sprint speed, followed closely by Manchester United, Liverpool, and Manchester City. Arsenal trails slightly in this metric. This insight might influence tactical decisions, as teams with faster players could adopt strategies that leverage pace to break through defensive lines or launch rapid counter-attacks.
Conclusion:
Concluding our initial exploration of Premier League teams, we've gleaned that competition is fierce, with top teams closely matched in player ratings. Manchester United stands out for nurturing homegrown talent, while strength and stamina are universally prioritized, with United and Liverpool leading the pack. Chelsea boasts the fastest players, highlighting their strategic speed advantage.
The SQL code and Excel files will be uploaded to my github repository.
These findings set the groundwork for the next installment, where we'll zoom in on Arsenal, comparing their squad with other Premier League talents to pinpoint their competitive edge. Join me in Part 2 for an in-depth analysis of the Gunners' prospects and potential.