Analyzing Olympics Data with SQL and Python
Welcome to your webinar workspace! Here, you can follow along as we load data from multiple sources and then answer some questions about the Olympics!
🏃 Load in the Olympics Data
The primary data is available in your directory in the path
The cell handles imports of the required packages and data.
# Import libraries # Import the data # Preview the DataFrame
# Inspect the DataFrame
# Check missing values
The missing values in the
medal column are because the dataset contains all competitors (not just those who won a medal). The remaining columns with missing values are not of interest to us today.
When exploring it, it looked as though some of the teams had hyphens and backslashes. Let's inspect it more closely by inspecting the unique values of the column.
# Inspect the team column
team column is messy and sometimes contains countries separated by forward slashes or hyphens. Let's clean this by using
.str.extract() to extract the first country mentioned in the cases of slashes or hyphens (e.g., "Denmark/Sweden" becomes "Denmark").
If you want to learn more about regular expressions in Python, check out our course on the subject!
# Split the team column on forward slashes and hyphens # Preview the new column
🌎 Bring in additional data
Let's query a MariaDB database containing information on world nations to provide some additional data.
We will store our query result as a pandas DataFrame named
We now have country data that we can join with the Olympics data! We will use the
.merge() method to combine the two DataFrames using the country and year columns.
A "left" join matches on rows in the olympics_data DataFrame, as some teams will not be present in the
# Perform a left join between the two DataFrames # Preview our data
🏆 Which countries have the most gold medals?
Let's start by calculating and visualizing the number of gold medals won by athletes from different countries. To do this, we can use the
.query() method to filter for rows where the
medal is "Gold" and then use .group_by() to aggregate by our
team_clean variable. We then use
.count() to count the number of rows per team.
A second line sorts the values of our query so that we see the top teams first!
# Count the number of gold medals earned by a country # Sort the values # Preview our count
# Create choropleth map of gold medal counts
📈 How has the number of sports grown over time?
Another question we can ask ourselves is whether the number of individual sports has increased over time.
We will do this by grouping by the year and season (i.e., summer or winter) and then counting the number of unique using .nunique().
# Group by year and season and count the number of unique values # Preview the DataFrame
# Create a line plot for Summer and Winter Olympics