Section05_Group04_Deliverable_Phase4.ipynb

{"cells":[{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"1cebe0df-ba5f-4848-8d51-e76dee1a7097","showTitle":false,"title":""}},"source":["# Project Title: Predicting Flight Delays through Machine Learning Classifiers at Scale\n","\n","Our objective is to help airlines better allocate resources and make business decisions through improved flight delay predictions."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"cc2d68e4-0979-4ae5-bd74-837d51c4c75e","showTitle":false,"title":""}},"source":["# Group20 - Phase 4 Deliverable"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"6baa47ce-dfe0-4fa4-b8cd-fcae410e44c0","showTitle":false,"title":""}},"source":["## Section 1: Abstract"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"7f7dcd85-93f9-43b0-bfe2-a16a6308a2ba","showTitle":false,"title":""}},"source":["Delays in commercial aviation are frequent and expensive. Utilizing Flight Data from the Bureau of Transportation Statistics and Weather Data from the National Oceanic and Atmospheric Administration, our Team is tasked with predicting U.S. domestic flight delays two hours before the scheduled departure time.\n","\n","We transformed the data after an initial Exploratory Data Analysis, joined all of our datasets utilizing composite keys, and created new features based on how each flight related to its network. The most important feature we created was previous flight delay, a binary indicator showing whether or not a plane with the same tail number was delayed on the previous flight. Five different models were trained using Decision Trees, Random Forests, Gradient Boosted Trees, Multilayer Perceptrons, and an ensemble to predict flight delays. These models were compared to each other and the baseline (a logistic regression) using the F2 metric. We found that the model with the best F-2 score was the ensemble model that predicted a delay if at least one model did, with an F2 score of 0.558. This value is much higher than our baseline model, which only had a F2 score of 0.091. We found that this was mainly due to the new features we created from the flight data, such as previous flight delay, and graph features such as PageRank."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"0b939307-ec6e-4b45-9bbe-c920858d0556","showTitle":false,"title":""}},"source":["## Section 2: EDA"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"6c344bf7-f97b-40b2-bdf6-63888d66bc71","showTitle":false,"title":""}},"source":["##### Flight Data\n","\n","The primary data source is the Flight Dataset from the Bureau of Transportation Statistics. The data covers flights from 2015 to 2021 and includes 109 features. The Flight Data provides us with the 2 potential response variables:\n","\n","- `DEP_DEL15` :  Classification of the flight being delayed by greater than 15 minutes\n","- `DEP_DELAY`:  Minutes elapsed between Scheduled and Actual Departure Time\n","\n","The dataset includes both numerical and categorical features that could be useful predictor variables. Numerical features include the time (`CRS_DEP_TIME`), date (`FL_DATE`) and distance flown for the flights. Categorical features such as Airline Carrier (`OP_CARRIER`), Plane Identifier (`TAIL_NUM`) are also of interest.\n","\n","We conducted an exploratory data analysis of the entire flight dataset covering years from the 2015 to 2021 (74 million recorded flights). The EDA was conducted with a special focus on computing % of missing values per feature, understanding the distribution, scale and range of values of the features.\n","\n","As a first step, we decided to drop any duplicated flights which dropped the total number of observations drastically from 74 million to 41 million flights. We also decided to filter out cancelled flights that did not have computed minutes in delay as we wanted to keep flights that were delayed to the point of being cancelled. We decided to drop features that are substantially missing, most notably features related to diverted flights.\n","\n","Table 1 Missing Values from Flight Data\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1JJvTJNUSvC0fheL8jXWrfBP50drVAX8M\" alt=\"Google Drive Image\" width=30%/>\n","\n","From calculating the proportion of the Delayed and Not-Delayed classes from the predictor variable, `DEP_DEL15`, we observe that the majority of flights are classified as \"not delayed\". Our sample dataset corroborates our literature review that approximately 17% of all flights are delayed.\n","\n","Table 2 Proportion of Flights that are Delayed (≥ 15 minutes)\n","\n","|DEP_DEL15|cnt_per_group|perc_of_count_total|\n","|---------|-------------|-------------------|\n","|      0.0|      34,374,886|  82.93|\n","|      1.0|       7,076,983| 17.07|\n","\n","We observe from the distribution of `DEP_DELAY` that amongst flights with delays, the majority of delays are less than 30 minutes. It is important to note that negative values are computed for flights that depart early than scheduled. \n","\n","Figure 1 Distribution of flight delays in minutes\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1vmY4OhSkmW1RTWTd-3nNg0wU3FuWTMAb\" alt=\"Google Drive Image\" width=35%/>\n","\n","Amongst the predictor variables in our dataset, we are particularly interested in the scheduled departure time (`DEP_TIME_BLK`), scheduled departure day of week (`DAY_OF_WEEK`), and Airlines Carrier (`OP_CARRIER`). We parsed the scheduled-departure hour of delayed flights and we observe that as the day progresses, the percentage of flights that are delayed steadily increase. We believe that these are network effects at play whereby earlier delayed flights affect later scheduled flight throughout the day. This might be a result of delayed scheduled flights taking up Airport Terminal and Gate capacity.\n","\n","Figure 2 Distribution of Flight Delays (in minutes) by Scheduled Departure Hour and Day of Week\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1aMgGBzs95QEUmZeeo925XG17Mfx1rgRr\" alt=\"Google Drive Image\" width=70%/>\n","\n","\n","As it relates to the day-of-the-week of scheduled flights, we observe upticks in flight delays as a % of total flights on Monday, Thursday, Friday, and Sunday. This may be a result of increased  demand and stress on the Airline/Airport systems on days when passengers are more likely to be traveling for business (Monday - Thu/Friday) split and weekend travellers departing and returning from a trip (Friday/Sunday). \n","\n","\n","Airline Carrier is a notable categorical variable and is of particular interest. We can see a substantial range in flight timeliness performance by Airline Carrier. The worst performing Airline, JetBlue (`B6`) is followed by Frontier Airlines (`F9`) and Virgin (`VX`).\n","\n","Figure 3 Distribution of flight delays by Airline Carrier\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1GBJ1kackeEuUbRFQTHInkHvFurbNL_ja\" alt=\"Google Drive Image\" width=40%/>"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"33f53640-0fbc-426a-bdda-555a70ebb955","showTitle":false,"title":""}},"source":["#### Weather Data\n","\n","The second data source is the Local Climatological Data (LCD) dataset from the National Centers for Environmental Information. The data covers the period from January 2015–December 2021). The LCD data contains summaries of climatological conditions from weather stations managed by the NWS, FAA, and DOD. Since the weather impacts airline flight operations, it is reasonable to assume that weather features may have predictive power in our model for flight delays.\n","\n","We conducted an exploratory data analysis (EDA) on the weather set to spot anomalies, calculate the number of missing values, understand the distribution, scale, and range of values of the features, and propose how the features may have explanatory value for our model.\n","\n"," The data includes hourly observations of temperature, humidity, wind direction and speed, barometric pressure, sky condition, visibility, and weather phenomena. These are critical observations of the atmosphere that help forecasters predict the weather.  \n","\n","We reviewed the contents of the LCD dataset covering the period January-2015- December 2021. The observations correspond to 15079 weather stations. Overall the dataset contains 124 columns and approximately 900 million rows. We filtered the observations to include only weather stations located at airports in the US and territories. After this reduction, the dataset contained weather observations for 379 weather stations with 35 million rows and 26 columns.\n","\n","We reviewed the contents of the weather observations to understand the contents, units of observation and report types. The dataset contained observations from seven differnt types of reports. Based on the report type, we decided to only allow observations from reports taken at hourly frequency (FM-15) and special weather reports (FM-16). This further reduced the nunber of rows to 31 million.\n","\n","We estimated the number of missing values from any of the weather features in the model. Table 3 shows the dataset's features and the number of missing values. Table 4 shows the data types in the raw data. We reviewed each weather feature to assess their usefulness in explaining weather delays. The pressence of many zeroes or missing values does not mean the feature has to be discarded.\n","\n","**Table 3 Missing Values from weather data** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1e2GVFKPLgwbTI_lvGByiqlcXYXqESSA9\" alt=\"Google Drive Image\" width=30%/>\n","\n","**Table 4. Data dictionary from weather data after removal of features** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1qIce1wYJ9ERAWB5ONOg94Hupx9MZHz9Z\" alt=\"Google Drive Image\" width=30%/>\n","\n","\n","We reviewed weather features that are typical causes for weather delays. According to the literature weather delays are related to the presence of thunderstorms, Ice, Freezing rain, snow and fog. These events may impact airport runwayt capacity, traffic control and ground operations and cause delays. The features in the weather data may be able to be used to predict delays at airports.\n","\n","#### Precipitation\n","\n","The feature 'HourlyPrecipitation' contains the values of liquid precipitation observation values given in inches to hundredths. Traces amounts are reported as T. Null or blank values indicate no precipittion was observed. We replaced all the null values and \"T\" values with zero.  values with \"S\" indicate suspect values. These values accout for only 0.1 percent of the data. Some of these suspect values correspond to very high amounts of precipitation per hour recorded, exceding 9 inches of rain per hour. These extreme events may be anomalies in the data or correspond to rare meteorological events. Overall 92 percent of the values in the \"HourlyPrecipitation\" feature are zero. We retain this feature because the amount of precipitation is likely related to weather delays at airports. Having such a large percentage of zeroes is expected since not all locations get rain every month or season.\n","\n","#### Temperature\n","\n","The weather data set contains three features for temperature observations, wet bulb, dry bulb, and dew point temperature. The difference between wet bulb and dry bulb temperatures is a measure of the humidity of the air. The higher the difference in these temperatures, the lower the humidity. The dew point is the temperature under which water vapor condenses. The higher the dew point, the higher the moisture in the air. Figure 4 shows a statistical summary of the three temperature features. The units of temperature are in degrees Fahrenheit.\n","\n","**Figure 4 Statistical summary temperature features** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1OHDGBSTRSLTifw6dsi3zY0VC1aZQ8UI4\" alt=\"Google Drive Image\" width=70%/>\n","\n","Figure 5 shows an example of the yearly and daily variation of the temperature. The left panel shows the seasonal variation of the reported temperatures reported by the Dallas-Fort Worth airport (DFW). The right panel shows the hourly variation of the temperature at this airport during a day (7-4-2015).  Changes in temperature may be indicative of weather events such as cold fronts, storms that may impact airport operations and cause delays.\n","\n","**Figure 5 Daily and average temperatures reported by the DFW airport weather station** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=10jnC7A3wUc2-Mt-XI8ync9qq5p21iLT2\" alt=\"Google Drive Image\" width=70%/>\n","\n","#### Pressure\n","\n","Figure 6 shows a statistical summary of the pressure features in the data set. The 'HourlyStationPressure' is the atmospheric pressure observed at the station and reported in inches of Mercury (in Hg). This feature has 2 percent of missing values. The range of values seems reasonable, with the lowest pressure reported approximately 9 psi, corresponding to higher altitude stations or weather events. \n","\n","The 'HourlyPressureTendency' indicates increases or decreases in pressure over the 3 previous hours. The 'HourlyPressureChange' indicates a difference in pressure over the past 3 hours. Rapid drops in atmospheric pressure measurements are associated with cloudy, rainy, or windy weather, while rapid increases are associated with clear skies. This feature could be valuable for our model however 77 percent of the values for these features are missing and they will not be introduced in our model. However we can estimate a similar feature using the 'HourlyStationPressure' to estimate the change of pressure over time and introduce that in the model.\n","\n","**Figure 6 Statistical summary pressure features** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1MohYdajBcUxgvEp3bysRt4Vf91hhnQoh\" alt=\"Google Drive Image\" width=70%/>\n","\n","#### Wind\n","\n","Figures 7 and 8 show the statistical summary of the wind-related features in the data set. Strong surface winds, such as crosswinds and gusts, can impact aircraft coming to land or takeoff, causing disruptions to airport operations. While operations depend on runway length and aircraft size, in general crosswinds in excess of 30-35 kts (about 34-40 mph) are generally prohibitive of take-off and landing.\n","\n","\n","Some anomalous high wind speed values were removed, and most observations are less than ten mph winds. The most significant frequency for wind direction was less than 36 degrees. \n","\n","**Table 5  Statistical summary of wind features** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1DJnTI3BgsOELhKR6x5X946vz-sADELKf\" alt=\"Google Drive Image\" width=50%/>\n","\n","To review the relationship of wind direction with delays we reviewed the distribution of the wind direction at airports. Figure 3 shows an example of the distribution of the winds reported at the DFW airport during 2015 and the runway configuration of this airport. The runway configuration was built considering the prevailing winds, to reduce the impacts of crosswinds on the runways which can cause delays. This indicates that to use the wind direction as a feature that can cause flight delays, the individual airport configurations must be considered. In Figure 3, we can observe that the runwas are placed in an orientation compatible with the prevailing winds. The only wind directions that may affect airport operations occurr at a very low frequency  (45 and 90 degrees)\n","\n","**Figure 7 Distribution of wind direction at DFW airport and runway configuration** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1KqxAnLtgkY2vLUVa6dt7PzIoNu-AIoI7\" alt=\"Google Drive Image\" width=70%/>\n","\n","Figure 4 shows a similar situation with LGA airport. In the figure we can see that the orientation of the runways at this airport is compatible with the prevailing winds in the area. The perpendicular runways at this airport leverage the direction of the prevailing winds, therefore the wind direction impact is lower. From this review we believed the wind direction combined with speed and runway orientation may explain some delays.\n","\n","**Figure 8 Distribution of wind direction at LGA airport and runway configuration** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1GUv1Oab2HMh_fbNbZ8IWhv_1YDtk7ZQ1\" alt=\"Google Drive Image\" width=70%/>\n","\n","Figure 9 shows the hourly wind and wind gusts speeds for the years 2015-2021 in the DFW airport.Since in general crosswinds in excess of about 34-40 mph are restrictive of take-off and landing, the use of these two features in adddition with the wind orientation may have explanatory power for some delays related to weather.\n","\n","**Figure 9 Distribution of wind and wind gust speeds at DFW airport** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=11EYqpIVCPTCtsftH-7XvHFmpZVN6xe9Y\" alt=\"Google Drive Image\" width=70%/>\n","\n","#### Visibility\n","\n","Table 6 shows the statistical summary of the visibility feature. For meteorology, this is the horizontal distance an object can be seen and identified in whole miles. The aviation industry uses visibility from the control tower and the pilots' visibility by looking at the runway markings. Weather changes that affect visibility in the control tower or runway will lead to disruption in airport operations, delays, or cancellations. There are some anomalous high values of visibility in the dataset, but most values are around 10 miles. These values were removed. The relationship between visibility and flight delays is complex. The systems required to support landing and take off operations under low visility depend on the airport. The ability to operate under restricted visibility such as fog also depends on the instrument rating of the pilot. In general commercial airlines in the US operate under regulations that may require a minimum of 1 mile visibility for two engine planes and  0.5 mile visibility for three ore more engines. These minimums for take-off are useful information to use this feature in our model to explain weather-related delays.\n","\n","**Table 6 Statistical summary of visibility features** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1S63BF8CBQVmLeQyPK5Nr5Le4Ily1Wghy\" alt=\"Google Drive Image\" width=70%/>\n","\n","Figures 10 and 11 show panels overlaying certain weather features and the delays caused by weather as indicated by the flight database.\n","From these examples we can see the correlation between weather events  weather delays. In this case snow and precipitation in the Boston and Dallas airports during 2019. The bottom panel in both figures shows the percentage of flights delays caused by weather, while the panels above show the relevant weather feature that preceded the delays.\n","\n","**Figure 10 Distribution of weather features and delays at DFW airport** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=11u1-6D8ZPpg3M1Zg0ueIeXOZSmR6fuN-\" alt=\"Google Drive Image\" width=70%/>\n","\n","**Figure 11 Distribution of weather features and delays at Boston airport** \\\n","<img src=\"https://drive.google.com/uc?export=view&id=1TknPdnYAbYIYtJ-KWC7nOb6wJwVhsLsO\" alt=\"Google Drive Image\" width=70%/>\n","\n","Figure 12 shows the distribution of entries per station in a 3 month period. If a station reports their data once every hour, they should have about 2160 entries in the dataset, which is clearly not the case for the majority of the stations. A lot of them seem to report their data every 20 minutes, which is why there're a lot of stations in the 6300 - 6750 bucket. There's also a not insignificant number of stations that seem to report their data every 5 minutes. There're also a lot of stations who report with a lower frequency that once an hour on average. This may pose a challenge when deciding how to join the weather data to the flight data, as some stations may not have recent enough data, and it may not be uniform across all flights\n","\n","**Figure 12 Reporting Frequency by Station**\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1G_ZWaStDVjX1r6aa2KHMbwfOB0WJBenY\" alt=\"Google Drive Image\" width=40%/>\n","\n","Figure 13 shows the pearson correlation between the previously discussed variables. As expected, all 3 temperature measurements, despite their differences, are highly positively correlated, which likely means that including more than one of them will not provide the model with more information. Humidity and Visibility also have a relatively high correlation, although negative in this case. As humidity increases, visibility decreases, as it becomes harder to see longer distances.\n","\n","**Figure 13 Pearson Correlation Matrix for Hourly Weather Data**\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1fV7nV_Wao9CCXSQ8KoP30JCbfq2Oh9gY\" alt=\"Google Drive Image\" width=40%/>"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"6c545fc0-88a0-4e7d-aec2-9f6607c1eebd","showTitle":false,"title":""}},"source":["### Joining the Datasets\n","\n","To join the flights and weather data, we need to figure out the closest weather station to a given airport through minimizing distance. One of the datasets provided includes information about each station and their proximity to other neighboring stations that have ICAO codes, this includes the distance to them. We noticed that a lot of these showed a disance of 0, which meant that the station was actually inside the airport.\n","\n","In order to assess whether the airports in our flights dataset had a station inside them we had to join them with the station dataset. However the flights dataset only included the IATA code, and not the ICAO code. This meant that we needed an external dataset to join these. For our initial pipeline we used the dataset provided by the [Python Package index](https://pypi.org/project/airportsdata/) which not only provided both IATA and ICAO codes, but also the respective timezones for each airport which will be needed for the join between flight and weather data.\n","\n","We then obtained all 388 airports in the flights dataset, by selecting the ORIGIN column and finding the distinct values within in. We then joined it with the external airport dataset to add the ICAO code. At the same time, we grouped all stations by nearest neighbour ICAO code, and found the station with the minimum distance for each of them. We then merged the closest station with the 388 airports and we found that 381 of those airports had a station with weather reports inside of them.\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1XbOJyf5XzmQJcF2PEvYM-50rgJ5qVUQX\" alt=\"Google Drive Image\" width=50%/>"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"adcdf383-1582-4f66-9406-d2a71b815130","showTitle":false,"title":""}},"source":["### Splitting the data and cross validation\n","\n","Given that we are dealing with a time series we can't just randomly assign the data to either the training or test set.\n","\n","Every flight from 2021 will be assigned to the blind test set, which will only be used for the final evaluation of the model.\n","\n","The rest of the flights (2015 to 2020) will be used for the training set. We will conduct cross validation by implementing a blocking split. The 6 year period will be divided into 5 parts, each of 14 months, with the last one consisting of 12. Each of those parts will be split into two, the first 12 months will be used for training while the remaining 2 will be the dev set. By having the last 2 months of the year in the dev set we will avoid any data leakage.\n","\n","The results of these models will be combined into a weighted average, which will be used to tune the model's hyperparameters until we find a model with satisfying results. \n","\n","```\n","from pyspark.sql.functions import col, monotonically_increasing_id\n","\n","train_set = spark.read.parquet(f\"{blob_url}/train_set\")\n","\n","train_set_1 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2016-01-01'\")\n","dev_set_1 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2016-03-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2016-01-01'\")\n","\n","train_set_1.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_set_1\")\n","dev_set_1.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_set_1\")\n","\n","train_set_2 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2017-03-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2016-03-01'\")\n","dev_set_2 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2017-05-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2017-03-01'\")\n","\n","train_set_2.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_set_2\")\n","dev_set_2.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_set_2\")\n","\n","train_set_3 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2018-07-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2017-05-01'\")\n","dev_set_3 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2018-09-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2018-07-01'\")\n","\n","train_set_3.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_set_3\")\n","dev_set_3.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_set_3\")\n","\n","train_set_4 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2019-09-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2018-09-01'\")\n","dev_set_4 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2019-11-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2019-09-01'\")\n","\n","train_set_4.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_set_4\")\n","dev_set_4.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_set_4\")\n","\n","train_set_5 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2020-10-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2019-11-01'\")\n","dev_set_5 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP >= '2020-10-01'\")\n","\n","train_set_5.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_set_5\")\n","dev_set_5.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_set_5\")\n","```"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"a714daf3-c7d3-48ca-a866-c2182200aa6f","showTitle":false,"title":""}},"source":["### Workflow Diagram\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1_Bxfsy4DabAr9ach0NTp6kd2ulXo5E5N\" alt=\"Google Drive Image\" width=50%/>"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"ea91caa6-0da9-48d4-ac71-ab724c1dda6e","showTitle":false,"title":""}},"source":["## Section 3: Feature Engineering"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"ee1c9b38-a561-4f8b-a143-816a9e916992","showTitle":false,"title":""}},"source":["### Weather features\n","\n","We introduced categorical features to introduce the present of weather events that may explain certain flight delays. The categorical features indicate the presence of weather related to flight delays such as thunderstorms, snow, fog and ice. \n","\n","### Previous Flight Delay\n","\n","Airlines have a finite number of aircrafts, so each aircraft has a route that it follows every day, going from airport to airport. An aircraft being delayed in one airport likely means it will arrive late at its destination, and that may impact that aircraft's next flight's departure time.\n","\n","In order to add this feature to our models, we utilized the Window object, partioned by Tail Number (the aircraft ID), and ordered it by departure time. Each flight then had the previous delay for the previous flight for its aircraft added as a feature. After an initial analysis, we saw that this had a relatively high correlation (> 0.3) with the current's flight delay. One constraint we added to this feature was that we only counted the prev_delay if it was recorded at least 2 hours before the flight's departure date, this helped us prevent the use of any data that could not be avaiable when making a new prediction.\n","\n","### Average Airport Delay\n","\n","Another feature we created was the percentage of flights that are delayed in a given time window. While the previous feature allowed us to see how an individual flight could be affected by previous delays, this feature provides information about the airport itself at a given moment in time. If there are a lot of flights delayed in the previous 3 hours, chances are that any following flights could potentially be delayed as well. We tested this on many windows of time and found that the 3 hours window had the bigger impact on our models. These features also had a relative high correlation with the 15 flight delay.\n","\n","Correlation with 12 hour delay ratio in Origin Airport 0.12216519883229544\n","\n","Correlation with 6 hour delay ratio in Origin Airport 0.21077215134158905\n","\n","Correlation with 3 hour delay ratio in Origin Airport 0.257984686955184\n","\n","Correlation with 1 hour delay ratio in Origin Airport 0.24283098426761895\n","\n","### PageRank Features\n","We were interested in a feature that can indicate the importance or influence of an airport and its role in propagating delays to other flights. Since the US airlines operate in a hub-and-spoke system, some airports play the role of hubs and are critical points of connection for passengers traveling to other airports. Therefore, we modeled the flight data as a directed graph and used the PageRank algorithm to rank the airports considering their connectivity to other airports.\n","\n","The PageRank score indicates the airport's importance in the network and its influence on the propagation of delays. We estimated PageRank scores quarterly and applied the results to the next quarter. We reused the functions for graph initialization and PageRank from Homeworks 5. To use these functions, we converted the flight data into an RDD in the appropriate format using the number of flights between airports as weights. Finally, we used the PageRank algorithm with a teleportation probability of 0.01 and a maximum number of iterations of 20. We introduced the PageRank features as a continuous variable for the origin and destination airports for every flight in the dataset. Figure 14 shows the PageRank for the top ten airports for the period 2015-2021. To prevent leakage, we calculated the PageRank only of the training set, from the previous quarter.\n","\n","From this calculation we can identify the most important airports in terms of their connectivity, with the major hubs Atlanta (ATL) and Chicago (ORD) ranked in the top. The black vertical line indicates the start of the COVID-19 pandemic travel disruption. During this period, airlines drastically reduced the number of commercial flights. From the figure it appears that the disruption affected the connectivity of the hubs, as Dallas DFW and Denver DEN increased their PageRank during this disruption.  \n","\n","Figure 14 PageRank for the airports\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1EcsjU8V7laoaKIsps-pjSoRwzCC7-imL\" alt=\"Google Drive Image\" width=60%/>\n","\n","### Delay States of the US airport network\n","\n","We identified in the litearure, a feature to capture the overall delay conditions of the network. The delay state represents the network's delay patterns at a point in time. The delay state is defined as the median delay in every origin-destination at every hour. These delay states form clusters around certain times and destinations and can be used as a feature in our model.  On Figure 15-1. We can see a map of the busiest city pairs in terms of airline operations and the average delay in minutes.\n","\n","Figure 15-1 100 busiest origin-destination pairs 2015-2021\n","<img src=\"https://drive.google.com/uc?export=view&id=1IeOChGNycW0PisPopmJwOtPNKjb2_Cve\" alt=\"Google Drive Image\" width=60%/>\n","\n","\n","We estimated the clusters of delay states using the K Means clustering algorithm over a period of time.\n","We selected 5 clusters every year after reviewing the metrics for the K Means algorithm. We calculated the delay states for each year from 2015-2020 using only the data available in the training sets. Figure 15 shows the clusters of pattern delays in 2015, from the lowest to the highest delays in the network. We can see interesting patterns, for example, in the cluster with the most delayed flights, most  of the these delays are concentrated in flights that involve DFW, ORD and LAX. This feature was introduced as a categorical feature.\n","\n","Figure 15 Delay State for Airline network in 2015\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1HEcklSgn9NpSrO2IvxOUbv8onLq5GyF6\" alt=\"Google Drive Image\" width=60%/>\n","\n","### Airport capacity and demand/capacity features\n","\n","According to the literature, there is a correlation between the number of delayed connecting flights and the total number of flights out of an airport. From this study we introduced three indicator variables defined as follows: \n","\n","The crowdedness of the airport is defined as the number of scheduled flights and flights that depart from the airport i at the interval \\\\(\\Delta t \\\\) :\\\n","<img src=\"https://drive.google.com/uc?export=view&id=1evseP-HJKL2lCSr1QbVKgjJnJwnUyHC7\" alt=\"Google Drive Image\" width=10%/> \n","\n","where  \\\\(f_i\\\\) and \\\\(f_i^s\\\\) represent the number of actual and scheduled flights at airport \\\\(i\\\\) in a time interval \\\\( [t - \\Delta t, t]\\\\) where t is the departure time  and \\\\(\\Delta t = \\{1h, 2h, ..., 12h}\\\\) \n","\n","\n","The ratio of actual flights over scheduled flights out of an airport  \\\\(i\\\\) at the interval  \\\\(\\Delta t \\\\) \\:\n","<img src=\"https://drive.google.com/uc?export=view&id=1kFnq9LGUrc_isZJOTIeUzUk_BwmRZxAy\" alt=\"Google Drive Image\" width=10%/>\n","\n","And the overall number of flights in the network at the interval  \\\\(\\Delta t \\\\) \\:\n","<img src=\"https://drive.google.com/uc?export=view&id=1xS_kzQ-5yhybe_T3bHur82iK1KKRqpik\" alt=\"Google Drive Image\" width=10%/>\n","\n","These features were introduced in the model as continous variables. We considered all the flights departing at an airport at a given hour are assigned the same values for these features. Since there are no scheduled operations betwen 11 pm and 5 am in most airports, those flights would not have a valid value for the ratio of actual over scheduled flights.  We replaced these null values with a value of one since airports at those hours are not congested."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"14a295b5-ec43-40ca-a2ae-f2622d074f9e","showTitle":false,"title":""}},"source":["## Section 4: Hyperparameter Tuning"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"7115361d-3ac2-4614-b999-63ccdb367649","showTitle":false,"title":""}},"source":["For our decision tree models, we focused primarily on two hyperparameters: max depth of the trees and max number of bins. Max depth indicates when the model stops splitting. Max number of bins is the number of bins used in discretizing continuous, numerical features. With a higher max depth are expressive in capturing outcomes and have potentially higher performance (in F2, accuracy, etc.), but run the risk of overfitting and taking too long to run. Having too many max bins has a similar tradeoff, wherein more bins allow for more granular split decisions, but can be computationally expensive. \n","\n","While not directly related to hyperparameter tuning, we've now realized that we may have too many features and will reduce the number of features in newer models. In addition, we will consider different types of impurity measures to decide splits like Gini and Entropy for decision trees. Similarly, we'll continue to tune the hyperparameters by validating results on other test data to prevent overfitting. In our decision trees, we used a max depth of 10 and max bins of 350. \n","\n","Random forests had an additional hyperparameter for the number of decision trees in the random forest. After testing different sets of parameters, we ended up going with a max depth of 10, and 100 decision trees.\n","\n","Gradient boosted trees have similar hyperparameters with the addition of maxiter and stepsize. The former refers to the max number of iterations of gradient boosting and latter refers to the learning rate of the model. When the max number of iterations is too high, the model runs the risk of overfitting. To avoid overfitting, we monitored the error between prediction and validation data. The number of iterations we choose after the hyperparameter tuning was 6.\n","\n","Our approach to hyperparameter tuning involved grid search and using F2 score as the scoring mechanism. The grid search method involves creating a \"grid\" among hyperparameters to be tuned and evaluating the F2 score for each combination in the grid. Evaluating the hyperparameter combinations requires cross validation hence the name GridSearchCV. The output of hyperparameter tuning would be the optimal F2 score along with the hyperparameter values needed to achieve the highest score. Other techniques include random search though this is employed in discovery and can be a time intensive process."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"ba80a8f6-9d63-49c7-b9f6-2380a6262d42","showTitle":false,"title":""}},"source":["## Section 5: Modeling Pipelines"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"1563999c-1783-40ea-95bc-d1ffce7df45d","showTitle":false,"title":""}},"source":["The ultimate goal of our modeling pipeline was three-fold: **Feature Selection**, **Hyper-Parameter Tuning**, and **Model Selection**.\n","\n","In our modeling pipelines, we tried to write as many reusable functions as we could to make configuring and tweaking our models easier. In the attached notebook, we compartamentalized the features and various helper functions used in the modeling pipelines. The modeling pipeline begins with ingesting the joined parquet files that were split by `FL_DATE` for our Blocked Time Series Cross Validation.\n","\n","Figure 16 Model Pipeline Flow Chart\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1aQIeCjpFDVhPG2bGNM-liTabslD6mFPO\" alt=\"Google Drive Image\" width=75%/>\n","\n","#### Feature Selection:\n","\n","We began by running decision tree models with different categories of features:\n","\n",">- Weather Features\n",">- Airport Capacity (QRN)\n",">- Airport PageRank\n",">- Clustered Delay States\n",">- Previous Flight Feature (based on Tail Number)\n",">- Other Flight Features (Airline Carrier, Seasonality)\n","\n","We utilized \"feature importance\", a measure of the decrease in node purity weighted by the probability of reaching that node. We made the decision to include the \"top 3\" features by relative importance from each category to include as part of our feature selection.\n","\n","#### Hyper Parameter Tuning and Model Selection:\n","\n","Once we finalized our feature selection process, we examined Decision Tree, Random Forest, Gradient Boosted Trees, and Multilayer Perceptron models. While both Decision Tree and MLP models were introduced both categorical and continuous features into the VectorAssembler, continuous features were preprocessed using`MinMaxScaler`. This preprocessing step done as many of the continuous features did not exhibit normal distributions. We utilized the loss function as the Gini impurity in the decision trees. The formula is:\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1hSGRzjkdIqE40J2M2ymrfxlOT_Gf-J6x\" alt=\"Google Drive Image\" width=25%/>\n","\n","where C is the total number of classes and i is a selected class out of C. Gini impurity (G) measures the homogeneity of nodes in the decision tree.\n","\n","We experimented through combinations of hyperparameters relevant for each type of model against cross validations. Utilizing average F2 scores, the best hyper-parameters were utilized to fit the full train dataset and evaluated on the full test dataset. We leveraged `mlflow` to log all relevant metrics with a focus on F2 and recall. Based on the results of the hyper parameterized models, we selected the best Model.\n","\n","#### Custom Ensemble Models:\n","\n","We also wanted to implement novel approaches including the use of ensemble methods whereby all four models (hyper-parameterized Decision Tree, Random Forest, Gradient Boosted Tree, and Multilayer Perceptron) \"vote\" on the final prediction:\n","> - Vote by Majority - the majority prediction of `DELAY` or `NO DELAY`\n","> - One Positive Voting - if one model suggests delay, predict `DELAY`\n","> - One Negative Voting - if one model suggests no-delay, predict `NO DELAY`"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"f9c6e5ac-7f0e-41cf-8748-659ef7892982","showTitle":false,"title":""}},"source":["#### Data Leakage\n","\n","One concern we had throughout this project was data leakage. Data Leakage is usually defined as information that is not part of the training dataset being used to train the model. Since most of our data comes from multiple time series (flights and weather data), our biggest concern in terms of data leakage was making sure when did not use data that would not have been available when making a prediction (2 hours before the scheduled departure). For example, data leakage could have occured when aggregating some of the hourly data. If a flight left at 10.30am, which meant the prediction happened at 8.30am, we couldn't just aggregate data about all flights from 8 to 9 am, since half of those would take place after our prediction. Any kind of time-based feature had to take into account when exactly the prediction was being made, in order to avoid leaking any future data into our model.\n","\n","Our pipeline does not suffer any data lakeage since we took several measures to prevent any data leakage:\n","1) Changing all timestamps to UTC so we would be able to compare all data, and prevent any accidental data leakage when joining it..\n","2) Create a \"prediction timestamp\" two hours before the scheduled timestamp and use that to do any joins.\n","3) When looking at the previous flight delay for a particular aircraft, we only counted it as delayed if it had happened prior to the 2 hour window.\n","4) The same applied when looking at the average flight delays by airport in a given time frame, and any other features that used other flights' data.\n","5) When training the model, we used 2021 as the test set, since using any previous year would mean we were predicting the past with data from its future."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"c85d5260-d40f-4145-ac1c-b84bc41a6ad9","showTitle":false,"title":""}},"source":["####Unbalanced class treament\n","The EDA indicated there was a significant data imbalance between the classes for the model. This imbalance has an enormous impact on the predictive power of models. Therefore, we attempted to implement SMOTE to solve the class imbalance. However, the running time of this facility needed to be faster to be of practical use. Thus, we decided to use a downsampling approach. We decided to use \"downsampling\" - sampling from the majority class to address class imbalances issues with flight delays"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"1e998518-e658-425d-b197-f15091c61248","showTitle":false,"title":""}},"source":["## Section 6: Results and Discussion"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"ceb29056-e9f1-4179-bc2d-cae395a1dfec","showTitle":false,"title":""}},"source":["#####Metrics  \n","We selected the metrics to evaluate the performance of our models considering the business impact on our customer (the airline) due to decisions made based on incorrect predictions from our model. In addition, since we are using a classification model, we considered the ramifications on the business from false positives and false negatives. \n","\n","We considered the costs incurred by the airline attributed to delays. Airlines must bear the additional costs from their crew, costs for accommodating disrupted passengers, and the costs of aircraft re-positioning. \n","There is also a cost of lost demand from potential customers who consider an airline's reputation for flight delays and customer service before purchasing. Given the competitive nature of the business on specific domestic markets, this cost may be substantial.\n","\n","While our model cannot be used to mitigate the costs of most of these factors, our model can assist the airline in improving its reputation for customer satisfaction in case of delays that disrupt travel. Taking proactive measures to assist passengers may positively impact reputation and have a measurable favorable effect on sales and customer loyalty.\n","\n","The consequences of our model errors in predictions can be summarised as follows:\n","\n","- False positive: The model predicted a delay for the flight, but the flight departed on time. The airline incurred additional expenses to meet a demand that did not materialize. There is no impact on the airline's reputation with its customers. \n","\n","- False negative: The model predicted an on-time departure for the flight, but the flight was delayed. The airline did not allocate resources to handle the surge in demand for staff. Passengers are negatively affected by the wait time to obtain information from the airline, and the airline's reputation with its customers suffers. The passenger lifetime value (LTV) is negatively affected by the false negative.\n","\n","The impact of these errors depends on the number of passengers and the customer mix of the flight (share of premium passengers and economy passengers). For example, a mainline carrier operating a route with high competition, such as JFK-LAX, with many first-class and business passengers paying the highest fares, may be more interested in avoiding false negative results to retain the loyalty of premium passengers.\n","On the other hand, low-cost carriers operating a leisure route, such as JFK-MCO, may be more interested in reducing customer service costs and prefer a model with fewer false positives. \n","\n","We used Fbeta score as a metric, with Beta =2  which allows us to give more weight to recall to address the needs of our airline customer.\n","\n","The formal definition of F Beta is:\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1VqqQB1KwiL1oa3fSrygezE_xSP2hzlwC\" alt=\"Google Drive Image\" width=40%/>"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"437cfb88-636d-47e4-84cc-c3bca5282a4a","showTitle":false,"title":""}},"source":["Below are a summary of all of the hyperparameter tuning experiments and the train/validation F2 scores:\n","\n","Table 6: Hyperparameter tuning \n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1gqqv_raoLaU_ljcciZzynONccYpTcWy4\" alt=\"Google Drive Image\" width=75%/>\n","\n","Table 7: Model Results\n","\n","We selected the best hyper-parameters to build our exploratory models. These models were combined into a final Ensemble model that has the best F2 score as shown in the below table.\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1n0OZU60zDUK1coN9l4O46cbyHpjTV5nw\" alt=\"Google Drive Image\" width=75%/>"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"9bf0a38b-3c22-4e35-a382-689a61b953e3","showTitle":false,"title":""}},"source":["###Discussion\n","\n","\n","####Feature Selection\n","Our investigation of flight delay literature suggested evaluating temporal features, such as day-of-month, day-of-week, month-of-year, departure and arrival time, carrier, and origin/destination airport. Therefore, we introduced these features as categorical variables. We considered other factors related to airports and their operations, such as delays of previous flights, scheduled and actual airport usage, crowdedness index, and weather conditions. We introduced the graph feature PageRank to consider the connectivity of the airports and Delay States features to capture the delay condition of the airline network. \n","\n","First, we used a decision tree model to assess the importance of the features. Then, we selected each factor's top three most critical features from this assessment. Figure 17 indicates the importance of the factors.  \n","\n","These are the features selected to our final model:\n","\n","- Weather Features: \n","> `ORIGIN_HourlyWindSpeed`\\\n","> `ORIGIN_HourlyDryBulbTemperature` \\\n","> `ORIGIN_HourlyVisibility` \\\n","> `ORIGIN_TS` (presence of thunderstorm at Origin)\\\n","> `ORIGIN_SNOW` (presence of snow at Origin)\\\n","> `DEST_SNOW` (presence of snow at Destination)\n","- Airport Capacity (QRN):\n","> `N5h_sum` (number of flights that took off in a five-hour interval)\\\n","> `R10h`(ratio of demand over capacity of airport in a 10-hour interval)\\\n","> `R12h`(ratio of demand over capacity of airport in a 12-hour interval)\n","- Airport PageRank\n","- Clustered Delay States : \n","> `delay_state` (clustered delayed states)\n","- Previous Flight Feature (based on Tail Number\n","> `PREV_DEP_DELAY`\\\n","> `AVG_DELAY_ORIGIN_LAST_3` (average delay in minutes from last 3 hours)\\\n","> `PER_DELAY_15_ORIGIN_LAST_3` (% of delays that were at least 15 mins late)\n","- Other Flight Features (Airline Carrier, Seasonality): \n","> `OP_UNIQUE_CARRIER` (categorical variable for airline carrier)\n","\n","Figure 17 Feature Importance\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1jQeHLEH2kUk99zJnMwMaPjpKsN_ojOML\" alt=\"Google Drive Image\" width=50%/>\n","\n","\n","From Figure 17, we can see the importance of the feature `PREV_DEP_DELAY` or the delay of the previous flight. This is expected as an aircraft delayed in one airport likely means it will arrive late at its destination, and that may impact that aircraft's next flight's departure time. Similarly, the average delay in minutes from the last 3 hours is also important. However, unlike the delay of the previous flight, the average delay in the last 3 hours captures any factors that are affecting the entire airport, unlike the previous flight delay, which only captures an effect on one particular flight. Factors like weather, security problems, and other temporary issues are represented by this feature.\n","\n","It is interesting to see that the sum of flights over a 12 and 10 hours interval had the most importance among that category of feature. The inbalance of demand and capacity over such long interval may capture the propagation of flight delays across the the network.\n","\n","\n","#### Exploratory Models\n","We investigated the use of classification algorithms to predict if a flight would be delayed. We considered classifier algorithms such as random forests (RF) and gradient boosting trees (GBT) for this binary classification problem. We also investigated using a Multilayer Perceptron neural network (MLP). \n","\n","Next, we built exploratory models using DT, RF, GBT, and MLP. We used a grid search with a cross-validation function to find the best parameters for the DT, GBT, and MLP models according to the F2 score.  The MLP model is a fully connected neural network that was relatively easy to integrate with our model pipeline. The MLP utilizes an initial layer of *m* nodes, representing the number of features, with a final output layer of 2. We had limited time to experiment with hyperparameter tuning, where we primarily changed the number of layers and the number of nodes per layer. We ultimately found that a MLP architecture of (44 - Sigmoid - 44 - Sigmoid - 2 - Softmax) produced the best F2 score for our selected features.\n","\n","#### Final Model\n","Finally we combined our four models into a single final model using a voting classifier approach. Although we are using primarily tree-based models, not all the models agree completely on the predictions. Our ensemble model uses a \"one positive voting\" rule. If any of the individual models predicts a delay, that flight is classified as a delayed flight. The performance of the final classification model is shown in Table 7, along with the performance of the four exploratory classification models.\n","\n","The F2, precision, and recall for the final ensemble model were 0.558, 0.366 and 0.643, respectively. These metrics were produced by evaluating on the blind test set. \n","\n","Figure 18 illustrates the confusion matrix of the final model. 67% of the delayed flights are correctly classified\n","\n","Figure 18\n","Confusion Matrix Final Model\n","\n","<img src=\"https://drive.google.com/uc?export=view&id=1JVne0agHsjtKQ0VLh3N716FV6KoKqv06\" alt=\"Google Drive Image\" width=50%/>\n","\n","Upon reflection on our model choices, we realize that we are heavily realuying on decision-tree type algorithms. We could improve the model by considering other types of architectors, such as suppor vector machines and other classifiers.\n","\n","These results are not completely satisfactory. In essence, we are only predicting about 2/3 of the delayed flights accurately, and a non-singificant number of flights would be marked as delayed despite not being so. Perhaps we could have found other sources of information that could have provided more data about the delays. Information like number of passengers in the flight, or aircraft capacity could provide information about other reasons why a flight is delayed (more passengers that need to board means more chances for the flight to be delayed).\n","\n","Despite that, we still think this could give the airlines a tool to better manage their staff, and customer expectations when it comes to the flight's departure time."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"c0ca24eb-c6f0-4274-8818-2c5a7f571a6c","showTitle":false,"title":""}},"source":["###Gap Analysis\n","\n","If we compare our results to the leaderboard, we can see that there are a few who have an f2 score close to 0.7, which is higher than ours.\n","\n","Our final model had an f2 score of 0.558. There is a significant difference in performance here. One particular method that we did not explore was weighing our training data differently. We could have determined if a particular year was more relevant than others for the 2021 data, and weighted that year higher than the rest.\n","\n","Another feature that we did not focus as much as we would have liked was departure and arrival times. We could have turned that \"continuous\" feature into a categorical ones. It's unlikely the relationship with the delay was linear, instead different times of day could have had a different result.\n","\n","Since we explored a variety of different models, it doesn't seem that the gap came from not trying a particular model. We could have however done a more extensive hyperparameter tuning, and even more experimenting."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"7bdee335-f149-405b-a038-55fd6db07877","showTitle":false,"title":""}},"source":["## Section 7: Conclusion\n","Every year flight delays cost the economy billions of dollars in lost productivity. We believe that having a model that can be used for forecast flight delays can be very attractive to airlines, airports and the travel industry in general. \n","\n","Our primary use case for the flight delay prediction model is Airline resource planning and allocation. For example, with a two-hour notice, airlines could allocate corporate resources in anticipation of the fallout from delays (i.e., surge resourcing for help and call centers). Airlines could also use the model to identify areas for improvement in flight scheduling and crew allocation, improving on-time performance and reducing costs.\n","\n","To build this model, we started with the hypothesis that flight departure delays can be explained by changes in weather patterns and features related to airport locations, origin and destination pairs, seasonal travel patterns as well as graph features that allow to model delay propagation accross the network.\n","\n","We generated a set of features that capture these dynamics.:\n","  * Weather conditions \n","  * Degree of connectivity of the airports in the network\n","  * Condition of flight delays across the network\n","  * Airport operations and demand and capacity balance.\n","\n","Using these features, we built classification models for U.S. domestic flights and assessed their power to predict flight delays. We conducted experiments on the entire data set to fine tune the hyperparameters of the models and finally we built a final ensemble model combining different types of architecture. We evaluated our model's predictive power using the 2021 blind test data.  As a result, our proposed model indicated an F2 score of 0.558. There is room for improvement in our model. A review of efforts from other teams indicated we can improve our models by weighing the training data differently and introduced other features to add more power to our model.\n","\n","Our model has limitations since it only involves U.S. domestic flights, and it does not consider the interaction of international flights and foreign airlines arriving or departing from the airports. An interesting future research project would be to extend the model to include these flights. Another exciting suggestion for future research would be to include the impact of major atmospheric weather patterns such as tradewinds across oceans and El Nino and La Nina phenomena on long-haul routes across the oceans."]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"10ef4841-d53d-4fc1-ad87-2e59457999ae","showTitle":false,"title":""}},"source":["## Data Lineage"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"479e2d9d-3d86-4a42-bd9e-8d2bc9b900eb","showTitle":false,"title":""}},"source":["###Lineage from Raw Data Files to Cleaned Joined Dataset\n","\n","####Initial setup of environment\n","\n","```python\n","# Initial Configuration\n","from pyspark.sql.functions import col, max\n","from pyspark.sql.functions import col, to_timestamp, monotonically_increasing_id, datediff, when\n","\n","blob_container = \"\" # The name of your container created in https://portal.azure.com\n","storage_account = \"\" # The name of your Storage account created in https://portal.azure.com\n","secret_scope = \"\" # The name of the scope created in your local computer using the Databricks CLI\n","secret_key = \"\" # The name of the secret key created in your local computer using the Databricks CLI \n","blob_url = f\"wasbs://{blob_container}@{storage_account}.blob.core.windows.net\"\n","mount_path = \"/mnt/mids-w261\"\n","\n","spark.conf.set(\n","  f\"fs.azure.sas.{blob_container}.{storage_account}.blob.core.windows.net\",\n","  dbutils.secrets.get(scope = secret_scope, key = secret_key)\n",")\n","\n","# Inspect the Mount's Final Project folder \n","# Please IGNORE dbutils.fs.cp(\"/mnt/mids-w261/datasets_final_project/stations_data/\", \"/mnt/mids-w261/datasets_final_project_2022/stations_data/\", recurse=True)\n","data_BASE_DIR = \"dbfs:/mnt/mids-w261/datasets_final_project_2022/\"\n","display(dbutils.fs.ls(f\"{data_BASE_DIR}\"))\n","\n","# Set partitions\n","spark.conf.set(\"spark.sql.shuffle.partitions\", 1000)\n","spark.conf.set(\"spark.sql.files.minPartitionNum\", 1000)\n","\n","```\n","###Download data for airport codes and timezones and join timezones to original flights and weather dataset\n","\n","We used an external table containing IATA, ICAO, and timezones as an intermediary between the flights, weather, and station datasets. We then joined the timezones and airport codes on these three datasets so that we can join the three tables.\n","\n","```python\n","\n","# Create and save the Aiport Dataset with IATA ICAO and Timezones\n","\n","!pip install -U airportsdata\n","import airportsdata\n","import pandas as pd\n","#get timezone\n","gadb = airportsdata.load()\n","df_gadb = pd.DataFrame.from_dict(gadb).transpose()\n","#get rid of blank iata codes\n","df_gadb = df_gadb[df_gadb['iata'] != \"\"]\n","df_gadb = spark.createDataFrame(df_gadb)\n","\n","df_airport_timezone = df_gadb.distinct()\n","\n","# There are 12 airports missing in this dataset present in the whole flights datasets\n","values = [\n","  (\"KLCK\", \"LCK\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/New_York\"),\n","  (\"KWYS\", \"WYS\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Denver\"),\n","  (\"KSCK\", \"SCK\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Los_Angeles\"),\n","  (\"KPGD\", \"PGD\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/New_York\"),\n","  (\"KVEL\", \"VEL\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Denver\"),\n","  (\"KCNY\", \"CNY\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Denver\"),\n","  (\"KHOB\", \"HOB\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Denver\"),\n","  (\"KESC\", \"ESC\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Detroit\"),\n","  (\"KRIW\", \"RIW\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Denver\"),\n","  (\"KCRQ\", \"CLD\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Los_Angeles\"),\n","  (\"KEFD\", \"EFD\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Chicago\"),\n","  (\"KTKI\", \"TKI\", \"x\", \"x\", \"x\", \"x\", 0.0, 0.0, 0.0, \"America/Chicago\")\n","]\n","\n","df_missing_airports = spark.createDataFrame(values, df_airport_timezone.columns)\n","\n","df_airport_timezone = df_airport_timezone.union(df_missing_airports)\n","\n","df_airport_timezone = df_airport_timezone.withColumnRenamed(\"name\", \"airport_name\") \\\n","  .withColumnRenamed(\"city\", \"airport_city\") \\\n","  .withColumnRenamed(\"subd\", \"airport_subd\") \\\n","  .withColumnRenamed(\"country\", \"airport_country\") \\\n","  .withColumnRenamed(\"lat\", \"airport_lat\") \\\n","  .withColumnRenamed(\"lon\", \"airport_lon\") \\\n","  .withColumnRenamed(\"tz\", \"airport_tz\")\n","\n","df_airport_timezone.write.mode(\"overwrite\").parquet(f\"{blob_url}/airport_codes_timezone\")\n","\n","# Load the Flights data and remove duplicates \n","df_airlines = spark.read.parquet(f\"{data_BASE_DIR}parquet_airlines_data/\")\n","df_airlines = df_airlines.distinct()\n","df_airlines.write.mode(\"overwrite\").parquet(f\"{blob_url}/df_airlines_distinct\")\n","\n","# Load all 4 datasets for initial joins\n","df_airlines = spark.read.parquet(f\"{blob_url}/df_airlines_distinct\")\n","df_airport_timezone = spark.read.parquet(f\"{blob_url}/airport_codes_timezone\")\n","df_weather = spark.read.parquet(f\"{data_BASE_DIR}parquet_weather_data/\")\n","df_stations = spark.read.parquet(f\"{data_BASE_DIR}stations_data/*\")\n","\n","# 3m 1403471 1y 7422037 all 42430592 3.66\n","df_airlines.count()\n","\n","# Drop all columns but neighbor id and icao\n","df_stations_icao = df_stations.select('neighbor_id', 'neighbor_call').withColumnRenamed(\"neighbor_call\", \"icao_station\").distinct()\n","\n","# Rename weather columns\n","df_weather = df_weather.withColumnRenamed(\"NAME\", \"STATION_NAME\") \\\n","  .withColumnRenamed(\"STATION\", \"STATION_ID\") \\\n","  .withColumnRenamed(\"ELEVATION\", \"STATION_ELEVATION\") \\\n","  .withColumnRenamed(\"LATITUDE\", \"STATION_LATITUDE\") \\\n","  .withColumnRenamed(\"LONGITUDE\", \"STATION_LONGITUDE\") \\\n","  .withColumnRenamed(\"DATE\", \"WEATHER_DATE\") \\\n","  .withColumnRenamed(\"REPORT_TYPE\", \"WEATHER_REPORT_TYPE\") \\\n","  .withColumnRenamed(\"SOURCE\", \"WEATHER_SOURCE\")\n","\n","columns_to_keep = [\"HourlyDewPointTemperature\", \"HourlyDryBulbTemperature\", \"HourlyPrecipitation\", \"HourlyPresentWeatherType\", \"HourlyStationPressure\", \"HourlyVisibility\", \"HourlyWetBulbTemperature\", \"HourlyWindDirection\", \"HourlyWindSpeed\", \"YEAR\"]\n","# Drop weather reports that are not FM-15 or 16\n","df_weather = df_weather.where(\"WEATHER_REPORT_TYPE in ('FM-15', 'FM-16')\").select(\"STATION_ID\", \"STATION_NAME\", \"STATION_ELEVATION\", \"STATION_LATITUDE\", \"STATION_LONGITUDE\", \"WEATHER_DATE\", \"WEATHER_REPORT_TYPE\", \"WEATHER_SOURCE\", *columns_to_keep)\n","\n","# Add valid ICAO to weather data\n","df_weather_icao = df_stations_icao.join(df_weather,\\\n","                                           df_weather.STATION_ID == df_stations_icao.neighbor_id,\\\n","                                           'left').where('neighbor_id IS NOT NULL').drop(\"neighbor_id\")\n","\n","#Pull in timezone for weather stations\n","df_weather_icao_tz = df_weather_icao.join(df_airport_timezone,\\\n","                                     df_weather_icao.icao_station == df_airport_timezone.icao,\\\n","                                     'left').drop(\"icao_station\")\n","\n","\n","# Merge airlines and timezones\n","flights_icao_tz = df_airlines.join(df_airport_timezone,\\\n","                                     df_airlines.ORIGIN == df_airport_timezone.iata,\\\n","                                     'left')\n","\n","flights_icao_tz = flights_icao_tz.withColumn(\"flight_id\", monotonically_increasing_id())\n","\n","```\n","\n","###Take joined flights, weather, and stations with timezones and convert dates into UTC timestamps\n","\n","```python\n","\n","\n","# Create unique origins/airports\n","origin_airports = df_airlines.select(\"ORIGIN\").distinct().withColumnRenamed(\"ORIGIN\", \"AIRPORT_IATA\")\n","dest_airports = df_airlines.select(\"DEST\").distinct().withColumnRenamed(\"ORIGIN\", \"AIRPORT_IATA\")\n","all_airports = origin_airports.union(dest_airports).distinct()\n","\n","all_airports_icao = all_airports.join(df_airport_timezone, df_airport_timezone.iata == all_airports.AIRPORT_IATA).where(\"iata is not NULL\").select(\"AIRPORT_IATA\", \"icao\").withColumnRenamed(\"icao\", \"icao_flight\")\n","\n","# Drop entries not in flights data\n","df_weather_icao_needed_tz = df_weather_icao_tz.join(all_airports_icao,\\\n","                                           df_weather_icao_tz.icao == all_airports_icao.icao_flight,\\\n","                                           'left').where('AIRPORT_IATA IS NOT NULL').drop(\"AIRPORT_IATA\", \"icao_flight\")\n","\n","# Save the new datasets\n","flights_icao_tz.write.mode(\"overwrite\").parquet(f\"{blob_url}/flights_with_icao_tz\")\n","df_weather_icao_needed_tz.write.mode(\"overwrite\").parquet(f\"{blob_url}/weather_with_icao_tz\")\n","\n","# Load the new datasets\n","flights_icao_tz = spark.read.parquet(f\"{blob_url}/flights_with_icao_tz\") # 42430592\n","df_weather_icao_needed_tz = spark.read.parquet(f\"{blob_url}/weather_with_icao_tz\")\n","\n","from datetime import  datetime, timedelta\n","from pytz import timezone\n","\n","def get_hour_minutes(depature_time):\n","    depature_time = str(depature_time)\n","    hour = ''\n","    if len(depature_time) <= 2:\n","      hour = '00'\n","      minutes = depature_time\n","    elif len(depature_time) == 3:\n","      hour = depature_time[0]\n","      minutes = depature_time[1:]\n","    elif len(depature_time) == 4:\n","      hour = depature_time[:2]\n","      minutes = depature_time[2:]\n","    return hour, minutes\n","  \n","def get_timestamp(year, month, day, hour_minutes, tz):\n","  hour, minutes = get_hour_minutes(hour_minutes)\n","  utc = timezone('UTC')\n","  tz = timezone(tz)\n","  timestamp = tz.localize(datetime(int(year), int(month), int(day), hour=int(hour), minute=int(minutes)))\n","  return timestamp.astimezone(utc)\n","\n","def get_hour_timestamp(year, month, day, hour_minutes, tz):\n","  hour, minutes = get_hour_minutes(hour_minutes)\n","  utc = timezone('UTC')\n","  tz = timezone(tz)\n","  timestamp = tz.localize(datetime(int(year), int(month), int(day), hour=int(hour)))\n","  return timestamp.astimezone(utc)\n","\n","def get_weather_timestamp(depature_timestamp, hours = 2):\n","  return (depature_timestamp - timedelta(hours=hours))\n","\n","def go_back_an_hour(depature_timestamp):\n","  return (depature_timestamp - timedelta(hours=1))\n"," \n","def get_string_timestamp(timestamp):\n","  return timestamp.strftime(\"%Y%m%d%H%M\")\n","\n","def get_hourly_string_timestamp(timestamp):\n","  return timestamp.strftime(\"%Y%m%d%H\")\n"," \n","get_timestamp = udf(get_timestamp)\n","get_hour_timestamp = udf(get_hour_timestamp)\n","get_weather_timestamp = udf(get_weather_timestamp)\n","get_string_timestamp = udf(get_string_timestamp)\n","get_hourly_string_timestamp = udf(get_hourly_string_timestamp)\n","go_back_an_hour = udf(go_back_an_hour)\n","\n","# Create the timestamp columns for the flight data\n","flights_icao_tz = flights_icao_tz.withColumn(\"FLIGHT_TIMESTAMP\", get_timestamp(\"YEAR\", \"MONTH\", \"DAY_OF_MONTH\", \"CRS_DEP_TIME\", \"airport_tz\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"HOUR_WEATHER_TIMESTAMP\", get_hour_timestamp(\"YEAR\", \"MONTH\", \"DAY_OF_MONTH\", \"CRS_DEP_TIME\", \"airport_tz\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"WEATHER_TIMESTAMP\", get_weather_timestamp(\"FLIGHT_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"HOUR_WEATHER_TIMESTAMP\", get_weather_timestamp(\"HOUR_WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"TWO_HOUR_WEATHER_TIMESTAMP\", go_back_an_hour(\"HOUR_WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"THREE_HOUR_WEATHER_TIMESTAMP\", go_back_an_hour(\"TWO_HOUR_WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"FLIGHT_TIMESTAMP\", get_string_timestamp(\"FLIGHT_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"WEATHER_TIMESTAMP\", get_string_timestamp(\"WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"HOUR_WEATHER_TIMESTAMP\", get_hourly_string_timestamp(\"HOUR_WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"TWO_HOUR_WEATHER_TIMESTAMP\", get_hourly_string_timestamp(\"TWO_HOUR_WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"THREE_HOUR_WEATHER_TIMESTAMP\", get_hourly_string_timestamp(\"THREE_HOUR_WEATHER_TIMESTAMP\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"FLIGHT_TIMESTAMP\", to_timestamp(col(\"FLIGHT_TIMESTAMP\"), \"yyyyMMddHHmm\"))\n","flights_icao_tz = flights_icao_tz.withColumn(\"WEATHER_TIMESTAMP\", to_timestamp(col(\"WEATHER_TIMESTAMP\"), \"yyyyMMddHHmm\"))\n","\n","# Update the file with timestamps\n","flights_icao_tz.write.mode(\"overwrite\").parquet(f\"{blob_url}/flights_with_icao_tz\")\n","\n","def get_utc_difference(tz):\n","  utc_diff = {\n","    \"America/Chicago\": 6,\n","    \"Pacific/Honolulu\": 10,\n","    \"America/Phoenix\": 7,\n","    \"America/Denver\": 7,\n","    \"America/Los_Angeles\": 8,\n","    \"America/New_York\": 5,\n","    \"America/Anchorage\": 9,\n","    \"America/Boise\": 7,\n","    \"America/Detroit\": 5,\n","    \"America/St_Thomas\": 4,\n","    \"America/Puerto_Rico\": 4,\n","    \"America/Adak\": 10,\n","    \"America/Juneau\": 9,\n","    \"America/Kentucky/Louisville\": 5,\n","    \"America/Indiana/Indianapolis\": 5,\n","  }\n","  \n","  return utc_diff[tz] if tz in utc_diff else 0\n","\n","def get_timestamp_weather_df(date, tz):\n","  if date is None or tz is None:\n","    return 'empty'\n","  diff = get_utc_difference(tz)\n","  utc = timezone('UTC')\n","  timestamp = utc.localize(datetime.fromisoformat(date)) + timedelta(hours=diff)\n","  return timestamp.strftime(\"%Y%m%d%H%M\")\n","\n","def get_hour_timestamp_weather_df(date, tz):\n","  if date is None or tz is None:\n","    return 'empty'\n","  diff = get_utc_difference(tz)\n","  utc = timezone('UTC')\n","  timestamp = utc.localize(datetime.fromisoformat(date))  + timedelta(hours=diff)\n","  return timestamp.strftime(\"%Y%m%d%H\")\n","\n","get_timestamp_weather_df = udf(get_timestamp_weather_df)\n","get_hour_timestamp_weather_df = udf(get_hour_timestamp_weather_df)\n","# Create the timestamp for the weather data\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn(\"STATION_TIMESTAMP\", get_timestamp_weather_df('WEATHER_DATE', 'airport_tz'))\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn(\"HOUR_TIMESTAMP\", get_hour_timestamp_weather_df('WEATHER_DATE', 'airport_tz'))\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn(\"STATION_TIMESTAMP\", to_timestamp(col(\"STATION_TIMESTAMP\"), \"yyyyMMddHHmm\"))\n","\n","```\n","\n","####Clean weather variables\n","\n","```python\n","\n","#clean values on hourly precipitation\n","def clean_hourly_precipitation(line, value= 0.0):\n","  if (line is None):\n","    value = 0.0\n","  elif ('T' in line) | (line == \"\") | ('*' in line):\n","    value = 0.0\n","  elif 's' in line:\n","    temp = line.replace('s', '')\n","    if len(line)>=8:\n","      value = (float(temp[:4])+float(temp[4:])) /2.0\n","    else:\n","      value = float(temp)\n","  elif len(line)>=8:\n","      value = (float(line[:4])+float(line[4:])) /2.0     \n","  else:\n","    value = float(line)\n","  return value  \n","\n","clean_hourly_precipitation = udf(clean_hourly_precipitation)\n","# Create the timestamp for the weather data\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn(\"HourlyPrecipitation\", clean_hourly_precipitation('HourlyPrecipitation'))\n","\n","# converte the hourly variables to numeric\n","from pyspark.sql.functions import col\n","columns_to_cast = [\"HourlyDewPointTemperature\",\"HourlyDryBulbTemperature\",\"HourlyWetBulbTemperature\", \"HourlyStationPressure\", \"HourlyWindDirection\", \"HourlyWindSpeed\", \"HourlyVisibility\"]\n","df_weather_icao_needed_tz = (\n","   df_weather_icao_needed_tz\n","   .select(\n","     *(c for c in df_weather_icao_needed_tz.columns if c not in columns_to_cast),\n","     *(col(c).cast(\"float\").alias(c) for c in columns_to_cast)\n","   )\n",")\n","\n","# Clean the Hourly weather event\n","from pyspark.sql.functions import col, to_timestamp, monotonically_increasing_id, datediff, when, split\n","\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn('AU_code', split(col('HourlyPresentWeatherType'), '\\\\|').getItem(0))\\\n","                               .withColumn('AW_code', split(col('HourlyPresentWeatherType'), '\\\\|').getItem(1))\\\n","                               .withColumn('MW_code', split(col('HourlyPresentWeatherType'), '\\\\|').getItem(2))\\\n","\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn('AU_TS', when( (col('AU_code').contains(\"+TS\")) | (col('AU_code').contains(\"FC\")),1).otherwise(0))\\\n","                                   .withColumn('AU_ICE', when(col('AU_code').contains(\"IC\" ), 1).otherwise(0))\\\n","                                   .withColumn('AU_SNOW',when(col('AU_code').contains(\"+SN\" ), 1).otherwise(0))\\\n","                                   .withColumn('AU_FOG', when(col('AU_code').contains(\"FG\" ), 1).otherwise(0))\n","\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn('AW_TS', when((col('AW_code').contains(\"TS\" ))| (col('AW_code').contains(\"FC\")) , 1).otherwise(0))\\\n","                                   .withColumn('AW_ICE', when(col('AW_code').contains(\"FZRA\" ), 1).otherwise(0))\\\n","                                   .withColumn('AW_SNOW',when(col('AW_code').contains(\"SN\"), 1).otherwise(0))\\\n","                                   .withColumn('AW_FOG', when(col('AW_code').contains(\"FG\"), 1).otherwise(0))\n","\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn('MW_TS', when((col('MW_code').contains(\"TS\" ))| (col('MW_code').contains(\"FC\")), 1).otherwise(0))\\\n","                                  .withColumn('MW_ICE', when(col('MW_code').contains(\"FZRA\"), 1).otherwise(0))\\\n","                                  .withColumn('MW_SNOW',when(col('MW_code').contains(\"SN\" ), 1).otherwise(0))\\\n","                                  .withColumn('MW_FOG', when(col('MW_code').contains(\"FG\"), 1).otherwise(0))\n","\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn('TS',when( (col('AU_TS')== 1) | (col('AW_TS')== 1)| (col('MW_TS')== 1), 1).otherwise(0))\\\n","                                  .withColumn('ICE', when((col('AU_ICE')== 1 )| (col('AW_ICE')== 1)| (col('MW_ICE')== 1), 1).otherwise(0))\\\n","                                  .withColumn('SNOW',when((col('AU_SNOW')== 1 )| (col('AW_SNOW')== 1)| (col('MW_SNOW')== 1), 1).otherwise(0))\\\n","                                  .withColumn('FOG', when((col('AU_FOG')== 1 )| (col('AW_FOG')== 1)| (col('MW_FOG')== 1), 1).otherwise(0))\\\n","\n","\n","# Update weather dataset\n","df_weather_icao_needed_tz.write.mode(\"overwrite\").parquet(f\"{blob_url}/weather_with_icao_tz\")\n","\n","# Load the new datasets\n","flights_icao_tz = spark.read.parquet(f\"{blob_url}/flights_with_icao_tz\") # 42430592\n","df_weather_icao_needed_tz = spark.read.parquet(f\"{blob_url}/weather_with_icao_tz\") # 31717569\n","\n","```\n","\n","###Create composite key to join weather and flights dataset\n","\n","```python\n","\n","def create_composite_key(code, timestamp):\n","  return f'{code}_{timestamp}'\n","\n","create_composite_key = udf(create_composite_key)\n","\n","# Create composite key for both datasets\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.withColumn(\"CODE_STATION_TIMESTAMP\", create_composite_key('icao', 'HOUR_TIMESTAMP'))\n","flights_icao_tz = flights_icao_tz.withColumn(\"CODE_TIMESTAMP\", create_composite_key('icao', 'HOUR_WEATHER_TIMESTAMP')) \\\n","  .withColumn(\"TWO_CODE_TIMESTAMP\", create_composite_key('icao', 'TWO_HOUR_WEATHER_TIMESTAMP')) \\\n","  .withColumn(\"THREE_CODE_TIMESTAMP\", create_composite_key('icao', 'THREE_HOUR_WEATHER_TIMESTAMP'))\n","\n","# Update the datasets\n","flights_icao_tz.write.mode(\"overwrite\").parquet(f\"{blob_url}/flights_with_icao_tz\")\n","df_weather_icao_needed_tz.write.mode(\"overwrite\").parquet(f\"{blob_url}/weather_with_icao_tz\")\n","\n","# Load the new datasets\n","flights_icao_tz = spark.read.parquet(f\"{blob_url}/flights_with_icao_tz\") # 42430592\n","df_weather_icao_needed_tz = spark.read.parquet(f\"{blob_url}/weather_with_icao_tz\") # 1057832\n","print(flights_icao_tz.count())\n","\n","# Drop unnecessary columns\n","columns_to_drop = ['airport_name', 'airport_city', 'airport_country', 'airport_tz', 'year', 'airport_subd', 'country', 'elevation', 'iata', 'airport_lon', 'airport_lat', 'icao']\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.drop(*columns_to_drop)\n","\n","```\n","\n","####Clean data further and execute joins\n","\n","```python\n","\n","# Join the datasets\n","joined_df_one = flights_icao_tz.join(df_weather_icao_needed_tz, flights_icao_tz.CODE_TIMESTAMP == df_weather_icao_needed_tz.CODE_STATION_TIMESTAMP, 'left')\n","joined_df_two = flights_icao_tz.join(df_weather_icao_needed_tz, flights_icao_tz.TWO_CODE_TIMESTAMP == df_weather_icao_needed_tz.CODE_STATION_TIMESTAMP, 'left')\n","joined_df_three = flights_icao_tz.join(df_weather_icao_needed_tz, flights_icao_tz.THREE_CODE_TIMESTAMP == df_weather_icao_needed_tz.CODE_STATION_TIMESTAMP, 'left')\n","\n","combined_df = joined_df_one.union(joined_df_two).union(joined_df_three) # 3m 6204054\n","combined_df.write.mode(\"overwrite\").parquet(f\"{blob_url}/combined\")\n","\n","combined_df = spark.read.parquet(f\"{blob_url}/combined\") # 184162262\n","combined_df.count()\n","\n","from pyspark.sql.functions import min\n","\n","joined_df_pos = combined_df.withColumn(\"distance_time\",col(\"WEATHER_TIMESTAMP\").cast(\"long\") - col(\"STATION_TIMESTAMP\").cast(\"long\")).where(\"distance_time >= 0\")\n","joined_df_pos_min = joined_df_pos.groupBy(\"flight_id\").agg(min(\"distance_time\").alias('minimum_distance')).withColumnRenamed(\"flight_id\", \"flight_id_min\")\n","joined_df_pos_min_final = joined_df_pos.join(joined_df_pos_min, (joined_df_pos_min.flight_id_min == joined_df_pos.flight_id) & (joined_df_pos_min.minimum_distance == joined_df_pos.distance_time), 'inner') # 3m 1396256 unique\n","\n","joined_df_clean = joined_df_pos_min_final.dropDuplicates([\"flight_id\"])\n","joined_df_clean.write.mode(\"overwrite\").parquet(f\"{blob_url}/joined_df_full\")\n","\n","# Drop temporary columns\n","columns_to_drop = ['CODE_STATION_TIMESTAMP', 'distance_time', 'flight_id_min', 'minimum_distance', 'CODE_TIMESTAMP', 'TWO_CODE_TIMESTAMP', 'THREE_CODE_TIMESTAMP']\n","\n","clean_full = spark.read.parquet(f\"{blob_url}/joined_df_full\") # 42227239\n","clean_full = clean_full.drop(*columns_to_drop)\n","\n","# Rename the weather features so it's clear they belong to the ORIGIN airport\n","columns_to_rename = ['STATION_ID', 'STATION_NAME', 'STATION_ELEVATION', 'STATION_LATITUDE', 'STATION_LONGITUDE', 'WEATHER_DATE', 'WEATHER_REPORT_TYPE', 'WEATHER_SOURCE', 'HourlyPrecipitation', 'HourlyPresentWeatherType', 'STATION_TIMESTAMP', 'HourlyDewPointTemperature', 'HourlyDryBulbTemperature', 'HourlyWetBulbTemperature', 'HourlyStationPressure', 'HourlyWindDirection', 'HourlyWindSpeed', 'HourlyVisibility', 'AU_code', 'AW_code', 'MW_code', 'AU_TS', 'AU_ICE', 'AU_SNOW', 'AU_FOG', 'AW_TS', 'AW_ICE', 'AW_SNOW', 'AW_FOG', 'MW_TS', 'MW_ICE', 'MW_SNOW', 'MW_FOG', 'TS', 'ICE', 'SNOW', 'FOG', 'icao', 'iata', 'airport_name', 'airport_city', 'airport_subd', 'airport_country', 'elevation', 'airport_lat', 'airport_lon', 'airport_tz', 'FLIGHT_TIMESTAMP']\n","\n","\n","for column in columns_to_rename:\n","  clean_full = clean_full.withColumnRenamed(column, \"ORIGIN_\" + column)\n","\n","clean_full.write.mode(\"overwrite\").parquet(f\"{blob_url}/joined_df_full\")\n","\n","clean_full = spark.read.parquet(f\"{blob_url}/joined_df_full\") # 42227239\n","origin_airports = clean_full.select(\"ORIGIN\").distinct()\n","dest_airports = clean_full.select(\"DEST\").distinct()\n","\n","joined = dest_airports.join(origin_airports, origin_airports.ORIGIN == dest_airports.DEST, 'left').where(\"ORIGIN is null\")\n","display(joined)\n","\n","clean_full = spark.read.parquet(f\"{blob_url}/joined_df_full\") # 42227239\n","full_dest_tz = clean_full.join(df_airport_timezone, clean_full.DEST == df_airport_timezone.iata, 'left')\n","\n","full_dest_tz = full_dest_tz.withColumn(\"CODE_TIMESTAMP\", create_composite_key('icao', 'HOUR_WEATHER_TIMESTAMP')) \\\n","  .withColumn(\"TWO_CODE_TIMESTAMP\", create_composite_key('icao', 'TWO_HOUR_WEATHER_TIMESTAMP')) \\\n","  .withColumn(\"THREE_CODE_TIMESTAMP\", create_composite_key('icao', 'THREE_HOUR_WEATHER_TIMESTAMP'))\n","\n","# Drop unnecessary columns\n","df_weather_icao_needed_tz = spark.read.parquet(f\"{blob_url}/weather_with_icao_tz\") # 1057832\n","columns_to_drop = ['airport_name', 'airport_city', 'airport_country', 'airport_tz', 'year', 'airport_subd', 'country', 'elevation', 'iata', 'airport_lon', 'airport_lat', 'icao', 'HOUR_TIMESTAMP']\n","df_weather_icao_needed_tz = df_weather_icao_needed_tz.drop(*columns_to_drop)\n","\n","# Join the datasets\n","joined_df_one = full_dest_tz.join(df_weather_icao_needed_tz, full_dest_tz.CODE_TIMESTAMP == df_weather_icao_needed_tz.CODE_STATION_TIMESTAMP, 'left')\n","joined_df_two = full_dest_tz.join(df_weather_icao_needed_tz, full_dest_tz.TWO_CODE_TIMESTAMP == df_weather_icao_needed_tz.CODE_STATION_TIMESTAMP, 'left')\n","joined_df_three = full_dest_tz.join(df_weather_icao_needed_tz, full_dest_tz.THREE_CODE_TIMESTAMP == df_weather_icao_needed_tz.CODE_STATION_TIMESTAMP, 'left')\n","\n","combined_df = joined_df_one.union(joined_df_two).union(joined_df_three) # 3m 6204054\n","combined_df.write.mode(\"overwrite\").parquet(f\"{blob_url}/combined_dest\")\n","\n","combined_df = spark.read.parquet(f\"{blob_url}/combined_dest\") # 184162262\n","\n","from pyspark.sql.functions import min\n","\n","joined_df_pos = combined_df.withColumn(\"distance_time\",col(\"WEATHER_TIMESTAMP\").cast(\"long\") - col(\"STATION_TIMESTAMP\").cast(\"long\")).where(\"distance_time >= 0\")\n","joined_df_pos_min = joined_df_pos.groupBy(\"flight_id\").agg(min(\"distance_time\").alias('minimum_distance')).withColumnRenamed(\"flight_id\", \"flight_id_min\")\n","joined_df_pos_min_final = joined_df_pos.join(joined_df_pos_min, (joined_df_pos_min.flight_id_min == joined_df_pos.flight_id) & (joined_df_pos_min.minimum_distance == joined_df_pos.distance_time), 'inner') # 3m 1396256 unique\n","\n","joined_df_clean = joined_df_pos_min_final.dropDuplicates([\"flight_id\"])\n","joined_df_clean.write.mode(\"overwrite\").parquet(f\"{blob_url}/joined_df_full_dest\")\n","\n","# Drop temporary columns\n","columns_to_drop = ['CODE_STATION_TIMESTAMP', 'distance_time', 'flight_id_min', 'minimum_distance', 'CODE_TIMESTAMP', 'TWO_CODE_TIMESTAMP', 'THREE_CODE_TIMESTAMP']\n","\n","join_df_origin_dest = spark.read.parquet(f\"{blob_url}/joined_df_full_dest\") # 184162262\n","join_df_origin_dest = join_df_origin_dest.drop(*columns_to_drop)\n","\n","# Rename the weather features so it's clear they belong to the ORIGIN airport\n","columns_to_rename = ['STATION_ID', 'STATION_NAME', 'STATION_ELEVATION', 'STATION_LATITUDE', 'STATION_LONGITUDE', 'WEATHER_DATE', 'WEATHER_REPORT_TYPE', 'WEATHER_SOURCE', 'HourlyPrecipitation', 'HourlyPresentWeatherType', 'STATION_TIMESTAMP', 'HourlyDewPointTemperature', 'HourlyDryBulbTemperature', 'HourlyWetBulbTemperature', 'HourlyStationPressure', 'HourlyWindDirection', 'HourlyWindSpeed', 'HourlyVisibility', 'AU_code', 'AW_code', 'MW_code', 'AU_TS', 'AU_ICE', 'AU_SNOW', 'AU_FOG', 'AW_TS', 'AW_ICE', 'AW_SNOW', 'AW_FOG', 'MW_TS', 'MW_ICE', 'MW_SNOW', 'MW_FOG', 'TS', 'ICE', 'SNOW', 'FOG', 'icao', 'iata', 'airport_name', 'airport_city', 'airport_subd', 'airport_country', 'elevation', 'airport_lat', 'airport_lon', 'airport_tz', 'FLIGHT_TIMESTAMP']\n","\n","\n","for column in columns_to_rename:\n","  join_df_origin_dest = join_df_origin_dest.withColumnRenamed(column, \"DEST_\" + column)\n","\n","join_df_origin_dest.write.mode(\"overwrite\").parquet(f\"{blob_url}/joined_df_full_dest\")\n","\n","join_df_origin_dest = spark.read.parquet(f\"{blob_url}/joined_df_full_dest\") # 184162262\n","display(join_df_origin_dest)\n","\n","df_airlines.where(\"ORIGIN in ('PSE', 'PPG', 'OGS', 'SPN', 'SJU', 'GUM', 'XWA')\").count()\n","\n","from pyspark.sql.functions import countDistinct\n","import numpy as np\n","from math import radians, cos, sin, asin, sqrt\n","\n","# Find missing airports\n","\n","matched_airpoorts = clean_full.select(\"ORIGIN\").withColumnRenamed(\"ORIGIN\", \"MATCHED_ORIGIN\").distinct()\n","\n","original_airports = df_airlines.select(\"ORIGIN\").distinct()\n","\n","missing_airports = original_airports.join(matched_airpoorts, original_airports.ORIGIN == matched_airpoorts.MATCHED_ORIGIN, \"left\").where(\"MATCHED_ORIGIN is NULL\")\n","\n","# Find nearest stations to each airport\n","just_stations = df_stations.select(\"station_id\", \"lon\", \"lat\").distinct()\n","\n","# Get airport data for missing airports\n","missing_airports_coords = missing_airports.join(df_airport_timezone, missing_airports.ORIGIN == df_airport_timezone.iata, \"left\")\n","\n","# Distance functions for airports and stations\n","def haversine_distance(lon1, lat1, lon2, lat2):\n","    lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])\n"," \n","    d_lon = lon2 - lon1 \n","    d_lat = lat2 - lat1 \n","    a = sin(d_lat/2)**2 + cos(lat1) * cos(lat2) * sin(d_lon/2)**2\n","    c = 2 * asin(sqrt(a)) \n","    rkm = 6371\n","    rm = 3956\n","    return c * rkm, c * rm\n","\n","stations_sc = sc.broadcast(just_stations.rdd.collect())\n","    \n","def calc_distances(airport):\n","  airport_lon = float(airport['airport_lon'])\n","  airport_lat = float(airport['airport_lat'])\n","\n","  for station in stations_sc.value:\n","    if not station['lon'] or not station['lat']:\n","        continue\n","    station_lon = float(station['lon'])\n","    station_lat = float(station['lat'])\n","    station_id = station['station_id']\n","    yield (airport['iata'], (station_id, haversine_distance(airport_lon, airport_lat, station_lon, station_lat)))\n","\n","def find_minimum(a, b):\n","  minimum_index = np.argmin([a[1][0], b[1][0]])\n","  if minimum_index == 0:\n","      return a\n","  else:\n","      return b\n","    \n","def map_values(a):\n","  return (a[0], a[1][0], a[1][1][0], a[1][1][1])\n","result = missing_airports_coords.rdd.flatMap(calc_distances).reduceByKey(find_minimum).map(map_values).collect()\n","new_stations = spark.createDataFrame(result, [\"IATA\", \"CLOSEST_STATION_ID\", \"DISTANCE in KM\", \"DISTANCE in Miles\"])\n","new_stations.show()\n","```\n","\n","### Join PageRank Features\n","```\n","qrn_full_set = spark.read.parquet(f\"{blob_url}/QRN_fullset_V3\")\n","\n","# TO JOIN FULL DATA SET WITH PAGE RANK FEATURES\n","page_rank = spark.read.parquet(f\"{blob_url}/df_PageRank\")\n","page_rank.cache().count()\n","\n","qrn_full_set = spark.read.parquet(f\"{blob_url}/QRN_fullset_V3\")\n","qrn_full_set.cache().count()\n","\n","joined_rank = qrn_full_set.withColumn(\"key_origin\", concat(col(\"QUARTER\"),lit('-'),col(\"YEAR\"),lit('-'),col(\"ORIGIN\")))\n","print(f'Dimensions of Full Set are: {joined_rank.count()}, {len(joined_rank.columns)}')\n","joined_rank = joined_rank.withColumn(\"key_dest\", concat(col(\"QUARTER\"),lit('-'),col(\"YEAR\"),lit('-'),col(\"DEST\")) )\n","joined_rank = joined_rank.join(page_rank, joined_rank.key_origin == page_rank.key)\n","joined_rank = joined_rank.drop(\"PageRank\", \"quarter\",\"node\", \"key_origin\",\"key\").withColumnRenamed(\"PageRank_use\", \"PageRank_origin\")\n","joined_rank = joined_rank.join(page_rank, joined_rank.key_dest == page_rank.key)\n","joined_rank = joined_rank.drop(\"PageRank\", \"quarter\",\"node\", \"key_dest\",\"key\").withColumnRenamed(\"PageRank_use\", \"PageRank_dest\")\n","\n","print(f'Dimensions of Full Set are: {joined_rank.count()}, {len(joined_rank.columns)}')\n","joined_rank.write.mode(\"overwrite\").parquet(f\"{blob_url}/rank_set\")\n","\n","```\n","\n","### JOIN Delay State Features\n","\n","```\n","delay = spark.read.parquet(f\"{blob_url}/df_delay_stateCV\") #df_delay_state\n","delay.cache().count()\n","\n","delay = delay.withColumnRenamed(\"hour_stamp\", \"delay_hour_stamp\")\n","\n","full_delay_set = full_rank_set.join(delay, full_rank_set.hour_stamp == delay.delay_hour_stamp)\n","full_delay_set = full_delay_set.drop(\"delay_hour_stamp\").withColumnRenamed(\"prediction\",\"delay_state\")\n","full_delay_set.cache().count()\n","\n","\n","full_delay_set_2015_2020 = full_delay_set.where(\"YEAR != '2021'\")\n","train_set = full_delay_set_2015_2020\n","train_set.cache().count()\n","```\n","\n","### Split into Parquet Files for Blocked Time Series CV\n","\n","```\n","train_set_1 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2016-01-01'\")\n","dev_set_1 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2016-03-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2016-01-01'\")\n","\n","train_set_1.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_delay_set1\")\n","dev_set_1.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_delay_set1\")\n","\n","train_set_2 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2017-03-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2016-03-01'\")\n","dev_set_2 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2017-05-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2017-03-01'\")\n","\n","train_set_2.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_delay_set2\")\n","dev_set_2.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_delay_set2\")\n","\n","train_set_3 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2018-07-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2017-05-01'\")\n","dev_set_3 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2018-09-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2018-07-01'\")\n","\n","train_set_3.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_delay_set3\")\n","dev_set_3.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_delay_set3\")\n","\n","train_set_4 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2019-09-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2018-09-01'\")\n","dev_set_4 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2019-11-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2019-09-01'\")\n","\n","train_set_4.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_delay_set4\")\n","dev_set_4.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_delay_set4\")\n","\n","train_set_5 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP < '2020-10-01' and ORIGIN_FLIGHT_TIMESTAMP >= '2019-11-01'\")\n","dev_set_5 = train_set.where(\"ORIGIN_FLIGHT_TIMESTAMP >= '2020-10-01'\")\n","\n","train_set_5.write.mode(\"overwrite\").parquet(f\"{blob_url}/train_delay_set5\")\n","dev_set_5.write.mode(\"overwrite\").parquet(f\"{blob_url}/dev_delay_set5\")\n","\n","\n","```\n","#### Train and Test Sets\n","```\n","delay = spark.read.parquet(f\"{blob_url}/df_delay_state_2015_2020\") #df_delay_state\n","delay.cache().count()\n","\n","delay = delay.withColumnRenamed(\"hour_stamp\", \"delay_hour_stamp\")\n","\n","full_delay_set = full_rank_set.join(delay, full_rank_set.hour_stamp == delay.delay_hour_stamp)\n","full_delay_set = full_delay_set.drop(\"delay_hour_stamp\").withColumnRenamed(\"prediction\",\"delay_state\")\n","full_delay_set.cache().count()\n","\n","full_delay_set_2021 = full_delay_set.where(\"YEAR == '2021'\")\n","```"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"966847a0-8a29-45d0-ae10-4d626cc7aa66","showTitle":false,"title":""}},"source":["##Links to notebooks\n","\n","### Cross Validation, Previous Flight delay\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/2495978572324738/command/2495978572324739\n","\n","### Flight EDA:\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/1020093804833107/command/1020093804833108\n","\n","### Flight Features:\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/623519246324776/command/1013197717894412\n","\n","### Feature Engineering - Delay State, Page Rank, Airport Capacity:\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/1215577238258908/command/1215577238258909\n","\n","### Join Notebook\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/364123876153144/command/3972121902874912\n","\n","### Join QRN, PageRank, Delay State Features\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/2495978572324738/command/1215577238242547\n","\n","### Modelling Pipelines\n","https://adb-731998097721284.4.azuredatabricks.net/?o=731998097721284#notebook/1215577238244380/command/1215577238244381"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"a29ec872-6197-4679-b0c7-aa3548b63aad","showTitle":false,"title":""}},"source":["#### Phase Leader Plan\n","\n","| Phase | Leader | Description |\n","| --- | --- | --- |\n","| 1 | Dominic Lim | Project Plan, describe datasets, joins, tasks, and metrics |\n","| 2 | Nathan Chiu | EDA, baseline pipeline on all data, Scalability, Efficiency, Distributed/parallel Training, and Scoring Pipeline |\n","| 3 | Raul Merino | Feature engineering + hyperparameter tuning, + in-class review |\n","| 4 | Javier Rondon | Advanced model architectures and loss functions, select an optimal algorithm, fine-tune & Final report write up  |"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"4c0d1a06-c81f-42e3-9afd-3efe2244ac25","showTitle":false,"title":""}},"source":["#### Credit Assignment\n","\n","| Task                           | Owner       | Hours (estimate) | Start Date | End Date | F2 Score Impact |\n","|--------------------------------|-------------|------------------| ---------- | -------- | --------------- |\n","| Implement MLP model                         | Raul |  5               |    11/28/22           | 12/04/22|0.02\n","| Debugging and hyperparameter tuning            | Javier, Dominic |30| 11/28/22 | 12/04/22 | 0.1\n","| Slides            | Nathan |5| 12/03/22 | 12/04/22 | NA\n","| Implement SMOTE into the pipeline            | Nathan |10| 11/28/22 | 12/01/22 | NA\n","| Ensemble Model                   | Raul |4| 12/02/22 | 12/04/22 | 0.05\n","| Run additional experiments using 2021 data as a blind test set                  | Dominic |10| 11/28/22 | 12/04/22 | 0.05\n","| Implement ML Flow to keep track of all experiments' results             | Dominic |5| 11/28/22 | 12/04/22 | NA   \n","| Update the report with the new results and methodology        | All |10| 12/02/22 | 12/04/22 | NA"]},{"cell_type":"markdown","metadata":{"application/vnd.databricks.v1+cell":{"cellMetadata":{},"inputWidgets":{},"nuid":"93b18a85-0e1a-41dc-a0de-e71103d7a123","showTitle":false,"title":""}},"source":["#### Sources used throughout this notebook:\n","- Li, Q, Jing, R. Generation and prediction of flight delays in air transport. IET Intell Transp Syst. 2021; 15: 740– 753. https://doi.org/10.1049/itr2.12057.\n","- Rebollo, J. J., Balakrishnan, H. Characterization and prediction of air traffic delays. Transportation Research Part C: Emerging Technologies, 44, 2014, 231–241. https://doi.org/10.1016/j.trc.2014.04.007\n","- Weili, Z, et al, (2021). A Deep Graph-Embedded LSTM Neural Network Approach for Airport Delay Prediction\n","https://doi.org/10.1155/2021/6638130\n","- Peterson, Everett B., et al. “The Economic Cost of Airline Flight Delay.” Journal of Transport Economics and Policy, vol. 47, no. 1, 2013, pp. 107–21. JSTOR, http://www.jstor.org/stable/24396355. Accessed 30 Oct. 2022.\n","- Aviation Data & Statistics. Aviation Data & Statistics | Federal Aviation Administration. (n.d.). Retrieved October 30, 2022, from https://www.faa.gov/data_research/aviation_data_statistics \n","- J. Rondon personal communication, October 27, 2022\n","- https://www.sciencedirect.com/topics/engineering/wet-bulb-temperature \n","- https://education.nationalgeographic.org/resource/barometer\n","- https://cardinalscholar.bsu.edu/bitstream/handle/123456789/200785/Algarin%20BallesterosJ_2017-3_BODY.pdf?sequence=1&isA llowed=n\n","- https://engineering.berkeley.edu/news/2010/11/flight-delays-cost-more-than-just-time/\n","- https://www.airlines.org/dataset/u-s-passenger-carrier-delay-costs/\n","- https://diybigdata.net/2019/12/airline-flight-data-analysis-airport-pagerank/\n","- https://www.quora.com/How-are-the-outer-runways-of-DFW-used-in-conjunction-with-the-others\n","- https://i0.wp.com/worldairlinenews.com/wp-content/uploads/2015/03/lga-airport-map-faalr-copy.jpg?resize=600%2C489&ssl=1\n","- https://www.skyscanner.com/tips-and-inspiration/what-windspeed-delays-flights\n","- https://www.faa.gov/about/office_org/headquarters_offices/ato/service_units/techops/navservices/lsg/rvr\n","- https://www.alicehuacal.com\n","- https://towardsdatascience.com/smote-fdce2f605729\n","- https://spark.apache.org/docs/2.1.0/mllib-decision-tree.html#node-impurity-and-information-gain\n","- https://towardsdatascience.com/under-the-hood-using-gini-impurity-to-your-advantage-in-decision-tree-classifiers-9be030a650d5#:~:text=First%20of%20all%2C%20the%20Gini,for%20you)%20than%20lower%20values.\n","- https://towardsdatascience.com/the-mathematics-of-decision-trees-random-forest-and-feature-importance-in-scikit-learn-and-spark-f2861df67e3"]}],"metadata":{"application/vnd.databricks.v1+notebook":{"dashboards":[],"language":"python","notebookMetadata":{"pythonIndentUnit":4},"notebookName":"Section05_Group04_Deliverable_Phase4","notebookOrigID":632558266968262,"widgets":{}},"language_info":{"name":"python"}},"nbformat":4,"nbformat_minor":0}