final_nb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "List of dfs needed:\n",
    "\n",
    "1. tyre_data_new_df x\n",
    "2. circuits_df x\n",
    "3. drivers_df x\n",
    "4. lap_times_df\n",
    "5. pit_stops_df x\n",
    "6. races_with_dummies (included weather data in dummie variables) x\n",
    "7. results_df x\n",
    "\n",
    "Optional:\n",
    "\n",
    "1. constructor_results_df\n",
    "2. constructors_df\n",
    "3. status_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "circuits_df = pd.read_csv(\"data/circuits.csv\")\n",
    "drivers_df = pd.read_csv(\"data/drivers.csv\")\n",
    "pit_stops_df = pd.read_csv(\"data/pit_stops.csv\")\n",
    "races_with_dummies_df = pd.read_csv(\"data/races_with_dummies.csv\")\n",
    "results_df = pd.read_csv(\"data/results.csv\")\n",
    "tyre_data_ID_df = pd.read_csv(\"data/tyre_data_drRef.csv\")\n",
    "lap_times_df = pd.read_csv(\"data/lap_times.csv\")\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13302 entries, 0 to 13301\n",
      "Data columns (total 12 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   Unnamed: 0  13302 non-null  int64  \n",
      " 1   Driver      13297 non-null  object \n",
      " 2   GP          13302 non-null  object \n",
      " 3   Tyres       13302 non-null  object \n",
      " 4   From        13302 non-null  int64  \n",
      " 5   To          13302 non-null  int64  \n",
      " 6   #Laps       13302 non-null  int64  \n",
      " 7   year        13302 non-null  int64  \n",
      " 8   location    11353 non-null  object \n",
      " 9   raceId      13302 non-null  int64  \n",
      " 10  driverId    13164 non-null  float64\n",
      " 11  driverRef   13297 non-null  object \n",
      "dtypes: float64(1), int64(6), object(5)\n",
      "memory usage: 1.2+ MB\n"
     ]
    }
   ],
   "source": [
    "tyre_data_ID_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Cleaning\n",
    "\n",
    "__Tyre_data_Id_df__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "tyres_mapping = {\n",
    "    'Hiper Blando Usado': 'Hyper Soft',\n",
    "    'Hiper Blando Nuevo': 'Hyper Soft',\n",
    "    'Lluvia Extrema Usado': 'Full Wet'\n",
    "}\n",
    "\n",
    "# Use the replace() method to update the values in the \"tyres\" column\n",
    "tyre_data_ID_df['Tyres'] = tyre_data_ID_df['Tyres'].replace(tyres_mapping)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Unnamed: 0            Driver           GP         Tyres  From  To   \n",
      "0               0     Jenson Button    Australia          Soft     1  16  \\\n",
      "1               1     Jenson Button    Australia        Medium    17  36   \n",
      "2               2     Jenson Button    Australia        Medium    37  58   \n",
      "3               3  Sebastian Vettel    Australia          Soft     1  16   \n",
      "4               4  Sebastian Vettel    Australia          Soft    17  37   \n",
      "...           ...               ...          ...           ...   ...  ..   \n",
      "13297       13297       Zhou Guanyu  Netherlands          Soft    38  60   \n",
      "13298       13298    Logan Sargeant  Netherlands          Soft     1  14   \n",
      "13299       13299    Max Verstappen  Netherlands  Intermediate    65  72   \n",
      "13300       13300      Pierre Gasly  Netherlands          Soft    47  60   \n",
      "13301       13301   Nico Hulkenberg  Netherlands        Medium    17  48   \n",
      "\n",
      "       #Laps  year   location  raceId  driverId   driverRef  \n",
      "0         16  2012   Adelaide     860      18.0      button  \n",
      "1         20  2012   Adelaide     860      18.0      button  \n",
      "2         22  2012   Adelaide     860      18.0      button  \n",
      "3         16  2012   Adelaide     860      20.0      vettel  \n",
      "4         21  2012   Adelaide     860      20.0      vettel  \n",
      "...      ...   ...        ...     ...       ...         ...  \n",
      "13297     23  2023  Zandvoort    1111       NaN      guanyu  \n",
      "13298     14  2023  Zandvoort    1111     858.0    sargeant  \n",
      "13299      8  2023  Zandvoort    1111      50.0  verstappen  \n",
      "13300     14  2023  Zandvoort    1111     842.0       gasly  \n",
      "13301     32  2023  Zandvoort    1111     807.0  hulkenberg  \n",
      "\n",
      "[13302 rows x 12 columns]\n"
     ]
    }
   ],
   "source": [
    "tyre_data_ID_df['Tyres'] = tyre_data_ID_df['Tyres'].str.replace(' Used', '').str.replace(' New', '')\n",
    "\n",
    "# Print the DataFrame to verify the changes\n",
    "print(tyre_data_ID_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Soft', 'Medium', 'Intermediate', 'Wet', 'Hard', 'Full Wet',\n",
       "       'Supersoft', 'Ultrasoft', 'Hyper Soft'], dtype=object)"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tyre_data_ID_df.Tyres.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          Driver         GP  year  location  raceId  driverId driverRef   \n",
      "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
      "\n",
      "   #Laps    Tyres_1     Tyres_2     Tyres_3 From_1 From_2 From_3 To_1 To_2   \n",
      "0     16  Soft Used  Medium New  Medium New      1     17     37   16   36  \\\n",
      "\n",
      "  To_3  \n",
      "0   58  \n"
     ]
    }
   ],
   "source": [
    "tyre_data_ID_df = pd.DataFrame(data)\n",
    "\n",
    "# Split the 'Tyres' column\n",
    "tyre_data_ID_df['Tyres'] = tyre_data_ID_df['Tyres'].str.split(', ')\n",
    "\n",
    "# Split the 'From' column\n",
    "tyre_data_ID_df['From'] = tyre_data_ID_df['From'].str.split(', ')\n",
    "\n",
    "# Split the 'To' column\n",
    "tyre_data_ID_df['To'] = tyre_data_ID_df['To'].str.split(', ')\n",
    "\n",
    "# Expand the columns without specifying 'Tyres' since it no longer exists\n",
    "tyres_columns = tyre_data_ID_df['Tyres'].apply(pd.Series)\n",
    "from_columns = tyre_data_ID_df['From'].apply(pd.Series)\n",
    "to_columns = tyre_data_ID_df['To'].apply(pd.Series)\n",
    "\n",
    "# Rename the expanded columns\n",
    "tyres_columns.columns = [f'Tyres_{i+1}' for i in range(tyres_columns.shape[1])]\n",
    "from_columns.columns = [f'From_{i+1}' for i in range(from_columns.shape[1])]\n",
    "to_columns.columns = [f'To_{i+1}' for i in range(to_columns.shape[1])]\n",
    "\n",
    "# Concatenate the expanded columns with the original DataFrame\n",
    "tyre_data_ID_df = pd.concat([tyre_data_ID_df, tyres_columns, from_columns, to_columns], axis=1)\n",
    "\n",
    "# Drop the original 'Tyres,' 'From,' and 'To' columns\n",
    "tyre_data_ID_df = tyre_data_ID_df.drop(['Tyres', 'From', 'To'], axis=1)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(tyre_data_ID_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>#Laps</th>\n",
       "      <th>Tyres_1</th>\n",
       "      <th>Tyres_2</th>\n",
       "      <th>Tyres_3</th>\n",
       "      <th>From_1</th>\n",
       "      <th>From_2</th>\n",
       "      <th>From_3</th>\n",
       "      <th>To_1</th>\n",
       "      <th>To_2</th>\n",
       "      <th>To_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>16</td>\n",
       "      <td>Soft Used</td>\n",
       "      <td>Medium New</td>\n",
       "      <td>Medium New</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>37</td>\n",
       "      <td>16</td>\n",
       "      <td>36</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP  year  location  raceId  driverId driverRef   \n",
       "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
       "\n",
       "   #Laps    Tyres_1     Tyres_2     Tyres_3 From_1 From_2 From_3 To_1 To_2   \n",
       "0     16  Soft Used  Medium New  Medium New      1     17     37   16   36  \\\n",
       "\n",
       "  To_3  \n",
       "0   58  "
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tyre_data_ID_df.head(40)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Pit_Stops_df__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>stop</th>\n",
       "      <th>lap</th>\n",
       "      <th>time</th>\n",
       "      <th>duration</th>\n",
       "      <th>milliseconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>841</td>\n",
       "      <td>153</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17:05:23</td>\n",
       "      <td>26.898</td>\n",
       "      <td>26898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>841</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17:05:52</td>\n",
       "      <td>25.021</td>\n",
       "      <td>25021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>841</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>17:20:48</td>\n",
       "      <td>23.426</td>\n",
       "      <td>23426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>841</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>17:22:34</td>\n",
       "      <td>23.251</td>\n",
       "      <td>23251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>841</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>17:24:10</td>\n",
       "      <td>23.842</td>\n",
       "      <td>23842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   raceId  driverId  stop  lap      time duration  milliseconds\n",
       "0     841       153     1    1  17:05:23   26.898         26898\n",
       "1     841        30     1    1  17:05:52   25.021         25021\n",
       "2     841        17     1   11  17:20:48   23.426         23426\n",
       "3     841         4     1   12  17:22:34   23.251         23251\n",
       "4     841        13     1   13  17:24:10   23.842         23842"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pit_stops_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 9634 entries, 0 to 9633\n",
      "Data columns (total 7 columns):\n",
      " #   Column        Non-Null Count  Dtype \n",
      "---  ------        --------------  ----- \n",
      " 0   raceId        9634 non-null   int64 \n",
      " 1   driverId      9634 non-null   int64 \n",
      " 2   stop          9634 non-null   int64 \n",
      " 3   lap           9634 non-null   int64 \n",
      " 4   time          9634 non-null   object\n",
      " 5   duration      9634 non-null   object\n",
      " 6   milliseconds  9634 non-null   int64 \n",
      "dtypes: int64(5), object(2)\n",
      "memory usage: 527.0+ KB\n"
     ]
    }
   ],
   "source": [
    "pit_stops_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_pit_stops_df = pit_stops_df.drop(columns=['duration'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>stop</th>\n",
       "      <th>lap</th>\n",
       "      <th>time</th>\n",
       "      <th>milliseconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>841</td>\n",
       "      <td>153</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17:05:23</td>\n",
       "      <td>26898</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>841</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>17:05:52</td>\n",
       "      <td>25021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>841</td>\n",
       "      <td>17</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>17:20:48</td>\n",
       "      <td>23426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>841</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>17:22:34</td>\n",
       "      <td>23251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>841</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>17:24:10</td>\n",
       "      <td>23842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>841</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>17:24:29</td>\n",
       "      <td>23643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>17:25:17</td>\n",
       "      <td>22603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>841</td>\n",
       "      <td>814</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>17:26:03</td>\n",
       "      <td>24863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>841</td>\n",
       "      <td>816</td>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>17:26:50</td>\n",
       "      <td>25259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>841</td>\n",
       "      <td>67</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>17:27:34</td>\n",
       "      <td>25342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>841</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>17:27:41</td>\n",
       "      <td>22994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>841</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:28:24</td>\n",
       "      <td>23227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>841</td>\n",
       "      <td>808</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:28:39</td>\n",
       "      <td>24535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>841</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:29:00</td>\n",
       "      <td>23716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>841</td>\n",
       "      <td>155</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:29:06</td>\n",
       "      <td>24064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>841</td>\n",
       "      <td>16</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:29:08</td>\n",
       "      <td>25978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>841</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:29:49</td>\n",
       "      <td>24899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>841</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>17:30:24</td>\n",
       "      <td>16867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>841</td>\n",
       "      <td>153</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>17:31:06</td>\n",
       "      <td>24463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>841</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>17:31:11</td>\n",
       "      <td>24865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>841</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>17:32:08</td>\n",
       "      <td>23988</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>841</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>17:33:02</td>\n",
       "      <td>23792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>841</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>19</td>\n",
       "      <td>17:33:53</td>\n",
       "      <td>23303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>841</td>\n",
       "      <td>815</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>17:40:27</td>\n",
       "      <td>23438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>841</td>\n",
       "      <td>22</td>\n",
       "      <td>2</td>\n",
       "      <td>23</td>\n",
       "      <td>17:40:45</td>\n",
       "      <td>37856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>841</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>17:44:29</td>\n",
       "      <td>22520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>841</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>17:46:04</td>\n",
       "      <td>24733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>841</td>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>28</td>\n",
       "      <td>17:49:07</td>\n",
       "      <td>16892</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>841</td>\n",
       "      <td>67</td>\n",
       "      <td>2</td>\n",
       "      <td>29</td>\n",
       "      <td>17:49:47</td>\n",
       "      <td>23100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>841</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>17:51:32</td>\n",
       "      <td>25098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>841</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>17:52:28</td>\n",
       "      <td>24500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>841</td>\n",
       "      <td>155</td>\n",
       "      <td>2</td>\n",
       "      <td>32</td>\n",
       "      <td>17:54:21</td>\n",
       "      <td>24192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>36</td>\n",
       "      <td>17:59:17</td>\n",
       "      <td>24036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>841</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>36</td>\n",
       "      <td>17:59:29</td>\n",
       "      <td>23199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>841</td>\n",
       "      <td>153</td>\n",
       "      <td>3</td>\n",
       "      <td>35</td>\n",
       "      <td>17:59:45</td>\n",
       "      <td>26348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>841</td>\n",
       "      <td>808</td>\n",
       "      <td>2</td>\n",
       "      <td>36</td>\n",
       "      <td>17:59:47</td>\n",
       "      <td>25683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>841</td>\n",
       "      <td>814</td>\n",
       "      <td>2</td>\n",
       "      <td>36</td>\n",
       "      <td>18:00:48</td>\n",
       "      <td>24332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>841</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>37</td>\n",
       "      <td>18:01:49</td>\n",
       "      <td>22681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>841</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>37</td>\n",
       "      <td>18:02:15</td>\n",
       "      <td>23871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>841</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "      <td>37</td>\n",
       "      <td>18:03:55</td>\n",
       "      <td>24848</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    raceId  driverId  stop  lap      time  milliseconds\n",
       "0      841       153     1    1  17:05:23         26898\n",
       "1      841        30     1    1  17:05:52         25021\n",
       "2      841        17     1   11  17:20:48         23426\n",
       "3      841         4     1   12  17:22:34         23251\n",
       "4      841        13     1   13  17:24:10         23842\n",
       "5      841        22     1   13  17:24:29         23643\n",
       "6      841        20     1   14  17:25:17         22603\n",
       "7      841       814     1   14  17:26:03         24863\n",
       "8      841       816     1   14  17:26:50         25259\n",
       "9      841        67     1   15  17:27:34         25342\n",
       "10     841         2     1   15  17:27:41         22994\n",
       "11     841         1     1   16  17:28:24         23227\n",
       "12     841       808     1   16  17:28:39         24535\n",
       "13     841         3     1   16  17:29:00         23716\n",
       "14     841       155     1   16  17:29:06         24064\n",
       "15     841        16     1   16  17:29:08         25978\n",
       "16     841        15     1   16  17:29:49         24899\n",
       "17     841        18     1   17  17:30:24         16867\n",
       "18     841       153     2   17  17:31:06         24463\n",
       "19     841         5     1   17  17:31:11         24865\n",
       "20     841        30     2   17  17:32:08         23988\n",
       "21     841        10     1   18  17:33:02         23792\n",
       "22     841        18     2   19  17:33:53         23303\n",
       "23     841       815     1   23  17:40:27         23438\n",
       "24     841        22     2   23  17:40:45         37856\n",
       "25     841        17     2   26  17:44:29         22520\n",
       "26     841         4     2   27  17:46:04         24733\n",
       "27     841        22     3   28  17:49:07         16892\n",
       "28     841        67     2   29  17:49:47         23100\n",
       "29     841         2     2   30  17:51:32         25098\n",
       "30     841        13     2   31  17:52:28         24500\n",
       "31     841       155     2   32  17:54:21         24192\n",
       "32     841        20     2   36  17:59:17         24036\n",
       "33     841         1     2   36  17:59:29         23199\n",
       "34     841       153     3   35  17:59:45         26348\n",
       "35     841       808     2   36  17:59:47         25683\n",
       "36     841       814     2   36  18:00:48         24332\n",
       "37     841        18     3   37  18:01:49         22681\n",
       "38     841        16     2   37  18:02:15         23871\n",
       "39     841        15     2   37  18:03:55         24848"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_pit_stops_df.head(40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      raceId  driverId  stop  lap      time  milliseconds\n",
      "0        841         1     3   52  17:28:24         46426\n",
      "1        841         2     3   45  17:27:41         48092\n",
      "2        841         3     1   16  17:29:00         23716\n",
      "3        841         4     6   81  17:22:34         72165\n",
      "4        841         5     1   17  17:31:11         24865\n",
      "...      ...       ...   ...  ...       ...           ...\n",
      "4696    1096       848     3   47  17:21:53         43591\n",
      "4697    1096       849     3   53  17:26:44         47597\n",
      "4698    1096       852     3   52  17:24:50         43853\n",
      "4699    1096       854     3   48  17:24:59         45806\n",
      "4700    1096       855     3   59  17:26:28         45594\n",
      "\n",
      "[4701 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Assuming you already have new_pit_stops_df\n",
    "\n",
    "# Group by \"raceId\" and \"driverId\" and aggregate the other columns\n",
    "aggregation_functions = {\n",
    "    'stop': 'sum',\n",
    "    'lap': 'sum',\n",
    "    'time': 'first',  # You can choose an aggregation method for time\n",
    "    'milliseconds': 'sum'\n",
    "}\n",
    "\n",
    "# Apply the aggregation functions\n",
    "result_df = new_pit_stops_df.groupby(['raceId', 'driverId']).agg(aggregation_functions).reset_index()\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(result_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>stop</th>\n",
       "      <th>lap</th>\n",
       "      <th>time</th>\n",
       "      <th>milliseconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>841</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "      <td>17:28:24</td>\n",
       "      <td>46426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>841</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>45</td>\n",
       "      <td>17:27:41</td>\n",
       "      <td>48092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>841</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>17:29:00</td>\n",
       "      <td>23716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>841</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>81</td>\n",
       "      <td>17:22:34</td>\n",
       "      <td>72165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>841</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>17:31:11</td>\n",
       "      <td>24865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>841</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>17:33:02</td>\n",
       "      <td>23792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>841</td>\n",
       "      <td>13</td>\n",
       "      <td>6</td>\n",
       "      <td>92</td>\n",
       "      <td>17:24:10</td>\n",
       "      <td>72437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>841</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>53</td>\n",
       "      <td>17:29:49</td>\n",
       "      <td>49747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>841</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>53</td>\n",
       "      <td>17:29:08</td>\n",
       "      <td>49849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>841</td>\n",
       "      <td>17</td>\n",
       "      <td>6</td>\n",
       "      <td>78</td>\n",
       "      <td>17:20:48</td>\n",
       "      <td>72176</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>841</td>\n",
       "      <td>18</td>\n",
       "      <td>6</td>\n",
       "      <td>73</td>\n",
       "      <td>17:30:24</td>\n",
       "      <td>62851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>17:25:17</td>\n",
       "      <td>46639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>841</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>104</td>\n",
       "      <td>17:24:29</td>\n",
       "      <td>104700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>841</td>\n",
       "      <td>30</td>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>17:05:52</td>\n",
       "      <td>49009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>841</td>\n",
       "      <td>67</td>\n",
       "      <td>3</td>\n",
       "      <td>44</td>\n",
       "      <td>17:27:34</td>\n",
       "      <td>48442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>841</td>\n",
       "      <td>153</td>\n",
       "      <td>6</td>\n",
       "      <td>53</td>\n",
       "      <td>17:05:23</td>\n",
       "      <td>77709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>841</td>\n",
       "      <td>155</td>\n",
       "      <td>3</td>\n",
       "      <td>48</td>\n",
       "      <td>17:29:06</td>\n",
       "      <td>48256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>841</td>\n",
       "      <td>808</td>\n",
       "      <td>3</td>\n",
       "      <td>52</td>\n",
       "      <td>17:28:39</td>\n",
       "      <td>50218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>841</td>\n",
       "      <td>814</td>\n",
       "      <td>3</td>\n",
       "      <td>50</td>\n",
       "      <td>17:26:03</td>\n",
       "      <td>49195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>841</td>\n",
       "      <td>815</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>17:40:27</td>\n",
       "      <td>23438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    raceId  driverId  stop  lap      time  milliseconds\n",
       "0      841         1     3   52  17:28:24         46426\n",
       "1      841         2     3   45  17:27:41         48092\n",
       "2      841         3     1   16  17:29:00         23716\n",
       "3      841         4     6   81  17:22:34         72165\n",
       "4      841         5     1   17  17:31:11         24865\n",
       "5      841        10     1   18  17:33:02         23792\n",
       "6      841        13     6   92  17:24:10         72437\n",
       "7      841        15     3   53  17:29:49         49747\n",
       "8      841        16     3   53  17:29:08         49849\n",
       "9      841        17     6   78  17:20:48         72176\n",
       "10     841        18     6   73  17:30:24         62851\n",
       "11     841        20     3   50  17:25:17         46639\n",
       "12     841        22    10  104  17:24:29        104700\n",
       "13     841        30     3   18  17:05:52         49009\n",
       "14     841        67     3   44  17:27:34         48442\n",
       "15     841       153     6   53  17:05:23         77709\n",
       "16     841       155     3   48  17:29:06         48256\n",
       "17     841       808     3   52  17:28:39         50218\n",
       "18     841       814     3   50  17:26:03         49195\n",
       "19     841       815     1   23  17:40:27         23438"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>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",
       "      <th>fp1_date</th>\n",
       "      <th>fp1_time</th>\n",
       "      <th>...</th>\n",
       "      <th>quali_time</th>\n",
       "      <th>sprint_date</th>\n",
       "      <th>sprint_time</th>\n",
       "      <th>weather_category</th>\n",
       "      <th>weather_cloudy</th>\n",
       "      <th>weather_cold</th>\n",
       "      <th>weather_dry</th>\n",
       "      <th>weather_unknown</th>\n",
       "      <th>weather_warm</th>\n",
       "      <th>weather_wet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\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",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>weather_warm</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\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",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>weather_dry</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\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",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>weather_wet</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\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",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>weather_warm</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\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",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>weather_warm</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   raceId  year  round  circuitId                   name        date   \n",
       "0       1  2009      1          1  Australian Grand Prix  2009-03-29  \\\n",
       "1       2  2009      2          2   Malaysian Grand Prix  2009-04-05   \n",
       "2       3  2009      3         17     Chinese Grand Prix  2009-04-19   \n",
       "3       4  2009      4          3     Bahrain Grand Prix  2009-04-26   \n",
       "4       5  2009      5          4     Spanish Grand Prix  2009-05-10   \n",
       "\n",
       "       time                                                url fp1_date   \n",
       "0  06:00:00  http://en.wikipedia.org/wiki/2009_Australian_G...       \\N  \\\n",
       "1  09:00:00  http://en.wikipedia.org/wiki/2009_Malaysian_Gr...       \\N   \n",
       "2  07:00:00  http://en.wikipedia.org/wiki/2009_Chinese_Gran...       \\N   \n",
       "3  12:00:00  http://en.wikipedia.org/wiki/2009_Bahrain_Gran...       \\N   \n",
       "4  12:00:00  http://en.wikipedia.org/wiki/2009_Spanish_Gran...       \\N   \n",
       "\n",
       "  fp1_time  ... quali_time sprint_date sprint_time weather_category   \n",
       "0       \\N  ...         \\N          \\N          \\N     weather_warm  \\\n",
       "1       \\N  ...         \\N          \\N          \\N      weather_dry   \n",
       "2       \\N  ...         \\N          \\N          \\N      weather_wet   \n",
       "3       \\N  ...         \\N          \\N          \\N     weather_warm   \n",
       "4       \\N  ...         \\N          \\N          \\N     weather_warm   \n",
       "\n",
       "  weather_cloudy weather_cold weather_dry weather_unknown weather_warm   \n",
       "0          False        False       False           False         True  \\\n",
       "1          False        False        True           False        False   \n",
       "2          False        False       False           False        False   \n",
       "3          False        False       False           False         True   \n",
       "4          False        False       False           False         True   \n",
       "\n",
       "   weather_wet  \n",
       "0        False  \n",
       "1        False  \n",
       "2         True  \n",
       "3        False  \n",
       "4        False  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races_with_dummies_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      raceId  driverId             Driver           GP         Tyres  From   \n",
      "0        860       1.0     Lewis Hamilton    Australia          Soft     1  \\\n",
      "1        860       3.0       Nico Rosberg    Australia          Soft     1   \n",
      "2        860       4.0    Fernando Alonso    Australia        Medium     1   \n",
      "3        860       5.0  Heikki Kovalainen    Australia          Soft     1   \n",
      "4        860       8.0     Kimi Räikkönen    Australia          Soft     1   \n",
      "...      ...       ...                ...          ...           ...   ...   \n",
      "4533    1111     847.0     George Russell  Netherlands          Soft     1   \n",
      "4534    1111     848.0    Alexander Albon  Netherlands          Soft     1   \n",
      "4535    1111     852.0       Yuki Tsunoda  Netherlands          Soft     1   \n",
      "4536    1111     857.0      Oscar Piastri  Netherlands  Intermediate     1   \n",
      "4537    1111     858.0     Logan Sargeant  Netherlands          Soft     1   \n",
      "\n",
      "      To  #Laps  year   location       driverRef  \n",
      "0     58     58  2012   Adelaide        hamilton  \n",
      "1     58     58  2012   Adelaide         rosberg  \n",
      "2     58     58  2012   Adelaide          alonso  \n",
      "3     38     38  2012   Adelaide      kovalainen  \n",
      "4     58     58  2012   Adelaide  kimi raikkonen  \n",
      "...   ..    ...   ...        ...             ...  \n",
      "4533  72     72  2023  Zandvoort         russell  \n",
      "4534  72     72  2023  Zandvoort           albon  \n",
      "4535  72     72  2023  Zandvoort         tsunoda  \n",
      "4536  72     72  2023  Zandvoort         piastri  \n",
      "4537  14     14  2023  Zandvoort        sargeant  \n",
      "\n",
      "[4538 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Assuming you have read the CSV file into a DataFrame named 'df'\n",
    "# Replace 'your_file.csv' with the actual path to your CSV file\n",
    "# df = pd.read_csv('your_file.csv')\n",
    "\n",
    "# Group by \"raceId\" and \"driverId\" (in this case, \"Jenson Button\")\n",
    "grouped = tyre_data_ID_df.groupby(['raceId', 'driverId'])\n",
    "\n",
    "# Define an aggregation function for each column (you can choose how to aggregate)\n",
    "aggregation = {\n",
    "    'Driver': 'first',   # Take the first driver's name\n",
    "    'GP': 'first',       # Take the first GP value\n",
    "    'Tyres': 'first',    # Take the first Tyres value\n",
    "    'From': 'min',       # Take the minimum value of 'From'\n",
    "    'To': 'max',         # Take the maximum value of 'To'\n",
    "    '#Laps': 'sum',      # Sum of '#Laps'\n",
    "    'year': 'first',     # Take the first year\n",
    "    'location': 'first', # Take the first location\n",
    "    'driverRef': 'first' # Take the first driverRef\n",
    "}\n",
    "\n",
    "# Apply the aggregation\n",
    "transformed_df = grouped.agg(aggregation).reset_index()\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(transformed_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>Tyres</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>#Laps</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Soft, Medium, Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>Intermediate, Wet, Intermediate, Intermediate,...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>2012</td>\n",
       "      <td>Kuala Lumpur</td>\n",
       "      <td>861</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>China</td>\n",
       "      <td>Soft, Soft, Medium, Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>2012</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>862</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Bahrain</td>\n",
       "      <td>Soft, Medium, Medium, Medium, Soft</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2012</td>\n",
       "      <td>Sakhir</td>\n",
       "      <td>863</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Spain</td>\n",
       "      <td>Soft, Hard, Soft, Hard</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9</td>\n",
       "      <td>2012</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>864</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP   \n",
       "0  Jenson Button  Australia  \\\n",
       "1  Jenson Button   Malaysia   \n",
       "2  Jenson Button      China   \n",
       "3  Jenson Button    Bahrain   \n",
       "4  Jenson Button      Spain   \n",
       "\n",
       "                                               Tyres  From    To  #Laps  year   \n",
       "0                               Soft, Medium, Medium  None  None     16  2012  \\\n",
       "1  Intermediate, Wet, Intermediate, Intermediate,...  None  None      4  2012   \n",
       "2                         Soft, Soft, Medium, Medium  None  None     11  2012   \n",
       "3                 Soft, Medium, Medium, Medium, Soft  None  None      8  2012   \n",
       "4                             Soft, Hard, Soft, Hard  None  None      9  2012   \n",
       "\n",
       "       location  raceId  driverId driverRef  \n",
       "0      Adelaide     860      18.0    button  \n",
       "1  Kuala Lumpur     861      18.0    button  \n",
       "2      Shanghai     862      18.0    button  \n",
       "3        Sakhir     863      18.0    button  \n",
       "4     Barcelona     864      18.0    button  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Assuming you have read the CSV file into a DataFrame named 'df'\n",
    "# Replace 'your_file.csv' with the actual path to your CSV file\n",
    "# df = pd.read_csv('your_file.csv')\n",
    "\n",
    "# Filter rows for Jenson Button\n",
    "jenson_button_df = tyre_data_ID_df[tyre_data_ID_df['Driver'] == 'Jenson Button']\n",
    "\n",
    "# Create an empty list to store DataFrames\n",
    "dfs_to_concat = []\n",
    "\n",
    "# Iterate through unique raceId and driverId combinations\n",
    "for race_id, driver_id in jenson_button_df[['raceId', 'driverId']].drop_duplicates().itertuples(index=False):\n",
    "    race_driver_df = jenson_button_df[(jenson_button_df['raceId'] == race_id) & (jenson_button_df['driverId'] == driver_id)]\n",
    "    \n",
    "    # Group by 'GP' and concatenate the different tire types\n",
    "    tire_types = ', '.join(race_driver_df['Tyres'])\n",
    "    \n",
    "    # Take the first value for other columns (assumes they are consistent for the same race and driver)\n",
    "    first_values = race_driver_df.iloc[0]\n",
    "    \n",
    "    # Create a new DataFrame for the row\n",
    "    new_row_df = pd.DataFrame({\n",
    "        'Driver': [first_values['Driver']],\n",
    "        'GP': [first_values['GP']],\n",
    "        'Tyres': [tire_types],  # Concatenated tire types\n",
    "        'From': [None],  # You can set this to None or adjust as needed\n",
    "        'To': [None],  # You can set this to None or adjust as needed\n",
    "        '#Laps': [first_values['#Laps']],\n",
    "        'year': [first_values['year']],\n",
    "        'location': [first_values['location']],\n",
    "        'raceId': [race_id],\n",
    "        'driverId': [driver_id],\n",
    "        'driverRef': [first_values['driverRef']]\n",
    "    })\n",
    "    \n",
    "    # Append the new row DataFrame to the list\n",
    "    dfs_to_concat.append(new_row_df)\n",
    "\n",
    "# Concatenate all the DataFrames in the list into one\n",
    "transformed_df = pd.concat(dfs_to_concat, ignore_index=True)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "transformed_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>#Laps</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>Soft</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>2012</td>\n",
       "      <td>Kuala Lumpur</td>\n",
       "      <td>861</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>Intermediate</td>\n",
       "      <td>Wet</td>\n",
       "      <td>Intermediate</td>\n",
       "      <td>Intermediate</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>China</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>2012</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>862</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>Soft</td>\n",
       "      <td>Soft</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Bahrain</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2012</td>\n",
       "      <td>Sakhir</td>\n",
       "      <td>863</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>Soft</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Medium</td>\n",
       "      <td>Soft</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Spain</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9</td>\n",
       "      <td>2012</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>864</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>Soft</td>\n",
       "      <td>Hard</td>\n",
       "      <td>Soft</td>\n",
       "      <td>Hard</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP  From    To  #Laps  year      location  raceId   \n",
       "0  Jenson Button  Australia  None  None     16  2012      Adelaide     860  \\\n",
       "1  Jenson Button   Malaysia  None  None      4  2012  Kuala Lumpur     861   \n",
       "2  Jenson Button      China  None  None     11  2012      Shanghai     862   \n",
       "3  Jenson Button    Bahrain  None  None      8  2012        Sakhir     863   \n",
       "4  Jenson Button      Spain  None  None      9  2012     Barcelona     864   \n",
       "\n",
       "   driverId driverRef             0       1             2             3   \n",
       "0      18.0    button          Soft  Medium        Medium          None  \\\n",
       "1      18.0    button  Intermediate     Wet  Intermediate  Intermediate   \n",
       "2      18.0    button          Soft    Soft        Medium        Medium   \n",
       "3      18.0    button          Soft  Medium        Medium        Medium   \n",
       "4      18.0    button          Soft    Hard          Soft          Hard   \n",
       "\n",
       "        4     5     6  \n",
       "0    None  None  None  \n",
       "1  Medium  None  None  \n",
       "2    None  None  None  \n",
       "3    Soft  None  None  \n",
       "4    None  None  None  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Assuming you have a DataFrame named 'transformed_df'\n",
    "# Split the values in the 'Tyres' column by ',' and expand them into separate columns\n",
    "tire_type_columns = transformed_df['Tyres'].str.split(', ', expand=True)\n",
    "\n",
    "# Concatenate the tire type columns with the original DataFrame\n",
    "transformed_df = pd.concat([transformed_df, tire_type_columns], axis=1)\n",
    "\n",
    "# Drop the original 'Tyres' column\n",
    "transformed_df = transformed_df.drop('Tyres', axis=1)\n",
    "\n",
    "transformed_df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>Tyres</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>#Laps</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Soft</td>\n",
       "      <td>1-16, 17-36, 37-58</td>\n",
       "      <td>None</td>\n",
       "      <td>16</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Kuala Lumpur</td>\n",
       "      <td>Soft</td>\n",
       "      <td>1-4, 5-13, 14-24, 25-39, 40-56</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>2012</td>\n",
       "      <td>Kuala Lumpur</td>\n",
       "      <td>861</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>Soft</td>\n",
       "      <td>1-11, 12-24, 25-39, 40-56</td>\n",
       "      <td>None</td>\n",
       "      <td>11</td>\n",
       "      <td>2012</td>\n",
       "      <td>Shanghai</td>\n",
       "      <td>862</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Sakhir</td>\n",
       "      <td>Soft</td>\n",
       "      <td>1-8, 9-22, 23-37, 38-53, 54-55</td>\n",
       "      <td>None</td>\n",
       "      <td>8</td>\n",
       "      <td>2012</td>\n",
       "      <td>Sakhir</td>\n",
       "      <td>863</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>Soft</td>\n",
       "      <td>1-9, 10-25, 26-38, 39-66</td>\n",
       "      <td>None</td>\n",
       "      <td>9</td>\n",
       "      <td>2012</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>864</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver            GP Tyres                            From    To   \n",
       "0  Jenson Button      Adelaide  Soft              1-16, 17-36, 37-58  None  \\\n",
       "1  Jenson Button  Kuala Lumpur  Soft  1-4, 5-13, 14-24, 25-39, 40-56  None   \n",
       "2  Jenson Button      Shanghai  Soft       1-11, 12-24, 25-39, 40-56  None   \n",
       "3  Jenson Button        Sakhir  Soft  1-8, 9-22, 23-37, 38-53, 54-55  None   \n",
       "4  Jenson Button     Barcelona  Soft        1-9, 10-25, 26-38, 39-66  None   \n",
       "\n",
       "   #Laps  year      location  raceId  driverId driverRef  \n",
       "0     16  2012      Adelaide     860      18.0    button  \n",
       "1      4  2012  Kuala Lumpur     861      18.0    button  \n",
       "2     11  2012      Shanghai     862      18.0    button  \n",
       "3      8  2012        Sakhir     863      18.0    button  \n",
       "4      9  2012     Barcelona     864      18.0    button  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Sample DataFrame\n",
    "data = {\n",
    "    'From': ['1-16, 17-36, 37-58', '1-4, 5-13, 14-24, 25-39, 40-56', '1-11, 12-24, 25-39, 40-56', '1-8, 9-22, 23-37, 38-53, 54-55', '1-9, 10-25, 26-38, 39-66'],\n",
    "    'To': [None, None, None, None, None],\n",
    "    '#Laps': [16, 4, 11, 8, 9],\n",
    "    'year': [2012, 2012, 2012, 2012, 2012],\n",
    "    'location': ['Adelaide', 'Kuala Lumpur', 'Shanghai', 'Sakhir', 'Barcelona'],\n",
    "    'raceId': [860, 861, 862, 863, 864],\n",
    "    'driverId': [18.0, 18.0, 18.0, 18.0, 18.0],\n",
    "    'driverRef': ['button', 'button', 'button', 'button', 'button']\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Add Driver and GP columns\n",
    "df['Driver'] = 'Jenson Button'\n",
    "df['GP'] = df['location']  # Assuming 'location' is the GP name\n",
    "\n",
    "# Add Tyres column (you can replace 'Soft' with the actual tire type if needed)\n",
    "df['Tyres'] = 'Soft'\n",
    "\n",
    "# Rearrange the columns if necessary\n",
    "df = df[['Driver', 'GP', 'Tyres', 'From', 'To', '#Laps', 'year', 'location', 'raceId', 'driverId', 'driverRef']]\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>Tyres</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>#Laps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>Soft Used, Medium New, Medium New</td>\n",
       "      <td>1, 17, 37</td>\n",
       "      <td>16, 36, 58</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP  year  location  raceId  driverId driverRef   \n",
       "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
       "\n",
       "                               Tyres       From          To  #Laps  \n",
       "0  Soft Used, Medium New, Medium New  1, 17, 37  16, 36, 58     16  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Sample DataFrame\n",
    "data = {\n",
    "    'Driver': ['Jenson Button', 'Jenson Button', 'Jenson Button'],\n",
    "    'GP': ['Australia', 'Australia', 'Australia'],\n",
    "    'Tyres': ['Soft Used', 'Medium New', 'Medium New'],\n",
    "    'From': [1, 17, 37],\n",
    "    'To': [16, 36, 58],\n",
    "    '#Laps': [16, 20, 22],\n",
    "    'year': [2012, 2012, 2012],\n",
    "    'location': ['Adelaide', 'Adelaide', 'Adelaide'],\n",
    "    'raceId': [860, 860, 860],\n",
    "    'driverId': [18.0, 18.0, 18.0],\n",
    "    'driverRef': ['button', 'button', 'button']\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Convert 'From' and 'To' columns to strings\n",
    "df['From'] = df['From'].astype(str)\n",
    "df['To'] = df['To'].astype(str)\n",
    "\n",
    "# Aggregate duplicate rows by concatenating values\n",
    "df = df.groupby(['Driver', 'GP', 'year', 'location', 'raceId', 'driverId', 'driverRef']).agg({\n",
    "    'Tyres': ', '.join,\n",
    "    'From': ', '.join,\n",
    "    'To': ', '.join,\n",
    "    '#Laps': 'first'  # Use 'first' to keep one value\n",
    "}).reset_index()\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>Tyres</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>#Laps</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>[Soft Used, Medium New, Medium New]</td>\n",
       "      <td>[1, 17, 37]</td>\n",
       "      <td>[16, 36, 58]</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP  year  location  raceId  driverId driverRef   \n",
       "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
       "\n",
       "                                 Tyres         From            To  #Laps  \n",
       "0  [Soft Used, Medium New, Medium New]  [1, 17, 37]  [16, 36, 58]     16  "
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Sample DataFrame\n",
    "data = {\n",
    "    'Driver': ['Jenson Button'],\n",
    "    'GP': ['Australia'],\n",
    "    'year': [2012],\n",
    "    'location': ['Adelaide'],\n",
    "    'raceId': [860],\n",
    "    'driverId': [18.0],\n",
    "    'driverRef': ['button'],\n",
    "    'Tyres': ['Soft Used, Medium New, Medium New'],\n",
    "    'From': ['1, 17, 37'],\n",
    "    'To': ['16, 36, 58'],\n",
    "    '#Laps': [16]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Split the 'Tyres' column\n",
    "df['Tyres'] = df['Tyres'].str.split(', ')\n",
    "\n",
    "# Split the 'From' column\n",
    "df['From'] = df['From'].str.split(', ')\n",
    "\n",
    "# Split the 'To' column\n",
    "df['To'] = df['To'].str.split(', ')\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          Driver         GP  year  location  raceId  driverId driverRef   \n",
      "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
      "\n",
      "   #Laps    Tyres_1     Tyres_2     Tyres_3 From_1 From_2 From_3 To_1 To_2   \n",
      "0     16  Soft Used  Medium New  Medium New      1     17     37   16   36  \\\n",
      "\n",
      "  To_3  \n",
      "0   58  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Sample DataFrame\n",
    "data = {\n",
    "    'Driver': ['Jenson Button'],\n",
    "    'GP': ['Australia'],\n",
    "    'year': [2012],\n",
    "    'location': ['Adelaide'],\n",
    "    'raceId': [860],\n",
    "    'driverId': [18.0],\n",
    "    'driverRef': ['button'],\n",
    "    'Tyres': ['Soft Used, Medium New, Medium New'],\n",
    "    'From': ['1, 17, 37'],\n",
    "    'To': ['16, 36, 58'],\n",
    "    '#Laps': [16]\n",
    "}\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Split the 'Tyres' column\n",
    "df['Tyres'] = df['Tyres'].str.split(', ')\n",
    "\n",
    "# Split the 'From' column\n",
    "df['From'] = df['From'].str.split(', ')\n",
    "\n",
    "# Split the 'To' column\n",
    "df['To'] = df['To'].str.split(', ')\n",
    "\n",
    "# Expand the columns without specifying 'Tyres' since it no longer exists\n",
    "tyres_columns = df['Tyres'].apply(pd.Series)\n",
    "from_columns = df['From'].apply(pd.Series)\n",
    "to_columns = df['To'].apply(pd.Series)\n",
    "\n",
    "# Rename the expanded columns\n",
    "tyres_columns.columns = [f'Tyres_{i+1}' for i in range(tyres_columns.shape[1])]\n",
    "from_columns.columns = [f'From_{i+1}' for i in range(from_columns.shape[1])]\n",
    "to_columns.columns = [f'To_{i+1}' for i in range(to_columns.shape[1])]\n",
    "\n",
    "# Concatenate the expanded columns with the original DataFrame\n",
    "df = pd.concat([df, tyres_columns, from_columns, to_columns], axis=1)\n",
    "\n",
    "# Drop the original 'Tyres,' 'From,' and 'To' columns\n",
    "df = df.drop(['Tyres', 'From', 'To'], axis=1)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>#Laps</th>\n",
       "      <th>Tyres_1</th>\n",
       "      <th>Tyres_2</th>\n",
       "      <th>Tyres_3</th>\n",
       "      <th>From_1</th>\n",
       "      <th>From_2</th>\n",
       "      <th>From_3</th>\n",
       "      <th>To_1</th>\n",
       "      <th>To_2</th>\n",
       "      <th>To_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>16</td>\n",
       "      <td>Soft Used</td>\n",
       "      <td>Medium New</td>\n",
       "      <td>Medium New</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>37</td>\n",
       "      <td>16</td>\n",
       "      <td>36</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP  year  location  raceId  driverId driverRef   \n",
       "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
       "\n",
       "   #Laps    Tyres_1     Tyres_2     Tyres_3 From_1 From_2 From_3 To_1 To_2   \n",
       "0     16  Soft Used  Medium New  Medium New      1     17     37   16   36  \\\n",
       "\n",
       "  To_3  \n",
       "0   58  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(data)\n",
    "\n",
    "# Split the 'Tyres' column\n",
    "df['Tyres'] = df['Tyres'].str.split(', ')\n",
    "\n",
    "# Split the 'From' column\n",
    "df['From'] = df['From'].str.split(', ')\n",
    "\n",
    "# Split the 'To' column\n",
    "df['To'] = df['To'].str.split(', ')\n",
    "\n",
    "# Expand the columns without specifying 'Tyres' since it no longer exists\n",
    "tyres_columns = df['Tyres'].apply(pd.Series)\n",
    "from_columns = df['From'].apply(pd.Series)\n",
    "to_columns = df['To'].apply(pd.Series)\n",
    "\n",
    "# Rename the expanded columns\n",
    "tyres_columns.columns = [f'Tyres_{i+1}' for i in range(tyres_columns.shape[1])]\n",
    "from_columns.columns = [f'From_{i+1}' for i in range(from_columns.shape[1])]\n",
    "to_columns.columns = [f'To_{i+1}' for i in range(to_columns.shape[1])]\n",
    "\n",
    "# Concatenate the expanded columns with the original DataFrame\n",
    "df = pd.concat([df, tyres_columns, from_columns, to_columns], axis=1)\n",
    "\n",
    "# Drop the original 'Tyres,' 'From,' and 'To' columns\n",
    "df = df.drop(['Tyres', 'From', 'To'], axis=1)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tyre_data_ID_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          Driver         GP  year  location  raceId  driverId driverRef   \n",
      "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
      "\n",
      "   #Laps    Tyres_1     Tyres_2     Tyres_3 From_1 From_2 From_3 To_1 To_2   \n",
      "0     16  Soft Used  Medium New  Medium New      1     17     37   16   36  \\\n",
      "\n",
      "  To_3  \n",
      "0   58  \n"
     ]
    }
   ],
   "source": [
    "tyre_data_ID_df = pd.DataFrame(data)\n",
    "\n",
    "# Split the 'Tyres' column\n",
    "tyre_data_ID_df['Tyres'] = tyre_data_ID_df['Tyres'].str.split(', ')\n",
    "\n",
    "# Split the 'From' column\n",
    "tyre_data_ID_df['From'] = tyre_data_ID_df['From'].str.split(', ')\n",
    "\n",
    "# Split the 'To' column\n",
    "tyre_data_ID_df['To'] = tyre_data_ID_df['To'].str.split(', ')\n",
    "\n",
    "# Expand the columns without specifying 'Tyres' since it no longer exists\n",
    "tyres_columns = tyre_data_ID_df['Tyres'].apply(pd.Series)\n",
    "from_columns = tyre_data_ID_df['From'].apply(pd.Series)\n",
    "to_columns = tyre_data_ID_df['To'].apply(pd.Series)\n",
    "\n",
    "# Rename the expanded columns\n",
    "tyres_columns.columns = [f'Tyres_{i+1}' for i in range(tyres_columns.shape[1])]\n",
    "from_columns.columns = [f'From_{i+1}' for i in range(from_columns.shape[1])]\n",
    "to_columns.columns = [f'To_{i+1}' for i in range(to_columns.shape[1])]\n",
    "\n",
    "# Concatenate the expanded columns with the original DataFrame\n",
    "tyre_data_ID_df = pd.concat([tyre_data_ID_df, tyres_columns, from_columns, to_columns], axis=1)\n",
    "\n",
    "# Drop the original 'Tyres,' 'From,' and 'To' columns\n",
    "tyre_data_ID_df = tyre_data_ID_df.drop(['Tyres', 'From', 'To'], axis=1)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(tyre_data_ID_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>Driver</th>\n",
       "      <th>GP</th>\n",
       "      <th>year</th>\n",
       "      <th>location</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>#Laps</th>\n",
       "      <th>Tyres_1</th>\n",
       "      <th>Tyres_2</th>\n",
       "      <th>Tyres_3</th>\n",
       "      <th>From_1</th>\n",
       "      <th>From_2</th>\n",
       "      <th>From_3</th>\n",
       "      <th>To_1</th>\n",
       "      <th>To_2</th>\n",
       "      <th>To_3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jenson Button</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2012</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>860</td>\n",
       "      <td>18.0</td>\n",
       "      <td>button</td>\n",
       "      <td>16</td>\n",
       "      <td>Soft Used</td>\n",
       "      <td>Medium New</td>\n",
       "      <td>Medium New</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>37</td>\n",
       "      <td>16</td>\n",
       "      <td>36</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Driver         GP  year  location  raceId  driverId driverRef   \n",
       "0  Jenson Button  Australia  2012  Adelaide     860      18.0    button  \\\n",
       "\n",
       "   #Laps    Tyres_1     Tyres_2     Tyres_3 From_1 From_2 From_3 To_1 To_2   \n",
       "0     16  Soft Used  Medium New  Medium New      1     17     37   16   36  \\\n",
       "\n",
       "  To_3  \n",
       "0   58  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tyre_data_ID_df.head(50)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}