<jupyter_start><jupyter_text>McLaren F1  Taking a deep dive into the McLaren F1 team over the past 10 years.<jupyter_code>import os 
os.environ['SPARK_HOME'] = '/Users/youssefragab/spark-3.0.1-bin-hadoop3.2/'
import findspark 
findspark.init()
from pyspark import SparkContext
from pyspark import HiveContext
spark = SparkContext(appName = 'mclaren_f1')
import numpy, matplotlib 
import matplotlib.pyplot as plt
spark
from pyspark import SQLContext 
sqlc = HiveContext(spark)
sqlc<jupyter_output><empty_output><jupyter_text>Loading the dataFor this analysis, we are going to use a comprehensive dataset from Kaggle. It can be found at this link: https://www.kaggle.com/rohanrao/formula-1-world-championship-1950-2020?select=lap_times.csv<jupyter_code>circuits = sqlc.read.csv('circuits.csv', header = True, inferSchema = True)
circuits.createOrReplaceTempView('circuits')

circuits.show(10)
constructor_results = sqlc.read.csv('constructor_results.csv', header = True, inferSchema = True)
constructor_results.createOrReplaceTempView('constructor_results')

constructor_results.show(10)
constructor_standings = sqlc.read.csv('constructor_standings.csv', header = True, inferSchema = True)
constructor_standings.createOrReplaceTempView('constructor_standings')

constructor_standings.show(10)
constructors = sqlc.read.csv('constructors.csv', header = True, inferSchema = True)
constructors.createOrReplaceTempView('constructors')

constructors.show(10)
driver_standings = sqlc.read.csv('driver_standings.csv', header = True, inferSchema = True)
driver_standings.createOrReplaceTempView('driver_standings')

driver_standings.show(10)
drivers = sqlc.read.csv('drivers.csv', header = True, inferSchema = True)
drivers.createOrReplaceTempView('drivers')

drivers.show(10)
lap_times = sqlc.read.csv('lap_times.csv', header = True, inferSchema = True)
lap_times.createOrReplaceTempView('lap_times')

lap_times.show(10)
pit_stops = sqlc.read.csv('pit_stops.csv', header = True, inferSchema = True)
pit_stops.createOrReplaceTempView('pit_stops')

pit_stops.show(10)
qualifying = sqlc.read.csv('qualifying.csv', header = True, inferSchema = True)
qualifying.createOrReplaceTempView('qualifying')

qualifying.show(10)
races = sqlc.read.csv('races.csv', header = True, inferSchema = True)
races.createOrReplaceTempView('races')

races.show(10)
results = sqlc.read.csv('results.csv', header = True, inferSchema = True)
results.createOrReplaceTempView('results')

results.show(10)
seasons = sqlc.read.csv('seasons.csv', header = True, inferSchema = True)
seasons.createOrReplaceTempView('seasons')

seasons.show(10)
status = sqlc.read.csv('status.csv', header = True, inferSchema = True)
status.createOrReplaceTempView('status')

status.show(10)<jupyter_output>+--------+------------+
|statusId|      status|
+--------+------------+
|       1|    Finished|
|       2|Disqualified|
|       3|    Accident|
|       4|   Collision|
|       5|      Engine|
|       6|     Gearbox|
|       7|Transmission|
|       8|      Clutch|
|       9|  Hydraulics|
|      10|  Electrical|
+--------+------------+
only showing top 10 rows<jupyter_text>McLaren recent historic performances We are going to look at the last 12 year's of McLaren's history, 2008 is the last year they won the driver's championship with <NAME>. Since then, McLaren has gone down the rankings as a constructor, but have in recent years been on a meteoric rise, we are going to see what happened, and where they can go from here.  Constructor's standings<jupyter_code>mcl_points = sqlc.sql("""
    SELECT sum(constructor_results.points) AS points, races.year
    FROM constructor_results, races
    WHERE constructorId == 1
        AND races.raceId == constructor_results.raceId
        AND races.year >= 2008
    GROUP BY races.year
    ORDER BY races.year ASC
""")

mcl_points.toPandas() \
    .plot \
    .bar(x = 'year',
             y = 'points')<jupyter_output><empty_output><jupyter_text>It's important to note that point awarding changed after 2010, whcih is why we see a very big uptick in the number of points McLaren has scored since. However, we do see a big dip in number of point scored, which has started going back up in the past 3 seasons. The 2021 season is still underway, but we can clearly see that McLaren has already outscored 5 of their past 9 seasons.<jupyter_code>sqlc.sql("""
    SELECT sum(constructor_results.points) AS points, constructors.name, races.year
    FROM constructor_results, races, constructors
    WHERE constructor_results.constructorId == constructors.constructorId
        AND races.raceId == constructor_results.raceId
        AND races.year >= 2010
    GROUP BY constructors.name, races.year
    ORDER BY races.year ASC,  points DESC
""").show()<jupyter_output>+------+-----------+----+
|points|       name|year|
+------+-----------+----+
| 498.0|   Red Bull|2010|
| 454.0|    McLaren|2010|
| 396.0|    Ferrari|2010|
| 214.0|   Mercedes|2010|
| 163.0|    Renault|2010|
|  69.0|   Williams|2010|
|  68.0|Force India|2010|
|  44.0|     Sauber|2010|
|  13.0| Toro Rosso|2010|
|   0.0|      Lotus|2010|
|   0.0|        HRT|2010|
|   0.0|     Virgin|2010|
| 650.0|   Red Bull|2011|
| 497.0|    McLaren|2011|
| 375.0|    Ferrari|2011|
| 165.0|   Mercedes|2011|
|  73.0|    Renault|2011|
|  69.0|Force India|2011|
|  44.0|     Sauber|2011|
|  41.0| Toro Rosso|2011|
+------+-----------+----+
only showing top 20 rows<jupyter_text>Pit crew performance We can also take a look at how well the team's pit crew was performing over that period. We know that pit stops are an integral part of the race, so it is important to investigate every avenue when looking at where the performance dipped.<jupyter_code>sqlc.sql("""
    SELECT * 
    FROM pit_stops
""").show()
sqlc.sql("""
    SELECT pit_stops.raceId, pit_stops.driverId, constructors.name, pit_stops.duration, pit_stops.milliseconds, pit_stops.stop, pit_stops.lap
    FROM pit_stops, constructors, results
    WHERE constructors.constructorId == results.constructorId
""").show()
sqlc.sql("""
    SELECT pit_stops.raceId, pit_stops.driverId, duration, pit_stops.milliseconds, stop, lap, results.constructorId
    FROM pit_stops, results, races
    WHERE results.raceId == pit_stops.raceId
""").show()
sqlc.sql("""
    SELECT concat(drivers.forename, ' ', drivers.surname) AS driver_name, constructors.name, mean(pit_stops.milliseconds)
    FROM results, pit_stops, drivers, constructors, races
    WHERE results.driverId == pit_stops.driverId
        AND drivers.driverId == pit_stops.driverId
        AND constructors.constructorId == results.constructorId
        AND races.raceId == pit_stops.raceId
        AND races.year >= 2008
    GROUP BY driver_name, constructors.name
    ORDER BY driver_name
""").show()
avg_pit_stops = sqlc.sql("""
    SELECT round(mean(pit_stops.milliseconds), 3) AS avg_pitstop, concat(drivers.forename, ' ', drivers.surname) AS driver_name, constructors.name
    FROM pit_stops, results, drivers, constructors
    WHERE pit_stops.raceId == results.raceId
        AND drivers.driverId == pit_stops.driverId
        AND constructors.constructorId == results.constructorId
    GROUP BY driver_name
""")

avg_pit_stops.createOrReplaceTempView('avg_pit_stops')
avg_pit_stops.show()
sqlc.sql("""
    SELECT * 
    FROM avg_pit_stops 
    ORDER BY avg_pitstop ASC
""").show()<jupyter_output>+-----------+------------------+------------+
|avg_pitstop|       driver_name|        name|
+-----------+------------------+------------+
|    21962.0| <NAME>|    Caterham|
|    21962.0| <NAME>|    Lotus F1|
|    21962.0| <NAME>|    Marussia|
|  22033.854|<NAME>|    Marussia|
|  22033.854|<NAME>|    Caterham|
|  22033.854|<NAME>|    Lotus F1|
|  22135.591|  <NAME>|         HRT|
|    22273.0|  <NAME>|      Virgin|
|    22273.0|  <NAME>|       Lotus|
|  22283.857|      <NAME>|       Lotus|
|  22283.857|      <NAME>|      Virgin|
|  22386.591|  <NAME>|         HRT|
|    22399.0|     <NAME>|Haas F1 Team|
|  22411.157|   <NAME>|         HRT|
|  22460.563|       <NAME>|         HRT|
|  22494.648|     Jenson Button|      Virgin|
|  22494.648|     Jenson Button|       Lotus|
|  22508.537|     Jenson Button|         HRT|
|  22541.644|<NAME>|  [...]<jupyter_text>Average finishing position at each trackDifferent tracks suit different cars, so it is interesting to see which tracks McLaren has been doing well in for the past 12 years.<jupyter_code>sqlc.sql("""
    SELECT circuits.name AS track_name, round(mean(position),2) AS avg_finishing_position
    FROM results, races, constructors, drivers, circuits
    WHERE races.raceId == results.raceId
        AND constructors.constructorId == results.constructorId
        AND position IN (1,2,3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22)
        AND results.constructorId == 1
        AND races.year >= 2008
        AND races.circuitId == circuits.circuitId
    GROUP BY circuits.name
    ORDER BY avg_finishing_position ASC
""").show(20, False)
sqlc.sql("""
    SELECT circuits.name AS track_name, round(mean(position),2) AS avg_finishing_position
    FROM results, races, constructors, drivers, circuits
    WHERE races.raceId == results.raceId
        AND constructors.constructorId == results.constructorId
        AND position IN (1,2,3,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22)
        AND results.constructorId == 1
        AND races.year >= 2008
        AND races.circuitId == circuits.circuitId
    GROUP BY circuits.name
    ORDER BY avg_finishing_position DESC
""").show(20, False)<jupyter_output>+----------------------------------+----------------------+
|track_name                        |avg_finishing_position|
+----------------------------------+----------------------+
|Fuji Speedway                     |12.0                  |
|<NAME>      |11.71                 |
|Sochi Autodrom                    |10.36                 |
|Suzuka Circuit                    |10.2                  |
|Circuit de Nevers Magny-Cours     |10.0                  |
|Silverstone Circuit               |9.71                  |
|Bahrain International Circuit     |9.32                  |
|Circuit Gilles Villeneuve         |9.29                  |
|Autodromo Nazionale di Monza      |9.05                  |
|Circuit Paul Ricard               |9.0                   |
|Baku City Circuit                 |8.56                  |
|Yas Marina Circuit                |8.55                  |
|Autódromo <NAME>        |8.5                   |
|Circuit de Barcelona-Catalunya    |8.38[...]<jupyter_text>As we can see, there are only 4 tracks where McLaren have, on average, finished outside the points, two of thos are located in Japan. The data might be slightly biased, since there are tracks that don't feature on the calendar every year. It is interesting however, to see that over the past 10 years, McLaren have not done well in their home race at Silverstone.  Different driver performance at McLaren We can now take a look at how the different drivers have done over the years at McLaren.   Average qualifying and finishing position<jupyter_code>avg_positions = sqlc.sql("""
    SELECT concat(drivers.forename, ' ', drivers.surname) AS driver_name, round(mean(results.position), 2) AS avg_position, round(mean(qualifying.position), 2) AS mean_qualifying
    FROM results, races, drivers, qualifying 
    WHERE results.constructorId == 1
        AND qualifying.constructorId == 1
        AND races.year >= 2008
        AND races.raceId == results.raceId 
        ANd races.raceId == qualifying.raceId
        AND drivers.driverId == results.driverId 
        AND results.driverId == qualifying.driverId
    GROUP BY driver_name
    ORDER BY avg_position ASC
""")
avg_positions.show()<jupyter_output>+-----------------+------------+---------------+
|      driver_name|avg_position|mean_qualifying|
+-----------------+------------+---------------+
|   <NAME>|         4.9|           4.57|
|<NAME>|        6.96|           7.29|
|     <NAME>|        7.35|           9.39|
|    <NAME>|        7.56|           9.34|
|     <NAME>|        7.76|           8.36|
| <NAME>|        8.33|          10.67|
|  <NAME>|        9.39|            8.6|
|     <NAME>|       10.16|           11.0|
|  <NAME>|       10.87|          13.06|
|<NAME>|       12.12|          15.07|
+-----------------+------------+---------------+<jupyter_text>This analysis is unfair to some of the drivers, the two drivers that were in the team during it's worse period are <NAME>, and <NAME>. Which is why they are very low in the rankings. It is important to note that all the drivers have a better average finsihing position than qualifying position, except for <NAME>. This is likely because Lewis already qualified so high, there are not as many places to make up starting from 5th compared to starting from 13th. It would also be interesting to see the team's average qualifying position by season:<jupyter_code>avg_positions_year = sqlc.sql("""
    SELECT races.year, round(mean(qualifying.position), 2) AS avg_qualifying, round(mean(results.position), 2) AS avg_position
    FROM races, qualifying, results
    WHERE races.year >= 2008
        AND qualifying.constructorId == 1
        AND results.constructorId == 1
        AND races.raceId == qualifying.raceId
        AND races.raceId == results.raceId
    GROUP BY races.year
    ORDER BY year ASC
""")

avg_positions_year\
    .toPandas() \
    .plot \
    .bar(x = 'year',
         y = ['avg_qualifying', 'avg_position'])
sqlc.sql("""
    SELECT races.year, round(mean(results.position), 2) AS avg_position 
    FROM races, results
    WHERE races.year >= 2008
        AND results.constructorId == 1
        AND races.raceId == results.raceId
    GROUP BY races.year
    ORDER BY year ASC
""") \
    .toPandas() \
    .plot \
    .bar(x = 'year',
         y = 'avg_position')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13838931022029613, 'fraction_numerical': 0.04333694474539545, 'mean_word_length': 2.789272030651341, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 68, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '383608', 'license': 'MIT', 'n_tokens_mistral': 4824, 'n_tokens_neox': 4289, 'n_words': 1180, 'path': 'McLaren.ipynb', 'repo_name': 'youssefragab99/formula_1_project', 'size': '14150'}