Business Investigation #1
Airline Operational Performance

Case Mandate

Mandato del caso

Investigate 1,391,578 flight records from raw structure through data quality, statistics, airport and route performance, temporal behavior, root-cause evidence, and executive prioritization. Every line of code answers a business question; every result generates evidence; every finding supports a decision.

Investigar 1,391,578 registros de vuelos desde la estructura cruda hasta calidad del dato, estadística, desempeño de aeropuertos y rutas, comportamiento temporal, evidencia de causa raíz y priorización ejecutiva. Cada línea de código responde una pregunta de negocio; cada resultado genera evidencia; cada hallazgo respalda una decisión.

BI4Humans Investigation Framework

Interim Executive Summary — Evidence Through Investigation 41

Resumen ejecutivo interino — Evidencia hasta la investigación 41

Phase 1 — Data Understanding

Fase 1 — Comprensión del dato

Phase 2 — Data Quality

Fase 2 — Calidad del dato

Phase 3 — Statistical Investigation

Fase 3 — Investigación estadística

Phase 4 — Airport and Route Investigation

Fase 4 — Investigación de aeropuertos y rutas

Phase 5 — ORD Root-Cause Case File

Fase 5 — Expediente de causa raíz ORD

Phase 6 — Data Decoding and Temporal Investigation

Fase 6 — Decodificación e investigación temporal

Phase 7 — Executive Decision Modeling

Fase 7 — Modelado de decisiones ejecutivas

Evidence & Findings Register

Executive Recommendations

Recomendaciones ejecutivas

Limitations and Governance

• The dataset has no year, carrier, flight number, aircraft, passenger, cost, weather, cancellation, gate, or crew fields.
• Duplicate-looking rows were retained because the available fields do not form a guaranteed unique business key.
• IQR defines statistical outliers, not operational failures.
• The Priority Index is a transparent case-specific model, not an industry benchmark.
• Correlation does not establish causation, and the absence of linear correlation does not rule out nonlinear or interaction effects.

Next Investigation Queue — Same Business Case

Próxima cola de investigación — Mismo caso de negocio

1. Monthly and day-of-month volume and delay patterns.
2. Hour × origin and hour × route heatmaps.
3. Pareto analysis: airports and routes generating 80% of delayed minutes.
4. Critical-delay exposure above 60, 120, and 180 minutes.
5. Early-departure outlier investigation below −28 minutes.
6. Outlier concentration by airport, route, and critical window.
7. Volume-risk-impact quadrant classification.
8. Comparison of ATL, ORD, DEN, and strong benchmark hubs.
9. Delta table persistence and Spark SQL reproduction.
10. Power BI executive dashboard and final project-manager action plan.

Final Validated Script — Current Evidence Set

This integrated script reproduces the completed investigations through the current decision-modeling phase.

Este script integrado reproduce las investigaciones completadas hasta la fase actual de modelado de decisiones.

Copy Full Script# ============================================================
# BUSINESS INVESTIGATION #1 — FINAL VALIDATED SCRIPT (CURRENT)
# Airline Operational Performance | Databricks + Apache Spark
# ============================================================
from pyspark.sql.functions import (
    col, sum, when, min, max, avg, stddev, count, desc,
    round, substring, length
)

# Load data
df = spark.read.csv(
    "/databricks-datasets/learning-spark-v2/flights/departuredelays.csv",
    header=True,
    inferSchema=True
)

# Reconnaissance
total_records = df.count()
df.printSchema()
df.show(10, truncate=False)

# Nulls
df.select([
    sum(when(col(c).isNull(), 1).otherwise(0)).alias(c)
    for c in df.columns
]).show()

# Duplicate-looking rows
unique_records = df.distinct().count()
print(f"Total: {total_records:,}")
print(f"Unique: {unique_records:,}")
print(f"Duplicate-looking: {total_records - unique_records:,}")

# Core statistics
df.select(min('delay'), avg('delay'), max('delay'), stddev('delay')).show()
percentiles = df.approxQuantile('delay',[0.25,0.50,0.75,0.90,0.95,0.99],0.001)
print(percentiles)
q1, q3 = percentiles[0], percentiles[2]
iqr = q3 - q1
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr
outlier_count = df.filter((col('delay') < lower_bound) | (col('delay') > upper_bound)).count()
print(f"Outlier rate: {outlier_count / total_records * 100:.2f}%")

# Status and severity
df_status = df.withColumn('flight_status',
    when(col('delay') < 0,'Early').when(col('delay') == 0,'On Time').otherwise('Delayed')
)
df_status.groupBy('flight_status').count().show()

# Airports and routes
airport_performance = df.groupBy('origin').agg(
    count('*').alias('Flights'), avg('delay').alias('Average_Delay')
).filter('Flights >= 10000')
airport_performance.orderBy(desc('Average_Delay')).show(30, truncate=False)

df.groupBy('origin','destination').agg(
    count('*').alias('Flights'), avg('delay').alias('Average_Delay')
).filter('Flights >= 1000').orderBy(desc('Average_Delay')).show(30, truncate=False)

print(f"Distance-delay correlation: {df.stat.corr('distance','delay'):.4f}")

# ORD case file
ord = df.filter(col('origin') == 'ORD')
ord_total = ord.count()
ord_outliers = ord.filter((col('delay') < lower_bound) | (col('delay') > upper_bound)).count()
print(f"ORD outlier rate: {ord_outliers / ord_total * 100:.2f}%")

ord_routes = ord.groupBy('destination').agg(
    count('*').alias('Flights'),
    avg('delay').alias('Average_Delay'),
    sum(when(col('delay') > 0,1).otherwise(0)).alias('Delayed_Flights')
).filter('Flights >= 500').withColumn(
    'Delay_Rate_%', round(col('Delayed_Flights') * 100.0 / col('Flights'),2)
)
ord_routes.orderBy(desc('Delayed_Flights')).show(30, truncate=False)
ord_routes.orderBy(desc('Delay_Rate_%')).show(30, truncate=False)

# Decode MDDHHMM date field
df_time = (df
    .withColumn('Month', substring(col('date'),1,1))
    .withColumn('Day', substring(col('date'),2,2))
    .withColumn('Hour', substring(col('date'),4,2))
    .withColumn('Minute', substring(col('date'),6,2))
)

hourly_performance = df_time.groupBy('Hour').agg(
    count('*').alias('Flights'),
    avg('delay').alias('Average_Delay'),
    sum(when(col('delay') > 0,1).otherwise(0)).alias('Delayed_Flights')
).withColumn(
    'Delay_Rate_%', round(col('Delayed_Flights') * 100.0 / col('Flights'),2)
)
hourly_performance.filter(col('Flights') >= 10000).orderBy(desc('Delay_Rate_%')).show(24, truncate=False)

# Critical window and priority index
afternoon = df_time.filter(col('Hour').isin('14','15','16','17','18','19','20','21'))
afternoon_priority = afternoon.groupBy('origin').agg(
    count('*').alias('Flights'), avg('delay').alias('Average_Delay')
).filter('Flights >= 1000').withColumn(
    'Priority_Index', round(col('Flights') * col('Average_Delay'),0)
).orderBy(desc('Priority_Index'))
afternoon_priority.show(30, truncate=False)