PySpark lead() Function
PySpark lead() looks forward: for each row, it returns the value of a column from a row after it in the same window partition. You import it from pyspark.sql.functions and attach it to a window with .over(w), where w has an orderBy that defines what "after" means.
Most examples here use three days of sales for stores S1 and S2, plus a small table of user logins. All outputs were produced by running the code on PySpark 3.5.5. To try a variation, paste any block into the PySpark online compiler.
Quick answer
F.lead("sales").over(w)puts the next row'ssalesvalue on the current row, with "next" decided by the window'sorderBy.- The final row of each partition has no row after it, so
lead()returnsNULLthere, or thedefaultyou pass, as inF.lead("sales", 1, 0). F.lead("sales", 2)reads two rows ahead; the last two rows of each partition then have nothing to read.- Pair
lead()withdatediff()to measure the days until the next event, and filter on itsNULLto find the last row of each group. - The window needs
orderBy, the call needs.over(), and a frame such asrowsBetween(0, 1)is rejected with "Cannot specify window frame for lead function."
from pyspark.sql import Window, functions as F lead(col, offset=1, default=None)
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("lead-example").getOrCreate()
data = [
("S1", "2024-01-01", 100),
("S1", "2024-01-02", 120),
("S1", "2024-01-03", 90),
("S2", "2024-01-01", 200),
("S2", "2024-01-02", 210),
("S2", "2024-01-03", 250),
]
df = spark.createDataFrame(data, ["store", "date", "sales"])
df = df.withColumn("date", F.to_date("date"))
w = Window.partitionBy("store").orderBy("date")
df.withColumn("next_sales", F.lead("sales").over(w)) \
.orderBy("store", "date") \
.show()
+-----+----------+-----+----------+ |store| date|sales|next_sales| +-----+----------+-----+----------+ | S1|2024-01-01| 100| 120| | S1|2024-01-02| 120| 90| | S1|2024-01-03| 90| NULL| | S2|2024-01-01| 200| 210| | S2|2024-01-02| 210| 250| | S2|2024-01-03| 250| NULL| +-----+----------+-----+----------+
On 2024-01-01, S1 shows next_sales of 120, which is its 2024-01-02 sales figure. The 2024-01-03 row of each store ends in NULL: that row is the end of its partition, and partitionBy("store") stops S1's last day from reading S2's first day (200).
Syntax and parameters
lead(col, offset=1, default=None)
| Parameter | Type | Default | Description |
|---|---|---|---|
| col | Column or str | required | The column, or column name, whose later value you want. |
| offset | int | 1 | How many rows ahead to read. |
| default | any | None | Returned when fewer than |
Returns: a Column with the value of col from offset rows after the current row within the window partition, or default when that row does not exist.
lead() was added in Spark 1.4.0. The default only fills in for a missing row: if the row offset places ahead exists but its value is NULL, you get NULL, not the default. On PySpark 3.5.5, F.lead("x", 1, -1) over rows (1, 10), (2, NULL) and (3, 30) returned NULL for row 1, whose next row holds NULL, and -1 for row 3, which has no next row. For more on building window specs, see the PySpark window functions tutorial.
lead() counts rows, not calendar days. If your data skips a date, the row before the gap reads whichever date comes next in the data. When two rows in one partition have the same orderBy value, the window does not say which of them comes first, so add a second column to orderBy if you need a predictable "next" row.
The documentation links on this page point to the Spark 3.5.7 docs, because the 3.5.5 API reference pages returned 404 when this page was tested; the signature is the same there.
lead() with an offset and a default value
Pass a second argument to read further ahead, and a third to replace the NULL at the end of each partition with a value of your choice.
df.select(
"store", "date", "sales",
F.lead("sales", 1, 0).over(w).alias("next_or_0"),
F.lead("sales", 2).over(w).alias("two_days_later"),
).orderBy("store", "date").show()
+-----+----------+-----+---------+--------------+ |store| date|sales|next_or_0|two_days_later| +-----+----------+-----+---------+--------------+ | S1|2024-01-01| 100| 120| 90| | S1|2024-01-02| 120| 90| NULL| | S1|2024-01-03| 90| 0| NULL| | S2|2024-01-01| 200| 210| 250| | S2|2024-01-02| 210| 250| NULL| | S2|2024-01-03| 250| 0| NULL| +-----+----------+-----+---------+--------------+
next_or_0 matches the quick answer except on 2024-01-03, where the default 0 takes the place of NULL for both stores. two_days_later skips a row: on 2024-01-01 it returns the 2024-01-03 sales (90 for S1, 250 for S2). It is NULL on the last two days of each store because fewer than two rows follow them, and no default was given for that column.
Calculate the days until the next event
To measure the gap between one event and the next, bring the next event's date onto the current row with lead(), then subtract with datediff().
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("lead-example").getOrCreate()
logins = spark.createDataFrame(
[
("u1", "2024-03-01"),
("u1", "2024-03-04"),
("u1", "2024-03-15"),
("u2", "2024-03-02"),
("u2", "2024-03-03"),
],
["user_id", "login_date"],
)
logins = logins.withColumn("login_date", F.to_date("login_date"))
w = Window.partitionBy("user_id").orderBy("login_date")
logins.withColumn("next_login", F.lead("login_date").over(w)) \
.withColumn("days_until_next", F.datediff("next_login", "login_date")) \
.orderBy("user_id", "login_date") \
.show()
+-------+----------+----------+---------------+ |user_id|login_date|next_login|days_until_next| +-------+----------+----------+---------------+ | u1|2024-03-01|2024-03-04| 3| | u1|2024-03-04|2024-03-15| 11| | u1|2024-03-15| NULL| NULL| | u2|2024-03-02|2024-03-03| 1| | u2|2024-03-03| NULL| NULL| +-------+----------+----------+---------------+
datediff(end, start) counts the days from start to end, so the end date goes first. User u1 waited 3 days after the 2024-03-01 login and 11 days after the 2024-03-04 login, because 2024-03-15 is the next row for that user. Each user's latest login has no next_login, and datediff() with a NULL input returns NULL. The PySpark date functions tutorial covers datediff() and related helpers.
Find the last row in each group with lead()
The row where lead() returns NULL is the final row of its partition, so filtering on that NULL keeps one row per group: here, each user's most recent login.
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("lead-example").getOrCreate()
logins = spark.createDataFrame(
[
("u1", "2024-03-01"),
("u1", "2024-03-04"),
("u1", "2024-03-15"),
("u2", "2024-03-02"),
("u2", "2024-03-03"),
],
["user_id", "login_date"],
)
logins = logins.withColumn("login_date", F.to_date("login_date"))
w = Window.partitionBy("user_id").orderBy("login_date")
# The last row of each partition is the one with no next row
logins.withColumn("next_login", F.lead("login_date").over(w)) \
.filter(F.col("next_login").isNull()) \
.select("user_id", F.col("login_date").alias("last_login")) \
.orderBy("user_id") \
.show()
+-------+----------+ |user_id|last_login| +-------+----------+ | u1|2024-03-15| | u2|2024-03-03| +-------+----------+
Five logins become two rows: 2024-03-15 for u1 and 2024-03-03 for u2, the dates that had nothing after them in the previous example. This works because login_date never holds NULL. If the column you pass to lead() can contain NULL, a row in the middle of a partition can also get NULL from lead(), so apply lead() to a column that is always filled, such as the orderBy column used here.
lead() without partitionBy
If the DataFrame is a single series, such as one total per day, a window with only orderBy is enough.
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
spark = SparkSession.builder.appName("lead-example").getOrCreate()
daily = spark.createDataFrame(
[("2024-01-01", 300), ("2024-01-02", 330), ("2024-01-03", 340), ("2024-01-04", 310)],
["date", "total_sales"],
)
# No partitionBy: the whole DataFrame is one window, ordered by date
w = Window.orderBy("date")
daily.withColumn("next_total", F.lead("total_sales").over(w)).show()
+----------+-----------+----------+ | date|total_sales|next_total| +----------+-----------+----------+ |2024-01-01| 300| 330| |2024-01-02| 330| 340| |2024-01-03| 340| 310| |2024-01-04| 310| NULL| +----------+-----------+----------+
With a single partition, only one row ends in NULL: 2024-01-04, the latest date. The 2024-01-03 row picks up 310 from the row after it. When it runs this window, PySpark 3.5.5 logs WARN WindowExec: No Partition Defined for Window operation! Moving all data to a single partition, this can cause serious performance degradation. A four-row table is not affected, but on large data you should partition by a key whenever the data has one.
lead() in Spark SQL
In SQL, LEAD takes an OVER clause with the same PARTITION BY and ORDER BY parts as the DataFrame window. Register the DataFrame as a temporary view and run the query with spark.sql().
df.createOrReplaceTempView("daily_sales")
spark.sql("""
SELECT store, date, sales,
LEAD(sales) OVER (PARTITION BY store ORDER BY date) AS next_sales
FROM daily_sales
ORDER BY store, date
""").show()
+-----+----------+-----+----------+ |store| date|sales|next_sales| +-----+----------+-----+----------+ | S1|2024-01-01| 100| 120| | S1|2024-01-02| 120| 90| | S1|2024-01-03| 90| NULL| | S2|2024-01-01| 200| 210| | S2|2024-01-02| 210| 250| | S2|2024-01-03| 250| NULL| +-----+----------+-----+----------+
This table is identical to the quick answer output, row for row, so LEAD(sales) OVER (PARTITION BY store ORDER BY date) and F.lead("sales").over(Window.partitionBy("store").orderBy("date")) give the same result. Use whichever form fits the rest of your pipeline.
Common errors and fixes
lead requires window to be ordered, please add ORDER BY clause
# The window has partitionBy but no orderBy
w = Window.partitionBy("store")
df.withColumn("next_sales", F.lead("sales").over(w)).show()
AnalysisException: Window function lead(sales, 1, null) requires window to be ordered, please add ORDER BY clause. For example SELECT lead(sales, 1, null)(value_expr) OVER (PARTITION BY window_partition ORDER BY window_ordering) from table.
Why it happens: Window.partitionBy("store") splits the rows by store but gives them no sequence, so there is no "next" row for lead() to read. Spark raises an AnalysisException instead of guessing an order.
Fix: Add orderBy to the window, typically on a date or timestamp column.
# Add orderBy to the window
w = Window.partitionBy("store").orderBy("date")
df.withColumn("next_sales", F.lead("sales").over(w)).orderBy("store", "date").show()
+-----+----------+-----+----------+ |store| date|sales|next_sales| +-----+----------+-----+----------+ | S1|2024-01-01| 100| 120| | S1|2024-01-02| 120| 90| | S1|2024-01-03| 90| NULL| | S2|2024-01-01| 200| 210| | S2|2024-01-02| 210| 250| | S2|2024-01-03| 250| NULL| +-----+----------+-----+----------+
WINDOW_FUNCTION_WITHOUT_OVER_CLAUSE: lead requires an OVER clause
# lead() used without .over(window)
df.withColumn("next_sales", F.lead("sales")).show()
AnalysisException: [WINDOW_FUNCTION_WITHOUT_OVER_CLAUSE] Window function "lead(sales, 1, NULL)" requires an OVER clause.
Why it happens: F.lead("sales") is a window function, and the error message names the missing piece: an OVER clause. Without .over(w), Spark has no partitions or row order to look ahead in.
Fix: Chain .over(w) onto F.lead(...), using a window that has orderBy and, if you need groups, partitionBy.
# Call .over() with an ordered window
w = Window.partitionBy("store").orderBy("date")
df.withColumn("next_sales", F.lead("sales").over(w)).orderBy("store", "date").show()
+-----+----------+-----+----------+ |store| date|sales|next_sales| +-----+----------+-----+----------+ | S1|2024-01-01| 100| 120| | S1|2024-01-02| 120| 90| | S1|2024-01-03| 90| NULL| | S2|2024-01-01| 200| 210| | S2|2024-01-02| 210| 250| | S2|2024-01-03| 250| NULL| +-----+----------+-----+----------+
Cannot specify window frame for lead function
# lead() does not accept a rowsBetween / rangeBetween frame
w = Window.partitionBy("store").orderBy("date").rowsBetween(0, 1)
df.withColumn("next_sales", F.lead("sales").over(w)).show()
AnalysisException: Cannot specify window frame for lead function.
Why it happens: It can look natural to describe "this row and the next one" with rowsBetween(0, 1), but lead() picks its row by position, using offset, not by a frame. Spark rejects a window with a frame such as rowsBetween(0, 1) when it is used with lead().
Fix: Remove the frame from the window spec and keep partitionBy and orderBy. To read further ahead, raise offset instead.
# Drop the frame: lead() always looks a fixed number of rows ahead
w = Window.partitionBy("store").orderBy("date")
df.withColumn("next_sales", F.lead("sales").over(w)).orderBy("store", "date").show()
+-----+----------+-----+----------+ |store| date|sales|next_sales| +-----+----------+-----+----------+ | S1|2024-01-01| 100| 120| | S1|2024-01-02| 120| 90| | S1|2024-01-03| 90| NULL| | S2|2024-01-01| 200| 210| | S2|2024-01-02| 210| 250| | S2|2024-01-03| 250| NULL| +-----+----------+-----+----------+
Related functions
- lag()
the mirror image of
lead(), with the same arguments, readingoffsetrows back instead of ahead. - datediff()
returns the number of days from a start date to an end date, which turns a
lead()date into a gap in days. - last()
an aggregate that returns the last value in a group, or the last non-null value when
ignorenulls=True, rather than the value a fixed number of rows ahead. - WindowSpec.orderBy()
sets the row order that decides which row counts as "next" for
lead(). - SparkSession.sql()
runs a SQL query, such as one using
LEAD(...) OVER (...), and returns the result as a DataFrame.
Practice problems
Read the full walkthrough in Lead and Lag Window Functions, then practise with these interview questions in the browser.
Frequently asked questions
What is the difference between lead and lag in PySpark?
They point in opposite directions. lead() reads a value from a row after the current row, and lag() reads one from a row before it. Both accept col, offset and default and need .over() with an ordered window. With the default offset of 1, lead() leaves the last row of each partition NULL, while lag() leaves the first row NULL.
Why does lead return null for the last row?
The last row of a partition has no row after it, so lead() falls back to its default, which is None and displays as NULL. With partitionBy, this happens on the last row of every group, as on 2024-01-03 for both stores in the quick answer. Pass a default such as F.lead("sales", 1, 0) to get a value there instead.
Can lead look more than one row ahead?
Yes. The offset argument sets the distance: F.lead("sales", 2) returns the value two rows ahead, as the two_days_later column shows. The last offset rows of each partition have nothing that far ahead, so they get NULL or the default you pass.
How do I get the next non-null value instead of the next row?
lead() returns whatever sits in the row ahead, and if that value is NULL, you get NULL even when you set a default. Spark SQL accepts IGNORE NULLS, as in LEAD(x) IGNORE NULLS OVER (ORDER BY id), which you can run from PySpark with spark.sql(). On PySpark 3.5.5, rows (1, 10), (2, NULL) and (3, 30) gave 30, 30 and NULL: row 1 skipped the NULL in row 2 and returned 30. The syntax is listed in the Spark SQL window functions reference.