The Airline Report, July 2015

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

Contents

  1. Import modules
  2. Get the source data
  3. Define reusable constants
  4. Load the data
  5. Clean the data
  6. Analyze the data
  7. Summarize findings

Step 1: Import the required modules

In [1]:
# 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 data

In [2]:
# Step 2: Get the source of the data
filename = '2015_07.csv'

Step 3: Define reusable constants

In [3]:
# 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

In [4]:
# Step 4: Load the data set
df = pd.read_csv(filename)

Step 5: Clean the data set

In [5]:
# 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)
In [6]:
# 5b. Replace NaN columns with appropriate placeholders
for col, value in fillna_values.items():
    df[col].fillna(value, inplace=True)
In [7]:
# 5c. Convert the flight date from string to datetime
df.FL_DATE = pd.to_datetime(df.FL_DATE, format='%Y-%m-%d')
In [8]:
# 5d. Add a new column for day of week
df['DAY_OF_WEEK'] = df.FL_DATE.dt.weekday
In [9]:
# 5e. Add a new column for airline name
df['AIRLINE'] = df.AIRLINE_ID.apply(lambda airline_id: airline_names[airline_id])

Step 6: Analyze the data

In [10]:
# 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)))
> Total number of flights recorded: 520718
> Total distance covered by all flights: 438172366.0 miles
> Average flying speed (distance / air_time): 446.66 mph
> Distance expressed as number of round trips to the Sun: 2
> Distance expressed as number of round trips to the Moon: 917
In [11]:
# 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()
In [12]:
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]))
> The busiest travel day of the month was Friday, July 24 with 17474 flights
> The slowest travel day of the month was Saturday, July 04 with 12634 flights
In [13]:
# 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]]))
> On average, Thursdays were the busiest day of the week, followed by Mondays and Tuesdays
> The slowest travel days were Saturdays, followed by Sundays
In [14]:
# 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]))
> The busiest airport was ATL with a total of 33735 departures
> ATL had 19.53% more departures than ORD at number 2
> With 67470 total departures and arrivals, ATL accounted for 12.96% of all flights for the month
In [15]:
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()
In [16]:
# 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]))
> The most popular route overall was from SFO to LAX
> This route had 1363 flights during the month, or about 43 flights per day
> The airline that had the most number of flights in a single route was Hawaiian, with 758 flights from HNL to OGG
In [17]:
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))
> ATL had the most number of direct routes (158) of any city in the United States
> At the other end of the spectrum, 74 airports were served by just one other airport
In [18]:
# 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()
In [19]:
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()
In [20]:
# 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]))
> For the month of July 2015, 107627 flights were delayed. This represents 20.92% of all flights for which data is available.
In [21]:
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()
In [22]:
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]))
> 72612 flights (14.12% of total) were delayed by less than an hour
> 454 flights (0.09% of total) were delayed by more than six hours
In [23]:
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()
In [24]:
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]))
> The route with the most number of delayed flights was SFO to LAX
> The route most prone to delays? TPA to CAK takes the cake with 61.76% of flights in this route experiencing a delay of 15 minutes or more
In [25]:
# 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]))
> 303348 flights (58.97% of total) arrived ahead of schedule
In [26]:
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]))
> With 67.51% flights arriving ahead of time, Delta was the winner, followed by Envoy Air (64.39%) and American (62.46%)
In [27]:
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))
> Want to minimize the chances of a delay? Pick a flight during morning time.
> Flights departing between 5 and 6 experience the least number of delays
In [28]:
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))
> Want to arrive early? Pick flights departing during afternoon time to increase your chances of arriving early.
> Flights departing between 18 and 19 have on average the most number of early arrivals

Step 7: Summary

  • Total flights: 520,718
  • Busiest day of week: Thursday
  • Slowest day of week: Saturday
  • Busiest airport: Atlanta
  • Most popular route: San Francisco (SFO) to Los Angeles (LAX)
  • Airline with the most flights: Southwest
  • Number of delayed flights: 107,627 (20.92%)
  • Airline with highest % delayed flights: Spirit
  • Airline with lowest % delayed flights: Hawaiian