pit_stop_one_row.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pit stops in one row\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "pit_stops_df = pd.read_csv(\"data/pit_stops.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      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   \n",
      "...      ...       ...   ...  ...       ...      ...           ...   \n",
      "4696    1096       830     1   20  17:33:31   22.735         22735   \n",
      "4697    1096       844     1   21  17:35:10   22.210         22210   \n",
      "4698    1096        20     1   25  17:41:51   23.186         23186   \n",
      "4699    1096       825     1   28  17:46:53   22.387         22387   \n",
      "4700    1096       822     1   30  17:49:54   22.573         22573   \n",
      "\n",
      "      pit_stop_2_lap pit_stop_2_time pit_stop_2_duration   \n",
      "0               17.0        17:31:06              24.463  \\\n",
      "1               17.0        17:32:08              23.988   \n",
      "2               26.0        17:44:29              22.520   \n",
      "3               27.0        17:46:04              24.733   \n",
      "4               31.0        17:52:28              24.500   \n",
      "...              ...             ...                 ...   \n",
      "4696             0.0               0                   0   \n",
      "4697             0.0               0                   0   \n",
      "4698             0.0               0                   0   \n",
      "4699             0.0               0                   0   \n",
      "4700             0.0               0                   0   \n",
      "\n",
      "      pit_stop_2_milliseconds  \n",
      "0                     24463.0  \n",
      "1                     23988.0  \n",
      "2                     22520.0  \n",
      "3                     24733.0  \n",
      "4                     24500.0  \n",
      "...                       ...  \n",
      "4696                      0.0  \n",
      "4697                      0.0  \n",
      "4698                      0.0  \n",
      "4699                      0.0  \n",
      "4700                      0.0  \n",
      "\n",
      "[4701 rows x 11 columns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Replace this with your actual data import method\n",
    "\n",
    "# Create a DataFrame for pit stops with 'stop' equal to 2\n",
    "pit_stop_2_df = pit_stops_df[pit_stops_df['stop'] == 2]\n",
    "\n",
    "# Rename columns in the pit_stop_2_df to avoid conflicts when merging\n",
    "pit_stop_2_df = pit_stop_2_df.rename(columns={\n",
    "    'lap': 'pit_stop_2_lap',\n",
    "    'time': 'pit_stop_2_time',\n",
    "    'duration': 'pit_stop_2_duration',\n",
    "    'milliseconds': 'pit_stop_2_milliseconds'\n",
    "}) \n",
    "\n",
    "# Merge the pit_stop_2_df with the original DataFrame using raceId and driverId\n",
    "merged_df = pit_stops_df.merge(pit_stop_2_df[['raceId', 'driverId', 'pit_stop_2_lap', 'pit_stop_2_time', 'pit_stop_2_duration', 'pit_stop_2_milliseconds']], on=['raceId', 'driverId'], how='left')\n",
    "\n",
    "# Fill NaN values with appropriate values (0 in this case)\n",
    "merged_df = merged_df.fillna(0)\n",
    "\n",
    "# Drop the duplicate rows with the same raceId and driverId\n",
    "merged_df = merged_df.drop_duplicates(subset=['raceId', 'driverId'])\n",
    "\n",
    "# Reset index if needed\n",
    "merged_df = merged_df.reset_index(drop=True)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      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   \n",
      "...      ...       ...   ...  ...       ...      ...           ...   \n",
      "4696    1096       830     1   20  17:33:31   22.735         22735   \n",
      "4697    1096       844     1   21  17:35:10   22.210         22210   \n",
      "4698    1096        20     1   25  17:41:51   23.186         23186   \n",
      "4699    1096       825     1   28  17:46:53   22.387         22387   \n",
      "4700    1096       822     1   30  17:49:54   22.573         22573   \n",
      "\n",
      "      pit_stop_1_lap pit_stop_1_time pit_stop_1_duration  ...   \n",
      "0                1.0        17:05:23              26.898  ...  \\\n",
      "1                1.0        17:05:52              25.021  ...   \n",
      "2               11.0        17:20:48              23.426  ...   \n",
      "3               12.0        17:22:34              23.251  ...   \n",
      "4               13.0        17:24:10              23.842  ...   \n",
      "...              ...             ...                 ...  ...   \n",
      "4696            20.0        17:33:31              22.735  ...   \n",
      "4697            21.0        17:35:10              22.210  ...   \n",
      "4698            25.0        17:41:51              23.186  ...   \n",
      "4699            28.0        17:46:53              22.387  ...   \n",
      "4700            30.0        17:49:54              22.573  ...   \n",
      "\n",
      "      pit_stop_4_duration  pit_stop_4_milliseconds pit_stop_5_lap   \n",
      "0                       0                      0.0            0.0  \\\n",
      "1                       0                      0.0            0.0   \n",
      "2                       0                      0.0            0.0   \n",
      "3                       0                      0.0            0.0   \n",
      "4                       0                      0.0            0.0   \n",
      "...                   ...                      ...            ...   \n",
      "4696                    0                      0.0            0.0   \n",
      "4697                    0                      0.0            0.0   \n",
      "4698                    0                      0.0            0.0   \n",
      "4699                    0                      0.0            0.0   \n",
      "4700                    0                      0.0            0.0   \n",
      "\n",
      "     pit_stop_5_time  pit_stop_5_duration  pit_stop_5_milliseconds   \n",
      "0                  0                    0                      0.0  \\\n",
      "1                  0                    0                      0.0   \n",
      "2                  0                    0                      0.0   \n",
      "3                  0                    0                      0.0   \n",
      "4                  0                    0                      0.0   \n",
      "...              ...                  ...                      ...   \n",
      "4696               0                    0                      0.0   \n",
      "4697               0                    0                      0.0   \n",
      "4698               0                    0                      0.0   \n",
      "4699               0                    0                      0.0   \n",
      "4700               0                    0                      0.0   \n",
      "\n",
      "     pit_stop_6_lap pit_stop_6_time  pit_stop_6_duration   \n",
      "0               0.0               0                    0  \\\n",
      "1               0.0               0                    0   \n",
      "2               0.0               0                    0   \n",
      "3               0.0               0                    0   \n",
      "4               0.0               0                    0   \n",
      "...             ...             ...                  ...   \n",
      "4696            0.0               0                    0   \n",
      "4697            0.0               0                    0   \n",
      "4698            0.0               0                    0   \n",
      "4699            0.0               0                    0   \n",
      "4700            0.0               0                    0   \n",
      "\n",
      "      pit_stop_6_milliseconds  \n",
      "0                         0.0  \n",
      "1                         0.0  \n",
      "2                         0.0  \n",
      "3                         0.0  \n",
      "4                         0.0  \n",
      "...                       ...  \n",
      "4696                      0.0  \n",
      "4697                      0.0  \n",
      "4698                      0.0  \n",
      "4699                      0.0  \n",
      "4700                      0.0  \n",
      "\n",
      "[4701 rows x 31 columns]\n"
     ]
    }
   ],
   "source": [
    "# Define a list of stop values to consider\n",
    "stop_values = [1, 2, 3, 4, 5, 6]\n",
    "\n",
    "# Create an empty DataFrame to store the merged data\n",
    "merged_df = pit_stops_df.copy()\n",
    "\n",
    "# Iterate through stop values and merge the corresponding DataFrames\n",
    "for stop_value in stop_values:\n",
    "    # Filter the DataFrame for the current stop value\n",
    "    pit_stop_df = pit_stops_df[pit_stops_df['stop'] == stop_value]\n",
    "    \n",
    "    # Rename columns to avoid conflicts when merging\n",
    "    pit_stop_df = pit_stop_df.rename(columns={\n",
    "        'lap': f'pit_stop_{stop_value}_lap',\n",
    "        'time': f'pit_stop_{stop_value}_time',\n",
    "        'duration': f'pit_stop_{stop_value}_duration',\n",
    "        'milliseconds': f'pit_stop_{stop_value}_milliseconds'\n",
    "    })\n",
    "\n",
    "    # Merge the current pit stop data with the merged DataFrame\n",
    "    merged_df = merged_df.merge(pit_stop_df[['raceId', 'driverId', f'pit_stop_{stop_value}_lap', f'pit_stop_{stop_value}_time', f'pit_stop_{stop_value}_duration', f'pit_stop_{stop_value}_milliseconds']], on=['raceId', 'driverId'], how='left')\n",
    "\n",
    "# Fill NaN values with appropriate values (0 in this case)\n",
    "merged_df = merged_df.fillna(0)\n",
    "\n",
    "# Drop the duplicate rows with the same raceId and driverId\n",
    "merged_df = merged_df.drop_duplicates(subset=['raceId', 'driverId'])\n",
    "\n",
    "# Reset index if needed\n",
    "merged_df = merged_df.reset_index(drop=True)\n",
    "\n",
    "# Print the resulting DataFrame\n",
    "print(merged_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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",
       "      <th>pit_stop_1_lap</th>\n",
       "      <th>pit_stop_1_time</th>\n",
       "      <th>pit_stop_1_duration</th>\n",
       "      <th>...</th>\n",
       "      <th>pit_stop_4_duration</th>\n",
       "      <th>pit_stop_4_milliseconds</th>\n",
       "      <th>pit_stop_5_lap</th>\n",
       "      <th>pit_stop_5_time</th>\n",
       "      <th>pit_stop_5_duration</th>\n",
       "      <th>pit_stop_5_milliseconds</th>\n",
       "      <th>pit_stop_6_lap</th>\n",
       "      <th>pit_stop_6_time</th>\n",
       "      <th>pit_stop_6_duration</th>\n",
       "      <th>pit_stop_6_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",
       "      <td>1.0</td>\n",
       "      <td>17:05:23</td>\n",
       "      <td>26.898</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <td>1.0</td>\n",
       "      <td>17:05:52</td>\n",
       "      <td>25.021</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <td>11.0</td>\n",
       "      <td>17:20:48</td>\n",
       "      <td>23.426</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <td>12.0</td>\n",
       "      <td>17:22:34</td>\n",
       "      <td>23.251</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <td>13.0</td>\n",
       "      <td>17:24:10</td>\n",
       "      <td>23.842</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\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   \n",
       "\n",
       "   pit_stop_1_lap pit_stop_1_time pit_stop_1_duration  ...   \n",
       "0             1.0        17:05:23              26.898  ...  \\\n",
       "1             1.0        17:05:52              25.021  ...   \n",
       "2            11.0        17:20:48              23.426  ...   \n",
       "3            12.0        17:22:34              23.251  ...   \n",
       "4            13.0        17:24:10              23.842  ...   \n",
       "\n",
       "   pit_stop_4_duration  pit_stop_4_milliseconds pit_stop_5_lap   \n",
       "0                    0                      0.0            0.0  \\\n",
       "1                    0                      0.0            0.0   \n",
       "2                    0                      0.0            0.0   \n",
       "3                    0                      0.0            0.0   \n",
       "4                    0                      0.0            0.0   \n",
       "\n",
       "  pit_stop_5_time  pit_stop_5_duration  pit_stop_5_milliseconds   \n",
       "0               0                    0                      0.0  \\\n",
       "1               0                    0                      0.0   \n",
       "2               0                    0                      0.0   \n",
       "3               0                    0                      0.0   \n",
       "4               0                    0                      0.0   \n",
       "\n",
       "  pit_stop_6_lap pit_stop_6_time  pit_stop_6_duration  pit_stop_6_milliseconds  \n",
       "0            0.0               0                    0                      0.0  \n",
       "1            0.0               0                    0                      0.0  \n",
       "2            0.0               0                    0                      0.0  \n",
       "3            0.0               0                    0                      0.0  \n",
       "4            0.0               0                    0                      0.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4701 entries, 0 to 4700\n",
      "Data columns (total 31 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   raceId                   4701 non-null   int64  \n",
      " 1   driverId                 4701 non-null   int64  \n",
      " 2   stop                     4701 non-null   int64  \n",
      " 3   lap                      4701 non-null   int64  \n",
      " 4   time                     4701 non-null   object \n",
      " 5   duration                 4701 non-null   object \n",
      " 6   milliseconds             4701 non-null   int64  \n",
      " 7   pit_stop_1_lap           4701 non-null   float64\n",
      " 8   pit_stop_1_time          4701 non-null   object \n",
      " 9   pit_stop_1_duration      4701 non-null   object \n",
      " 10  pit_stop_1_milliseconds  4701 non-null   float64\n",
      " 11  pit_stop_2_lap           4701 non-null   float64\n",
      " 12  pit_stop_2_time          4701 non-null   object \n",
      " 13  pit_stop_2_duration      4701 non-null   object \n",
      " 14  pit_stop_2_milliseconds  4701 non-null   float64\n",
      " 15  pit_stop_3_lap           4701 non-null   float64\n",
      " 16  pit_stop_3_time          4701 non-null   object \n",
      " 17  pit_stop_3_duration      4701 non-null   object \n",
      " 18  pit_stop_3_milliseconds  4701 non-null   float64\n",
      " 19  pit_stop_4_lap           4701 non-null   float64\n",
      " 20  pit_stop_4_time          4701 non-null   object \n",
      " 21  pit_stop_4_duration      4701 non-null   object \n",
      " 22  pit_stop_4_milliseconds  4701 non-null   float64\n",
      " 23  pit_stop_5_lap           4701 non-null   float64\n",
      " 24  pit_stop_5_time          4701 non-null   object \n",
      " 25  pit_stop_5_duration      4701 non-null   object \n",
      " 26  pit_stop_5_milliseconds  4701 non-null   float64\n",
      " 27  pit_stop_6_lap           4701 non-null   float64\n",
      " 28  pit_stop_6_time          4701 non-null   object \n",
      " 29  pit_stop_6_duration      4701 non-null   object \n",
      " 30  pit_stop_6_milliseconds  4701 non-null   float64\n",
      "dtypes: float64(12), int64(5), object(14)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "merged_df.info()"
   ]
  }
 ],
 "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.9.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}