last(ignorenulls)

What it does

Sensors, prices and statuses often only record a value when it changes. To get a value for every row, carry the last known value forward until a new one appears.

Step by step

1. 1
   The window is partitioned by sensor and ordered by ts.
2. 2
   The frame runs from the first row of the partition to the current row.
3. 3
   last(temp, ignorenulls=True) looks at that frame and returns the latest non-null temp.
4. 4
   Sensor B's first row has no earlier value, so it stays null.

Run the example

from pyspark.sql import functions as F
from pyspark.sql.window import Window

w = Window.partitionBy("sensor").orderBy("ts")
frame = w.rowsBetween(Window.unboundedPreceding, Window.currentRow)
result = readings.withColumn("temp_filled", F.last("temp", ignorenulls=True).over(frame))

SELECT sensor, ts, temp,
       LAST(temp, TRUE) OVER (
         PARTITION BY sensor ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp_filled
FROM readings

Spark 3.2 and later also accept the standard form LAST(temp) IGNORE NULLS OVER (...).

Why the explicit frame

The default frame for an ordered window is a range up to the current row, so rows with the same ts are all in each other's frame. With duplicate timestamps, a row could pick up a value from a tied row that comes "after" it. An explicit rowsBetween(unboundedPreceding, currentRow) makes the behaviour exact, and is clearer to read.

Back-filling

To fill from the next known value instead, look forward: F.first("temp", ignorenulls=True) over rowsBetween(Window.currentRow, Window.unboundedFollowing). Combining both, forward fill then back fill, fills everything except partitions with no values at all.

When not to forward-fill

Forward-filling asserts that the value did not change. That is true for a status or a slowly changing price, and false for a sensor that went offline for a day. Consider capping how far a value can be carried, for example by also carrying the timestamp of the last reading with last(F.when(temp.isNotNull(), ts), True) and nulling fills older than a threshold.

Common mistakes