Olympic Data Analysis & Visualization

A relational database and data visualization project analyzing historical Olympic Games data. This repository sets up a normalized PostgreSQL database to manage Olympic host cities, athlete profiles, and medal records, coupled with Python scripts that query the database and generate visual insights on Olympic history.

Project Overview

• Database Design & ETL: Cleans and loads raw CSV datasets into a relational PostgreSQL database using staging tables and normalized relational tables (hosts, athletes, medals).
• Relational Querying: Analyzes over 21,000+ medal events and 75,000+ athletes to extract historical trends across countries, years, seasons, and event categories.
• Visual Insights: Leverages Python (psycopg2 + matplotlib) to interface directly with the database and produce visual reports.

Visualizations & Insights

1. Top 10 Countries by Total Medals

Visualizes the all-time leading nations in total Olympic medal counts, led by the United States (USA), Soviet Union (URS), and Germany (GER).

2. Total Medals Awarded by Year (Summer vs. Winter)

Tracks the growth in medals awarded across the modern Olympic era, illustrating both the expansion of events over time and the comparative scale of the Summer vs. Winter Games.

3. Medals by Event Gender

Breaks down the distribution of awarded medals across event categories: Men, Women, Open, and Mixed.

🗄️ Database Schema

The database is built on three core tables linked via relational keys:

• hosts:
  • game_slug (VARCHAR PRIMARY KEY)
  • game_name, game_season, game_year, game_location
• athletes:
  • athlete_url (TEXT PRIMARY KEY)
  • athlete_full_name, games_participations, first_game, athlete_year_birth
• medals:
  • medal_id (SERIAL PRIMARY KEY)
  • slug_game (FOREIGN KEY references hosts.game_slug)
  • discipline_title, event_title, event_gender, medal_type
  • athlete_full_name, country_name, country_3_letter_code

🛠️ Tech Stack & Prerequisites

• Database: PostgreSQL
• Language: Python
• Libraries: psycopg2, matplotlib
• Dataset: Kaggle Olympic Dataset (olympic_hosts.csv, olympic_athletes.csv, olympic_medals.csv)

🚀 Setup & Execution

1. Clone the Repository

git clone https://github.com/dagr04/visualizing_olympic_data.git
cd visualizing_olympic_data

2. Install Python Dependencies

pip install psycopg2-binary matplotlib

3. Initialize Database & Load Data

Run the SQL schema creation script inside PostgreSQL (ensure the CSV paths match your local data directory):

psql -d <your_database_name> -f creates_db.sql

4. Run Analytical Queries (Optional)

psql -d <your_database_name> -f DevAgrawal_proj2.sql

5. Generate Visualizations

Update the database connection details in visualizations.py if necessary, then run:

python visualizations.py

This will query your PostgreSQL database and save the resulting plots (.png) to your project directory.

---

👤 Author

• Dev Agrawal
• GitHub: @dagr04