{"cells":[{"cell_type":"markdown","metadata":{},"source":["# Save fastf1 data a to SQL database\n","\n","Useful for generating quick reports during the race.\n"]},{"cell_type":"code","execution_count":1,"metadata":{},"outputs":[{"name":"stderr","output_type":"stream","text":["req WARNING \tDEFAULT CACHE ENABLED! (1.04 GB) /Users/diegomaradona/Library/Caches/fastf1\n","core INFO \tLoading data for Spanish Grand Prix - Practice 1 [v3.3.3]\n","req INFO \tUsing cached data for session_info\n","req INFO \tUsing cached data for driver_info\n","req INFO \tUsing cached data for session_status_data\n","req INFO \tUsing cached data for track_status_data\n","req INFO \tUsing cached data for _extended_timing_data\n","req INFO \tUsing cached data for timing_app_data\n","core INFO \tProcessing timing data...\n","core WARNING \tNo lap data for driver 27\n","core WARNING \tFailed to perform lap accuracy check - all laps marked as inaccurate (driver 27)\n","req INFO \tUsing cached data for car_data\n","req INFO \tUsing cached data for position_data\n","core INFO \tFinished loading data for 21 drivers: ['1', '10', '11', '14', '16', '18', '2', '20', '22', '23', '24', '27', '3', '31', '4', '44', '50', '55', '63', '77', '81']\n"]}],"source":["import fastf1\n","\n","GRAND_PRIX = 'Spain'\n","YEAR = 2024\n","SESSION = 'FP1'\n","\n","session = fastf1.get_session(YEAR, GRAND_PRIX, SESSION)\n","session.load(weather=False, messages=False)"]},{"cell_type":"code","execution_count":2,"metadata":{},"outputs":[],"source":["from pandas import DataFrame\n","\n","\n","def feature_engineering(raw_df: DataFrame) -> DataFrame:\n"," \"\"\"\n"," Perform feature engineering on the input DataFrame.\n","\n"," Parameters:\n"," raw_df (DataFrame): The input DataFrame to be processed.\n","\n"," Returns:\n"," DataFrame: The processed DataFrame after feature engineering.\n"," \"\"\"\n"," df = raw_df.copy()\n","\n"," df = df[df['Deleted'].isna()]\n"," df['LapNumber'] = df['LapNumber'].astype(int)\n"," df = df.sort_values(by='LapNumber')\n","\n"," time_columns = ['LapTime', 'PitOutTime', 'PitInTime',\n"," 'Sector1Time', 'Sector2Time', 'Sector3Time', 'LapStartTime']\n"," for col in time_columns:\n"," if col in df.columns:\n"," df[col + 'Seconds'] = df[col].dt.total_seconds()\n"," df.drop(columns=[col], inplace=True, axis=1)\n"," print(f\"Converted {col} to seconds.\")\n","\n"," df.drop(columns=[col for col in ['Sector1SessionTime', 'Sector2SessionTime',\n"," 'Sector3SessionTime'] if col in df.columns], inplace=True, axis=1)\n","\n"," df['TimeInMinutes'] = df['Time'].dt.total_seconds() / 60\n"," df.drop(columns=['Time'], inplace=True, axis=1)\n","\n"," return df"]},{"cell_type":"code","execution_count":3,"metadata":{},"outputs":[{"name":"stdout","output_type":"stream","text":["Converted LapTime to seconds.\n","Converted PitOutTime to seconds.\n","Converted PitInTime to seconds.\n","Converted Sector1Time to seconds.\n","Converted Sector2Time to seconds.\n","Converted Sector3Time to seconds.\n","Converted LapStartTime to seconds.\n","Converted LapTime to seconds.\n","Converted PitOutTime to seconds.\n","Converted PitInTime to seconds.\n","Converted Sector1Time to seconds.\n","Converted Sector2Time to seconds.\n","Converted Sector3Time to seconds.\n","Converted LapStartTime to seconds.\n","Converted LapTime to seconds.\n","Converted PitOutTime to seconds.\n","Converted PitInTime to seconds.\n","Converted Sector1Time to seconds.\n","Converted Sector2Time to seconds.\n","Converted Sector3Time to seconds.\n","Converted LapStartTime to seconds.\n","Converted LapTime to seconds.\n","Converted PitOutTime to seconds.\n","Converted PitInTime to seconds.\n","Converted Sector1Time to seconds.\n","Converted Sector2Time to seconds.\n","Converted Sector3Time to seconds.\n","Converted LapStartTime to seconds.\n"]}],"source":["hamilton_laps = feature_engineering(session.laps.pick_driver('HAM'))\n","russel_laps = feature_engineering(session.laps.pick_driver('RUS'))\n","verstappen_laps = feature_engineering(session.laps.pick_driver('VER'))\n","norris_laps = feature_engineering(session.laps.pick_driver('NOR'))"]},{"cell_type":"code","execution_count":4,"metadata":{},"outputs":[{"data":{"text/html":["
\n","\n","\n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n"," \n","
DriverDriverNumberLapNumberStintSpeedI1SpeedI2SpeedFLSpeedSTIsPersonalBestCompound...FastF1GeneratedIsAccurateLapTimeSecondsPitOutTimeSecondsPitInTimeSecondsSector1TimeSecondsSector2TimeSecondsSector3TimeSecondsLapStartTimeSecondsTimeInMinutes
368HAM4411.0215.0220.0281.0207.0FalseHARD...FalseFalseNaN855.834NaNNaN35.81724.869855.83416.018517
369HAM4421.0287.0287.0282.0316.0TrueHARD...FalseTrue77.632NaNNaN22.81531.37523.442961.11117.312383
370HAM4431.0124.0127.0NaN252.0FalseHARD...FalseFalse138.480NaN1174.5434.77660.31343.3911038.74319.620383
371HAM4442.0162.0209.0280.0149.0FalseHARD...FalseFalse138.9691202.155NaN60.71846.94031.3111177.22321.936533
372HAM4452.0288.0301.0280.0318.0TrueHARD...FalseTrue76.650NaNNaN22.56330.89023.1971316.19223.214033
\n","

5 rows × 28 columns

\n","
"],"text/plain":[" Driver DriverNumber LapNumber Stint SpeedI1 SpeedI2 SpeedFL SpeedST \\\n","368 HAM 44 1 1.0 215.0 220.0 281.0 207.0 \n","369 HAM 44 2 1.0 287.0 287.0 282.0 316.0 \n","370 HAM 44 3 1.0 124.0 127.0 NaN 252.0 \n","371 HAM 44 4 2.0 162.0 209.0 280.0 149.0 \n","372 HAM 44 5 2.0 288.0 301.0 280.0 318.0 \n","\n"," IsPersonalBest Compound ... FastF1Generated IsAccurate LapTimeSeconds \\\n","368 False HARD ... False False NaN \n","369 True HARD ... False True 77.632 \n","370 False HARD ... False False 138.480 \n","371 False HARD ... False False 138.969 \n","372 True HARD ... False True 76.650 \n","\n"," PitOutTimeSeconds PitInTimeSeconds Sector1TimeSeconds Sector2TimeSeconds \\\n","368 855.834 NaN NaN 35.817 \n","369 NaN NaN 22.815 31.375 \n","370 NaN 1174.54 34.776 60.313 \n","371 1202.155 NaN 60.718 46.940 \n","372 NaN NaN 22.563 30.890 \n","\n"," Sector3TimeSeconds LapStartTimeSeconds TimeInMinutes \n","368 24.869 855.834 16.018517 \n","369 23.442 961.111 17.312383 \n","370 43.391 1038.743 19.620383 \n","371 31.311 1177.223 21.936533 \n","372 23.197 1316.192 23.214033 \n","\n","[5 rows x 28 columns]"]},"execution_count":4,"metadata":{},"output_type":"execute_result"}],"source":["hamilton_laps.head()"]},{"cell_type":"markdown","metadata":{},"source":["## Convert to SQL Database\n"]},{"cell_type":"code","execution_count":5,"metadata":{},"outputs":[{"data":{"text/plain":["27"]},"execution_count":5,"metadata":{},"output_type":"execute_result"}],"source":["from sqlalchemy import create_engine\n","\n","GRAND_PRIX = 'Spain'\n","YEAR = 2024\n","SESSION = 'FP1'\n","\n","engine = create_engine(f'sqlite:///{GRAND_PRIX}_{YEAR}_{SESSION}.db')\n","\n","hamilton_laps.to_sql(name=\"hamilton\", con=engine)\n","russel_laps.to_sql(name=\"russel\", con=engine)\n","verstappen_laps.to_sql(name=\"verstappen\", con=engine)\n","norris_laps.to_sql(name=\"norris\", con=engine)"]}],"metadata":{"kernelspec":{"display_name":"formula-1","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.0"}},"nbformat":4,"nbformat_minor":2}