DOT Airline On-Time Performance Dataset 2018–Present

import pandas as pd

df = pd.read_parquet("dot_airline_ontime.parquet")

# On-time rate by carrier
carrier_perf = (
    df[df["cancelled"] == 0]
    .groupby("carrier")["is_delayed"]
    .agg(flights="count", delayed="sum")
    .assign(delay_rate=lambda x: (x["delayed"] / x["flights"] * 100).round(1))
    .sort_values("delay_rate")
)
print(carrier_perf)

# Worst routes by average arrival delay
route_delays = (
    df[(df["cancelled"] == 0) & df["arr_delay"].notna()]
    .groupby("route")["arr_delay"]
    .agg(flights="count", avg_delay="mean")
    .query("flights >= 500")
    .sort_values("avg_delay", ascending=False)
    .head(20)
)
print(route_delays)

# COVID impact: monthly flight volume 2018-2024
monthly = (
    df.groupby(["year", "month"])
    .size()
    .reset_index(name="flights")
    .sort_values(["year", "month"])
)

# Delay cause attribution (minutes) by carrier
delay_causes = (
    df[df["arr_del15"] == 1]
    .groupby("carrier")[
        ["carrier_delay", "weather_delay", "nas_delay",
         "security_delay", "late_aircraft_delay"]
    ]
    .mean()
    .round(1)
)

# Best connecting airports by NAS delay rate
hub_nas = (
    df[df["cancelled"] == 0]
    .groupby("origin")["nas_delay"]
    .agg(flights="count", avg_nas="mean")
    .query("flights >= 10000")
    .sort_values("avg_nas")
    .head(15)
)

# Year-partitioned read (much faster for single year)
df_2023 = pd.read_parquet("by_year/year=2023/data.parquet")