Analysis of 120 Years of Olympics Data

Esther Njihia — Sun, 22 Jan 2023 06:12:18 +0000

Overview

The Olympic Games are one of the most prestigious and widely-followed sporting events in the world. With a history dating back over a century, the Olympics have seen countless athletes from all corners of the globe compete for glory and honor. In this project, we will use Python, SQL and Tableau to analyze 120 years of Olympics data sourced from Kaggle.

Data Collection

The data for this analysis was sourced from Kaggle and includes information on athletes, teams, events, and medals. The data was collected for all Summer and Winter Olympics from 1896 to 2016.
Data source: Kaggle

Data Cleaning and Preparation

Before we could begin our analysis, the data needed to be cleaned and prepared for use. This involved removing any duplicate or irrelevant data, correcting any errors or inconsistencies, and formatting the data in a way that would be compatible with our analysis tools.

Data Cleaning with Python

Load data

df = pd.read_csv("data/athlete_events.csv")

Calculate the number of missing values in the data

df.isnull().sum()

We got some missing values with the age, height and weight variables. How about we fix that.
Fill in the age missing values with its mean.

df['Age'].fillna(df['Age'].mean(), inplace= True)

Repeat the same for the height and weight variables.

SQL Analysis

Once the data was cleaned and prepared, we used SQL to extract and analyze the data. We were able to answer questions such as:
1.What is the Gender distribution of athletes across different sports and event?

SELECT Sport, Event, Sex, COUNT(Name) as athlete_count
FROM Athlete
GROUP BY Sport, Event, Sex
ORDER BY athlete_count DESC;

2.Which city and season have the most successful athletes?

SELECT City, Season, COUNT(Medal) as Medal_count
FROM Athlete
WHERE Medal IS NOT NULL
GROUP BY City, Season
ORDER BY Medal_count DESC

3.Which athletes are the most successful in terms of number of medals?

SELECT Name, COUNT(Medal) as medal_count
FROM Athlete
GROUP BY Name
ORDER BY medal_count DESC;

4.Trend analysis to identify any patterns in the number of medals over time
By year

SELECT Year, COUNT(Medal) as medal_count
FROM Athlete
GROUP BY Year
ORDER BY Year ;

5.Use statistical analysis to find factors associated with winning more medals.

first create a sub query to find the total count of medals per city and season

WITH city_season_medals AS (
    SELECT City, Season, COUNT(Medal) AS medal_count
    FROM Athlete
    GROUP BY City, Season
)

use statistical methods to find the relationship between city & season and the total number of medals. Fo this instance I used z-score. The higher the z-score the higher the correlation.

SELECT City, Season, medal_count, (medal_count - AVG(medal_count) OVER()) / STDEV(medal_count) OVER() AS z_score
FROM city_season_medals
ORDER BY z_score DESC

These are some of the questions that were answered during the SQL analysis.

Visualization with Tableau

To make the data more accessible and understandable, Tableau was used to create visualizations of our findings. Some of the visualizations we created include:

A pie chart showing the gender distribution across all sports
A bar chart showing the most successful athletes over time.
line charts to show trends and patterns in the number of medals won over time

View the whole Olympics dashboard:Olympics Dashboard

Conclusion

Through this analysis, I was able to gain a deeper understanding of the history of the Olympics and the performances of countries and athletes over the past 120 years. By using SQL and Tableau, I was able to extract valuable insights from the data and present them in a clear and accessible way.

Code and Data

The code and data used for this project is available at Github

By using the above techniques, we can also use the data to predict future results, and also to identify patterns and trends that can be used to improve performance and achieve success in future Olympic games.

Data engineering 102: Introduction to python for data engineering.

Esther Njihia — Sun, 04 Sep 2022 20:39:09 +0000

Overview

The most interesting thing about python language is sort of all rounded. Due to its readability which is easy and the fact that it has very many applications including data engineering. In this post, we will discuss the most important concepts in the python language.

Python Syntax

Python, builds the code structure using whitespace and indentation.

Comments

The comments are just as crucial as the code itself, because they explain why a piece of code was produced
The comments are ignored by the Python interpreter when it runs the code.
A single line comment in Python starts with the hash symbol (#), which is followed by the comment.

Identifiers

Python uses identifiers, or names, to designate variables, functions, modules, classes, and other types of objects.
An identifier's name must begin with a letter or an underscore. After that, only underscores or alphanumeric characters are allowed.

Keywords

Keywords are words that have special meaning in python. Python has a special module keyword that outlines all the keywords that are used in the Python Language.

Control statements

If statements.

If statements are conditional statements. Below is the summary of the different if statements:
When you want to run a code block based on a condition, use the if statement.
If you want to execute a different code block if the condition is not True, use the if...else statement.
When you wish to check multiple conditions and execute the associated code block after the condition that evaluates to True, use the if...elif...else statement.

for loop

A code block can be performed a predetermined number of times by using the for loop statement.
To alter the loop, use the range(start, stop, step).

while loop.

If a condition is True, use the Python while loop statement to run a code block.

Functions

A named, reusable chunk of code called a function in Python executes a command or returns a value.
Create a new function by using the def keyword. The function definition and body make up a function.
A function may have 0 parameters or more. You must pass a function the same amount of arguments if it has one or more parameters.
A function can do a task or give back a value. If you want to return a value from a function, use the return statement.

Lists

An orderly group of elements is referred to as a list.
If you want to retrieve a list element by its index, use the square bracket notation []. The index of the first element is zero.
To reach a list element from the list's end, use a negative index. The final component has an index of -1.
To change an entry from a list, use list[index] = new value.
To add a new element to the end of a list, use the append() method.
To insert a new element at a certain location in a list, use insert().
To remove an entry from a list and return it, use the pop() function.
To eliminate an element from a list, use remove().
Tuples are immutable lists.
Use tuples when you want to define a list that cannot change.

Dictionary

A Python dictionary is a group of key-value pairs, each of which has a corresponding value.
In order to access a value by its key, use square brackets or the get() method.
To remove a key-value pair by the key from a dictionary, use the del statement.
To iterate through the keys, values, and key-value pairs in a dictionary, use a for loop.

This post includes a summarization of some most important concepts as a beginner in Python.

DEV Community: Esther Njihia

Analysis of 120 Years of Olympics Data

Overview

Data Collection

Data Cleaning and Preparation

Data Cleaning with Python

SQL Analysis

Visualization with Tableau

Conclusion

Code and Data

Data engineering 102: Introduction to python for data engineering.

Overview

Python Syntax

Comments

Identifiers

Keywords

Control statements

If statements.

for loop

while loop.

Functions

Lists

Dictionary