Monthly on-time performance report of domestic airlines, for the month of July 2015
Data source: On-Time Performance data published by the Bureau of Transportation Statistics
Visit The Airline Report, July 2015 for a summary and a downloadable slide deck
# Step 1: Import required modules
import matplotlib.pyplot as plt
import pandas as pd
from matplotlib import cm
from commons_otp import *
# Step 2: Get the source of the data
filename = '2015_07.csv'
# Step 3: Define some reusable "constants"
current_month = int(filename[5:7])
current_year = int(filename[:4])
weekday_counts = get_weekday_counts(current_year, current_month)
total_number_of_days = sum(weekday_counts.values())
str_current_month = months_of_year[current_month]
# Step 4: Load the data set
df = pd.read_csv(filename)
# Step 5: Clean the data set
# 5a. Remove columns named "Unnamed"
unnamed_cols = [col for col in df.columns if col.find('Unnamed') != -1]
df.drop(unnamed_cols, axis=1, inplace=True)
# 5b. Replace NaN columns with appropriate placeholders
for col, value in fillna_values.items():
df[col].fillna(value, inplace=True)
# 5c. Convert the flight date from string to datetime
df.FL_DATE = pd.to_datetime(df.FL_DATE, format='%Y-%m-%d')
# 5d. Add a new column for day of week
df['DAY_OF_WEEK'] = df.FL_DATE.dt.weekday
# 5e. Add a new column for airline name
df['AIRLINE'] = df.AIRLINE_ID.apply(lambda airline_id: airline_names[airline_id])
# Step 6: Look for, and display, trends in the data
# 6a. Start with some general observations
total_distance = df.DISTANCE.sum()
print('> Total number of flights recorded: {0}'.format(df.shape[0]))
print('> Total distance covered by all flights: {0} miles'.format(total_distance))
print('> Average flying speed (distance / air_time): {0:.2f} mph'.format(60 * total_distance / df.AIR_TIME.sum()))
# Oh, and it never hurts to put things in perspective
print('> Distance expressed as number of round trips to the Sun: {0}'.format(round(0.5 * total_distance / earth_sun_distance)))
print('> Distance expressed as number of round trips to the Moon: {0}'.format(round(0.5 * total_distance / earth_moon_distance)))
# 6b. Daily Statistics
flights_per_day = df.groupby('FL_DATE').size()
plt.plot(flights_per_day)
plt.title('Flights per day ({0} {1})'.format(str_current_month, current_year), fontsize=24)
plt.xlabel('Days', fontsize=18)
plt.ylabel('Number of flights', fontsize=18)
plt.axhline(df.shape[0] / total_number_of_days, linestyle='dashed', linewidth=2, color='brown')
plt.grid()
plt.show()
sorted_flights_per_day = flights_per_day.sort_values()
print('> The busiest travel day of the month was {0} with {1} flights'.format(
sorted_flights_per_day.tail(1).keys()[0].to_pydatetime().strftime('%A, %B %d'),
sorted_flights_per_day.tail(1).values[0]))
print('> The slowest travel day of the month was {0} with {1} flights'.format(
sorted_flights_per_day.head(1).keys()[0].to_pydatetime().strftime('%A, %B %d'),
sorted_flights_per_day.head(1).values[0]))
# 6c. Weekly Statistics
flights_by_day_of_week = df.groupby('DAY_OF_WEEK').size().sort_values()
weekday_flight_average = get_weekday_flight_average(flights_by_day_of_week, weekday_counts)
weekday_flight_average = sorted(weekday_flight_average, key=weekday_flight_average.get)
print('> On average, {0}s were the busiest day of the week, followed by {1}s and {2}s'.format(
days_of_week[weekday_flight_average[-1]],
days_of_week[weekday_flight_average[-2]],
days_of_week[weekday_flight_average[-3]]))
print('> The slowest travel days were {0}s, followed by {1}s'.format(
days_of_week[weekday_flight_average[0]],
days_of_week[weekday_flight_average[1]]))
# 6d. Airports
origin_airports_sorted = df.groupby('ORIGIN').size().sort_values(ascending=False)
top_origin_airport = origin_airports_sorted.head(1).keys()[-1]
top_origin_airport_flights = origin_airports_sorted.head(1).values[-1]
second_origin_airport_flights = origin_airports_sorted.head(2).values[-1]
second_origin_airport = origin_airports_sorted.head(2).keys()[-1]
dest_airports_sorted = df.groupby('DEST').size().sort_values(ascending=False)
top_origin_total_flights = top_origin_airport_flights + dest_airports_sorted.get(top_origin_airport)
print('> The busiest airport was {0} with a total of {1} departures'.format(top_origin_airport, top_origin_airport_flights))
print('> {0} had {1:.2f}% more departures than {2} at number 2'.format(top_origin_airport,
100 * (top_origin_airport_flights / second_origin_airport_flights - 1), second_origin_airport))
print('> With {0} total departures and arrivals, {1} accounted for {2:.2f}% of all flights for the '
'month'.format(top_origin_total_flights, top_origin_airport, 100 * (top_origin_total_flights) / df.shape[0]))
plt.hist(df.groupby('ORIGIN').size(), bins=20)
plt.title('Departures by airport ({0} {1})'.format(str_current_month, current_year),
fontsize=24)
plt.xlabel('Total number of departures', fontsize=18)
plt.ylabel('Number of airports', fontsize=18)
plt.grid()
plt.show()
# 6e. Airline routes
route_airline_groups = df.groupby(['ORIGIN', 'DEST', 'AIRLINE']).size()
top_overall_route = df.groupby(['ORIGIN', 'DEST']).size().sort_values(ascending=False).keys()[0]
top_route_flights = df.groupby(['ORIGIN', 'DEST']).size().sort_values(ascending=False).values[0]
top_route_single_carrier = route_airline_groups.sort_values(ascending=False).keys()[0]
top_route_single_carrier_flights = route_airline_groups.sort_values(ascending=False).values[0]
print('> The most popular route overall was from {0} to {1}'.format(top_overall_route[0], top_overall_route[1]))
print('> This route had {0} flights during the month, or about {1} flights per day'.format(top_route_flights,
top_route_flights // total_number_of_days))
print('> The airline that had the most number of flights in a single route was {0}, with {1} flights '
'from {2} to {3}'.format(top_route_single_carrier[2], top_route_single_carrier_flights,
top_route_single_carrier[0], top_route_single_carrier[1]))
airport_connections = get_airport_connections(df)
airport_with_most_connections = max(airport_connections, key=airport_connections.get)
airports_with_one_connection = len(get_airports_with_n_connections(airport_connections))
print('> {0} had the most number of direct routes ({1}) of any city in the United States'.
format(airport_with_most_connections, airport_connections[airport_with_most_connections]))
print('> At the other end of the spectrum, {0} airports were served by just one other airport'.
format(airports_with_one_connection))
# 6f. Airlines
carriers_by_flight_count = df.AIRLINE.value_counts()
airlines = df.AIRLINE.unique()
plt.barh(np.arange(len(airlines)), carriers_by_flight_count)
plt.yticks(np.arange(len(airlines)), carriers_by_flight_count.keys())
plt.title('Flights by carrier ({0} {1})'.format(str_current_month, current_year),
fontsize=24)
plt.xlabel('Number of flights', fontsize=18)
plt.ylabel('Carriers', fontsize=18)
plt.show()
airline_route_groups = df.groupby(['AIRLINE', 'ORIGIN', 'DEST']).size()
airline_routes = {}
for airline in airlines:
airline_routes[airline] = airline_route_groups.get(airline).agg('count')
plt.barh(np.arange(len(airline_routes)), airline_routes.values())
plt.yticks(np.arange(len(airline_routes)), airline_routes.keys())
plt.xlabel('Number of routes', fontsize=18)
plt.ylabel('Carriers', fontsize=18)
plt.title('Number of routes by airline ({0} {1})'.format(str_current_month, current_year), fontsize=24)
plt.show()
# 6g. Delays
df = df.dropna()
df['DEP_TIME'] = pd.to_datetime(df['DEP_TIME'].transform(convert_string_to_time), format='%H%M').transform(lambda x: x.time())
df['ARR_TIME'] = pd.to_datetime(df['ARR_TIME'].transform(convert_string_to_time), format='%H%M').transform(lambda x: x.time())
delayed_arrivals = df[df.ARR_DELAY >= 15]
airline_delay_pct = (100 * df[df.ARR_DELAY >= 15].AIRLINE.value_counts() / df.AIRLINE.value_counts()).sort_values()
print('> For the month of {0} {1}, {2} flights were delayed. This represents {3:.2f}% of all flights '
'for which data is available.'.format(str_current_month, current_year, delayed_arrivals.shape[0],
100 * delayed_arrivals.shape[0] / df.shape[0]))
colors = cm.hsv_r(1.2 * airline_delay_pct / max(airline_delay_pct))
plt.barh(np.arange(len(airline_delay_pct)), airline_delay_pct, color=colors)
plt.axvline(100 * delayed_arrivals.shape[0] / df.shape[0], linestyle='dashed', linewidth=2)
plt.yticks(np.arange(len(airline_delay_pct)), airline_delay_pct.keys())
plt.xlabel('Delay %', fontsize=18)
plt.ylabel('Carrier', fontsize=18)
plt.title('Percentage of delayed flights by carrier ({0} {1})'.format(str_current_month, current_year), fontsize=24)
plt.show()
print('> {0} flights ({1:.2f}% of total) were delayed by less than an hour'.format(
delayed_arrivals[delayed_arrivals.ARR_DELAY < 60].shape[0],
100 * delayed_arrivals[delayed_arrivals.ARR_DELAY < 60].shape[0] / df.shape[0]))
print('> {0} flights ({1:.2f}% of total) were delayed by more than six hours'.format(
delayed_arrivals[delayed_arrivals.ARR_DELAY > 360].shape[0],
100 * delayed_arrivals[delayed_arrivals.ARR_DELAY > 360].shape[0] / df.shape[0]))
delayed_arrivals_15_360 = delayed_arrivals[delayed_arrivals.ARR_DELAY <= 360]
plt.hist(delayed_arrivals_15_360.ARR_DELAY, bins=30, orientation='vertical')
plt.axvline(np.mean(delayed_arrivals_15_360.ARR_DELAY), linestyle='dashed', linewidth=3, color='brown')
plt.xticks(np.arange(0, 360, 60))
plt.ylabel('Number of delayed flights', fontsize=14)
plt.xlabel('Delay (minutes)', fontsize=14)
plt.title('Flight delays by delay time ({0} {1})'.format(str_current_month, current_year), fontsize=24)
plt.grid()
plt.show()
route_delay_counts = get_route_delay_counts(delayed_arrivals_15_360)
route_with_most_delays = max(route_delay_counts, key=route_delay_counts.get)
print('> The route with the most number of delayed flights was {0} to {1}'.format(route_with_most_delays[0],
route_with_most_delays[1]))
route_delay_likelihood = get_route_delay_likelihood(df, route_delay_counts)
max_route_delay_likelihood = max(route_delay_likelihood, key=route_delay_likelihood.get)
print('> The route most prone to delays? {0} to {1} takes the cake with {2:.2f}% of flights in this '
'route experiencing a delay of 15 minutes or more'.format(max_route_delay_likelihood[0], max_route_delay_likelihood[1],
route_delay_likelihood[max_route_delay_likelihood]))
# 6h. Early arrivals
df['DEP_HOUR'] = df.DEP_TIME.apply(lambda x: x.hour)
early_flights = df[df.ARR_DELAY < 0]
print('> {0} flights ({1:.2f}% of total) arrived ahead of schedule'.format(early_flights.shape[0],
100 * early_flights.shape[0] / df.shape[0]))
airlines_with_early_flights = (100 * early_flights.AIRLINE.value_counts() / df.AIRLINE.value_counts()).sort_values()
print('> With {0:.2f}% flights arriving ahead of time, {1} was the winner, followed by {2} ({'
'3:.2f}%) and {4} ({5:.2f}%)'.format(airlines_with_early_flights[-1],
airlines_with_early_flights.keys()[-1],
airlines_with_early_flights.keys()[-2],
airlines_with_early_flights[-2],
airlines_with_early_flights.keys()[-3],
airlines_with_early_flights[-3]))
delayed_flights = df[np.logical_and(df.ARR_DELAY >= 15, df.ARR_DELAY <= 360)]
delay_counts_by_hour = get_flight_counts_by_departure_hour(delayed_flights)
avg_delay_by_hour_groups = get_counts_by_hour_groups(delay_counts_by_hour)
print('> Want to minimize the chances of a delay? Pick a flight during {0} time.'.format(min(avg_delay_by_hour_groups,
key=avg_delay_by_hour_groups.get)))
best_hour_for_least_delays = min(delay_counts_by_hour, key=delay_counts_by_hour.get)
print('> Flights departing between {0} and {1} experience the least number of delays'.format(best_hour_for_least_delays,
1 + best_hour_for_least_delays))
early_counts_by_hour = get_flight_counts_by_departure_hour(early_flights)
avg_early_flights_by_hour_groups = get_counts_by_hour_groups(early_counts_by_hour)
best_hour_for_early_arrivals = max(early_counts_by_hour, key=early_counts_by_hour.get)
print('> Want to arrive early? Pick flights departing during {0} time to increase your chances of '
'arriving early.'.format(max(avg_early_flights_by_hour_groups, key=avg_early_flights_by_hour_groups.get)))
print('> Flights departing between {0} and {1} have on average the most number of early '
'arrivals'.format(best_hour_for_early_arrivals, 1 + best_hour_for_early_arrivals))