Predicting Airline Delay Patterns

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "flight_id":    ["FL001","FL002","FL003","FL004","FL005","FL006","FL007","FL008",
                     "FL009","FL010","FL011","FL012","FL013","FL014","FL015","FL016",
                     "FL017","FL018","FL019","FL020"],
    "route":        ["JFK-LAX","ORD-MIA","LAX-SEA","DFW-BOS","JFK-LAX",
                     "ORD-MIA","LAX-SEA","DFW-BOS","JFK-LAX","ORD-MIA",
                     "LAX-SEA","DFW-BOS","JFK-LAX","ORD-MIA","LAX-SEA",
                     "DFW-BOS","JFK-LAX","ORD-MIA","LAX-SEA","DFW-BOS"],
    "dep_hour":     [7,9,6,14,11,16,8,18,13,7,5,20,15,10,6,17,9,14,7,19],
    "carrier_delay":[12,0,0,28,5,18,0,32,8,0,0,25,15,0,0,30,10,22,0,28],
    "weather_delay":[0,35,0,0,18,0,0,0,0,42,0,0,0,28,0,0,0,0,0,0],
    "nas_delay":    [8,5,0,12,0,10,0,15,5,8,0,10,8,6,0,12,4,9,0,11],
    "late_aircraft":[22,0,0,35,14,25,0,40,18,0,0,30,20,0,0,38,15,28,0,35],
    "total_delay":  [42,40,0,75,37,53,0,87,31,50,0,65,43,34,0,80,29,59,0,74],
    "on_time":      [0,0,1,0,0,0,1,0,0,0,1,0,0,0,1,0,0,0,1,0]
})

# Overall on-time performance
otp = df["on_time"].mean()
print(f"Flights: {len(df)} | On-time: {df['on_time'].sum()} | OTP: {otp:.1%}")
print(f"Delayed flights: {(df['on_time']==0).sum()} | Mean delay (all): {df['total_delay'].mean():.1f} min")

# Departure hour banding
df["dep_period"] = pd.cut(
    df["dep_hour"],
    bins   = [0, 9, 14, 18, 24],
    labels = ["Early (0–9)","Midday (10–14)","Afternoon (15–18)","Evening (19+)"],
    right  = False
)

# Route performance
route_stats = df.groupby("route").agg(
    flights      = ("flight_id",    "count"),
    on_time_rate = ("on_time",      "mean"),
    mean_delay   = ("total_delay",  "mean"),
    max_delay    = ("total_delay",  "max"),
    total_delay  = ("total_delay",  "sum")
).round(2).reset_index()
route_stats["delay_rate"] = (1 - route_stats["on_time_rate"]).round(2)
route_stats["otp_pct"]    = (route_stats["on_time_rate"] * 100).round(0)
route_stats = route_stats.sort_values("mean_delay", ascending=False)

print("\nRoute performance — ranked by mean delay:")
print(route_stats[["route","flights","otp_pct","mean_delay","max_delay","total_delay"]].to_string(index=False))

delay_cols = ["carrier_delay","weather_delay","nas_delay","late_aircraft"]

# Total minutes by cause across all flights
cause_totals = df[delay_cols].sum().rename("total_minutes")
grand_total  = cause_totals.sum()

# Share of total delay per cause
cause_share = (cause_totals / grand_total * 100).round(1)
cause_df = pd.DataFrame({
    "cause":         delay_cols,
    "total_minutes": cause_totals.values,
    "share_pct":     cause_share.values,
    "controllable":  [True, False, False, True]   # carrier + late_aircraft = controllable
})
print("Delay cause attribution:")
print(cause_df.to_string(index=False))

# Controllable vs uncontrollable split
ctrl_mins   = cause_df[cause_df["controllable"]==True]["total_minutes"].sum()
unctrl_mins = cause_df[cause_df["controllable"]==False]["total_minutes"].sum()
ctrl_pct    = ctrl_mins / grand_total * 100
print(f"\nControllable delay:   {ctrl_mins} min ({ctrl_pct:.1f}%)")
print(f"Uncontrollable delay: {unctrl_mins} min ({100-ctrl_pct:.1f}%)")

# Cause breakdown by route
route_cause = df.groupby("route")[delay_cols].mean().round(1)
route_cause["controllable_avg"] = route_cause["carrier_delay"] + route_cause["late_aircraft"]
route_cause["uncontrollable_avg"] = route_cause["weather_delay"] + route_cause["nas_delay"]
print("\nMean delay minutes by cause and route:")
print(route_cause.to_string())

# Delay by departure period
period_stats = df.groupby("dep_period", observed=True).agg(
    flights      = ("flight_id",   "count"),
    otp          = ("on_time",     "mean"),
    mean_delay   = ("total_delay", "mean"),
    mean_carrier = ("carrier_delay","mean"),
    mean_late_ac = ("late_aircraft","mean")
).round(2).reset_index()
period_stats["otp_pct"]      = (period_stats["otp"] * 100).round(0)
period_stats["delay_rate"]   = (1 - period_stats["otp"]).round(2)

print("Delay by departure period:")
print(period_stats[["dep_period","flights","otp_pct","mean_delay",
                     "mean_carrier","mean_late_ac"]].to_string(index=False))

# Pearson correlation: dep_hour vs total_delay
hour_delay_corr = df["dep_hour"].corr(df["total_delay"]).round(3)
hour_carrier_corr = df["dep_hour"].corr(df["carrier_delay"]).round(3)
hour_late_corr  = df["dep_hour"].corr(df["late_aircraft"]).round(3)
print(f"\nCorrelation — departure hour vs:")
print(f"  total_delay:    r = {hour_delay_corr:+.3f}")
print(f"  carrier_delay:  r = {hour_carrier_corr:+.3f}")
print(f"  late_aircraft:  r = {hour_late_corr:+.3f}")

# On-time flights: what do they share?
on_time_flights  = df[df["on_time"] == 1]
delayed_flights  = df[df["on_time"] == 0]
print(f"\nOn-time flights profile:")
print(f"  Mean dep_hour:  {on_time_flights['dep_hour'].mean():.1f}")
print(f"  Mean total_delay: {on_time_flights['total_delay'].mean():.1f} min")
print(f"\nDelayed flights profile:")
print(f"  Mean dep_hour:  {delayed_flights['dep_hour'].mean():.1f}")
print(f"  Mean total_delay: {delayed_flights['total_delay'].mean():.1f} min")

COST_PER_MIN = 74   # USD per delay minute — Airlines for America industry average

# Controllable delay per flight
df["controllable_delay"] = df["carrier_delay"] + df["late_aircraft"]
df["uncontrollable_delay"] = df["weather_delay"] + df["nas_delay"]

total_ctrl_mins   = df["controllable_delay"].sum()
total_unctrl_mins = df["uncontrollable_delay"].sum()
total_ctrl_cost   = total_ctrl_mins * COST_PER_MIN
total_unctrl_cost = total_uctrl_mins = total_unctrl_mins * COST_PER_MIN

print(f"Cost-per-delay-minute assumption: ${COST_PER_MIN} (Airlines for America)")
print(f"\nControllable delay:   {total_ctrl_mins} min → ${total_ctrl_cost:,} cost")
print(f"Uncontrollable delay: {total_unctrl_mins} min → ${total_uctrl_mins:,} cost")
print(f"Total delay cost:     ${(total_ctrl_mins + total_unctrl_mins) * COST_PER_MIN:,}")

# Cost by route — controllable only (what the airline can fix)
route_ctrl = df.groupby("route")["controllable_delay"].agg(
    ["sum","mean"]
).round(1).reset_index()
route_ctrl.columns = ["route","total_ctrl_mins","mean_ctrl_mins"]
route_ctrl["ctrl_cost"] = (route_ctrl["total_ctrl_mins"] * COST_PER_MIN).astype(int)
route_ctrl = route_ctrl.sort_values("ctrl_cost", ascending=False)
print("\nControllable delay cost by route:")
print(route_ctrl.to_string(index=False))

# Saving scenario: if DFW-BOS hits 80% OTP (industry target)
current_otp  = df[df["route"]=="DFW-BOS"]["on_time"].mean()
target_otp   = 0.80
current_ctrl = df[df["route"]=="DFW-BOS"]["controllable_delay"].mean()
saved_mins   = current_ctrl * (target_otp - current_otp) * len(df[df["route"]=="DFW-BOS"])
saved_cost   = saved_mins * COST_PER_MIN
print(f"\nDFW-BOS current OTP: {current_otp:.0%} → target: {target_otp:.0%}")
print(f"Estimated controllable minutes saved: {saved_mins:.0f}")
print(f"Estimated cost saving:               ${saved_cost:,.0f}")

# Melt delay columns to long format for pivot
delay_long = df.melt(
    id_vars    = ["flight_id","route"],
    value_vars = delay_cols,
    var_name   = "cause",
    value_name = "minutes"
)

# Pivot: mean delay minutes by route x cause
pivot_delay = pd.pivot_table(
    delay_long,
    index      = "route",
    columns    = "cause",
    values     = "minutes",
    aggfunc    = "mean",
    fill_value = 0
).round(1)

# Rename columns for readability
pivot_delay.columns = ["Carrier","Late Aircraft","NAS","Weather"]
print("Mean delay (min) by route × cause:")
print(pivot_delay.to_string())

# Add totals and controllable share
pivot_delay["Total"] = pivot_delay.sum(axis=1).round(1)
pivot_delay["Ctrl%"] = (
    (pivot_delay["Carrier"] + pivot_delay["Late Aircraft"]) / pivot_delay["Total"] * 100
).round(0).fillna(0)
print("\nWith totals and controllable share (%):")
print(pivot_delay[["Carrier","Late Aircraft","NAS","Weather","Total","Ctrl%"]].to_string())