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 athlete_events.csv.
The cell handles imports of the required packages and data.
# Import libraries
# Import the data
# Preview the DataFrame
We can inspect the data types and the number of non-null rows per column using the .info() method.
# 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.
By using .value_counts() combined with .to_frame(), we can inspect the unique team names by frequency inside the interactive table viewer.
# Inspect the team column
The 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 nations_data.
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 countries_data DataFrame.
# 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
We can visualize this using Plotly. Let's create a choropleth map (i.e., a world map), where the country's color is based upon the medal 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
Let's plot this data using a line plot, broken up by season!
# Create a line plot for Summer and Winter Olympics