Solution_with_concise_explanation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fb2b165c-54db-48a7-b4fe-c18637895f1c",
   "metadata": {},
   "source": [
    "# Comprehensive Project Challenge"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "616a725d-3609-4a08-bdcd-8d6609449831",
   "metadata": {},
   "source": [
    "# Summer Olympic Games, Medal Tables 1896-2012"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ce225cc-0041-4080-9e77-0953c28e136c",
   "metadata": {},
   "source": [
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28e78cae-3296-4f08-9f31-b929b10c9086",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## Welcome to the coding challenge!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e15dd58f-72c1-4f38-a31c-8ead493ecdb6",
   "metadata": {},
   "source": [
    "This challenge requires you to __apply__ and __combine__ many concepts and methods."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c1648bf-f70a-465a-8565-046cc5e4e173",
   "metadata": {},
   "source": [
    "It is frequently used in job application processes / assessment centers in Data Science to check the candidates´ abilities to work with, manipulate and aggregate data. Also experienced Data Scientists have difficulties to solve the problem. And the reason for this is not that the challenge is coding-wise extremely complex. But it requires a combination of <br>\n",
    "- __solid coding skills__, <br><br>\n",
    "and, even more important <br><br>\n",
    "   \n",
    "- the ability to __interpret__ and __understand__ the __underlying data__ and to __incorporate inputs__ from subject matter __experts__ (in this case Sports experts) <br> <br>\n",
    "__\"Thinking in Data Structures!\"__ -> requires some practise but also some talent. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2000a7e3-e877-4139-88d7-d9fde4b36e61",
   "metadata": {},
   "source": [
    "## What is the Goal of the Challenge?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21dbb46f-b48f-46df-9f57-aa41dbe6d3fa",
   "metadata": {},
   "source": [
    "It´s your first day in a Data Science advisory firm and your boss asks you to produce the __official Summer Olympic Games Medal Tables for all Editions from 1896 to 2012__. <br><br>\n",
    "All you can use is a dataset with raw data containing over 31,000 medals (__summer.csv__) and the official Medal Tables for the Editions 1996 and 1976 from Wikipedia. (__wik_1996.csv__, __wik_1976.csv__). Use the two official Medal Tables as a __reference__ to check whether your code produces the correct output! <br><br>\n",
    "Your goal is to __minimize the divergence__ between your aggregated Medal Tables and the official Medal Tables. Let´s assume that the official number of Gold Medals for the United States in the Edition 1996 is 44 and your code produces 46. This is an absolute divergence of 2. <br> <br>\n",
    "__Calculate the total absolute divergence for the Editions 1996 and 1976 (the \"Score\")!__ The __optimal Score is 0__! "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25066b2b-7f97-469d-93a1-054e0217f945",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "### Naive approach"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "f6a72145-e1c9-4573-8c03-a054e4c508ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "dfc5be26-4391-4d53-8252-113b5e1b3f35",
   "metadata": {},
   "outputs": [],
   "source": [
    "summer = pd.read_csv(\"summer.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "19755ff8-7ec9-4093-827b-159632ca4436",
   "metadata": {},
   "outputs": [],
   "source": [
    "summer76 = summer.loc[summer.Year == 1976]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6f1f6914-2d9b-4a53-a1a6-30ba2963accd",
   "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>Year</th>\n",
       "      <th>City</th>\n",
       "      <th>Sport</th>\n",
       "      <th>Discipline</th>\n",
       "      <th>Athlete</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Event</th>\n",
       "      <th>Medal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13900</th>\n",
       "      <td>1976</td>\n",
       "      <td>Montreal</td>\n",
       "      <td>Aquatics</td>\n",
       "      <td>Diving</td>\n",
       "      <td>ALEINIK, Vladimir</td>\n",
       "      <td>URS</td>\n",
       "      <td>Men</td>\n",
       "      <td>10M Platform</td>\n",
       "      <td>Bronze</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13901</th>\n",
       "      <td>1976</td>\n",
       "      <td>Montreal</td>\n",
       "      <td>Aquatics</td>\n",
       "      <td>Diving</td>\n",
       "      <td>DIBIASI, Klaus</td>\n",
       "      <td>ITA</td>\n",
       "      <td>Men</td>\n",
       "      <td>10M Platform</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13902</th>\n",
       "      <td>1976</td>\n",
       "      <td>Montreal</td>\n",
       "      <td>Aquatics</td>\n",
       "      <td>Diving</td>\n",
       "      <td>LOUGANIS, Gregory</td>\n",
       "      <td>USA</td>\n",
       "      <td>Men</td>\n",
       "      <td>10M Platform</td>\n",
       "      <td>Silver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13903</th>\n",
       "      <td>1976</td>\n",
       "      <td>Montreal</td>\n",
       "      <td>Aquatics</td>\n",
       "      <td>Diving</td>\n",
       "      <td>WILSON, Deborah Keplar</td>\n",
       "      <td>USA</td>\n",
       "      <td>Women</td>\n",
       "      <td>10M Platform</td>\n",
       "      <td>Bronze</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13904</th>\n",
       "      <td>1976</td>\n",
       "      <td>Montreal</td>\n",
       "      <td>Aquatics</td>\n",
       "      <td>Diving</td>\n",
       "      <td>VAYTSEKHOVSKAYA, Elena</td>\n",
       "      <td>URS</td>\n",
       "      <td>Women</td>\n",
       "      <td>10M Platform</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Year      City     Sport Discipline                 Athlete Country  \\\n",
       "13900  1976  Montreal  Aquatics     Diving       ALEINIK, Vladimir     URS   \n",
       "13901  1976  Montreal  Aquatics     Diving          DIBIASI, Klaus     ITA   \n",
       "13902  1976  Montreal  Aquatics     Diving       LOUGANIS, Gregory     USA   \n",
       "13903  1976  Montreal  Aquatics     Diving  WILSON, Deborah Keplar     USA   \n",
       "13904  1976  Montreal  Aquatics     Diving  VAYTSEKHOVSKAYA, Elena     URS   \n",
       "\n",
       "      Gender         Event   Medal  \n",
       "13900    Men  10M Platform  Bronze  \n",
       "13901    Men  10M Platform    Gold  \n",
       "13902    Men  10M Platform  Silver  \n",
       "13903  Women  10M Platform  Bronze  \n",
       "13904  Women  10M Platform    Gold  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summer76.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3ce49152-6e18-4740-8164-69accb53b585",
   "metadata": {
    "scrolled": true
   },
   "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>Medal</th>\n",
       "      <th>Bronze</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUT</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEL</th>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BER</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRA</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal    Bronze  Gold  Silver\n",
       "Country                      \n",
       "AUS           8     0      16\n",
       "AUT           1     0       0\n",
       "BEL           6     0       3\n",
       "BER           1     0       0\n",
       "BRA           3     0       0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mt76 = summer76.groupby([\"Country\", \"Medal\"]).Medal.count().unstack(fill_value = 0)\n",
    "mt76.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "28c2de49-cb06-4658-8a60-165e1342cf73",
   "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>Medal</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>URS</th>\n",
       "      <td>113</td>\n",
       "      <td>93</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GDR</th>\n",
       "      <td>99</td>\n",
       "      <td>51</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>63</td>\n",
       "      <td>56</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPN</th>\n",
       "      <td>25</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRG</th>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POL</th>\n",
       "      <td>18</td>\n",
       "      <td>29</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NZL</th>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HUN</th>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWE</th>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUL</th>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal    Gold  Silver  Bronze\n",
       "Country                      \n",
       "URS       113      93      79\n",
       "GDR        99      51      42\n",
       "USA        63      56      36\n",
       "JPN        25       6      10\n",
       "FRG        21      24      30\n",
       "POL        18      29      26\n",
       "NZL        17       1       9\n",
       "HUN        14       6      35\n",
       "SWE         9       1       0\n",
       "BUL         8      13      18"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mt76 = mt76.sort_values([\"Gold\", \"Silver\", \"Bronze\"], ascending = False)[[\"Gold\", \"Silver\", \"Bronze\"]]\n",
    "mt76.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2872d600-80e8-4972-a524-124b4b6db2f3",
   "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>Rank</th>\n",
       "      <th>NOC</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Soviet Union (URS)</td>\n",
       "      <td>49</td>\n",
       "      <td>41</td>\n",
       "      <td>35</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>East Germany (GDR)</td>\n",
       "      <td>40</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>United States (USA)</td>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>25</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>West Germany (FRG)</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Japan (JPN)</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Poland (POL)</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>13</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Bulgaria (BUL)</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Cuba (CUB)</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Romania (ROU)</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>14</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Hungary (HUN)</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Finland (FIN)</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>Sweden (SWE)</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>Great Britain (GBR)</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14</td>\n",
       "      <td>Italy (ITA)</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>France (FRA)</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>16</td>\n",
       "      <td>Yugoslavia (YUG)</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>17</td>\n",
       "      <td>Czechoslovakia (TCH)</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>18</td>\n",
       "      <td>New Zealand (NZL)</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>19</td>\n",
       "      <td>South Korea (KOR)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20</td>\n",
       "      <td>Switzerland (SUI)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>21</td>\n",
       "      <td>Jamaica (JAM)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>North Korea (PRK)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>21</td>\n",
       "      <td>Norway (NOR)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>Denmark (DEN)</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>25</td>\n",
       "      <td>Mexico (MEX)</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>26</td>\n",
       "      <td>Trinidad and Tobago (TRI)</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>27</td>\n",
       "      <td>Canada (CAN)*</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>28</td>\n",
       "      <td>Belgium (BEL)</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>29</td>\n",
       "      <td>Netherlands (NED)</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>30</td>\n",
       "      <td>Portugal (POR)</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30</td>\n",
       "      <td>Spain (ESP)</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32</td>\n",
       "      <td>Australia (AUS)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>33</td>\n",
       "      <td>Iran (IRI)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>34</td>\n",
       "      <td>Mongolia (MGL)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>34</td>\n",
       "      <td>Venezuela (VEN)</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>36</td>\n",
       "      <td>Brazil (BRA)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>37</td>\n",
       "      <td>Austria (AUT)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>37</td>\n",
       "      <td>Bermuda (BER)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>37</td>\n",
       "      <td>Pakistan (PAK)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>37</td>\n",
       "      <td>Puerto Rico (PUR)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>37</td>\n",
       "      <td>Thailand (THA)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Totals (41 NOCs)</td>\n",
       "      <td>Totals (41 NOCs)</td>\n",
       "      <td>198</td>\n",
       "      <td>199</td>\n",
       "      <td>216</td>\n",
       "      <td>613</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Rank                        NOC  Gold  Silver  Bronze  Total\n",
       "0                  1         Soviet Union (URS)    49      41      35    125\n",
       "1                  2         East Germany (GDR)    40      25      25     90\n",
       "2                  3        United States (USA)    34      35      25     94\n",
       "3                  4         West Germany (FRG)    10      12      17     39\n",
       "4                  5                Japan (JPN)     9       6      10     25\n",
       "5                  6               Poland (POL)     7       6      13     26\n",
       "6                  7             Bulgaria (BUL)     6       9       7     22\n",
       "7                  8                 Cuba (CUB)     6       4       3     13\n",
       "8                  9              Romania (ROU)     4       9      14     27\n",
       "9                 10              Hungary (HUN)     4       5      13     22\n",
       "10                11              Finland (FIN)     4       2       0      6\n",
       "11                12               Sweden (SWE)     4       1       0      5\n",
       "12                13        Great Britain (GBR)     3       5       5     13\n",
       "13                14                Italy (ITA)     2       7       4     13\n",
       "14                15               France (FRA)     2       3       4      9\n",
       "15                16           Yugoslavia (YUG)     2       3       3      8\n",
       "16                17       Czechoslovakia (TCH)     2       2       4      8\n",
       "17                18          New Zealand (NZL)     2       1       1      4\n",
       "18                19          South Korea (KOR)     1       1       4      6\n",
       "19                20          Switzerland (SUI)     1       1       2      4\n",
       "20                21              Jamaica (JAM)     1       1       0      2\n",
       "21                21          North Korea (PRK)     1       1       0      2\n",
       "22                21               Norway (NOR)     1       1       0      2\n",
       "23                24              Denmark (DEN)     1       0       2      3\n",
       "24                25               Mexico (MEX)     1       0       1      2\n",
       "25                26  Trinidad and Tobago (TRI)     1       0       0      1\n",
       "26                27              Canada (CAN)*     0       5       6     11\n",
       "27                28              Belgium (BEL)     0       3       3      6\n",
       "28                29          Netherlands (NED)     0       2       3      5\n",
       "29                30             Portugal (POR)     0       2       0      2\n",
       "30                30                Spain (ESP)     0       2       0      2\n",
       "31                32            Australia (AUS)     0       1       4      5\n",
       "32                33                 Iran (IRI)     0       1       1      2\n",
       "33                34             Mongolia (MGL)     0       1       0      1\n",
       "34                34            Venezuela (VEN)     0       1       0      1\n",
       "35                36               Brazil (BRA)     0       0       2      2\n",
       "36                37              Austria (AUT)     0       0       1      1\n",
       "37                37              Bermuda (BER)     0       0       1      1\n",
       "38                37             Pakistan (PAK)     0       0       1      1\n",
       "39                37          Puerto Rico (PUR)     0       0       1      1\n",
       "40                37             Thailand (THA)     0       0       1      1\n",
       "41  Totals (41 NOCs)           Totals (41 NOCs)   198     199     216    613"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"wik_1976.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6ba6358-dfb4-4017-934c-19ac6165f835",
   "metadata": {},
   "source": [
    "-> __Far away from the target!!!There is a big divergence between medal tables. The target to get zero divergence.__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "843ab71d-cd9f-48a2-9814-86db3bd3f55b",
   "metadata": {},
   "source": [
    "## Some useful information from Sports experts"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21c056ae-8a3c-420f-8e86-56c623a9454a",
   "metadata": {},
   "source": [
    "\n",
    "Medals awarded in __Team Events__ (one medal for each member of the team) only count as __one Medal__. For example, the Basketball Team of the United States won the Gold Medal in the Edition 2012. In total __12 Basketball Athletes__ from the United States were awarded with a Gold Medal. For the official Medal Table 2012, this only counts as __one Gold Medal__ for the United States!<br> <br>\n",
    "All Events with __5 or less than 5 medals__ shall be deemed __Singles Events__. All Events with __more than 5 medals__ shall be deemed __Team Events__. It frequently happens that 2 or 3 Athletes share the Bronze medal. Therefore, in total 4 or 5 medals are awarded in these Singles Events. All of these medals count for the official Medal Table! It also happens in Team Events that two Teams share the Bronze medal. Also in this case, in total 4 medals count for the official Medal Table (1 Gold, 1 Silver, 2 Bronze).\n",
    "<br><br>\n",
    "To identify all unique Events, the __Event Gender matters__! There are __Men__ Events, __Women__ Events and __Mixed__ Events. Assume that the following medals have been awarded in __Mixed Events__:\n",
    "- the Event is marked with \"__mixed__\" or \"__pairs__\"\n",
    "- all \"__Equestrian__\" Events\n",
    "- all \"__Sailing__\" Events __before 1988__ (until and including 1984)\n",
    "- the following medals (index labels) were awarded in __Badminton mixed Double Events__: [21773, 21782, 21776, 21785, 21770, 21779, 23703, 23712, 23706, 23715, 23709, 23700, 25720, 25729, 25723, 25732, 25726, 25717, 27727, 27736, 27730, 27739, 27724, 27733, 29784, 29785, 29786, 29787, 29788, 29789]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc2a7a74-6d46-4d2e-aa67-3306371332f6",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## **Clean and aleign datasets**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "182322bc-e33d-422f-8b46-c27ce493c772",
   "metadata": {},
   "outputs": [],
   "source": [
    "# run the cell!\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "901283e9-10bb-45f6-8386-9d8f50359a52",
   "metadata": {},
   "outputs": [],
   "source": [
    "# run the cell!\n",
    "summer = pd.read_csv(\"summer.csv\")\n",
    "wik_1996 = pd.read_csv(\"wik_1996.csv\")\n",
    "wik_1976 = pd.read_csv(\"wik_1976.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "945e706a-4620-4e78-a358-38f2f8a8c4a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 31165 entries, 0 to 31164\n",
      "Data columns (total 9 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Year        31165 non-null  int64 \n",
      " 1   City        31165 non-null  object\n",
      " 2   Sport       31165 non-null  object\n",
      " 3   Discipline  31165 non-null  object\n",
      " 4   Athlete     31165 non-null  object\n",
      " 5   Country     31161 non-null  object\n",
      " 6   Gender      31165 non-null  object\n",
      " 7   Event       31165 non-null  object\n",
      " 8   Medal       31165 non-null  object\n",
      "dtypes: int64(1), object(8)\n",
      "memory usage: 2.1+ MB\n"
     ]
    }
   ],
   "source": [
    "summer.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8c59c550-ad79-40a8-a0a9-c05aa6d27e83",
   "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>Year</th>\n",
       "      <th>City</th>\n",
       "      <th>Sport</th>\n",
       "      <th>Discipline</th>\n",
       "      <th>Athlete</th>\n",
       "      <th>Country</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Event</th>\n",
       "      <th>Medal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29603</th>\n",
       "      <td>2012</td>\n",
       "      <td>London</td>\n",
       "      <td>Athletics</td>\n",
       "      <td>Athletics</td>\n",
       "      <td>Pending</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Women</td>\n",
       "      <td>1500M</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31072</th>\n",
       "      <td>2012</td>\n",
       "      <td>London</td>\n",
       "      <td>Weightlifting</td>\n",
       "      <td>Weightlifting</td>\n",
       "      <td>Pending</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Women</td>\n",
       "      <td>63KG</td>\n",
       "      <td>Gold</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31091</th>\n",
       "      <td>2012</td>\n",
       "      <td>London</td>\n",
       "      <td>Weightlifting</td>\n",
       "      <td>Weightlifting</td>\n",
       "      <td>Pending</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Men</td>\n",
       "      <td>94KG</td>\n",
       "      <td>Silver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31110</th>\n",
       "      <td>2012</td>\n",
       "      <td>London</td>\n",
       "      <td>Wrestling</td>\n",
       "      <td>Wrestling Freestyle</td>\n",
       "      <td>KUDUKHOV, Besik</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Men</td>\n",
       "      <td>Wf 60 KG</td>\n",
       "      <td>Silver</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Year    City          Sport           Discipline          Athlete  \\\n",
       "29603  2012  London      Athletics            Athletics          Pending   \n",
       "31072  2012  London  Weightlifting        Weightlifting          Pending   \n",
       "31091  2012  London  Weightlifting        Weightlifting          Pending   \n",
       "31110  2012  London      Wrestling  Wrestling Freestyle  KUDUKHOV, Besik   \n",
       "\n",
       "      Country Gender     Event   Medal  \n",
       "29603     NaN  Women     1500M    Gold  \n",
       "31072     NaN  Women      63KG    Gold  \n",
       "31091     NaN    Men      94KG  Silver  \n",
       "31110     NaN    Men  Wf 60 KG  Silver  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "summer[summer.isna().any(axis = 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "46429ae8-73e8-4b91-942d-5fc29dcb8b6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "summer.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4e9691dc-85ec-4785-b14a-8a0228bcc464",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 31161 entries, 0 to 31164\n",
      "Data columns (total 9 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   Year        31161 non-null  int64 \n",
      " 1   City        31161 non-null  object\n",
      " 2   Sport       31161 non-null  object\n",
      " 3   Discipline  31161 non-null  object\n",
      " 4   Athlete     31161 non-null  object\n",
      " 5   Country     31161 non-null  object\n",
      " 6   Gender      31161 non-null  object\n",
      " 7   Event       31161 non-null  object\n",
      " 8   Medal       31161 non-null  object\n",
      "dtypes: int64(1), object(8)\n",
      "memory usage: 2.4+ MB\n"
     ]
    }
   ],
   "source": [
    "summer.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9db29b69-2741-4302-91ba-0e9cb24cdaa7",
   "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>Rank</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>United States (USA)*</td>\n",
       "      <td>44</td>\n",
       "      <td>32</td>\n",
       "      <td>25</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Russia (RUS)</td>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>16</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Germany (GER)</td>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>China (CHN)</td>\n",
       "      <td>16</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>France (FRA)</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Rank                Nation  Gold  Silver  Bronze  Total\n",
       "0    1  United States (USA)*    44      32      25    101\n",
       "1    2          Russia (RUS)    26      21      16     63\n",
       "2    3         Germany (GER)    20      18      27     65\n",
       "3    4           China (CHN)    16      22      12     50\n",
       "4    5          France (FRA)    15       7      15     37"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wik_1996.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "620a0e10-36d1-447d-99ee-b8ef1c43cb4b",
   "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>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>URS</th>\n",
       "      <td>49</td>\n",
       "      <td>41</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GDR</th>\n",
       "      <td>40</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRG</th>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPN</th>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Gold  Silver  Bronze\n",
       "Country                      \n",
       "URS        49      41      35\n",
       "GDR        40      25      25\n",
       "USA        34      35      25\n",
       "FRG        10      12      17\n",
       "JPN         9       6      10"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wik_1976 = pd.read_csv(\"wik_1976.csv\")\n",
    "wik_1976[\"Country\"] = wik_1976['NOC'].str.split('(', expand=True).iloc[:,1].str.rstrip(')*')\n",
    "wik_1976 = wik_1976.drop(columns =[\"Rank\", \"NOC\", \"Total\"]).set_index(\"Country\")\n",
    "wik_1976.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "02052bcb-85a7-41d1-aa0e-5d0add411e3d",
   "metadata": {
    "scrolled": true
   },
   "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>Rank</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>United States (USA)*</td>\n",
       "      <td>44</td>\n",
       "      <td>32</td>\n",
       "      <td>25</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Russia (RUS)</td>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>16</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Germany (GER)</td>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>China (CHN)</td>\n",
       "      <td>16</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>France (FRA)</td>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>71</td>\n",
       "      <td>Mozambique (MOZ)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>71</td>\n",
       "      <td>Puerto Rico (PUR)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>71</td>\n",
       "      <td>Tunisia (TUN)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>71</td>\n",
       "      <td>Uganda (UGA)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Totals (79 nations)</td>\n",
       "      <td>Totals (79 nations)</td>\n",
       "      <td>271</td>\n",
       "      <td>273</td>\n",
       "      <td>298</td>\n",
       "      <td>842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Rank                Nation  Gold  Silver  Bronze  Total\n",
       "0                     1  United States (USA)*    44      32      25    101\n",
       "1                     2          Russia (RUS)    26      21      16     63\n",
       "2                     3         Germany (GER)    20      18      27     65\n",
       "3                     4           China (CHN)    16      22      12     50\n",
       "4                     5          France (FRA)    15       7      15     37\n",
       "..                  ...                   ...   ...     ...     ...    ...\n",
       "75                   71      Mozambique (MOZ)     0       0       1      1\n",
       "76                   71     Puerto Rico (PUR)     0       0       1      1\n",
       "77                   71         Tunisia (TUN)     0       0       1      1\n",
       "78                   71          Uganda (UGA)     0       0       1      1\n",
       "79  Totals (79 nations)   Totals (79 nations)   271     273     298    842\n",
       "\n",
       "[80 rows x 6 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wik_1996"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "98c58f9c-6511-40a4-a63d-13960a0936de",
   "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>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>44</td>\n",
       "      <td>32</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RUS</th>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GER</th>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHN</th>\n",
       "      <td>16</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRA</th>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MOZ</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PUR</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TUN</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UGA</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79 nations</th>\n",
       "      <td>271</td>\n",
       "      <td>273</td>\n",
       "      <td>298</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>80 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Gold  Silver  Bronze\n",
       "Country                         \n",
       "USA           44      32      25\n",
       "RUS           26      21      16\n",
       "GER           20      18      27\n",
       "CHN           16      22      12\n",
       "FRA           15       7      15\n",
       "...          ...     ...     ...\n",
       "MOZ            0       0       1\n",
       "PUR            0       0       1\n",
       "TUN            0       0       1\n",
       "UGA            0       0       1\n",
       "79 nations   271     273     298\n",
       "\n",
       "[80 rows x 3 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wik_1996[\"Country\"] = wik_1996['Nation'].str.split('(', expand=True).iloc[:,1].str.rstrip(')*')\n",
    "wik_1996 = wik_1996.drop(columns =[\"Rank\", \"Nation\", \"Total\"]).set_index(\"Country\")\n",
    "wik_1996"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1259e0b1-90ca-49f6-817e-46e4aee1d079",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## **Concise Code**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a32ed4f3-9ef7-4499-a7da-63b37b6d3b3e",
   "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>Medal</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Year</th>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1896</th>\n",
       "      <th>AUS</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUT</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEN</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRA</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBR</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2012</th>\n",
       "      <th>UGA</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UKR</th>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>46</td>\n",
       "      <td>28</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UZB</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VEN</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1157 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal         Gold  Silver  Bronze\n",
       "Year Country                      \n",
       "1896 AUS         2       0       0\n",
       "     AUT         2       1       2\n",
       "     DEN         1       2       3\n",
       "     FRA         5       4       2\n",
       "     GBR         2       3       2\n",
       "...            ...     ...     ...\n",
       "2012 UGA         1       0       0\n",
       "     UKR         6       4       9\n",
       "     USA        46      28      29\n",
       "     UZB         1       0       2\n",
       "     VEN         1       0       0\n",
       "\n",
       "[1157 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# (1) Creating the Column Event_Gender\n",
    "summer[\"Event_Gender\"] = summer.Gender\n",
    "\n",
    "# (2) Identify unique Mixed events\n",
    "mask1 = summer['Event'].str.contains('pairs|mixed', case=False) # watch out no space between |\n",
    "mask2 = (summer['Sport'].str.contains('Equestrian', case=False))\n",
    "mask3 = ((summer['Sport'] == 'Sailing') & (summer['Year'] <= 1984))\n",
    "mask4 = (summer.index.isin([21773, 21782, 21776, 21785, 21770, 21779, \n",
    "                            23703, 23712, 23706, 23715, 23709, 23700, 25720, \n",
    "                            25729, 25723, 25732, 25726, 25717, 27727, 27736, \n",
    "                            27730, 27739, 27724, 27733, 29784, 29785, 29786, \n",
    "                            29787, 29788, 29789]))\n",
    "summer.loc[ mask1 | mask2 | mask3| mask4, \"Event_Gender\"] = \"Mixed\"\n",
    "\n",
    "# (3) Identify all unique Events and count the amount of medals in each Event. Create the Column Event_Medals. \n",
    "summer[\"Event_Medals\"] = summer.groupby([\"Year\", \"Sport\", \"Discipline\",\"Event\", \"Event_Gender\"])[\"Medal\"].transform(\"count\")\n",
    "# summer[\"Event_Medals\"].value_counts().sort_index(ascending=True)\n",
    "\n",
    "# (4) Identifying Team Events. Create the Column Team. \n",
    "summer[\"Team\"] = summer[\"Event_Medal\"].apply(lambda x: \"Yes\" if x>5 else \"No\")\n",
    "\n",
    "# (5) Removing Duplicated Medals in Team Events. My job is to identify what is considered to be a team. A team is played \n",
    "# in the same year, it plays the same sport and discipline, it is from the same country, it plays the same event, the team \n",
    "# earns one medal, and the team can either be men's team, women team, or mixed team so Event_Gender matters. The subset to \n",
    "# determine duplicates are formed by the columns [\"Year\", \"Sport\", \"Discipline\", \"Country\", \"Event\", \"Event_Gender\", \"Medal\"]. \n",
    "# So only for rows or for medals where all values in those columns are identical those rows shall be deemed duplicates. Keep='first'\n",
    "#by default keeps one row\n",
    "summer.reset_index(inplace=True) # The original indexing is maintained, making it easier to trace back to the original dataset if needed.\n",
    "singles = summer.loc[summer.Team == \"No\"].copy()\n",
    "team = summer.loc[summer.Team == \"Yes\"].copy()\n",
    "team.drop_duplicates(subset = [\"Year\", \"Sport\", \"Discipline\", \"Country\", \"Event\", \"Event_Gender\", \"Medal\"], inplace = True)\n",
    "summer_new = pd.concat([singles, team])\n",
    "summer_new.set_index(\"index\", inplace= True) # The original indexing is maintained, making it easier to trace back to the original dataset if needed.\n",
    "\n",
    "# (6) Creating the official Medal Table for all Editions\n",
    "medal_tables = summer_new.groupby([\"Year\", \"Country\", \"Medal\"]).Medal.count().unstack(fill_value = 0)[[\"Gold\", \"Silver\", \"Bronze\"]]\n",
    "medal_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd5c35da-c812-4576-a716-dcff231bcd5d",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true
   },
   "source": [
    "## **Comparison with Wikipedia Medal Tables**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c0e68865-bc36-4e48-9ce3-bf2337a8e362",
   "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>Medal</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>URS</th>\n",
       "      <td>49</td>\n",
       "      <td>41</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GDR</th>\n",
       "      <td>40</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRG</th>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPN</th>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal    Gold  Silver  Bronze\n",
       "Country                      \n",
       "URS        49      41      35\n",
       "GDR        40      25      25\n",
       "USA        34      35      25\n",
       "FRG        10      12      17\n",
       "JPN         9       6      10"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_1976 = medal_tables.loc[1976].sort_values([\"Gold\", \"Silver\", \"Bronze\"], ascending = False).copy()\n",
    "agg_1976.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "676ce1fc-301d-4416-af4b-7d1721f454a5",
   "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>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>URS</th>\n",
       "      <td>49</td>\n",
       "      <td>41</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GDR</th>\n",
       "      <td>40</td>\n",
       "      <td>25</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>34</td>\n",
       "      <td>35</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRG</th>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPN</th>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Gold  Silver  Bronze\n",
       "Country                      \n",
       "URS        49      41      35\n",
       "GDR        40      25      25\n",
       "USA        34      35      25\n",
       "FRG        10      12      17\n",
       "JPN         9       6      10"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wik_1976.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8dbeb552-8f01-4385-97d6-2090b5db1903",
   "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>Medal</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUT</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BER</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BRA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BUL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CAN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUB</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ESP</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRG</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GDR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HUN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IRI</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ITA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JAM</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KOR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MEX</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MGL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NED</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NOR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NZL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PAK</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POL</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PRK</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PUR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ROU</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SUI</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWE</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TCH</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>THA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TRI</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>URS</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VEN</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YUG</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal    Gold  Silver  Bronze\n",
       "Country                      \n",
       "AUS       0.0     0.0     0.0\n",
       "AUT       0.0     0.0     0.0\n",
       "BEL       0.0     0.0     0.0\n",
       "BER       0.0     0.0     0.0\n",
       "BRA       0.0     0.0     0.0\n",
       "BUL       0.0     0.0     0.0\n",
       "CAN       0.0     0.0     0.0\n",
       "CUB       0.0     0.0     0.0\n",
       "DEN       0.0     0.0     0.0\n",
       "ESP       0.0     0.0     0.0\n",
       "FIN       0.0     0.0     0.0\n",
       "FRA       0.0     0.0     0.0\n",
       "FRG       0.0     0.0     0.0\n",
       "GBR       0.0     0.0     0.0\n",
       "GDR       0.0     0.0     0.0\n",
       "HUN       0.0     0.0     0.0\n",
       "IRI       0.0     0.0     0.0\n",
       "ITA       0.0     0.0     0.0\n",
       "JAM       0.0     0.0     0.0\n",
       "JPN       0.0     0.0     0.0\n",
       "KOR       0.0     0.0     0.0\n",
       "MEX       0.0     0.0     0.0\n",
       "MGL       0.0     0.0     0.0\n",
       "NED       0.0     0.0     0.0\n",
       "NOR       0.0     0.0     0.0\n",
       "NZL       0.0     0.0     0.0\n",
       "PAK       0.0     0.0     0.0\n",
       "POL       0.0     0.0     0.0\n",
       "POR       0.0     0.0     0.0\n",
       "PRK       0.0     0.0     0.0\n",
       "PUR       0.0     0.0     0.0\n",
       "ROU       0.0     0.0     0.0\n",
       "SUI       0.0     0.0     0.0\n",
       "SWE       0.0     0.0     0.0\n",
       "TCH       0.0     0.0     0.0\n",
       "THA       0.0     0.0     0.0\n",
       "TRI       0.0     0.0     0.0\n",
       "URS       0.0     0.0     0.0\n",
       "USA       0.0     0.0     0.0\n",
       "VEN       0.0     0.0     0.0\n",
       "YUG       0.0     0.0     0.0"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "div_76 = agg_1976.sub(wik_1976).abs().dropna()\n",
    "div_76"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "64b82f3c-d946-4935-a3b7-a74ca45ab1fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_76 = div_76.sum().sum()\n",
    "score_76"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "bd96ae62-4b99-484b-8100-5fa174986d20",
   "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>Medal</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>44</td>\n",
       "      <td>32</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RUS</th>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GER</th>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHN</th>\n",
       "      <td>16</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRA</th>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal    Gold  Silver  Bronze\n",
       "Country                      \n",
       "USA        44      32      25\n",
       "RUS        26      21      16\n",
       "GER        20      18      27\n",
       "CHN        16      22      12\n",
       "FRA        15       7      15"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agg_1996 = medal_tables.loc[1996].sort_values([\"Gold\", \"Silver\", \"Bronze\"], ascending = False).copy()\n",
    "agg_1996.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "94bc01f8-9f30-418e-a4ed-9e631bf67180",
   "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>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>44</td>\n",
       "      <td>32</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RUS</th>\n",
       "      <td>26</td>\n",
       "      <td>21</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GER</th>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHN</th>\n",
       "      <td>16</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRA</th>\n",
       "      <td>15</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Gold  Silver  Bronze\n",
       "Country                      \n",
       "USA        44      32      25\n",
       "RUS        26      21      16\n",
       "GER        20      18      27\n",
       "CHN        16      22      12\n",
       "FRA        15       7      15"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wik_1996.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "8890ca24-963e-4f19-88fd-a70121d7354b",
   "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>Medal</th>\n",
       "      <th>Gold</th>\n",
       "      <th>Silver</th>\n",
       "      <th>Bronze</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Country</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ALG</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ARG</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ARM</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUT</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UKR</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USA</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UZB</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>YUG</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ZAM</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>79 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Medal    Gold  Silver  Bronze\n",
       "Country                      \n",
       "ALG       0.0     0.0     0.0\n",
       "ARG       0.0     0.0     0.0\n",
       "ARM       0.0     0.0     0.0\n",
       "AUS       0.0     0.0     0.0\n",
       "AUT       0.0     0.0     0.0\n",
       "...       ...     ...     ...\n",
       "UKR       0.0     0.0     0.0\n",
       "USA       0.0     0.0     0.0\n",
       "UZB       0.0     0.0     0.0\n",
       "YUG       0.0     0.0     0.0\n",
       "ZAM       0.0     0.0     0.0\n",
       "\n",
       "[79 rows x 3 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "div_96 = agg_1996.sub(wik_1996).abs().dropna()\n",
    "div_96"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "109a2e79-cd47-4814-99b3-d9eca82eca53",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_96 = div_96.sum().sum()\n",
    "score_96"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pandas",
   "language": "python",
   "name": "pandas"
  },
  "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}