Clean ADA Race Results.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Clean and Prep ADA Race Results**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Run on Python 3.12 | No errors | No warnings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import packages\n",
    "\n",
    "# For data manipulation\n",
    "import pandas as pd\n",
    "\n",
    "# for displaying and modifying the working directory\n",
    "import os as os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define custom NA values, excluding 'NA' to make sure that Country Code NA (Namibia) is not interpreted as missing data\n",
    "custom_na_values = ['N/A', 'NaN', 'null', '']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Country</th>\n",
       "      <th>Time</th>\n",
       "      <th>Age Group</th>\n",
       "      <th>Enrollment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Youssouf Mahamat Allamine Tahir</td>\n",
       "      <td>TD</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>30 - 34</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jhan Carlos</td>\n",
       "      <td>CO</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>20 - 24</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Nyasha Dzivai</td>\n",
       "      <td>ZA</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>35 - 39</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Chadwick Pridgen</td>\n",
       "      <td>US</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>40 - 44</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Neftali Pérez</td>\n",
       "      <td>VE</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>50 - 54</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Name Country      Time Age Group  Enrollment\n",
       "0  Youssouf Mahamat Allamine Tahir      TD  00:14:45   30 - 34  Run / Walk\n",
       "1                      Jhan Carlos      CO  00:14:45   20 - 24  Run / Walk\n",
       "2                    Nyasha Dzivai      ZA  00:14:45   35 - 39  Run / Walk\n",
       "3                 Chadwick Pridgen      US  00:14:45   40 - 44  Run / Walk\n",
       "4                    Neftali Pérez      VE  00:14:45   50 - 54  Run / Walk"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Tell python where to find the dataset and load it to dataframe df0\n",
    "os.chdir(r'D:\\OneDrive\\Documents\\Python\\Current\\Race Results') # absolute path, using \\ and r prefix\n",
    "\n",
    "#Load specific columns, ignore the default NA values and use the custom ones\n",
    "df0 = pd.read_csv(\"ADA_race_results.csv\", usecols=['Name', 'Country', 'Time', 'Age Group', 'Enrollment'], keep_default_na=False, na_values=custom_na_values)\n",
    "\n",
    "# Display the first 5 rows of the dataframe\n",
    "df0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5743 entries, 0 to 5742\n",
      "Data columns (total 5 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Name        5743 non-null   object\n",
      " 1   Country     5743 non-null   object\n",
      " 2   Time        5743 non-null   object\n",
      " 3   Age Group   5743 non-null   object\n",
      " 4   Enrollment  5743 non-null   object\n",
      "dtypes: object(5)\n",
      "memory usage: 224.5+ KB\n"
     ]
    }
   ],
   "source": [
    "# Display basic information about the data \n",
    "df0.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name          0\n",
       "Country       0\n",
       "Time          0\n",
       "Age Group     0\n",
       "Enrollment    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Display the number of missing values in each column\n",
    "df0.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "358"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count the number of duplicate rows\n",
    "df0.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop duplicate rows\n",
    "df0 = df0.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 5385 entries, 0 to 5741\n",
      "Data columns (total 5 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Name        5385 non-null   object\n",
      " 1   Country     5385 non-null   object\n",
      " 2   Time        5385 non-null   object\n",
      " 3   Age Group   5385 non-null   object\n",
      " 4   Enrollment  5385 non-null   object\n",
      "dtypes: object(5)\n",
      "memory usage: 252.4+ KB\n"
     ]
    }
   ],
   "source": [
    "# Verify that the duplicates have been removed\n",
    "df0.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the Name column\n",
    "df0 = df0.drop(columns=['Name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add a column for the year\n",
    "df0['Year'] = 2024"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add a column for the race name\n",
    "df0['Race'] = 'ADA'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Time</th>\n",
       "      <th>Age Group</th>\n",
       "      <th>Enrollment</th>\n",
       "      <th>Year</th>\n",
       "      <th>Race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TD</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>30 - 34</td>\n",
       "      <td>Run / Walk</td>\n",
       "      <td>2024</td>\n",
       "      <td>ADA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CO</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>20 - 24</td>\n",
       "      <td>Run / Walk</td>\n",
       "      <td>2024</td>\n",
       "      <td>ADA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ZA</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>35 - 39</td>\n",
       "      <td>Run / Walk</td>\n",
       "      <td>2024</td>\n",
       "      <td>ADA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>40 - 44</td>\n",
       "      <td>Run / Walk</td>\n",
       "      <td>2024</td>\n",
       "      <td>ADA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>VE</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>50 - 54</td>\n",
       "      <td>Run / Walk</td>\n",
       "      <td>2024</td>\n",
       "      <td>ADA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country      Time Age Group  Enrollment  Year Race\n",
       "0      TD  00:14:45   30 - 34  Run / Walk  2024  ADA\n",
       "1      CO  00:14:45   20 - 24  Run / Walk  2024  ADA\n",
       "2      ZA  00:14:45   35 - 39  Run / Walk  2024  ADA\n",
       "3      US  00:14:45   40 - 44  Run / Walk  2024  ADA\n",
       "4      VE  00:14:45   50 - 54  Run / Walk  2024  ADA"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verify the changes\n",
    "df0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename Country to Country Code\n",
    "df0 = df0.rename(columns={'Country': 'Country Code'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change the column order\n",
    "df0 = df0[['Race', 'Year', 'Country Code', 'Age Group', 'Time', 'Enrollment']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Race</th>\n",
       "      <th>Year</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Age Group</th>\n",
       "      <th>Time</th>\n",
       "      <th>Enrollment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ADA</td>\n",
       "      <td>2024</td>\n",
       "      <td>TD</td>\n",
       "      <td>30 - 34</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADA</td>\n",
       "      <td>2024</td>\n",
       "      <td>CO</td>\n",
       "      <td>20 - 24</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADA</td>\n",
       "      <td>2024</td>\n",
       "      <td>ZA</td>\n",
       "      <td>35 - 39</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ADA</td>\n",
       "      <td>2024</td>\n",
       "      <td>US</td>\n",
       "      <td>40 - 44</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ADA</td>\n",
       "      <td>2024</td>\n",
       "      <td>VE</td>\n",
       "      <td>50 - 54</td>\n",
       "      <td>00:14:45</td>\n",
       "      <td>Run / Walk</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Race  Year Country Code Age Group      Time  Enrollment\n",
       "0  ADA  2024           TD   30 - 34  00:14:45  Run / Walk\n",
       "1  ADA  2024           CO   20 - 24  00:14:45  Run / Walk\n",
       "2  ADA  2024           ZA   35 - 39  00:14:45  Run / Walk\n",
       "3  ADA  2024           US   40 - 44  00:14:45  Run / Walk\n",
       "4  ADA  2024           VE   50 - 54  00:14:45  Run / Walk"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verify the changes\n",
    "df0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the cleaned data to a new csv file\n",
    "df0.to_csv('ADA Race Data for Visualization.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}