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! To consult the solution, head over to the file browser and select notebook-solution.ipynb.
🏃 Load in the Olympics Data
The primary data is available in your directory in the path athlete_events.csv.
The cell below handles imports of the required packages and data.
# Import libraries
import pandas as pd
import plotly.express as px
# Import the data
olympics = pd.read_csv("athlete_events.csv")
# Preview the DataFrame
olympicsWe can inspect the data types and the number of non-null rows per column using the .info() method.
# Inspect the DataFrame
olympics.info()# Check missing values
olympics.isna().sum()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 the data, 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
olympics["team"].value_counts().to_frame()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
olympics["team_clean"] = olympics["team"].str.split("[/-]").str[0]
# Preview the new column
olympics["team_clean"].unique()🌎 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.
SELECT
name AS country,
year,
population
FROM countries
INNER JOIN country_stats USING(country_id)SELECT * FROM nation.country_statsSELECT * FROM nation.country_stats