<jupyter_start><jupyter_text>Flight Delays and Twitter SentimentDATA 512 Ethics Final ProjectFall 2018Date: December 9th, 2018**Analysis and report prepared by: <NAME>** IntroductionEvery day, more than a million people take to the skies to travel in the United States. Far removed from the golden age of flying in the 1960s, air travel today mainly conjures up memories of flight delays, rude customers, and long lies at the airport.Delays are an unavoidable part of flying. Many delays occur due to weather and other events outside anyone's control, while others occur because of mismanagement by airlines/airports or the flight crew. Quantifying this would be an interesting exercise; which airlines are most commonly delayed? Which airports? Are certain routes most susceptible to delays? What about cancellations?Regardless of the reasons for delays, passengers often vent their frustration of air travel on various social media mediums,  Twitter being a prominent example. While this is a popular pastime for many people, one can't help but wonder how grounded in reality people's tweets about airlines are. Are the negative (or positive) sentiments about an airline reflective of its on-time performance?The goal of this project is to characterize causes of flight delays in the United States and identify most commonly delayed airlines, flights, airports, and routes in 2015. Second part of this project is an analysis of the Twitter sentiment of various airlines in the month of February, 2015. Positive and negative sentiments on Twitter are to be characterized and compared with on-time performance of each airline to see if they are reflective of reality.  Related WorkThe US Bureau of Transportation Statistics publishes the flight delays and cancellations data annually. In addition with data publication, they perform some basic analysis at the aggregate level for the air transportation industry as a whole, which is posted on their website [here](https://www.bts.gov/topics/airlines-and-airports-0). They also have a dashboard which users can query for specific airlines and airports for their on-time statistics in the same website. However, none of these analyses contain any kind of comparison with sentiment on social media.  MethodsThe analysis falls into 2 board verticals. First is the characterization of flight delays, and second is the comparison between airline on-time performance and Twitter sentiment. The end user for this analysis is meant to be an airline passenger, airline worker/pilot, or a general aviation enthusiast with little technicial backgroud. Therefore, one important human-centered consideration that went into communicating the results from this analysis is to keep things simple and clear. Therefore, the analysis focuses more on visualization and simple statistical modeling rather than complex machine learning models. Various plots and aggregated tables are used to answer the posed research questions and communicate findings from the analysis to a non-technical audience.   1. Characterization of flight delaysThe goal of this analysis is to characterize the flight delays in the dataset. Three main research questions are asked:* What causes flight delays? What proportion is due to airlines, air traffic control, weather, security, etc?* Are there any seasonal/temporal patterns to flight delays?* Which airlines/airports/routes are most delayed? Least delayed?When looking at on-time metrics, **average minutes of delay per flight** is used. To calculate this, the dataset is filtered to desired flights and grouped into airlines/airports/routes/timeframe. It is important to make sure the correct delay time is used for each comparison. The following are used in the analysis:| Group by metric | Delay type used                 | |-----------------|---------------------------------|| Airline         | Arrival Delay, Airline Delay    || Airport/Route   | Arrival Delay, Air System Delay || Timeframe       | All types of delays             |Arrival delay is the overall delay of the flight (arrival time - scheduled arrival time), and is a measure of what the passenger experiences. It is used in all characterizations of flight delays in this analysis. Airline delay is delay specifically due to the airline, and is used only when comparing airline specific performances. Finally, air system delay, which is delay due to air traffic control and airport operations, is used only when comparing airport/route comparisons. For these analysis, only routes with at least 100 flights per year are considered.  For studying time aspect nature of delays, a 7-day running average of various types of delays are plotted for interpretation and visualization. 7-day running average is a good balance between reducing noise using a meaningful and interpretable window (there's variations in flights during each day of a week) and using a timeframe that is too large. Also a weekly average is more interpretable and simpler to understand for the end user of this analysis.  2. Airline on-time performance and passenger Tweet sentiment comparisonFor this portion of the analysis, the following research questions are asked:* Which airlines have most negative/positive sentiment on Twitter? How do these sentiments compare with their on-time performance?* What does the above analysis look like when just looking at Tweets that complain about late flights?Sentiment for each Tweet are already classified in the `Tweets.csv` dataset. Simple groupby aggregations are used to calculate sentiment for each airline using an industry popular metric called **Net Promoter Score**, or NPS. NPS is calculated using the following formula:$$NPS = \% \space positive Tweets - \% \space negative Tweets$$NPS is not a perfect way of aggregating sentiments (it usually relies on a 5 point scale that is not linear). But because of the human-centered considerations of the non-technical audience, it was chosen for it's simplicity and ability to quantify sentiment in a single number.  For the second research question, the Twitter data is further filtered to those classified as `late flight` for `negativereason` column. This reduces the Tweets down to just those that complained negatively about an airline being late. And only flights that are late due to the fault of the airline are considered. These flights are defined as being more than an hour late with over half of the delay time due to the fault of the airline (`AIRLINE_DELAY` > `ARRIVAL_DELAY` / 2). FindingsThe findings from this analysis gave some interesting insights into what causes flight delays and how airlines and airports performed in 2015.  1. Characterization of flight delays* **What causes flight delays? What proportion is due to airlines, air traffic control, weather, security, etc?**![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/delay_type.PNG?raw=true "Delays types")By far the most common type of delay is what's called **late aircraft delay**, which is delay due to the previous aircraft being late. But this can be for any of the other reasons, and doesn't tell much about the real causes. Discarding these delays, the rest of the delays are dominated by airline delays and air system delays. Weather delays only accounted for less than 5% of all delayed minutes in 2015, and security delays accouting for mere 0.13%.* **Are there any seasonal/temporal patterns to flight delays?**The 7-day time averages of all types of delays are shown in plot below.![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/delays_overtime.png?raw=true "Delays over time")Most types of delays actually remain fairly constant throughout the year. The overall delay, `ARRIVAL_DELAY` column does show, however, seasonal peaks. They correpond with seasonal peaks in the late aircraft delay. What about day of the week? Do average flight delays vary between days of the week?![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/day_of_week.png?raw=true)As it turns out, weekends, especially saturdays, are the worst days to fly in terms of overall delay. * **Which airlines/airports/routes are most delayed? Least delayed?**Here the most/least delayed routes are shown. Air traffic delay, or the `AIR_SYSTEM_DELAY` column, is aggregated here and averaged per flight. The table below shows the top 10 most delayed routes in 2015 using this metric. ![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/most_delayed_routes.PNG?raw=true)It's quite remarkable to see Newark airport in so many of the destinations. San Francisco airport tops the list as well. These are all routes that fly into very busy airports, with average air system delay of over 45 minutes! Now what about the least delayed routes in 2015?![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/least_delayed_routes.PNG?raw=true)Again, there is remarkable similarities between the top 10 least delayed routes, with all the destinations being in Hawaii. In fact, most flights are short hops between Hawaiian Islands. Now let's look at airlines:![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/airline_clustering.png?raw=true)This shows the airline delay per flight on the y-axis and arrival delay per flight on the x-axis. This shows that airlines vary in their overall delays and delays due to their fault. For example, Spirit airlines are late often, but usually not due to their fault. Delta airlines, on the otherhand, are on time more often, but when they are late it is more likely to be their fault than other airlines. The plot also shows that Alaska and Virgin airlines are the least delayed both overall and due to their own fault.  2. Airline on-time performance and passenger Tweet sentiment comparisonFirst, what do people on Twitter complain about the most when it comes to negative comments about airlines?![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/negative_reasons.png?raw=true)The most common complaint is customer service. However, late flights comes in at second place, followed by ambiguous complaints and cancelled flights, lost luggage, etc. So late flights are a common source of complaints for people on Twitter. * **Which airlines have most negative/positive sentiment on Twitter? How do these sentiments compare with their on-time performance?**![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/twitter_sentiment_arrival_delay.png?raw=true)The above plot shows little correlation between average minutes of delay per flight (overall) and airline sentiment on Twitter for February 2015. While the correlation coefficient is negative, there is no statisticals significance. Part of this is due to the small sample size (6) of airlines represented. ![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/twitter_sentiment_airline_delay.png?raw=true)The above plot shows little correlation between average minutes of delay per flight (airline) and airline sentiment on Twitter for February 2015. Again, while the correlation coefficient is negative, there is no statisticals significance, partly due to the small sample size. * **What does the above analysis look like when just looking at Tweets that complain about late flights?**![alt text](https://github.com/ryanbae89/flight-delays-and-twitter/blob/master/images/twitter_sentiment2.png?raw=true)To answer this question, the number of late flights that are the fault of the airline are plotted against the number of Tweets for that airline complaining about late flights. The above plot shows no clear correlation between number of late flights and number of Tweets about late flights for the 6 airlines shown.  Discussions/ImplicatonsSo what does all this mean? Let's start with flight delays characterization. First is that while there is seasonal variation in average overall delay time per flight, it is mainly due to the aircraft arriving late. The other delay factors such as weather, airline, and air traffic are fairly consistant throughout the year. This means the increase in delays during holidays, for example, is largely exacerbated by the increase in number of flights. For what it's worth, airlines and air systems operate with similar efficiencies with increased loads, an interesting finding. Regarding airports and routes, it is good to avoid large, busy airports for good on-time performance. In fact, of the three major airports in New York City, two of them made the top 10 list and Newark airport made the list 8 times! It is suggested to avoid these airports when possible. And when picking days to fly, avoid the weekends, especially saturdays, as it is the most delayed day of the week. As for the airlines, the plot shows some interesting clusters of airlines. Most airlines, such as Southwest, US Airways, American Eagle, JetBlue, etc have average overall on-time performance with average airline delay performance. Two airlines that most people have good opinions of, Virigin and Alaska, were the most on-time and least delayed due to their own fault. And the ultra low-cost airlines such as Frontier and Spirit airlines had worst overall on-time performance, but per flight airline delays for these airlines were quite good. Some airlines, such as Delta and Hawaiian, had good overall on-time performance that could be improved by doing better on their airline delays. These self-clustering of airlines on the airline comparison visualization was an interesting finding that reflects the different routes, operations, and customers that they serve in the US. Now for the Twitter analysis, all findings came negative. There were no statistically significant correlations between airline Twitter sentiment and their on-time performance. Even when only the Tweets that complained about late flights are plotted alongside actual number of late flights, the correlation was poor. However, this comes with few caveats; the number of airlines (the sample size) is very small, at only 6. Also, only a single month of the year was considered, and a more clear pattern may emerge when a wider timeframe is considered. The final plot does have, however, some interesting insights about passenger's sentiments about speicfic airlines. Virgin American airlines, an airline with very few late flights and delays, does have fewest number of complaints about late flights on Twitter. US Airways and United, however, has proportionally larger number of complaints about late flights than airlines like Delta and American. And surprisingly, Southwest airlines, while having the most late flights that are their fault, received second least number of late flight complaints on Twitter. These data points suggest that people's complaints and sentiments about airlines are not very reflective of reality, at least when it comes to their on-time performance except in exceptional cases, such as Virgin airlines. **Limitations**As mentioned above, there are several limitations to this study. First is the limited timeframe of the Twitter data (only February 2015) as well as the small number of airlines classified (only 6). In addition, some important characteristics about the airport are also missing. They can include things like number of flights and human traffic for an given airport, number of runways, etc that may all contribute to number of delayed flights.  ConclusionsOverall, this analysis was an interesting exercise into the world of commercial aviation and Twitter. There were some interesting insights about causes of flight delays and best/worst performing airlines and airports. While most of the findings confirmed suspicions, such as large airports like Newark being the worst at air system delays, others were more surprising. They include variations between airlines in their overall on-time performance and airline delay performance. The Twitter sentiment analysis, while ultimately a negative result, comes with a caveat of small sample size of airlines and timeframe. This means further work in this area can provide more conclusive insights. Finally, the over complaints of airlines like United and under complaints of airlines like Southwest was an interesting insight, although not too unexpected based on my own anecdotal experience. My hope is that both those who work in commercial aviation and passengers can derive useful insights and knowledge from this analysis.  References[1] [Flight delays and cancellation dataset on Kaggle](https://www.kaggle.com/usdot/flight-delays)[2] [Bureau of Transportation Statistics](https://www.bts.gov/)[3] [Bureau of Transportation Statistics Airlines and Airports](https://www.bts.gov/topics/airlines-and-airports-0)[4] [Twitter airline sentiment dataset on Kaggle](https://www.kaggle.com/crowdflower/twitter-airline-sentiment)[5] [Figure Eight website](https://www.figure-eight.com/data-for-everyone/)[6] University of Washington DATA 512 Fall 2018 Course Website, https://wiki.communitydata.cc/Human_Centered_Data_Science_(Fall_2018) Analysis CodeThe rest of this notebook contains the actual analysis done with the code, comments, and explanations.<jupyter_code># import libraries
import numpy as np
import pandas as pd
pd.set_option('display.max_columns', 50)
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns
from scipy.stats.stats import pearsonr
# data paths
DATASET_PATH = 'data/'
IMAGE_PATH = 'images/'

# load datasets
flights = pd.read_csv(DATASET_PATH + 'flights.csv')
airlines = pd.read_csv(DATASET_PATH + 'airlines.csv')
airports = pd.read_csv(DATASET_PATH + 'airports.csv')
tweets = pd.read_csv(DATASET_PATH + 'Tweets.csv')

# get dataset shapes
print('flights: {}'.format(flights.shape))
print('airlines: {}'.format(airlines.shape))
print('airports: {}'.format(airports.shape))
print('tweets: {}'.format(tweets.shape))
# sample flights to save for github repo
flights_sampled = flights.sample(frac=0.05)
flights_sampled.shape
flights_sampled.to_csv(DATASET_PATH + 'flights_sampled.csv')<jupyter_output><empty_output><jupyter_text>Data explorationHere we first explore the dataset to get a feel for the data. We look at various summary statistics and visualizations before performing more detailed analysis.We see that there are about 5 million unique flights in the `flights` table, with 31 columns. First let's look at the dataframe head to see what it looks like.<jupyter_code># list features and show dataframe head
print(flights.columns)
flights.head()
# summary statistics of numerical data
numerical_features = ['SCHEDULED_DEPARTURE', 'DEPARTURE_TIME', 'DEPARTURE_DELAY',
                     'TAXI_OUT', 'WHEELS_OFF', 'SCHEDULED_TIME', 'ELAPSED_TIME',
                     'AIR_TIME', 'DISTANCE', 'WHEELS_ON', 'TAXI_IN', 'SCHEDULED_ARRIVAL',
                     'ARRIVAL_TIME', 'ARRIVAL_DELAY']
flights[numerical_features].describe()
# summary statistics of categorical data
flights.describe(include=[np.object])<jupyter_output><empty_output><jupyter_text>Lets see if we can see the distribution of the some of the numerical features.<jupyter_code>def plot_quant_features(df, quant_features):
    n_rows, n_cols = int(len(quant_features)/2)+1, 2
    clr = ["blue", "green", "red", 'orange']*3
    figure_height = int(len(quant_features)/2)*6
    fig, axs = plt.subplots(ncols=n_cols, figsize=(18, figure_height))
    plt.figure(1)
    for i, feature in enumerate(quant_features):
        df_clean = df[feature]
        df_clean = df_clean.dropna()
        plt.subplot(n_rows, n_cols, 1 + i)
        sns.distplot(df_clean,  color = clr[i])
        plt.axvline(df_clean.mean(), color=clr[i], linestyle='solid', linewidth=2)
        plt.axvline(df_clean.median(), color=clr[i], linestyle='dashed', linewidth=2)
# general flight quantitative variable distributions
flight_quant_features = ['SCHEDULED_DEPARTURE', 'SCHEDULED_TIME', 'ELAPSED_TIME',
                     'DISTANCE', 'SCHEDULED_ARRIVAL']
plot_quant_features(flights, flight_quant_features)
# delay quantitative variable distributions
delay_quant_features = ['DEPARTURE_DELAY','TAXI_OUT', 'WHEELS_OFF', 'ELAPSED_TIME',
                     'AIR_TIME', 'WHEELS_ON', 'TAXI_IN', 'ARRIVAL_DELAY']
plot_quant_features(flights, delay_quant_features)
# What cancellations are most common?
flights.groupby('CANCELLATION_REASON')['AIRLINE'].count().to_frame().reset_index()<jupyter_output><empty_output><jupyter_text>We see that most common reasons for cancellations are category B, which is weather. Second reason is the airlines themselves, which is category A, followed by the category C, which is the air traffic control. The final category D, security based cancellations, are very rare.<jupyter_code># what delays are most common?
delay_times_features = ['AIR_SYSTEM_DELAY', 'SECURITY_DELAY', 'AIRLINE_DELAY', 
         'LATE_AIRCRAFT_DELAY', 'WEATHER_DELAY']
delays = flights[delay_times_features].sum().to_frame().reset_index()
delays = delays.rename(columns={0:'Minutes'})
delays = delays.sort_values('Minutes', ascending=False)
delays['percentage'] = delays['Minutes']/delays['Minutes'].sum()*100
delays<jupyter_output><empty_output><jupyter_text>Most delay minutes occur due to late aircraft, which means the aircraft itself came late to the airport for the flight. This is due to delay from previous flight, which can be due to all the other delay reasons. Second to this, the most other minutes are delayed due to the airline, followed by the air system (air traffic control, airport, etc), and weather. It is interesting to note that while weather was the most commonly listed reason for cancelled flights, in terms of the total minutes of delay it is also the smallest.   Delays as function of timeIn this analysis, we look at how delays change over different time scales. First, we look throughout the year by week and then average over each day of the week.<jupyter_code># get dates feature
flights['date'] = pd.to_datetime((flights['YEAR']*10000+flights['MONTH']*100+flights['DAY']).apply(str),format='%Y%m%d')
# group delay times by each day and average per number of flights that day
delays = flights.groupby('date')[delay_times_features].agg(['sum', 'count']).reset_index()
for delay_type in delay_times_features:
    delays[delay_type + '_per_flight'] = delays[delay_type]['sum']/delays[delay_type]['count']
    # time average over a week to reduce noise
    delays[delay_type + '_per_flight_7day_avg'] = delays[delay_type + '_per_flight'].rolling(7).mean()
delays['total_flights'] = delays['AIRLINE_DELAY']['count']
# plot delays over time
airline_delay_perflight = ['AIRLINE_DELAY_per_flight']
delay_times_perflight = ['AIR_SYSTEM_DELAY_per_flight',
                        'AIRLINE_DELAY_per_flight', 'LATE_AIRCRAFT_DELAY_per_flight',
                        'WEATHER_DELAY_per_flight', 'ARRIVAL_DELAY_per_flight']

fig, axs = plt.subplots(figsize=(15, 8))
axs = sns.lineplot(x="date", y='AIR_SYSTEM_DELAY_per_flight_7day_avg', data=delays)
# axs = sns.lineplot(x="date", y='SECURITY_DELAY_per_flight_7day_avg', data=delays)
axs = sns.lineplot(x="date", y='AIRLINE_DELAY_per_flight_7day_avg', data=delays)
axs = sns.lineplot(x="date", y='LATE_AIRCRAFT_DELAY_per_flight_7day_avg', data=delays)
axs = sns.lineplot(x="date", y='WEATHER_DELAY_per_flight_7day_avg', data=delays)
axs = sns.lineplot(x="date", y='ARRIVAL_DELAY_per_flight_7day_avg', data=delays, color='black', lw=3)
plt.legend(delay_times_perflight)
plt.grid() 
plt.xlabel('Year-Month')
plt.ylabel('Average Minutes of Delay per Flight')

plt.savefig(IMAGE_PATH + 'delays_over_time.png')
# plot number of flights over time
delays['n_flights'] = delays['AIR_SYSTEM_DELAY']['count'].rolling(7).mean()
# delays['n_flights_per_flight_7day_avg'] = delays[delay_type + '_per_flight'].rolling(7).mean()
# plot delays over time
fig, axs = plt.subplots(figsize=(15, 8))
axs = sns.lineplot(x="date", y='n_flights', data=delays)
axs = sns.lineplot(x="date", y='ARRIVAL_DELAY_per_flight_7day_avg', data=delays)
plt.grid()
plt.xlabel('Year-Month')
plt.ylabel('Flights Per Day')
plt.figure()<jupyter_output><empty_output><jupyter_text>The overall delay seems to follow the total number of flights almost exactly.Now let's look at delays as a function of day of the week. We know virtually every time series data has seasonality in relation to a working week.<jupyter_code># group by day of the week
weekly_delays = flights.groupby('DAY_OF_WEEK') \
    [delay_times_features].agg(['count', 'sum']).reset_index()
    
for delay_type in delay_times_features:
    weekly_delays[delay_type + '_per_flight'] = \
        weekly_delays[delay_type]['sum'] / weekly_delays[delay_type]['count']
        
weekly_delays['total_flights'] = weekly_delays['AIRLINE_DELAY']['count']

weekday_mapping = {1:'Monday', 2:'Tuesday', 3:'Wednesday', 4:'Thursday',
                  5:'Friday', 6:'Saturday', 7:'Sunday'}
weekly_delays['DAY_OF_WEEK'] = weekly_delays['DAY_OF_WEEK'].map(weekday_mapping)
fig, axs = plt.subplots(figsize=(10, 5))
axs = sns.barplot(x="DAY_OF_WEEK", y='AIRLINE_DELAY_per_flight',
                  data=weekly_delays, color='orange')
plt.xlabel('Day of the Week')
plt.ylabel('Minutes of Delay per Flight')

plt.savefig(IMAGE_PATH + 'day_of_week.png')<jupyter_output><empty_output><jupyter_text>Most delayed airportsNow we find the most delayed airports in United States. Because some airports have more flights, the data needs to be normalized to the number of flights out of the airport for 2015. Also, because there are both origin airport, destination airport, and also airpor where the aircraft for the flight is coming from, the analysis be done differently for each airport. 1. Origin Airport Delays: `DEPARTURE_DELAY` and `AIR_SYSTEM_DELAY`2. Destination Airport Delays: `AIR_SYSTEM_DELAY`3. Air Route Delays: `AIR_SYSTEM_DELAY`  1. Most Delayed Origin Airports<jupyter_code># group by origin airport and sum delay minutes
origin_airport_delays = flights.groupby('ORIGIN_AIRPORT')['DEPARTURE_DELAY'].agg(['count', 'sum']).reset_index()
origin_airport_delays = origin_airport_delays[origin_airport_delays['count'] > 1000]

# normalize to per flight delay minutes
origin_airport_delays['DEPARTURE_DELAY' + '_per_flight'] = \
        origin_airport_delays['sum'] / origin_airport_delays['count']

# sort by delay and join with airport names 
origin_airport_delays = origin_airport_delays.merge(right=airports,
                                                    left_on='ORIGIN_AIRPORT',
                                                    right_on='IATA_CODE')
origin_airport_delays = origin_airport_delays.sort_values(by='DEPARTURE_DELAY_per_flight',
                                                          ascending=False)
        
# check results
print(origin_airport_delays.shape)
origin_airport_delays[['AIRPORT', 'DEPARTURE_DELAY_per_flight']].head(10)
# group by origin airport and sum delay minutes
origin_airport_delays = flights.groupby('ORIGIN_AIRPORT')['AIR_SYSTEM_DELAY'].agg(['count', 'sum']).reset_index()
origin_airport_delays = origin_airport_delays[origin_airport_delays['count'] > 100]

# normalize to per flight delay minutes
origin_airport_delays['AIR_SYSTEM_DELAY' + '_per_flight'] = \
        origin_airport_delays['sum'] / origin_airport_delays['count']

# sort by delay and join with airport names 
origin_airport_delays = origin_airport_delays.merge(right=airports,
                                                    left_on='ORIGIN_AIRPORT',
                                                    right_on='IATA_CODE')
origin_airport_delays = origin_airport_delays.sort_values(by='AIR_SYSTEM_DELAY_per_flight',
                                                          ascending=False)
        
# check results
print(origin_airport_delays.shape)
origin_airport_delays[['AIRPORT', 'AIR_SYSTEM_DELAY_per_flight']].head(10)<jupyter_output>(270, 11)<jupyter_text>2. Most Delayed Destination Airports<jupyter_code># group by origin airport and sum delay minutes
dest_airport_delays = flights.groupby('DESTINATION_AIRPORT') \
    ['AIR_SYSTEM_DELAY'].agg(['count', 'sum']).reset_index()
dest_airport_delays = dest_airport_delays[dest_airport_delays['count'] > 1000]

# normalize to per flight delay minutes
dest_airport_delays['AIR_SYSTEM_DELAY' + '_per_flight'] = \
        dest_airport_delays['sum'] / dest_airport_delays['count']

# sort by delay and join with airport names 
dest_airport_delays = dest_airport_delays.merge(right=airports,
                                                    left_on='DESTINATION_AIRPORT',
                                                    right_on='IATA_CODE')
dest_airport_delays = dest_airport_delays.sort_values(by='AIR_SYSTEM_DELAY_per_flight',
                                                          ascending=False)
        
# check results
print(dest_airport_delays.shape)
dest_airport_delays[['AIRPORT', 'AIR_SYSTEM_DELAY_per_flight', 'STATE']].head(10)<jupyter_output>(112, 11)<jupyter_text>3. Most Delayed Routes<jupyter_code># group by origin and destination airports
route_delays = flights.groupby(['ORIGIN_AIRPORT','DESTINATION_AIRPORT']) \
    ['AIR_SYSTEM_DELAY'].agg(['count', 'sum']).reset_index()
route_delays = route_delays[route_delays['count'] > 100]
    
# normalize to per flight delay minutes
route_delays['AIR_SYSTEM_DELAY' + '_per_flight'] = \
        route_delays['sum'] / route_delays['count']
    
# sort by delay and join with airport names 
route_delays = route_delays.merge(right=airports,
                                left_on='ORIGIN_AIRPORT',
                                right_on='IATA_CODE')
route_delays = route_delays.merge(right=airports,
                                left_on='DESTINATION_AIRPORT',
                                right_on='IATA_CODE')
route_delays = route_delays.sort_values(by='AIR_SYSTEM_DELAY_per_flight',
                                ascending=False)
route_delays = route_delays.rename(columns={'AIRPORT_x':'ORIGIN_AIRPORT_NAME', 'AIRPORT_y':'DESTINATION_AIRPORT_NAME'})
# check results
print(route_delays.shape)
route_delays[['ORIGIN_AIRPORT_NAME', 'DESTINATION_AIRPORT_NAME', 'AIR_SYSTEM_DELAY_per_flight']].head(10)
route_delays = route_delays.sort_values(by='AIR_SYSTEM_DELAY_per_flight',
                                ascending=True)
route_delays[['ORIGIN_AIRPORT_NAME', 'DESTINATION_AIRPORT_NAME', 'AIR_SYSTEM_DELAY_per_flight']].head(10)<jupyter_output><empty_output><jupyter_text>Most delayed airlinesHere we do a similar analysis to find the most delayed airlines. Again, we normalize for the number of flights each airline had in 2015. When looking at most delayed airlines, there's several ways to define **delay**:* **Delay due to the airline**: `AIRLINE_DELAY` feature, this is the delay due to the fault of the airline* **Overall delay**: `ARRIVAL_DELAY` feature, this is the overall delay of a flight, due to all factors including that of the airlineIn addition, we can look at a subset of flights based on the delays. For example, some flights are likely slightly early and offset the delay average per flight for an airline. So we can look at the data in following way:* **All flights**: look at average delay per flight for all flights in the dataset* **Delayed flights only**: look at average delay per flight for flights that were delayed only, `ARRIVAL_DELAY` > 0* **Very delayed flights only**: look at average delay per flight for flights that had long delays only, for example: `ARRIVAL_DELAY` > 60 (flights that were delayed by an hour or more)<jupyter_code># This function calculates the average delay time in minutes per flight for each airline
def get_airline_delays(flights, airlines, delay_times_features, delay_limit=None):
    '''
    Inputs
        flights = pandas dataframe of flights
        airlines = pandas dataframe of airlines
        delay_times_features = list of features to average (various delay times)
        delay_limit = 'ARRIVAL_DELAY' feature threshold, must be greater to count towards average
    Outputs
        airline_delays = pandas dataframe of each airline both in flights and airlines table
                            and their average delay time for each category
    '''
    # get average of all flights, including ones on time or early
    if delay_limit is None:
        airline_delays = flights.groupby('AIRLINE')[delay_times_features].mean().reset_index()
        counts = flights.groupby('AIRLINE')['AIRLINE'].count().to_frame()
        counts = counts.rename(columns={'AIRLINE':'n_flights'}).reset_index()
        airline_delays = pd.concat([airline_delays, counts['n_flights']], axis=1)
    # get average of subselection of flights based on their arrival delay
    else:
        flights = flights[flights['ARRIVAL_DELAY'] > delay_limit]
        airline_delays = flights.groupby('AIRLINE')[delay_times_features].mean().reset_index()
        counts = flights.groupby('AIRLINE')['AIRLINE'].count().to_frame()
        counts = counts.rename(columns={'AIRLINE':'n_flights'}).reset_index()
        airline_delays = pd.concat([airline_delays, counts['n_flights']], axis=1)
    # rename columns and merge with airlines     
    airline_delays = airlines.merge(right=airline_delays, how='inner',
                                    left_on='IATA_CODE', right_on='AIRLINE')
    airline_delays = airline_delays.rename(columns={'AIRLINE_x':'AIRLINE_NAME'})
    airline_delays = airline_delays[['AIRLINE_NAME', 'IATA_CODE', 'n_flights'] + delay_times_features]
    return airline_delays<jupyter_output><empty_output><jupyter_text>Most delayed airlines due to their own faultFor this analysis, we look at average delay times for `AIRLINE_DELAY` feature, which is due to the fault of the airline.<jupyter_code>airline_delays_airline_all.sort_values(by='n_flights', ascending=False)
sorting_col = 'AIRLINE_DELAY'

# for all flights
airline_delays_airline_all = get_airline_delays(flights, airlines, delay_times_features) \
    .sort_values(by=sorting_col, ascending=False)
# for delayed flights only
airline_delays_airline_delay = get_airline_delays(flights, airlines, delay_times_features, 0) \
    .sort_values(by=sorting_col, ascending=False)
# for very delayed flights only (> 1hr or more)
airline_delays_airline_longdelay = get_airline_delays(flights, airlines, delay_times_features, 60) \
    .sort_values(by=sorting_col, ascending=False)
airline_delays_airline_all[['AIRLINE_NAME','AIRLINE_DELAY']].head()
airline_delays_airline_delay[['AIRLINE_NAME','AIRLINE_DELAY']].head()
airline_delays_airline_longdelay[['AIRLINE_NAME','AIRLINE_DELAY']].head()<jupyter_output><empty_output><jupyter_text>Most delayed airline in general (all delays)This is what a passenger experiences for a flight. This shows airlines on-time performance per flight with all delays combined, which is simply actual arrival time - scheduled arrival time.<jupyter_code>sorting_col = 'ARRIVAL_DELAY'

# for all flights
airline_delays_airline_all = get_airline_delays(flights, airlines, delay_times_features) \
    .sort_values(by=sorting_col, ascending=False)
# for delayed flights only
airline_delays_airline_delay = get_airline_delays(flights, airlines, delay_times_features, 0) \
    .sort_values(by=sorting_col, ascending=False)
# for very delayed flights only (> 1hr or more)
airline_delays_airline_longdelay = get_airline_delays(flights, airlines, delay_times_features, 60) \
    .sort_values(by=sorting_col, ascending=False)
airline_delays_airline_all[['AIRLINE_NAME','ARRIVAL_DELAY']].head()
airline_delays_airline_delay[['AIRLINE_NAME','ARRIVAL_DELAY']].head()
airline_delays_airline_longdelay[['AIRLINE_NAME','ARRIVAL_DELAY']].head()<jupyter_output><empty_output><jupyter_text>Visualization<jupyter_code># get airlines
airline_delays = get_airline_delays(flights, airlines, delay_times_features)
# figure and plot
fig, axs = plt.subplots(figsize=(12, 8))
axs = sns.scatterplot('ARRIVAL_DELAY', 'AIRLINE_DELAY', data=airline_delays)
plt.grid()
plt.xlabel('Arrival Delay per Flight (min)', fontsize=14)
plt.ylabel('Airline Delay per Flight (min)', fontsize=14)
plt.title('Arrival Delay vs. Airline Delay', fontsize=16)
# add annotations one by one with a loop
airline_names = airline_delays['AIRLINE_NAME'].unique()
for line in range(0,airline_delays.shape[0]):
    axs.text(airline_delays['ARRIVAL_DELAY'][line]-1, airline_delays['AIRLINE_DELAY'][line]+0.2, airline_names[line], 
            horizontalalignment='left', size='medium', color='black', weight='semibold')
    
plt.savefig(IMAGE_PATH + 'airline_clustering.png')
# get average distance of each airline
avg_dist = flights.groupby('AIRLINE')['DISTANCE'].mean().to_frame().reset_index().sort_values('DISTANCE', ascending=False)
avg_dist = avg_dist.merge(airlines, how='outer', left_on='AIRLINE', right_on='IATA_CODE')
avg_dist[['AIRLINE_y','DISTANCE']]<jupyter_output><empty_output><jupyter_text>Twitter sentiment analysis<jupyter_code># map airline codes to tweets table
airline_codes = {'Virgin America':'VX', 'United':'UA', 'Southwest':'WN', 
                 'Delta':'DL', 'US Airways':'US', 'American':'AA'}
tweets['IATA_CODE'] = tweets['airline'].map(airline_codes)
print(tweets.shape)
tweets.iloc[25].to_frame().reset_index()
# groupby airline and get counts of airline_sentiment
sent = tweets.groupby(['IATA_CODE','airline_sentiment'])['tweet_id'].count().to_frame()
sent = sent.unstack(1).reset_index()
sent.columns = sent.columns.droplevel()
sent = sent.rename(columns={'':'IATA_CODE'})

# get NPS for each airlines
sent['total'] = sent['positive'] + sent['neutral'] + sent['negative']
sent['NPS'] = (sent['positive']/sent['total'] - sent['negative']/sent['total'])*100
sent = sent.sort_values('NPS', ascending=True)
sent<jupyter_output><empty_output><jupyter_text>We see that during month of February, US Airways, American, and United had the worst NPS. How does this compare to delays data during the same month for each airline?<jupyter_code># get flights in February only
flights_feb = flights[flights['MONTH'] == 2]
flights_feb.shape
sorting_col = 'AIRLINE_DELAY'

# for all flights
airline_delays_feb = get_airline_delays(flights_feb, airlines, delay_times_features) \
    .sort_values(by=sorting_col, ascending=False)  
# for very late flights
airline_delays_feb_verylate = get_airline_delays(flights_feb, airlines, delay_times_features,  ) \
    .sort_values(by=sorting_col, ascending=False) 
sorting_col = 'ARRIVAL_DELAY'

# for all flights
arrival_delays_feb_all = get_airline_delays(flights_feb, airlines, delay_times_features) \
    .sort_values(by=sorting_col, ascending=False) 
arrival_delays_feb_late = get_airline_delays(flights_feb, airlines, delay_times_features, 0) \
    .sort_values(by=sorting_col, ascending=False) 
arrival_delays_feb_verylate = get_airline_delays(flights_feb, airlines, delay_times_features, 120) \
    .sort_values(by=sorting_col, ascending=False)     
# merge with NPS from tweets
sent = sent.merge(airline_delays_feb[['IATA_CODE', 'AIRLINE_DELAY']], how='inner', on='IATA_CODE')
sent = sent.rename(columns={'AIRLINE_DELAY':'AIRLINE_DELAY (ALL FLIGHTS)'})
sent = sent.merge(airline_delays_feb_verylate[['IATA_CODE', 'AIRLINE_DELAY']], how='inner', on='IATA_CODE')
sent = sent.rename(columns={'AIRLINE_DELAY':'AIRLINE_DELAY (VERY LATE FLIGHTS)'})
sent = sent.merge(arrival_delays_feb_all[['IATA_CODE', 'ARRIVAL_DELAY']], how='inner', on='IATA_CODE')
sent = sent.rename(columns={'ARRIVAL_DELAY':'ARRIVAL_DELAY (ALL FLIGHTS)'})
sent = sent.merge(arrival_delays_feb_late[['IATA_CODE', 'ARRIVAL_DELAY']], how='inner', on='IATA_CODE')
sent = sent.rename(columns={'ARRIVAL_DELAY':'ARRIVAL_DELAY (LATE FLIGHTS)'})
# merge with airline names
sent = sent.merge(airlines, how='inner', on='IATA_CODE')
sent
def plot_delays_and_sentiments(sent, delay_column):
    # figure and plot
    fig, axs = plt.subplots(figsize=(10, 7))
    axs = sns.regplot(delay_column, "NPS", data=sent)
    plt.grid()
    plt.xlabel('Minutes Delay Per Flight')
    plt.ylabel('Twitter Sentiment NPS')
    plt.ylim([-100, 50])
    plt.title(delay_column + ' vs. Twitter Sentiment in February 2015')
    # add annotations one by one with a loop
    airline_labels = sent['AIRLINE'].unique()
    for line in range(0,sent.shape[0]):
         axs.text(sent[delay_column][line], sent['NPS'][line]+3.5, airline_labels[line], 
                 horizontalalignment='left', size='medium', color='black', weight='semibold')
    # calculate pearson correlation coefficient
    pc, p_val = pearsonr(sent[delay_column], sent['NPS'])
    x_pos = np.max(sent[delay_column])
    axs.text(0.7*x_pos, 40, 'Pearson Coefficient: {}'.format(round(pc, 3)), fontsize=14, weight='semibold')
    axs.text(0.7*x_pos, 30, 'P-Value: {}'.format(round(p_val, 3)), fontsize=14, weight='semibold')
plot_delays_and_sentiments(sent, 'AIRLINE_DELAY (ALL FLIGHTS)')
plt.savefig(IMAGE_PATH + 'twitter_sentiment_airline_delay.png')
plot_delays_and_sentiments(sent, 'AIRLINE_DELAY (VERY LATE FLIGHTS)')
plot_delays_and_sentiments(sent, 'ARRIVAL_DELAY (ALL FLIGHTS)')
plt.savefig(IMAGE_PATH + 'twitter_sentiment_arrival_delay.png')
plot_delays_and_sentiments(sent, 'ARRIVAL_DELAY (LATE FLIGHTS)')<jupyter_output><empty_output><jupyter_text>Twitter negative tweet reason analysisWhat are negative tweets compaining about? They fall into several categories:<jupyter_code>reasons = tweets.groupby('negativereason')['tweet_id'].count().to_frame().reset_index()
reasons = reasons.rename(columns={'tweet_id':'number'})
reasons = reasons.sort_values('number', ascending=False)
reasons
# plot complaint reasons
fig, axs = plt.subplots(figsize=(15, 7))
g = sns.barplot(reasons['negativereason'], reasons['number'])
g.set_xticklabels(labels=reasons['negativereason'], rotation=30, fontsize=12)
plt.ylabel('Number of Negative Tweets', fontsize=15)
plt.xlabel('Complaint Reason', fontsize=15)

# save figure
plt.savefig(IMAGE_PATH + 'negative_reasons.png')<jupyter_output><empty_output><jupyter_text>We see that while customer service issue is the dominant complaint topic, late flights are the second most popular reason for negative tweet.<jupyter_code># get late flight complaints only
late_comps = tweets[tweets['negativereason'] == 'Late Flight']
late_comps = late_comps.groupby('airline')['tweet_id'].count().to_frame().reset_index()
late_comps = late_comps.rename(columns={'tweet_id':'count'})
late_comps['AIRLINE'] = late_comps['airline'].map(airline_codes)
late_comps<jupyter_output><empty_output><jupyter_text>Of course, airlines with more flights are more likely going to have more delays just due to the shear number of flights. So how does this compare with actual number of delayed flights due to the airline's fault?<jupyter_code># get flights more than 60 min late with airline accounting for more than half of the delay
flights_late = flights[(flights['ARRIVAL_DELAY'] > 60) & (flights['AIRLINE_DELAY'] >= 0.5*flights['ARRIVAL_DELAY'])]
flights_late = flights_late[flights_late['MONTH'] == 1]
flights_late.shape
# groupby to find late flights due to the fault of the airline and count them for each airline
late_flights = flights_late.groupby('AIRLINE')['FLIGHT_NUMBER'].count().to_frame()
late_flights = late_flights.rename(columns={'FLIGHT_NUMBER':'n_flights'})
late_flights = late_flights.reset_index()
late_flights.head()
# join with tweet complaints
late_comps = late_comps.merge(right=late_flights, how='inner', on='AIRLINE')
late_comps
# plot results
fig, axs = plt.subplots(figsize=(10, 6))
axs = sns.scatterplot('n_flights', 'count', data=late_comps)
plt.grid()
plt.xlabel('Number of Late Flights', fontsize=14)
plt.ylabel('Number of Negative Tweets about Late Flights', fontsize=14)
# plt.ylim([-100, 50])
plt.title('Late Flights vs. Negative Tweets about Late Flights in February 2015', fontsize=16)
# add annotations one by one with a loop
airline_labels = late_comps['airline'].unique()
for line in range(0,sent.shape[0]):
    axs.text(late_comps['n_flights'][line]-50, late_comps['count'][line]+10, airline_labels[line],
             horizontalalignment='left', size='medium', color='black', weight='semibold')

# diagonal line
dl_x = [0, 1200]
dl_y = [0, 600]
plt.plot(dl_x, dl_y, color='gray',linestyle='--')
    
# save figure
plt.savefig(IMAGE_PATH + 'twitter_sentiment2.png')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08986356973728718, 'fraction_numerical': 0.00791521028300823, 'mean_word_length': 5.2662144976685035, 'pattern_counts': {'":': 0, '<': 72, '<?xml version=': 0, '>': 84, 'https://': 17, 'lorem ipsum': 0, 'www.': 6, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 19, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '660563', 'license': 'MIT', 'n_tokens_mistral': 13345, 'n_tokens_neox': 12519, 'n_words': 4429, 'path': 'flight_delays.ipynb', 'repo_name': 'ryanbae89/flight-delays-and-twitter', 'size': '44347'}