5 Minute Analysis

Olympics, Rising Competition and Equality

─

Dataset

Kaggle 120 years of Olympic history: athletes and results

This blog post explores and analyzes the data using PivotBillions, available freely on docker.

Docker Image

Overview

Exploring the Kaggle historical Olympic athletic data in real-time, filtering the data, and pivoting the data in various ways to understand the trends in participation by country and by sex over time.

Goals

1. Load the data to Pivot Billions and view its structure.
2. Explore the data and filter it by season using Pivot Billions’ built-in features.
3. Pivot the data to organize it by country and sex and understand the trend in gender equality in United States Olympic participation.
4. Pivot the data by country to see the trend in Olympic participation.
5. Reorganize the data by filtering it the opposite Olympic season and view the effect on our observed trends.

Steps

Load the Data and View its Structure

1. Download the dataset from Kaggle.
2. Unzip your downloaded data.
3. Access the Pivot Billions URL for your machine.
4. Click the Plus (+) icon on the top right hand side of the window.
5. Select Drag & Drop.

1. Drag your downloaded “athlete_events.csv” file to the Drag & Drop box in Pivot Billions.
2. Click the dropdown arrow to the right of the file in Pivot Billions to view the schema of the data and see a sample.
3. Then select the left checkbox next to the file and click Preview at the bottom of the screen.

You can now see the columns and types of the dataset and modify them as you see fit. You can also view or change which column or columns are set as primary keys. When you are done viewing or modifying the data structure to be imported, click Import.

Explore and Filter the Data

After the data has been quickly imported you can now see and access all 271,116 rows of the data.

By hovering over each column name you can sort the data by that column, view that column’s distribution over all of the data, filter by the data in that column, or rename that column. Let’s filter all of the data by season.

1. Click on the second-from-the-right icon (filter) in for the season column.
2. Select Equals from the dropdown and then enter “Summer” and press enter.

All of the data is immediately filtered to data regarding the Olympic Summer Season.

Pivot the Data to Explore U.S. Olympic Participation by Gender

Now that we’ve separated the seasons, let’s reorganize our data to dive into gender equality.

1. Click the Pivot icon in the the top right of your data table.
2. Click the Plus (+) icon under Dimensions and select the “Sex” column.
3. Click the Plus (+) icon again and select the “Team” column.
4. Click the Plus (+) icon one last time and select the “Games” column.
5. Click View to pivot your data.

Pivot Billions now quickly reorganizes your data by gender, country, and Olympic games for the summer season. It also provides counts of the occurrence of each unique combination of these attributes in the data. You can sort by a column or filter the data. Here we’ll add a filter to restrict the data to just that of the United States.

1. In the top-left of the pivot widget, click the Plus (+) button.
2. Select “Team” and “Equals”.
3. Enter “United States” and press enter.

You can see the filter immediately applied and the data reduced from 6,408 unique combinations to just the 52 unique combinations for the United States Team.

We’ll now interactively view the data.

1. Click the Switch View Type icon in the top right of the pivot widget and select Pivot View.
2. Drag the Team box to below the drop down selection box.
3. Drag the Sex box to below the drop down selection box.
4. Drag the Games box to the right of the drop down selection box as shown below.
5. Change the topleft drop down selection box from “Table” to “Bar Chart”.

It is pretty clear that there is a rising trend of female participation and equality in the US Olympic Team during the summer season. It is also worth noting that the 2016 Summer Games were the first games with higher female participation than male for the US Team.

Pivot the Data to Explore Olympic Participation by Country

Now that we’ve discovered rising equality for the US Team, let’s reorganize our data to take a look at each country’s participation in the Olympics.

6. Click the Pivot icon in the the top right of your data table.
7. Click the Plus (+) icon under Dimensions and select the “Team” column.
8. Click the Plus (+) icon one last time and select the “Games” column.
9. Click View to pivot your data.
10. Click the Switch View Type icon in the top right of the pivot widget and select Pivot View.
11. Drag the Games box to below the drop down selection box.
12. Drag the Team box to the right of the drop down selection box as shown below.
13. Change the topleft drop down selection box from “Table” to “Scatter Chart”.

Over the years there has definitely been a large increase in the countries participating in the Olympics, though in recent Summer Games the increase has been minor. We can easily view the total participants for each Team by hovering over any of these data points or we can switch the graph type to continue to interact with and explore the data.

Get a Fresh Look at the data by Winter Season

All of our analysis so far has been based off the summer season. Pivot Billions makes updating our analysis to reflect the winter season extremely easy.

1. In the main data table at the top of the page, click the filter icon for the “Season” column again.
2. Then click the pencil icon next to “Equals Summer” at the bottom of the pop-out box.
3. Then replace “Summer” with “Winter”

That’s it! All of our data, pivots, and analyses are automatically updated to reflect only the Olympic Winter Season.

By scrolling down on your page you can see the updated analyses:

Winter Season Gender Equality:

Winter Season Olympic Participation:

We can still see our trends in increased gender equality and olympic participation; however, the US Winter Team still has some room to improve to reach full gender equality.