Analyzing Flight Delays, Cancellations and Diversions, 2018–2024

45,968,068 reported flight records across all 84 months, with all 109 BTS source fields, exact delay values and separate cancellation/diversion outcomes. This tutorial describes reported outcomes. It does not train or validate a predictive model.

Load all 84 months

Install duckdb and pandas, extract the complete archive, and run this Python code from its root. Each monthly Parquet contains the same 111 fields. The source is the BTS Reporting Carrier table, not every US flight or the separate Marketing Carrier network table.

import duckdb
con = duckdb.connect()
con.execute("CREATE VIEW flights AS SELECT * FROM read_parquet('parquet/*.parquet')")
rows, months = con.execute("SELECT count(*), count(DISTINCT source_month) FROM flights").fetchone()
print(rows, months)
monthly = con.execute("""
SELECT source_month, count(*) AS reported_records,
       count(*) FILTER (WHERE Cancelled = 1) AS cancelled_records,
       count(*) FILTER (WHERE Diverted = 1) AS diverted_records,
       count(*) FILTER (WHERE ArrDel15 IS NOT NULL AND Cancelled = 0 AND Diverted = 0) AS arrival_denominator,
       count(*) FILTER (WHERE ArrDel15 = 1 AND Cancelled = 0 AND Diverted = 0) AS delayed_arrivals
FROM flights GROUP BY source_month ORDER BY source_month
""").df()
monthly["delay_rate"] = monthly["delayed_arrivals"] / monthly["arrival_denominator"].replace(0, float("nan"))
print(monthly.head())

The complete release returns 45968068 rows and 84 months. The numerator and denominator both exclude cancellations and diversions. Missing ArrDel15 values stay outside the arrival denominator; they are not on-time flights.

Keep cancellations and diversions separate

There are 1,001,102 cancelled records, 108,460 diverted records and 1,109,916 missing ArrDelay values. Missing arrival delay does not mean on time. Use separate Cancelled, Diverted and ArrDel15 fields, and state the denominator. Diverted arrival outcomes use DivReachedDest and DivArrDelay.

diversions = con.execute("""
SELECT Year, count(*) AS diverted_records,
       count(*) FILTER (WHERE DivReachedDest = 1) AS reached_scheduled_destination,
       count(DivArrDelay) AS reported_diverted_arrival_delays,
       avg(DivArrDelay) AS mean_reported_diverted_arrival_delay
FROM flights WHERE Diverted = 1 GROUP BY Year ORDER BY Year
""").df()
print(diversions)

Preserve identifiers and time meaning

The candidate key FlightDate + Reporting_Airline + Flight_Number_Reporting_Airline + Origin + Dest has 2 excess duplicate rows, all retained. source_month + source_row is unique only within this release and is not a stable flight identity across agency revisions. Local HHMM times are strings, preserving leading zeros and the source 2400 convention. They are not UTC timestamps; do not subtract HHMM integers or infer elapsed time without dates/time zones. Negative departure/arrival delays mean early movement and are retained. Delay measures use exact decimal types; blank source values become null in Parquet.

Limits of prediction and comparison

Delay-cause minutes are conditional reported outcomes, not universal weather observations. Blank causes are not automatically zero. Post-flight fields are not available when making pre-departure predictions. No airport/weather joins, exposure adjustment, predictive model or risk score is supplied. Use time-based evaluation and only fields available at prediction time if you build your own model. This snapshot does not establish model accuracy.