recipes/prep_medal_tokyo.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Gold Medal</th>\n",
       "      <th>Silver Medal</th>\n",
       "      <th>Bronze Medal</th>\n",
       "      <th>Total</th>\n",
       "      <th>Rank By Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>39</td>\n",
       "      <td>41</td>\n",
       "      <td>33</td>\n",
       "      <td>113</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>People's Republic of China</td>\n",
       "      <td>38</td>\n",
       "      <td>32</td>\n",
       "      <td>18</td>\n",
       "      <td>88</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Japan</td>\n",
       "      <td>27</td>\n",
       "      <td>14</td>\n",
       "      <td>17</td>\n",
       "      <td>58</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Great Britain</td>\n",
       "      <td>22</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "      <td>65</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ROC</td>\n",
       "      <td>20</td>\n",
       "      <td>28</td>\n",
       "      <td>23</td>\n",
       "      <td>71</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      Country  Gold Medal  Silver Medal  Bronze Medal  Total  \\\n",
       "0    United States of America          39            41            33    113   \n",
       "1  People's Republic of China          38            32            18     88   \n",
       "2                       Japan          27            14            17     58   \n",
       "3               Great Britain          22            21            22     65   \n",
       "4                         ROC          20            28            23     71   \n",
       "\n",
       "   Rank By Total  \n",
       "0              1  \n",
       "1              2  \n",
       "2              5  \n",
       "3              4  \n",
       "4              3  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "medals = pd.read_csv('../extras/medals_tokyo_raw.csv')\n",
    "medals.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>gold</th>\n",
       "      <th>silver</th>\n",
       "      <th>bronze</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>39</td>\n",
       "      <td>41</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>People's Republic of China</td>\n",
       "      <td>38</td>\n",
       "      <td>32</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Japan</td>\n",
       "      <td>27</td>\n",
       "      <td>14</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Great Britain</td>\n",
       "      <td>22</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ROC</td>\n",
       "      <td>20</td>\n",
       "      <td>28</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Ghana</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Grenada</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Kuwait</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Republic of Moldova</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Syrian Arab Republic</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>93 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       country  gold  silver  bronze\n",
       "0     United States of America    39      41      33\n",
       "1   People's Republic of China    38      32      18\n",
       "2                        Japan    27      14      17\n",
       "3                Great Britain    22      21      22\n",
       "4                          ROC    20      28      23\n",
       "..                         ...   ...     ...     ...\n",
       "88                       Ghana     0       0       1\n",
       "89                     Grenada     0       0       1\n",
       "90                      Kuwait     0       0       1\n",
       "91         Republic of Moldova     0       0       1\n",
       "92        Syrian Arab Republic     0       0       1\n",
       "\n",
       "[93 rows x 4 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "medals_recode = {\n",
    "    'Country': 'country',\n",
    "    'Gold Medal': 'gold',\n",
    "    'Silver Medal': 'silver',\n",
    "    'Bronze Medal': 'bronze'\n",
    "}\n",
    "medals = medals[list(medals_recode.keys())]\n",
    "medals.rename(columns=medals_recode, inplace=True)\n",
    "medals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Appending 3-letter ISO code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "ioc_countries = pd.read_csv('../extras/ioc_countries.csv').set_index('country')\n",
    "ioc_countries = ioc_countries.to_dict()\n",
    "country_recode = ioc_countries.get('code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals['code'] = medals.country.replace(country_recode)\n",
    "\n",
    "assert medals.code.isna().sum() == 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Output prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>code</th>\n",
       "      <th>country</th>\n",
       "      <th>gold</th>\n",
       "      <th>silver</th>\n",
       "      <th>bronze</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USA</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>39</td>\n",
       "      <td>41</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CHN</td>\n",
       "      <td>People's Republic of China</td>\n",
       "      <td>38</td>\n",
       "      <td>32</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>JPN</td>\n",
       "      <td>Japan</td>\n",
       "      <td>27</td>\n",
       "      <td>14</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GBR</td>\n",
       "      <td>Great Britain</td>\n",
       "      <td>22</td>\n",
       "      <td>21</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ROC</td>\n",
       "      <td>ROC</td>\n",
       "      <td>20</td>\n",
       "      <td>28</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  code                     country  gold  silver  bronze\n",
       "0  USA    United States of America    39      41      33\n",
       "1  CHN  People's Republic of China    38      32      18\n",
       "2  JPN                       Japan    27      14      17\n",
       "3  GBR               Great Britain    22      21      22\n",
       "4  ROC                         ROC    20      28      23"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "medals = medals[['code'] + list(medals_recode.values())]\n",
    "medals.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals.to_csv(\"../datasets/medal_countries_tokyo.wide.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals_long = (\n",
    "    medals\n",
    "    .melt(\n",
    "        id_vars=['code', 'country'],\n",
    "        var_name = 'medal',\n",
    "        value_name = 'quantity'\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "medals_long.to_csv(\"../datasets/medal_countries_tokyo.long.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}