In this project, we will take data from a Wikipedia page on the men’s footballers with 500+ goals scored in their career. We will import the data in two ways: using read_html() from Pandas and from a csv. Wikipedia is prone to change, so this book includes a dataset that is stagnant. This way, your data will track with the data from the time of writing. Nevertheless, we will still go over ways of reading in tabular data from a website because Pandas makes it so easy without having to manually scrape the data and learn html syntax to do so.
After importing the data, we will use the random module to reproduce a stat line for every game played by each player in the dataset, using the average goals per game statistic provided on the Wikipedia page. Reproducing a stat line is a common task in sports analytics, as it allows us to simulate player performance and analyze various scenarios.
Reading the Data
For this project, we will need two modules: pandas and random. Let’s go ahead and import those two. We can also import them in one line separated by a comma below to save space. To read the data, Pandas makes this really simple. We can pass the URL—saved in a variable url—into the read_html function that Pandas provides for us. It works the same way as the read_csv function that we used last chapter and that we will use again. read_html will go to the website, read any tabular data that exists there, and return it as a DataFrame. If there are multiple tables on the website (as there are on ours), it will return a list of DataFrames.
Wikipedia has begun to block requests it doesn’t recognize as operating from a browser. We will get around this by passing a User-Aget header to the request. We will do this by creating a dictionary with User-Agent header, then passing that header into the request using the requests library. The code below will bypass Wikipedia restrictions and allow us to read the tables. Typically, we can read tables form the internet directly into Pandas, using pd.read_html(url), but in this case, we need to use the requests library to get around Wikipedia’s restrictions. Then we have to convert the HTML content into a string buffer using StringIO so that pd.read_html can read it.
Recognize, you might need to run uv add requests in your terminal to install the requests library if you haven’t already.
import pandas as pd, randomimport requestsfrom io import StringIOurl ="https://en.wikipedia.org/wiki/List_of_footballers_with_500_or_more_goals"headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36"}response = requests.get(url, headers=headers)html_content = response.texthtml_string = StringIO(html_content)df_list = pd.read_html(html_string)for df in df_list:print(df.head())
0 1
0 NaN This section needs more citations. Please help...
Rank Player Club Country and other Total \
Rank Player League Cup Continental Country and other Total
0 1 Cristiano Ronaldo 603[a] 57 173 146 979[65]
1 2 Lionel Messi* 574[b] 71 161 126 932[68]
2 3 Pelé* 604[c] 49 26 83 762
3 4 Romário 545[d] 93 54 64 756
4 5 Ferenc Puskás 516[e] 69 56 84 725
Career span
Career span
0 2002–present
1 2004–present
2 1957–1977
3 1985–2007
4 1943–1966
0 1
0 NaN This section may present fringe theories, with...
Rank Player Goals Matches Ratio Career span
0 1 Cristiano Ronaldo 994 1366 0.73 2002–present
1 2 Erwin Helmchen 989+ 582 1.70 1924–1951
2 3 Josef Bican 950+ 624 1.52 1930–1957
3 4 Lionel Messi 948 1220 0.78 2003–present
4 5 Ronnie Rooke 940+ 1035 0.91 1929–1961
I have looped over the list and printed the head of each DataFrame to save space, but you could simply print the DataFrame and see there are multiple. We can now choose one of these (we want the third table on the page), using index notation: df_list[2].
I will use the DataFrame created from the csv for the rest of this project because it provides a static dataset that will not change over time as Wikipedia pages do.
Clean Data
First, let’s look at the columns of the DataFrame and see if there is any cleaning we need to do. For instance, we need to find out what data types each of the fields are and if we have any missing values.
The non-null count for each column tells us if we have any missing values. We can see that all columns have 79 non-null counts, which means that all columns have the same number of non-missing values. This is a good sign, as it indicates that there are no missing values in the dataset. Secondly, DType tells us the data type of the value in each column. We can see that player, goals, matches, and career-span are all object data types, which means that they are not numeric. objects are how Pandas represents non-numeric data, such as strings. We will need to convert goals and matches to numeric data types so that we can perform calculations on them. But we will also have to clean them of any non-numeric characters first.
We can transform strings with the series.str accessor. This accessor allows us to apply string methods to each element in a Series. We can use the replace method to remove any non-numeric characters from the goals and matches columns. We will use a regular expression to match any character that is not a digit or a decimal point and replace it with an empty string. Then we can convert the cleaned strings to numeric data types using the pd.to_numeric function. Remember, we cannot use the int() function because it works for single data points, but these are series of data points.
Now we want to get the year in which players started their career to generate their career. We can do this by splitting the Career span column on the hyphen and taking the first element of the resulting list. We can use the str.split() method to split the string and the str[0] accessor to take the first element. Then we can convert the cleaned strings to numeric data types using the pd.to_numeric function.
Now we can see that Goals, Matches, and Career span are all int64 data types, which means that they are numeric. We can now perform calculations on these columns.
Finally, let’s rename the columns to make them easier to work with. We can use the rename method to rename the columns. And then let’s sort the values by Career span in ascending order so that we can see the players who started their careers the earliest. We can use the sort_values method to sort the DataFrame by a specific column.
I also changed all the names of the columns to lowercase to make them easier to type.
Reproduce Game Data
Right now our data is summarized at the career level. We have a total number of goals and a total number of matches played for each player. This means we can reproduce a stat line for every game played by each player in the dataset, using the average goals per game statistic provided on the Wikipedia page.
To do this, we will iterate over each row in the DataFrame using the df.itertuples() method. This method returns an iterator that yields named tuples for each row in the DataFrame. We can then access the values in each row using dot notation. We will need to create a game_data list to store the reproduced stat lines for each game played by each player.
import randomfrom math import floorgame_data = []columns = ["player", "shots", "goals"]sigma =1# standard deviationfor row in df.itertuples(): player = row.player mu = row.goals / row.matches # average goals per gamefor match inrange(row.matches): goals = random.normalvariate(mu=mu, sigma=sigma) goals = floor(goals) if goals >0else0 shots = random.randint(goals, goals + random.randint(0, 5)) game_data.append([player, shots, goals])game_df = pd.DataFrame(game_data, columns=columns)print(game_df.shape)game_df.head()
(56860, 3)
player
shots
goals
0
Steve Bloomer
0
0
1
Steve Bloomer
3
2
2
Steve Bloomer
0
0
3
Steve Bloomer
0
0
4
Steve Bloomer
1
0
In the code above, we first import the random module to generate random numbers and the floor function from the math module to round down the number of goals to the nearest integer. We then create an empty list called game_data to store the reproduced stat lines for each game played by each player. We also define a list of column names for the new DataFrame that we will create later.
We then iterate over each row in the original DataFrame using the df.itertuples() method. For each player, we calculate their average goals per game by dividing their total goals by their total matches. We then simulate each match they played by generating a random number of goals scored using a normal distribution with the calculated average as the mean and a standard deviation of 1. We use the floor function to round down the number of goals to the nearest integer and ensure that it is not negative. We also generate a random number of shots taken in the match, which is at least equal to the number of goals scored and can be up to 5 more than that. Finally, we append a list containing the player’s name, shots taken, and goals scored to the game_data list. After iterating over all players and their matches, we create a new DataFrame called game_df from the game_data list, using the defined column names. We then print the shape of the new DataFrame to see how many rows and columns it has, and display the first few rows using the head() method.
The resulting game_df DataFrame contains a row for each game played by each player, with columns for the player’s name, shots taken, and goals scored. This allows us to analyze player performance at a more granular level than the original career-level data.
Now let’s create a column for assists that is inversely related to goals. We will do this by applying a lambda function to the goals column.
game_df["assists"] = game_df["goals"].apply(lambda x: random.randint(0, 4- x) if x <4else0)game_df.head()
player
shots
goals
assists
0
Steve Bloomer
0
0
3
1
Steve Bloomer
3
2
0
2
Steve Bloomer
0
0
4
3
Steve Bloomer
0
0
1
4
Steve Bloomer
1
0
2
In the code above, we create a new column called assists in the game_df DataFrame. We use the apply method to apply a lambda function to each value in the goals column. The lambda function takes a single argument x, which represents the number of goals scored in a game. The function generates a random integer between 0 and 4 - x if x is less than 4, and returns 0 if x is 4 or greater. This ensures that the total number of goals and assists in a game does not exceed 4, which is unlikely.