Carrier-level window aggregations

Window functions compute per-row aggregates over a partition without collapsing the rows. That is how we end up with a carrier_year_month_avg_arr_delay column on every flight: the mean of its carrier-month group, attached to the row, ready for downstream group-bys.

partitionBy: how rows get grouped without collapsing

Each row keeps its identity. The window adds a new column with the partition mean.
transform.py
python
carrier_year_month = Window.partitionBy("year", "month", "carrier")
carrier_year_hour = Window.partitionBy("year", "hour", "carrier")
carrier_year = Window.partitionBy("year", "carrier")

carrier_windows = {
    "year_hour": carrier_year_hour,
    "year_month": carrier_year_month,
    "year_avg": carrier_year,
}

Three carrier windows we will reuse: month-level, hour-level, and year-level. Defining the windows once at the top of the function keeps the per-column code below readable.

transform.py
python
delay_columns = ["arr_delay", "dep_delay", "total_delay"]

for column in delay_columns:
    for partition, window in carrier_windows.items():
        df = df.withColumn(f"carrier_{partition}_avg_{column}", mean(column).over(window))

The loop applies each window to each delay column, naming the resulting column by partition and metric. The naming convention is what makes the downstream group-by readable.

For an unbounded mean across a whole partition, no. orderBy plus a frame is for cumulative or ranking patterns: running totals, top-N per group, lag and lead. The mean over a partition does not care about row order.

Code playground: Build a partitioned mean in pandas

Loading practice…

Quiz: Quiz

Loading practice…