data_cleansing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. **Normalización de los dataset tipo json a csv.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**constructors.json**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "constructors = pd.read_json(r\"./datasets_raw/constructors.json\",\n",
    "                             lines = True, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "constructorId      int64\n",
       "constructorRef    object\n",
       "name              object\n",
       "nationality       object\n",
       "url               object\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(constructors.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(211, 5)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "constructors.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in ID Column\n",
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicates in Id column\n",
    "print(f'{constructors.constructorId.duplicated().sum()} duplicates in ID Column')\n",
    "# Checking duplicatines in dataframe\n",
    "print(f'{constructors.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "constructorId     0\n",
       "constructorRef    0\n",
       "name              0\n",
       "nationality       0\n",
       "url               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking null data\n",
    "constructors.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change .json to csv\n",
    "constructors.to_csv('constructor.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**drivers.json**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "drivers = pd.read_json(r\"./datasets_raw/drivers.json\",\n",
    "                     encoding = 'utf-8', lines = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "driverId        int64\n",
       "driverRef      object\n",
       "number         object\n",
       "code           object\n",
       "name           object\n",
       "dob            object\n",
       "nationality    object\n",
       "url            object\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(drivers.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "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>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>number</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>hamilton</td>\n",
       "      <td>44</td>\n",
       "      <td>HAM</td>\n",
       "      <td>{'forename': 'Lewis', 'surname': 'Hamilton'}</td>\n",
       "      <td>1985-01-07</td>\n",
       "      <td>British</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Lewis_Hamilton</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   driverId driverRef number code  \\\n",
       "0         1  hamilton     44  HAM   \n",
       "\n",
       "                                           name         dob nationality  \\\n",
       "0  {'forename': 'Lewis', 'surname': 'Hamilton'}  1985-01-07     British   \n",
       "\n",
       "                                           url  \n",
       "0  http://en.wikipedia.org/wiki/Lewis_Hamilton  "
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drivers.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(853, 8)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drivers.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "driverId       0\n",
       "driverRef      0\n",
       "number         0\n",
       "code           0\n",
       "name           0\n",
       "dob            0\n",
       "nationality    0\n",
       "url            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking null data\n",
    "drivers.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Name column has important information in dict format. Let's change that\n",
    "names = [(i['surname'] + \" \" + i['forename']) for i in drivers.name]\n",
    "drivers.name = names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in ID Column\n",
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicates in Id column\n",
    "print(f'{drivers.driverId.duplicated().sum()} duplicates in ID Column')\n",
    "# Checking duplicatines in dataframe\n",
    "print(f'{drivers.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([44, '\\\\N', 6, 14, 7, 88, 19, 99, 22, 5, 8, 10, 27, 13, 11, 3, 25,\n",
       "       4, 21, 77, 17, 20, 26, 45, 9, 28, 33, 12, 55, 98, 53, 30, 94, 2,\n",
       "       31, 18, 16, 35, 63, 23, 51, 89], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking Null data in another format\n",
    "drivers.number.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['HAM', 'HEI', 'ROS', 'ALO', 'KOV', 'NAK', 'BOU', 'RAI', 'KUB',\n",
       "       'GLO', 'SAT', 'PIQ', 'MAS', 'COU', 'TRU', 'SUT', 'WEB', 'BUT',\n",
       "       'DAV', 'VET', 'FIS', 'BAR', 'SCH', 'LIU', 'WUR', 'SPE', 'ALB',\n",
       "       'WIN', 'YAM', 'MSC', 'MON', 'KLI', 'TMO', 'IDE', 'VIL', 'FMO',\n",
       "       'DLR', 'DOO', 'KAR', 'FRI', 'ZON', 'PIZ', '\\\\N', 'BUE', 'BAD',\n",
       "       'MAG', 'ALG', 'GRO', 'KOB', 'BIA', 'GAS', 'HUL', 'PET', 'DIG',\n",
       "       'SEN', 'CHA', 'MAL', 'DIR', 'PER', 'DAM', 'RIC', 'VER', 'PIC',\n",
       "       'CHI', 'GUT', 'BOT', 'VDG', 'KVY', 'LOT', 'ERI', 'STE', 'NAS',\n",
       "       'SAI', 'MER', 'RSS', 'PAL', 'WEH', 'HAR', 'VAN', 'OCO', 'STR',\n",
       "       'GIO', 'LEC', 'SIR', 'NOR', 'RUS', 'LAT', 'FIT', 'AIT', 'TSU',\n",
       "       'MAZ'], dtype=object)"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drivers.code.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Changing \\\\N data -> \"no data\"\n",
    "drivers.replace('\\\\N', \"no data\", inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>driverId</th>\n",
       "      <th>driverRef</th>\n",
       "      <th>number</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>dob</th>\n",
       "      <th>nationality</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>101</td>\n",
       "      <td>brabham</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Brabham David</td>\n",
       "      <td>1965-09-05</td>\n",
       "      <td>Australian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/David_Brabham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>102</td>\n",
       "      <td>senna</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Senna Ayrton</td>\n",
       "      <td>1960-03-21</td>\n",
       "      <td>Brazilian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Ayrton_Senna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>103</td>\n",
       "      <td>bernard</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Bernard Éric</td>\n",
       "      <td>1964-08-24</td>\n",
       "      <td>French</td>\n",
       "      <td>http://en.wikipedia.org/wiki/%C3%89ric_Bernard</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>104</td>\n",
       "      <td>fittipaldi</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Fittipaldi Christian</td>\n",
       "      <td>1971-01-18</td>\n",
       "      <td>Brazilian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Christian_Fittipaldi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>105</td>\n",
       "      <td>alboreto</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Alboreto Michele</td>\n",
       "      <td>1956-12-23</td>\n",
       "      <td>Italian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Michele_Alboreto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>196</td>\n",
       "      <td>boesel</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Boesel Raul</td>\n",
       "      <td>1957-12-04</td>\n",
       "      <td>Brazilian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Raul_Boesel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>197</td>\n",
       "      <td>jarier</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Jarier Jean-Pierre</td>\n",
       "      <td>1946-07-10</td>\n",
       "      <td>French</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Jean-Pierre_Jarier</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>198</td>\n",
       "      <td>villeneuve_sr</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Villeneuve Sr. Jacques</td>\n",
       "      <td>1953-11-04</td>\n",
       "      <td>Canadian</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Jacques_Villeneuv...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>199</td>\n",
       "      <td>reutemann</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Reutemann Carlos</td>\n",
       "      <td>1942-04-12</td>\n",
       "      <td>Argentine</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Carlos_Reutemann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>200</td>\n",
       "      <td>mass</td>\n",
       "      <td>no data</td>\n",
       "      <td>no data</td>\n",
       "      <td>Mass Jochen</td>\n",
       "      <td>1946-09-30</td>\n",
       "      <td>German</td>\n",
       "      <td>http://en.wikipedia.org/wiki/Jochen_Mass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     driverId      driverRef   number     code                    name  \\\n",
       "100       101        brabham  no data  no data           Brabham David   \n",
       "101       102          senna  no data  no data            Senna Ayrton   \n",
       "102       103        bernard  no data  no data            Bernard Éric   \n",
       "103       104     fittipaldi  no data  no data    Fittipaldi Christian   \n",
       "104       105       alboreto  no data  no data        Alboreto Michele   \n",
       "..        ...            ...      ...      ...                     ...   \n",
       "195       196         boesel  no data  no data             Boesel Raul   \n",
       "196       197         jarier  no data  no data      Jarier Jean-Pierre   \n",
       "197       198  villeneuve_sr  no data  no data  Villeneuve Sr. Jacques   \n",
       "198       199      reutemann  no data  no data        Reutemann Carlos   \n",
       "199       200           mass  no data  no data             Mass Jochen   \n",
       "\n",
       "            dob nationality                                                url  \n",
       "100  1965-09-05  Australian         http://en.wikipedia.org/wiki/David_Brabham  \n",
       "101  1960-03-21   Brazilian          http://en.wikipedia.org/wiki/Ayrton_Senna  \n",
       "102  1964-08-24      French     http://en.wikipedia.org/wiki/%C3%89ric_Bernard  \n",
       "103  1971-01-18   Brazilian  http://en.wikipedia.org/wiki/Christian_Fittipaldi  \n",
       "104  1956-12-23     Italian      http://en.wikipedia.org/wiki/Michele_Alboreto  \n",
       "..          ...         ...                                                ...  \n",
       "195  1957-12-04   Brazilian           http://en.wikipedia.org/wiki/Raul_Boesel  \n",
       "196  1946-07-10      French    http://en.wikipedia.org/wiki/Jean-Pierre_Jarier  \n",
       "197  1953-11-04    Canadian  http://en.wikipedia.org/wiki/Jacques_Villeneuv...  \n",
       "198  1942-04-12   Argentine      http://en.wikipedia.org/wiki/Carlos_Reutemann  \n",
       "199  1946-09-30      German           http://en.wikipedia.org/wiki/Jochen_Mass  \n",
       "\n",
       "[100 rows x 8 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drivers[100:200]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "drivers.to_csv(\"drivers.csv\", index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**results.json**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dataset 3: results.json\n",
    "results = pd.read_json(r\"./datasets_raw/results.json\",\n",
    "                    lines = True, encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "resultId             int64\n",
       "raceId               int64\n",
       "driverId             int64\n",
       "constructorId        int64\n",
       "number              object\n",
       "grid                 int64\n",
       "position            object\n",
       "positionText        object\n",
       "positionOrder        int64\n",
       "points             float64\n",
       "laps                 int64\n",
       "time                object\n",
       "milliseconds        object\n",
       "fastestLap          object\n",
       "rank                object\n",
       "fastestLapTime      object\n",
       "fastestLapSpeed     object\n",
       "statusId             int64\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(results.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "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>resultId</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>constructorId</th>\n",
       "      <th>number</th>\n",
       "      <th>grid</th>\n",
       "      <th>position</th>\n",
       "      <th>positionText</th>\n",
       "      <th>positionOrder</th>\n",
       "      <th>points</th>\n",
       "      <th>laps</th>\n",
       "      <th>time</th>\n",
       "      <th>milliseconds</th>\n",
       "      <th>fastestLap</th>\n",
       "      <th>rank</th>\n",
       "      <th>fastestLapTime</th>\n",
       "      <th>fastestLapSpeed</th>\n",
       "      <th>statusId</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>58</td>\n",
       "      <td>1:34:50.616</td>\n",
       "      <td>5690616</td>\n",
       "      <td>39</td>\n",
       "      <td>2</td>\n",
       "      <td>1:27.452</td>\n",
       "      <td>218.3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8.0</td>\n",
       "      <td>58</td>\n",
       "      <td>+5.478</td>\n",
       "      <td>5696094</td>\n",
       "      <td>41</td>\n",
       "      <td>3</td>\n",
       "      <td>1:27.739</td>\n",
       "      <td>217.586</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>18</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>6.0</td>\n",
       "      <td>58</td>\n",
       "      <td>+8.163</td>\n",
       "      <td>5698779</td>\n",
       "      <td>41</td>\n",
       "      <td>5</td>\n",
       "      <td>1:28.090</td>\n",
       "      <td>216.719</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>58</td>\n",
       "      <td>+17.181</td>\n",
       "      <td>5707797</td>\n",
       "      <td>58</td>\n",
       "      <td>7</td>\n",
       "      <td>1:28.603</td>\n",
       "      <td>215.464</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>4.0</td>\n",
       "      <td>58</td>\n",
       "      <td>+18.014</td>\n",
       "      <td>5708630</td>\n",
       "      <td>43</td>\n",
       "      <td>1</td>\n",
       "      <td>1:27.418</td>\n",
       "      <td>218.385</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   resultId  raceId  driverId  constructorId number  grid position  \\\n",
       "0         1      18         1              1     22     1        1   \n",
       "1         2      18         2              2      3     5        2   \n",
       "2         3      18         3              3      7     7        3   \n",
       "3         4      18         4              4      5    11        4   \n",
       "4         5      18         5              1     23     3        5   \n",
       "\n",
       "  positionText  positionOrder  points  laps         time milliseconds  \\\n",
       "0            1              1    10.0    58  1:34:50.616      5690616   \n",
       "1            2              2     8.0    58       +5.478      5696094   \n",
       "2            3              3     6.0    58       +8.163      5698779   \n",
       "3            4              4     5.0    58      +17.181      5707797   \n",
       "4            5              5     4.0    58      +18.014      5708630   \n",
       "\n",
       "  fastestLap rank fastestLapTime fastestLapSpeed  statusId  \n",
       "0         39    2       1:27.452           218.3         1  \n",
       "1         41    3       1:27.739         217.586         1  \n",
       "2         41    5       1:28.090         216.719         1  \n",
       "3         58    7       1:28.603         215.464         1  \n",
       "4         43    1       1:27.418         218.385         1  "
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "resultId           0\n",
       "raceId             0\n",
       "driverId           0\n",
       "constructorId      0\n",
       "number             0\n",
       "grid               0\n",
       "position           0\n",
       "positionText       0\n",
       "positionOrder      0\n",
       "points             0\n",
       "laps               0\n",
       "time               0\n",
       "milliseconds       0\n",
       "fastestLap         0\n",
       "rank               0\n",
       "fastestLapTime     0\n",
       "fastestLapSpeed    0\n",
       "statusId           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checkin for nulls\n",
    "results.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in ID Column\n",
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicates in Id column\n",
    "print(f'{results.resultId.duplicated().sum()} duplicates in ID Column')\n",
    "# Checking duplicatines in dataframe\n",
    "print(f'{results.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "results.replace('\\\\N', 'No Data', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "results.to_csv('results.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**pitstops.json**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "pitstops = pd.read_json(r\"./datasets_raw/pit_stops.json\",\n",
    "                         lines = False, orient = 'str', encoding = 'utf-8-sig')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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",
       "  </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"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pitstops.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "raceId           int64\n",
       "driverId         int64\n",
       "stop             int64\n",
       "lap              int64\n",
       "time            object\n",
       "duration        object\n",
       "milliseconds     int64\n",
       "dtype: object"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(pitstops.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "raceId          0\n",
       "driverId        0\n",
       "stop            0\n",
       "lap             0\n",
       "time            0\n",
       "duration        0\n",
       "milliseconds    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking null data\n",
    "pitstops.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicatines in dataframe\n",
    "print(f'{results.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating pitstopsId column\n",
    "pitstops['pitstopId'] = [i for i in range(1,pitstops.shape[0] + 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_column = pitstops.pop(\"pitstopId\")\n",
    "pitstops.insert(0, \"pitstopId\", first_column)"
   ]
  },
  {
   "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>pitstopId</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>1</td>\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>2</td>\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>3</td>\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>4</td>\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>5</td>\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": [
       "   pitstopId  raceId  driverId  stop  lap      time duration  milliseconds\n",
       "0          1     841       153     1    1  17:05:23   26.898         26898\n",
       "1          2     841        30     1    1  17:05:52   25.021         25021\n",
       "2          3     841        17     1   11  17:20:48   23.426         23426\n",
       "3          4     841         4     1   12  17:22:34   23.251         23251\n",
       "4          5     841        13     1   13  17:24:10   23.842         23842"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pitstops.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "pitstops.to_csv('pitstops.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**races.csv**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "races = pd.read_csv(\"./datasets_raw/races.csv\", encoding = 'utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\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  \n",
       "0  06:00:00  http://en.wikipedia.org/wiki/2009_Australian_G...  \n",
       "1  09:00:00  http://en.wikipedia.org/wiki/2009_Malaysian_Gr...  \n",
       "2  07:00:00  http://en.wikipedia.org/wiki/2009_Chinese_Gran...  \n",
       "3  12:00:00  http://en.wikipedia.org/wiki/2009_Bahrain_Gran...  \n",
       "4  12:00:00  http://en.wikipedia.org/wiki/2009_Spanish_Gran...  "
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "raceId        int64\n",
       "year          int64\n",
       "round         int64\n",
       "circuitId     int64\n",
       "name         object\n",
       "date         object\n",
       "time         object\n",
       "url          object\n",
       "dtype: object"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1058, 8)"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "races.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in ID Column\n",
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicates in Id column\n",
    "print(f'{races.raceId.duplicated().sum()} duplicates in ID Column')\n",
    "# Checking duplicatines in dataframe\n",
    "print(f'{races.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "raceId       0\n",
       "year         0\n",
       "round        0\n",
       "circuitId    0\n",
       "name         0\n",
       "date         0\n",
       "time         0\n",
       "url          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking for null data\n",
    "races.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "races.replace('\\\\N', 'no data', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "races.to_csv('races.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Uniendo los datasets lap_times y Qualifying\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**lap_times.csv**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_csv('./datasets_raw/lap_times/lap_times_split_1.csv')\n",
    "df2 = pd.read_csv('./datasets_raw/lap_times/lap_times_split_2.csv')\n",
    "df3 = pd.read_csv('./datasets_raw/lap_times/lap_times_split_3.csv')\n",
    "df4 = pd.read_csv('./datasets_raw/lap_times/lap_times_split_4.csv')\n",
    "df5 = pd.read_csv('./datasets_raw/lap_times/lap_times_split_5.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>841</th>\n",
       "      <th>20</th>\n",
       "      <th>1</th>\n",
       "      <th>1.1</th>\n",
       "      <th>1:38.109</th>\n",
       "      <th>98109</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1:33.006</td>\n",
       "      <td>93006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.713</td>\n",
       "      <td>92713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.803</td>\n",
       "      <td>92803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.342</td>\n",
       "      <td>92342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.605</td>\n",
       "      <td>92605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   841  20  1  1.1  1:38.109  98109\n",
       "0  841  20  2    1  1:33.006  93006\n",
       "1  841  20  3    1  1:32.713  92713\n",
       "2  841  20  4    1  1:32.803  92803\n",
       "3  841  20  5    1  1:32.342  92342\n",
       "4  841  20  6    1  1:32.605  92605"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# No columns in these datasets\n",
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding columns\n",
    "df1.columns = ['raceId', 'driverId', 'laptimeStop', 'laptimeTime', 'duration', 'milliseconds']\n",
    "df2.columns = ['raceId', 'driverId', 'laptimeStop', 'laptimeTime', 'duration', 'milliseconds']\n",
    "df3.columns = ['raceId', 'driverId', 'laptimeStop', 'laptimeTime', 'duration', 'milliseconds']\n",
    "df4.columns = ['raceId', 'driverId', 'laptimeStop', 'laptimeTime', 'duration', 'milliseconds']\n",
    "df5.columns = ['raceId', 'driverId', 'laptimeStop', 'laptimeTime', 'duration', 'milliseconds']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "lap_times = pd.concat([df1, df2, df3, df4, df5], ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(490899, 6)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lap_times.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "raceId           int64\n",
       "driverId         int64\n",
       "laptimeStop      int64\n",
       "laptimeTime      int64\n",
       "duration        object\n",
       "milliseconds     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lap_times.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding lap_times ID\n",
    "lap_timesId = [i for i in range(1, len(lap_times) + 1)]\n",
    "lap_times.insert(0, \"laptimeId\", lap_timesId)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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>laptimeId</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>laptimeStop</th>\n",
       "      <th>laptimeTime</th>\n",
       "      <th>duration</th>\n",
       "      <th>milliseconds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1:33.006</td>\n",
       "      <td>93006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.713</td>\n",
       "      <td>92713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.803</td>\n",
       "      <td>92803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.342</td>\n",
       "      <td>92342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>841</td>\n",
       "      <td>20</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1:32.605</td>\n",
       "      <td>92605</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   laptimeId  raceId  driverId  laptimeStop  laptimeTime  duration  \\\n",
       "0          1     841        20            2            1  1:33.006   \n",
       "1          2     841        20            3            1  1:32.713   \n",
       "2          3     841        20            4            1  1:32.803   \n",
       "3          4     841        20            5            1  1:32.342   \n",
       "4          5     841        20            6            1  1:32.605   \n",
       "\n",
       "   milliseconds  \n",
       "0         93006  \n",
       "1         92713  \n",
       "2         92803  \n",
       "3         92342  \n",
       "4         92605  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lap_times.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "laptimeId       0\n",
       "raceId          0\n",
       "driverId        0\n",
       "laptimeStop     0\n",
       "laptimeTime     0\n",
       "duration        0\n",
       "milliseconds    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking for null data\n",
    "lap_times.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicatines in dataframe\n",
    "print(f'{lap_times.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "lap_times.replace('\\\\N', 'no data', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "lap_times.to_csv('laptimes.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**qualifying.csv**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "q1 = pd.read_json('./datasets_raw/Qualifying/qualifying_split_1.json')\n",
    "q2 = pd.read_json('./datasets_raw/Qualifying/qualifying_split_2.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>qualifyId</th>\n",
       "      <th>raceId</th>\n",
       "      <th>driverId</th>\n",
       "      <th>constructorId</th>\n",
       "      <th>number</th>\n",
       "      <th>position</th>\n",
       "      <th>q1</th>\n",
       "      <th>q2</th>\n",
       "      <th>q3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>1:26.572</td>\n",
       "      <td>1:25.187</td>\n",
       "      <td>1:26.714</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   qualifyId  raceId  driverId  constructorId  number  position        q1  \\\n",
       "0          1      18         1              1      22         1  1:26.572   \n",
       "\n",
       "         q2        q3  \n",
       "0  1:25.187  1:26.714  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q1.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "qualifying = pd.concat([q1, q2], ignore_index = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "qualifyId         int64\n",
       "raceId            int64\n",
       "driverId          int64\n",
       "constructorId     int64\n",
       "number            int64\n",
       "position          int64\n",
       "q1               object\n",
       "q2               object\n",
       "q3               object\n",
       "dtype: object"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualifying.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8694, 9)"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualifying.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 duplicates in ID Column\n",
      "0 duplicates in Dataframe\n"
     ]
    }
   ],
   "source": [
    "# Checking duplicates in Id column\n",
    "print(f'{qualifying.qualifyId.duplicated().sum()} duplicates in ID Column')\n",
    "# Checking duplicatines in dataframe\n",
    "print(f'{qualifying.duplicated().sum()} duplicates in Dataframe')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "qualifyId        0\n",
       "raceId           0\n",
       "driverId         0\n",
       "constructorId    0\n",
       "number           0\n",
       "position         0\n",
       "q1               0\n",
       "q2               0\n",
       "q3               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking for null data\n",
    "qualifying.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "qualifying.replace('\\\\N', 'no data', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [],
   "source": [
    "qualifying.to_csv('qualifying.csv', index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.6 ('fastapi-crud-henrydata')",
   "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.6"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "7470f23f83c6d3dddb997e36bcfe87022c1b240f615cf25cc7242e78aca071ba"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}