lectures/24_optional_multiple_tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "![](img/330-banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "# (Optional)<br>Lecture 24: Combining Multiple Tables\n",
    "\n",
    "UBC 2022 Summer\n",
    "\n",
    "Instructor: Mehrdad Oveisi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "from sklearn.preprocessing import StandardScaler, OneHotEncoder\n",
    "from sklearn.linear_model import Ridge, LogisticRegression, SGDClassifier, SGDRegressor\n",
    "from sklearn.pipeline import Pipeline, make_pipeline\n",
    "from sklearn.dummy import DummyClassifier, DummyRegressor\n",
    "from sklearn.compose import ColumnTransformer, make_column_transformer\n",
    "from sklearn.model_selection import train_test_split, GridSearchCV, cross_validate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Take a look at the [Formula 1 race data set](https://www.kaggle.com/cjgdev/formula-1-race-data-19502017) from Kaggle. \n",
    "- The dataset contains **multiple CSV files**.\n",
    "- Let's read in one of them:"
   ]
  },
  {
   "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>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>constructorId</th>\n",
       "      <th>number</th>\n",
       "      <th>grid</th>\n",
       "      <th>position</th>\n",
       "      <th>positionText</th>\n",
       "      <th>positionOrder</th>\n",
       "      <th>points</th>\n",
       "      <th>laps</th>\n",
       "      <th>time</th>\n",
       "      <th>milliseconds</th>\n",
       "      <th>fastestLap</th>\n",
       "      <th>rank</th>\n",
       "      <th>fastestLapTime</th>\n",
       "      <th>fastestLapSpeed</th>\n",
       "      <th>statusId</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resultId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>58</td>\n",
       "      <td>34:50.6</td>\n",
       "      <td>5690616.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>01:27.5</td>\n",
       "      <td>218.3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>58</td>\n",
       "      <td>5.478</td>\n",
       "      <td>5696094.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>01:27.7</td>\n",
       "      <td>217.586</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>58</td>\n",
       "      <td>8.163</td>\n",
       "      <td>5698779.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>01:28.1</td>\n",
       "      <td>216.719</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>11</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>58</td>\n",
       "      <td>17.181</td>\n",
       "      <td>5707797.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>01:28.6</td>\n",
       "      <td>215.464</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>23.0</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>58</td>\n",
       "      <td>18.014</td>\n",
       "      <td>5708630.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>01:27.4</td>\n",
       "      <td>218.385</td>\n",
       "      <td>1</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23777</th>\n",
       "      <td>988</td>\n",
       "      <td>842</td>\n",
       "      <td>5</td>\n",
       "      <td>10.0</td>\n",
       "      <td>17</td>\n",
       "      <td>16.0</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>01:43.8</td>\n",
       "      <td>192.542</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23778</th>\n",
       "      <td>988</td>\n",
       "      <td>828</td>\n",
       "      <td>15</td>\n",
       "      <td>9.0</td>\n",
       "      <td>19</td>\n",
       "      <td>17.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>01:43.6</td>\n",
       "      <td>193.057</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23779</th>\n",
       "      <td>988</td>\n",
       "      <td>840</td>\n",
       "      <td>3</td>\n",
       "      <td>18.0</td>\n",
       "      <td>15</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18</td>\n",
       "      <td>18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>01:42.3</td>\n",
       "      <td>195.402</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23780</th>\n",
       "      <td>988</td>\n",
       "      <td>832</td>\n",
       "      <td>4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>01:43.4</td>\n",
       "      <td>193.41</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23781</th>\n",
       "      <td>988</td>\n",
       "      <td>817</td>\n",
       "      <td>9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R</td>\n",
       "      <td>20</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>01:42.8</td>\n",
       "      <td>194.579</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>23777 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          raceId  driverId  constructorId  number  grid  position  \\\n",
       "resultId                                                            \n",
       "1             18         1              1    22.0     1       1.0   \n",
       "2             18         2              2     3.0     5       2.0   \n",
       "3             18         3              3     7.0     7       3.0   \n",
       "4             18         4              4     5.0    11       4.0   \n",
       "5             18         5              1    23.0     3       5.0   \n",
       "...          ...       ...            ...     ...   ...       ...   \n",
       "23777        988       842              5    10.0    17      16.0   \n",
       "23778        988       828             15     9.0    19      17.0   \n",
       "23779        988       840              3    18.0    15      18.0   \n",
       "23780        988       832              4    55.0    12       NaN   \n",
       "23781        988       817              9     3.0     4       NaN   \n",
       "\n",
       "         positionText  positionOrder  points  laps     time  milliseconds  \\\n",
       "resultId                                                                    \n",
       "1                   1              1    10.0    58  34:50.6     5690616.0   \n",
       "2                   2              2     8.0    58    5.478     5696094.0   \n",
       "3                   3              3     6.0    58    8.163     5698779.0   \n",
       "4                   4              4     5.0    58   17.181     5707797.0   \n",
       "5                   5              5     4.0    58   18.014     5708630.0   \n",
       "...               ...            ...     ...   ...      ...           ...   \n",
       "23777              16             16     0.0    54      NaN           NaN   \n",
       "23778              17             17     0.0    54      NaN           NaN   \n",
       "23779              18             18     0.0    54      NaN           NaN   \n",
       "23780               R             19     0.0    31      NaN           NaN   \n",
       "23781               R             20     0.0    20      NaN           NaN   \n",
       "\n",
       "          fastestLap  rank fastestLapTime fastestLapSpeed  statusId  \n",
       "resultId                                                             \n",
       "1               39.0   2.0        01:27.5           218.3         1  \n",
       "2               41.0   3.0        01:27.7         217.586         1  \n",
       "3               41.0   5.0        01:28.1         216.719         1  \n",
       "4               58.0   7.0        01:28.6         215.464         1  \n",
       "5               43.0   1.0        01:27.4         218.385         1  \n",
       "...              ...   ...            ...             ...       ...  \n",
       "23777           33.0  16.0        01:43.8         192.542        11  \n",
       "23778           36.0  15.0        01:43.6         193.057        11  \n",
       "23779           52.0   6.0        01:42.3         195.402        11  \n",
       "23780           26.0  14.0        01:43.4          193.41        36  \n",
       "23781           13.0  12.0        01:42.8         194.579         9  \n",
       "\n",
       "[23777 rows x 17 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "racing_results_df = pd.read_csv(\"data/formula-1-race-data-19502017/results.csv\", index_col=0)\n",
    "racing_results_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Let's say we want to predict the `milliseconds` column, namely the total length of time it takes a driver to finish a race. \n",
    "- In that case, we should not have access to most of these other columns. \n",
    "- But we would have the `raceId` and `driverId`:"
   ]
  },
  {
   "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>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>milliseconds</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>resultId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>5690616.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5696094.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>5698779.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>5707797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>5708630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23765</th>\n",
       "      <td>988</td>\n",
       "      <td>8</td>\n",
       "      <td>5699448.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23766</th>\n",
       "      <td>988</td>\n",
       "      <td>830</td>\n",
       "      <td>5700331.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23767</th>\n",
       "      <td>988</td>\n",
       "      <td>807</td>\n",
       "      <td>5739775.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23768</th>\n",
       "      <td>988</td>\n",
       "      <td>815</td>\n",
       "      <td>5746124.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23769</th>\n",
       "      <td>988</td>\n",
       "      <td>839</td>\n",
       "      <td>5752973.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6003 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          raceId  driverId  milliseconds\n",
       "resultId                                \n",
       "1             18         1     5690616.0\n",
       "2             18         2     5696094.0\n",
       "3             18         3     5698779.0\n",
       "4             18         4     5707797.0\n",
       "5             18         5     5708630.0\n",
       "...          ...       ...           ...\n",
       "23765        988         8     5699448.0\n",
       "23766        988       830     5700331.0\n",
       "23767        988       807     5739775.0\n",
       "23768        988       815     5746124.0\n",
       "23769        988       839     5752973.0\n",
       "\n",
       "[6003 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "racing_results_df_subset = racing_results_df.dropna(\n",
    "    subset=[\"milliseconds\"])[['raceId', 'driverId', 'milliseconds']]\n",
    "racing_results_df_subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "racing_train, racing_test = train_test_split(racing_results_df_subset, random_state=123)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can try some baselines based on the ID values themselves, although:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fit_time       0.001079\n",
       "score_time     0.000510\n",
       "test_score    -0.002014\n",
       "train_score    0.000000\n",
       "dtype: float64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(cross_validate(DummyRegressor(), racing_train[[\"raceId\", \"driverId\"]], \n",
    "                            racing_train[\"milliseconds\"], return_train_score=True, cv=20)).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fit_time       0.012328\n",
       "score_time     0.001758\n",
       "test_score     0.949340\n",
       "train_score    0.981948\n",
       "dtype: float64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lr = make_pipeline(OneHotEncoder(handle_unknown='ignore'), Ridge())\n",
    "pd.DataFrame(cross_validate(lr, racing_train[[\"raceId\", \"driverId\"]], \n",
    "                            racing_train[\"milliseconds\"], return_train_score=True, cv=20)).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That is actually pretty good *facepalm*."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But what if we want to predict for a _new_ race track or a _new_ driver??"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Enter the other tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>driverRef</th>\n",
       "      <th>number</th>\n",
       "      <th>code</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>driverId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>hamilton</td>\n",
       "      <td>44.0</td>\n",
       "      <td>HAM</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>heidfeld</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HEI</td>\n",
       "      <td>Nick</td>\n",
       "      <td>Heidfeld</td>\n",
       "      <td>1977-05-10</td>\n",
       "      <td>German</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Nick_Heidfeld</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>rosberg</td>\n",
       "      <td>6.0</td>\n",
       "      <td>ROS</td>\n",
       "      <td>Nico</td>\n",
       "      <td>Rosberg</td>\n",
       "      <td>1985-06-27</td>\n",
       "      <td>German</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Nico_Rosberg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>alonso</td>\n",
       "      <td>14.0</td>\n",
       "      <td>ALO</td>\n",
       "      <td>Fernando</td>\n",
       "      <td>Alonso</td>\n",
       "      <td>1981-07-29</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Fernando_Alonso</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>kovalainen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>KOV</td>\n",
       "      <td>Heikki</td>\n",
       "      <td>Kovalainen</td>\n",
       "      <td>1981-10-19</td>\n",
       "      <td>Finnish</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Heikki_Kovalainen</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>838</th>\n",
       "      <td>vandoorne</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VAN</td>\n",
       "      <td>Stoffel</td>\n",
       "      <td>Vandoorne</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Stoffel_Vandoorne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>839</th>\n",
       "      <td>ocon</td>\n",
       "      <td>31.0</td>\n",
       "      <td>OCO</td>\n",
       "      <td>Esteban</td>\n",
       "      <td>Ocon</td>\n",
       "      <td>1996-09-17</td>\n",
       "      <td>French</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Esteban_Ocon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>840</th>\n",
       "      <td>stroll</td>\n",
       "      <td>18.0</td>\n",
       "      <td>STR</td>\n",
       "      <td>Lance</td>\n",
       "      <td>Stroll</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lance_Stroll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>841</th>\n",
       "      <td>giovinazzi</td>\n",
       "      <td>36.0</td>\n",
       "      <td>GIO</td>\n",
       "      <td>Antonio</td>\n",
       "      <td>Giovinazzi</td>\n",
       "      <td>1993-12-14</td>\n",
       "      <td>Italian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Antonio_Giovinazzi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>843</th>\n",
       "      <td>brendon_hartley</td>\n",
       "      <td>39.0</td>\n",
       "      <td>HAR</td>\n",
       "      <td>Brendon</td>\n",
       "      <td>Hartley</td>\n",
       "      <td>1989-11-10</td>\n",
       "      <td>New Zealander</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Brendon_Hartley</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>842 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                driverRef  number code  forename     surname        dob  \\\n",
       "driverId                                                                  \n",
       "1                hamilton    44.0  HAM     Lewis    Hamilton 1985-01-07   \n",
       "2                heidfeld     NaN  HEI      Nick    Heidfeld 1977-05-10   \n",
       "3                 rosberg     6.0  ROS      Nico     Rosberg 1985-06-27   \n",
       "4                  alonso    14.0  ALO  Fernando      Alonso 1981-07-29   \n",
       "5              kovalainen     NaN  KOV    Heikki  Kovalainen 1981-10-19   \n",
       "...                   ...     ...  ...       ...         ...        ...   \n",
       "838             vandoorne     2.0  VAN   Stoffel   Vandoorne 1992-03-26   \n",
       "839                  ocon    31.0  OCO   Esteban        Ocon 1996-09-17   \n",
       "840                stroll    18.0  STR     Lance      Stroll 1998-10-29   \n",
       "841            giovinazzi    36.0  GIO   Antonio  Giovinazzi 1993-12-14   \n",
       "843       brendon_hartley    39.0  HAR   Brendon     Hartley 1989-11-10   \n",
       "\n",
       "            nationality                                              url  \n",
       "driverId                                                                  \n",
       "1               British      http://en.wikipedia.org/wiki/Lewis_Hamilton  \n",
       "2                German       http://en.wikipedia.org/wiki/Nick_Heidfeld  \n",
       "3                German        http://en.wikipedia.org/wiki/Nico_Rosberg  \n",
       "4               Spanish     http://en.wikipedia.org/wiki/Fernando_Alonso  \n",
       "5               Finnish   http://en.wikipedia.org/wiki/Heikki_Kovalainen  \n",
       "...                 ...                                              ...  \n",
       "838             Belgian   http://en.wikipedia.org/wiki/Stoffel_Vandoorne  \n",
       "839              French        http://en.wikipedia.org/wiki/Esteban_Ocon  \n",
       "840            Canadian        http://en.wikipedia.org/wiki/Lance_Stroll  \n",
       "841             Italian  http://en.wikipedia.org/wiki/Antonio_Giovinazzi  \n",
       "843       New Zealander     http://en.wikipedia.org/wiki/Brendon_Hartley  \n",
       "\n",
       "[842 rows x 8 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "racing_drivers_df = pd.read_csv(\"data/formula-1-race-data-19502017/drivers.csv\", \n",
    "                                encoding='latin-1', index_col=0, \n",
    "                                dayfirst=True, parse_dates=['dob'])\n",
    "racing_drivers_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Can we use the driver's nationality and age as features?\n",
    "- `pd.merge` can take care of this for us."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>milliseconds</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>number</th>\n",
       "      <th>code</th>\n",
       "      <th>forename</th>\n",
       "      <th>surname</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>5690616.0</td>\n",
       "      <td>hamilton</td>\n",
       "      <td>44.0</td>\n",
       "      <td>HAM</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>5525103.0</td>\n",
       "      <td>hamilton</td>\n",
       "      <td>44.0</td>\n",
       "      <td>HAM</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>5903238.0</td>\n",
       "      <td>hamilton</td>\n",
       "      <td>44.0</td>\n",
       "      <td>HAM</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>5213230.0</td>\n",
       "      <td>hamilton</td>\n",
       "      <td>44.0</td>\n",
       "      <td>HAM</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>7242742.0</td>\n",
       "      <td>hamilton</td>\n",
       "      <td>44.0</td>\n",
       "      <td>HAM</td>\n",
       "      <td>Lewis</td>\n",
       "      <td>Hamilton</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5998</th>\n",
       "      <td>981</td>\n",
       "      <td>840</td>\n",
       "      <td>4606468.0</td>\n",
       "      <td>stroll</td>\n",
       "      <td>18.0</td>\n",
       "      <td>STR</td>\n",
       "      <td>Lance</td>\n",
       "      <td>Stroll</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lance_Stroll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5999</th>\n",
       "      <td>982</td>\n",
       "      <td>840</td>\n",
       "      <td>7445240.0</td>\n",
       "      <td>stroll</td>\n",
       "      <td>18.0</td>\n",
       "      <td>STR</td>\n",
       "      <td>Lance</td>\n",
       "      <td>Stroll</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lance_Stroll</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6000</th>\n",
       "      <td>976</td>\n",
       "      <td>838</td>\n",
       "      <td>7527733.0</td>\n",
       "      <td>vandoorne</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VAN</td>\n",
       "      <td>Stoffel</td>\n",
       "      <td>Vandoorne</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Stoffel_Vandoorne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6001</th>\n",
       "      <td>980</td>\n",
       "      <td>838</td>\n",
       "      <td>5139898.0</td>\n",
       "      <td>vandoorne</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VAN</td>\n",
       "      <td>Stoffel</td>\n",
       "      <td>Vandoorne</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Stoffel_Vandoorne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6002</th>\n",
       "      <td>982</td>\n",
       "      <td>838</td>\n",
       "      <td>7433932.0</td>\n",
       "      <td>vandoorne</td>\n",
       "      <td>2.0</td>\n",
       "      <td>VAN</td>\n",
       "      <td>Stoffel</td>\n",
       "      <td>Vandoorne</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Stoffel_Vandoorne</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6003 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      raceId  driverId  milliseconds  driverRef  number code forename  \\\n",
       "0         18         1     5690616.0   hamilton    44.0  HAM    Lewis   \n",
       "1         19         1     5525103.0   hamilton    44.0  HAM    Lewis   \n",
       "2         21         1     5903238.0   hamilton    44.0  HAM    Lewis   \n",
       "3         22         1     5213230.0   hamilton    44.0  HAM    Lewis   \n",
       "4         23         1     7242742.0   hamilton    44.0  HAM    Lewis   \n",
       "...      ...       ...           ...        ...     ...  ...      ...   \n",
       "5998     981       840     4606468.0     stroll    18.0  STR    Lance   \n",
       "5999     982       840     7445240.0     stroll    18.0  STR    Lance   \n",
       "6000     976       838     7527733.0  vandoorne     2.0  VAN  Stoffel   \n",
       "6001     980       838     5139898.0  vandoorne     2.0  VAN  Stoffel   \n",
       "6002     982       838     7433932.0  vandoorne     2.0  VAN  Stoffel   \n",
       "\n",
       "        surname        dob nationality  \\\n",
       "0      Hamilton 1985-01-07     British   \n",
       "1      Hamilton 1985-01-07     British   \n",
       "2      Hamilton 1985-01-07     British   \n",
       "3      Hamilton 1985-01-07     British   \n",
       "4      Hamilton 1985-01-07     British   \n",
       "...         ...        ...         ...   \n",
       "5998     Stroll 1998-10-29    Canadian   \n",
       "5999     Stroll 1998-10-29    Canadian   \n",
       "6000  Vandoorne 1992-03-26     Belgian   \n",
       "6001  Vandoorne 1992-03-26     Belgian   \n",
       "6002  Vandoorne 1992-03-26     Belgian   \n",
       "\n",
       "                                                 url  \n",
       "0        http://en.wikipedia.org/wiki/Lewis_Hamilton  \n",
       "1        http://en.wikipedia.org/wiki/Lewis_Hamilton  \n",
       "2        http://en.wikipedia.org/wiki/Lewis_Hamilton  \n",
       "3        http://en.wikipedia.org/wiki/Lewis_Hamilton  \n",
       "4        http://en.wikipedia.org/wiki/Lewis_Hamilton  \n",
       "...                                              ...  \n",
       "5998       http://en.wikipedia.org/wiki/Lance_Stroll  \n",
       "5999       http://en.wikipedia.org/wiki/Lance_Stroll  \n",
       "6000  http://en.wikipedia.org/wiki/Stoffel_Vandoorne  \n",
       "6001  http://en.wikipedia.org/wiki/Stoffel_Vandoorne  \n",
       "6002  http://en.wikipedia.org/wiki/Stoffel_Vandoorne  \n",
       "\n",
       "[6003 rows x 11 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = pd.merge(racing_results_df_subset, racing_drivers_df, on=\"driverId\")\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The `on` keyword told it which column to use to match up the rows of the two dataframes.\n",
    "- Note that the first 5 rows have the same `driverId`, so they pulled the same data from `racing_drivers_df`.\n",
    "- Now we could keep only the columns we plan to encode:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>milliseconds</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>5690616.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>5525103.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>5903238.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>5213230.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>7242742.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5998</th>\n",
       "      <td>981</td>\n",
       "      <td>840</td>\n",
       "      <td>4606468.0</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5999</th>\n",
       "      <td>982</td>\n",
       "      <td>840</td>\n",
       "      <td>7445240.0</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6000</th>\n",
       "      <td>976</td>\n",
       "      <td>838</td>\n",
       "      <td>7527733.0</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6001</th>\n",
       "      <td>980</td>\n",
       "      <td>838</td>\n",
       "      <td>5139898.0</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6002</th>\n",
       "      <td>982</td>\n",
       "      <td>838</td>\n",
       "      <td>7433932.0</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6003 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      raceId  driverId  milliseconds        dob nationality\n",
       "0         18         1     5690616.0 1985-01-07     British\n",
       "1         19         1     5525103.0 1985-01-07     British\n",
       "2         21         1     5903238.0 1985-01-07     British\n",
       "3         22         1     5213230.0 1985-01-07     British\n",
       "4         23         1     7242742.0 1985-01-07     British\n",
       "...      ...       ...           ...        ...         ...\n",
       "5998     981       840     4606468.0 1998-10-29    Canadian\n",
       "5999     982       840     7445240.0 1998-10-29    Canadian\n",
       "6000     976       838     7527733.0 1992-03-26     Belgian\n",
       "6001     980       838     5139898.0 1992-03-26     Belgian\n",
       "6002     982       838     7433932.0 1992-03-26     Belgian\n",
       "\n",
       "[6003 rows x 5 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df_subset = merged_df[['raceId', 'driverId', 'milliseconds', 'dob', 'nationality']]\n",
    "merged_df_subset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can process the `dob` column to get age:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>milliseconds</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>5690616.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>5525103.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>5903238.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>5213230.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>7242742.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</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>5998</th>\n",
       "      <td>981</td>\n",
       "      <td>840</td>\n",
       "      <td>4606468.0</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "      <td>23.664056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5999</th>\n",
       "      <td>982</td>\n",
       "      <td>840</td>\n",
       "      <td>7445240.0</td>\n",
       "      <td>1998-10-29</td>\n",
       "      <td>Canadian</td>\n",
       "      <td>23.664056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6000</th>\n",
       "      <td>976</td>\n",
       "      <td>838</td>\n",
       "      <td>7527733.0</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>30.261316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6001</th>\n",
       "      <td>980</td>\n",
       "      <td>838</td>\n",
       "      <td>5139898.0</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>30.261316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6002</th>\n",
       "      <td>982</td>\n",
       "      <td>838</td>\n",
       "      <td>7433932.0</td>\n",
       "      <td>1992-03-26</td>\n",
       "      <td>Belgian</td>\n",
       "      <td>30.261316</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6003 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      raceId  driverId  milliseconds        dob nationality        age\n",
       "0         18         1     5690616.0 1985-01-07     British  37.480494\n",
       "1         19         1     5525103.0 1985-01-07     British  37.480494\n",
       "2         21         1     5903238.0 1985-01-07     British  37.480494\n",
       "3         22         1     5213230.0 1985-01-07     British  37.480494\n",
       "4         23         1     7242742.0 1985-01-07     British  37.480494\n",
       "...      ...       ...           ...        ...         ...        ...\n",
       "5998     981       840     4606468.0 1998-10-29    Canadian  23.664056\n",
       "5999     982       840     7445240.0 1998-10-29    Canadian  23.664056\n",
       "6000     976       838     7527733.0 1992-03-26     Belgian  30.261316\n",
       "6001     980       838     5139898.0 1992-03-26     Belgian  30.261316\n",
       "6002     982       838     7433932.0 1992-03-26     Belgian  30.261316\n",
       "\n",
       "[6003 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ages = (pd.Timestamp.now() - merged_df_subset[\"dob\"]).apply(lambda x: x.total_seconds()/3600/24/365)\n",
    "merged_df_age = merged_df_subset.assign(age=ages)\n",
    "merged_df_age"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- So far we got information for each driver.\n",
    "- Likewise, we can get information about the races, and use those as well."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>round</th>\n",
       "      <th>circuitId</th>\n",
       "      <th>name</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>raceId</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Australian Grand Prix</td>\n",
       "      <td>2009-03-29</td>\n",
       "      <td>06:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2009_Australian_G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Malaysian Grand Prix</td>\n",
       "      <td>2009-04-05</td>\n",
       "      <td>09:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2009_Malaysian_Gr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009</td>\n",
       "      <td>3</td>\n",
       "      <td>17</td>\n",
       "      <td>Chinese Grand Prix</td>\n",
       "      <td>2009-04-19</td>\n",
       "      <td>07:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2009_Chinese_Gran...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>Bahrain Grand Prix</td>\n",
       "      <td>2009-04-26</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2009_Bahrain_Gran...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2009</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>Spanish Grand Prix</td>\n",
       "      <td>2009-05-10</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2009_Spanish_Gran...</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1005</th>\n",
       "      <td>2018</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "      <td>Japanese Grand Prix</td>\n",
       "      <td>2018-10-07</td>\n",
       "      <td>05:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2018_Japanese_Gra...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1006</th>\n",
       "      <td>2018</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>United States Grand Prix</td>\n",
       "      <td>2018-10-21</td>\n",
       "      <td>19:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2018_United_State...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1007</th>\n",
       "      <td>2018</td>\n",
       "      <td>19</td>\n",
       "      <td>32</td>\n",
       "      <td>Mexican Grand Prix</td>\n",
       "      <td>2018-10-28</td>\n",
       "      <td>19:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2018_Mexican_Gran...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1008</th>\n",
       "      <td>2018</td>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>Brazilian Grand Prix</td>\n",
       "      <td>2018-11-11</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2018_Brazilian_Gr...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1009</th>\n",
       "      <td>2018</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>Abu Dhabi Grand Prix</td>\n",
       "      <td>2018-11-25</td>\n",
       "      <td>13:00:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2018_Abu_Dhabi_Gr...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>997 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        year  round  circuitId                      name        date  \\\n",
       "raceId                                                                 \n",
       "1       2009      1          1     Australian Grand Prix  2009-03-29   \n",
       "2       2009      2          2      Malaysian Grand Prix  2009-04-05   \n",
       "3       2009      3         17        Chinese Grand Prix  2009-04-19   \n",
       "4       2009      4          3        Bahrain Grand Prix  2009-04-26   \n",
       "5       2009      5          4        Spanish Grand Prix  2009-05-10   \n",
       "...      ...    ...        ...                       ...         ...   \n",
       "1005    2018     17         22       Japanese Grand Prix  2018-10-07   \n",
       "1006    2018     18         69  United States Grand Prix  2018-10-21   \n",
       "1007    2018     19         32        Mexican Grand Prix  2018-10-28   \n",
       "1008    2018     20         18      Brazilian Grand Prix  2018-11-11   \n",
       "1009    2018     21         24      Abu Dhabi Grand Prix  2018-11-25   \n",
       "\n",
       "            time                                                url  \n",
       "raceId                                                               \n",
       "1       06:00:00  http://en.wikipedia.org/wiki/2009_Australian_G...  \n",
       "2       09:00:00  http://en.wikipedia.org/wiki/2009_Malaysian_Gr...  \n",
       "3       07:00:00  http://en.wikipedia.org/wiki/2009_Chinese_Gran...  \n",
       "4       12:00:00  http://en.wikipedia.org/wiki/2009_Bahrain_Gran...  \n",
       "5       12:00:00  http://en.wikipedia.org/wiki/2009_Spanish_Gran...  \n",
       "...          ...                                                ...  \n",
       "1005    05:00:00  http://en.wikipedia.org/wiki/2018_Japanese_Gra...  \n",
       "1006    19:00:00  http://en.wikipedia.org/wiki/2018_United_State...  \n",
       "1007    19:00:00  http://en.wikipedia.org/wiki/2018_Mexican_Gran...  \n",
       "1008    16:00:00  http://en.wikipedia.org/wiki/2018_Brazilian_Gr...  \n",
       "1009    13:00:00  http://en.wikipedia.org/wiki/2018_Abu_Dhabi_Gr...  \n",
       "\n",
       "[997 rows x 7 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "racing_races_df = pd.read_csv(\"data/formula-1-race-data-19502017/races.csv\", encoding='latin-1', index_col=0)\n",
    "racing_races_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Etc."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- For those who have taken CPSC 304 or some have other database training, you'll recognize this type of multi-table situation, with foreign keys connecting the tables.\n",
    "- `pd.merge` supports several types of joins, see the documentation:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>milliseconds</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "      <th>age</th>\n",
       "      <th>year</th>\n",
       "      <th>round</th>\n",
       "      <th>circuitId</th>\n",
       "      <th>name</th>\n",
       "      <th>date</th>\n",
       "      <th>time</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>5690616.0</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Australian Grand Prix</td>\n",
       "      <td>2008-03-16</td>\n",
       "      <td>04:30:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2008_Australian_G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>5696094.0</td>\n",
       "      <td>1977-05-10</td>\n",
       "      <td>German</td>\n",
       "      <td>45.148988</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Australian Grand Prix</td>\n",
       "      <td>2008-03-16</td>\n",
       "      <td>04:30:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2008_Australian_G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>5698779.0</td>\n",
       "      <td>1985-06-27</td>\n",
       "      <td>German</td>\n",
       "      <td>37.012001</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Australian Grand Prix</td>\n",
       "      <td>2008-03-16</td>\n",
       "      <td>04:30:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2008_Australian_G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>5707797.0</td>\n",
       "      <td>1981-07-29</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>40.927070</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Australian Grand Prix</td>\n",
       "      <td>2008-03-16</td>\n",
       "      <td>04:30:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2008_Australian_G...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>5708630.0</td>\n",
       "      <td>1981-10-19</td>\n",
       "      <td>Finnish</td>\n",
       "      <td>40.702412</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Australian Grand Prix</td>\n",
       "      <td>2008-03-16</td>\n",
       "      <td>04:30:00</td>\n",
       "      <td>http://en.wikipedia.org/wiki/2008_Australian_G...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   raceId  driverId  milliseconds        dob nationality        age  year  \\\n",
       "0      18         1     5690616.0 1985-01-07     British  37.480494  2008   \n",
       "1      18         2     5696094.0 1977-05-10      German  45.148988  2008   \n",
       "2      18         3     5698779.0 1985-06-27      German  37.012001  2008   \n",
       "3      18         4     5707797.0 1981-07-29     Spanish  40.927070  2008   \n",
       "4      18         5     5708630.0 1981-10-19     Finnish  40.702412  2008   \n",
       "\n",
       "   round  circuitId                   name        date      time  \\\n",
       "0      1          1  Australian Grand Prix  2008-03-16  04:30:00   \n",
       "1      1          1  Australian Grand Prix  2008-03-16  04:30:00   \n",
       "2      1          1  Australian Grand Prix  2008-03-16  04:30:00   \n",
       "3      1          1  Australian Grand Prix  2008-03-16  04:30:00   \n",
       "4      1          1  Australian Grand Prix  2008-03-16  04:30:00   \n",
       "\n",
       "                                                 url  \n",
       "0  http://en.wikipedia.org/wiki/2008_Australian_G...  \n",
       "1  http://en.wikipedia.org/wiki/2008_Australian_G...  \n",
       "2  http://en.wikipedia.org/wiki/2008_Australian_G...  \n",
       "3  http://en.wikipedia.org/wiki/2008_Australian_G...  \n",
       "4  http://en.wikipedia.org/wiki/2008_Australian_G...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df_full = pd.merge(merged_df_age, racing_races_df, on=\"raceId\")\n",
    "merged_df_full.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maybe we just take the circuit ID:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>milliseconds</th>\n",
       "      <th>nationality</th>\n",
       "      <th>age</th>\n",
       "      <th>year</th>\n",
       "      <th>round</th>\n",
       "      <th>circuitId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5690616.0</td>\n",
       "      <td>British</td>\n",
       "      <td>37.480494</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5696094.0</td>\n",
       "      <td>German</td>\n",
       "      <td>45.148988</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5698779.0</td>\n",
       "      <td>German</td>\n",
       "      <td>37.012001</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5707797.0</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>40.927070</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5708630.0</td>\n",
       "      <td>Finnish</td>\n",
       "      <td>40.702412</td>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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>5998</th>\n",
       "      <td>10864600.0</td>\n",
       "      <td>Italian</td>\n",
       "      <td>104.014741</td>\n",
       "      <td>1953</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5999</th>\n",
       "      <td>10110300.0</td>\n",
       "      <td>Italian</td>\n",
       "      <td>104.014741</td>\n",
       "      <td>1953</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6000</th>\n",
       "      <td>10278500.0</td>\n",
       "      <td>Italian</td>\n",
       "      <td>113.179125</td>\n",
       "      <td>1953</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6001</th>\n",
       "      <td>10800000.0</td>\n",
       "      <td>Italian</td>\n",
       "      <td>104.014741</td>\n",
       "      <td>1952</td>\n",
       "      <td>4</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6002</th>\n",
       "      <td>9971000.0</td>\n",
       "      <td>Italian</td>\n",
       "      <td>104.014741</td>\n",
       "      <td>1952</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6003 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      milliseconds nationality         age  year  round  circuitId\n",
       "0        5690616.0     British   37.480494  2008      1          1\n",
       "1        5696094.0      German   45.148988  2008      1          1\n",
       "2        5698779.0      German   37.012001  2008      1          1\n",
       "3        5707797.0     Spanish   40.927070  2008      1          1\n",
       "4        5708630.0     Finnish   40.702412  2008      1          1\n",
       "...            ...         ...         ...   ...    ...        ...\n",
       "5998    10864600.0     Italian  104.014741  1953      1         25\n",
       "5999    10110300.0     Italian  104.014741  1953      4         13\n",
       "6000    10278500.0     Italian  113.179125  1953      4         13\n",
       "6001    10800000.0     Italian  104.014741  1952      4         53\n",
       "6002     9971000.0     Italian  104.014741  1952      5          9\n",
       "\n",
       "[6003 rows x 6 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df_final = merged_df_full.drop(\n",
    "    columns=[\"dob\", \"name\", \"date\", \"time\", \"url\", \"raceId\", \"driverId\"])\n",
    "merged_df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "categorical_features = [\"nationality\", \"round\", \"circuitId\"]\n",
    "numeric_features = [\"age\", \"year\"]\n",
    "\n",
    "preprocessing = make_column_transformer(\n",
    "    (OneHotEncoder(handle_unknown='ignore'), categorical_features),\n",
    "    (StandardScaler(), numeric_features)\n",
    ")\n",
    "lr = make_pipeline(preprocessing, Ridge())\n",
    "# pd.DataFrame(cross_validate(lr, merged_df_final.drop(columns=[\"milliseconds\"]), \n",
    "#                             merged_df_final[\"milliseconds\"], return_train_score=True, cv=20)).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "lr.fit(merged_df_final.drop(columns=[\"milliseconds\"]), merged_df_final[\"milliseconds\"]);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This new model can be used even for new drivers and races because it's based on features we got from these other tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Coefficient</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>nationality_American</th>\n",
       "      <td>9.444187e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nationality_Argentine</th>\n",
       "      <td>4.820780e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nationality_Australian</th>\n",
       "      <td>3.168159e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nationality_Austrian</th>\n",
       "      <td>-4.008903e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nationality_Belgian</th>\n",
       "      <td>-3.497972e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circuitId_70</th>\n",
       "      <td>8.110393e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circuitId_71</th>\n",
       "      <td>3.071178e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>circuitId_73</th>\n",
       "      <td>1.340473e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>1.286382e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <td>-6.954938e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>123 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         Coefficient\n",
       "nationality_American    9.444187e+05\n",
       "nationality_Argentine   4.820780e+05\n",
       "nationality_Australian  3.168159e+04\n",
       "nationality_Austrian   -4.008903e+05\n",
       "nationality_Belgian    -3.497972e+05\n",
       "...                              ...\n",
       "circuitId_70            8.110393e+04\n",
       "circuitId_71            3.071178e+05\n",
       "circuitId_73            1.340473e+06\n",
       "age                     1.286382e+05\n",
       "year                   -6.954938e+05\n",
       "\n",
       "[123 rows x 1 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# preprocessing.fit(merged_df_final)\n",
    "ohe_columns = list(\n",
    "    preprocessing.named_transformers_['onehotencoder']\n",
    "    .get_feature_names_out(categorical_features))\n",
    "new_columns = ohe_columns + numeric_features\n",
    "lr_coefs = pd.DataFrame(data=lr[1].coef_, index=new_columns, columns=[\"Coefficient\"])\n",
    "lr_coefs"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python [conda env:cpsc330]",
   "language": "python",
   "name": "conda-env-cpsc330-py"
  },
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}