PySpark lag() Function

pyspark.sql.functionsWindowSince Spark 1.4.0

PySpark lag() returns the value of a column from a row before the current row, inside a window partition. It is a window function in pyspark.sql.functions, so you always call it with .over(window), and the window must have an orderBy.

The examples below use daily sales for two stores, S1 and S2. Every output on this page comes from a real run on PySpark 3.5.5, and you can paste any example into the PySpark online compiler to run it yourself.

Quick answer

  • F.lag("sales").over(w) returns the previous row's sales value, where "previous" follows the window's orderBy.
  • The window needs orderBy; without it Spark raises an AnalysisException asking you to add an ORDER BY clause.
  • partitionBy restarts the look-back for each group, so the first row of every partition gets NULL (or the default you pass).
  • F.lag("sales", 2) looks two rows back, and F.lag("sales", 1, 0) returns 0 instead of NULL when there is no previous row (if the previous row exists but holds NULL, you still get NULL).
  • Do not add a frame to the window: lag() sets its own one-row frame, and a different frame such as rowsBetween(-1, 0) or rangeBetween(-1, 0) raises "Cannot specify window frame for lag function."
from pyspark.sql import Window, functions as F

lag(col, offset=1, default=None)
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("lag-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("prev_sales", F.lag("sales").over(w)) \
  .orderBy("store", "date") \
  .show()
Output
+-----+----------+-----+----------+
|store|      date|sales|prev_sales|
+-----+----------+-----+----------+
|   S1|2024-01-01|  100|      NULL|
|   S1|2024-01-02|  120|       100|
|   S1|2024-01-03|   90|       120|
|   S2|2024-01-01|  200|      NULL|
|   S2|2024-01-02|  210|       200|
|   S2|2024-01-03|  250|       210|
+-----+----------+-----+----------+

Each row's prev_sales holds the sales value from the previous row of the same store, ordered by date: S1 on 2024-01-02 gets 100, and S1 on 2024-01-03 gets 120. Each store's first day is NULL because nothing comes before it in its partition, and partitionBy("store") keeps S2 from reading S1's last row.

Syntax and parameters

lag(col, offset=1, default=None)
ParameterTypeDefaultDescription
colColumn or strrequired

The column (or column name) whose earlier value you want.

offsetint1

How many rows back to look. 1 is the previous row, 2 is two rows back.

defaultanyNone

The value returned when there is no row offset rows back. None shows as NULL.

Returns: a Column holding the value of col from offset rows before the current row in the window partition, or default when no such row exists.

lag() has been in PySpark since Spark 1.4.0. Import it with from pyspark.sql import Window, functions as F, then build the window with Window.partitionBy(...).orderBy(...). For a wider introduction to window specs, see the PySpark window functions tutorial.

lag() reads the previous row, not the previous calendar date: if a date is missing from the data, the row after the gap reads the last date that exists. If two rows in a partition share the same orderBy value (two rows for the same store on the same date, for example), the ordering does not say which one comes first, so add a tiebreaker column to orderBy to make the result deterministic.

The documentation links on this page point to the Spark 3.5.7 docs (the 3.5 docs online when this page was tested, because the 3.5.5 API pages are no longer hosted); lag() behaves the same across 3.5.x.

lag() with an offset and a default value

Use the offset argument when you need a value from further back, and the default argument when you want a number instead of NULL on the first rows.

df.select(
    "store", "date", "sales",
    F.lag("sales", 1, 0).over(w).alias("prev_or_0"),
    F.lag("sales", 2).over(w).alias("two_days_ago"),
).orderBy("store", "date").show()
Output
+-----+----------+-----+---------+------------+
|store|      date|sales|prev_or_0|two_days_ago|
+-----+----------+-----+---------+------------+
|   S1|2024-01-01|  100|        0|        NULL|
|   S1|2024-01-02|  120|      100|        NULL|
|   S1|2024-01-03|   90|      120|         100|
|   S2|2024-01-01|  200|        0|        NULL|
|   S2|2024-01-02|  210|      200|        NULL|
|   S2|2024-01-03|  250|      210|         200|
+-----+----------+-----+---------+------------+

prev_or_0 shows 0 on 2024-01-01 for both stores because F.lag("sales", 1, 0) replaces the missing previous row with the default 0. two_days_ago is NULL on the first two days of each store, since there is no row two places back, and on 2024-01-03 it picks up the 2024-01-01 value (100 for S1, 200 for S2).

Calculate the change from the previous row

A common use of lag() is a row-over-row difference, such as day-over-day sales: put the previous value next to the current one, then subtract.

result = (
    df.withColumn("prev_sales", F.lag("sales").over(w))
      .withColumn("change", F.col("sales") - F.col("prev_sales"))
      .withColumn(
          "pct_change",
          F.round((F.col("sales") - F.col("prev_sales")) / F.col("prev_sales") * 100, 1),
      )
)
result.orderBy("store", "date").show()
Output
+-----+----------+-----+----------+------+----------+
|store|      date|sales|prev_sales|change|pct_change|
+-----+----------+-----+----------+------+----------+
|   S1|2024-01-01|  100|      NULL|  NULL|      NULL|
|   S1|2024-01-02|  120|       100|    20|      20.0|
|   S1|2024-01-03|   90|       120|   -30|     -25.0|
|   S2|2024-01-01|  200|      NULL|  NULL|      NULL|
|   S2|2024-01-02|  210|       200|    10|       5.0|
|   S2|2024-01-03|  250|       210|    40|      19.0|
+-----+----------+-----+----------+------+----------+

S1 rose by 20 (20.0%) on 2024-01-02 and fell by 30 (-25.0%) on 2024-01-03. S2's last day went from 210 to 250, a change of 40, which rounds to 19.0 percent.

The first day of each store has NULL in change and pct_change because arithmetic with NULL gives NULL. To show 0 instead, wrap change and pct_change in coalesce(..., F.lit(0)). Passing a default of 0 to lag() would not help here: change would become the whole day's sales, and pct_change would divide by zero.

Find rows where a value changed

Compare each row with lag() of the same column to keep only the rows where something switched, such as a user changing subscription plan.

from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("lag-example").getOrCreate()

events = spark.createDataFrame(
    [
        ("u1", "2024-01-01", "free"),
        ("u1", "2024-02-01", "free"),
        ("u1", "2024-03-01", "pro"),
        ("u2", "2024-01-15", "pro"),
        ("u2", "2024-02-15", "team"),
        ("u2", "2024-03-15", "team"),
    ],
    ["user_id", "month", "plan"],
)

w = Window.partitionBy("user_id").orderBy("month")

changes = (
    events.withColumn("prev_plan", F.lag("plan").over(w))
          .filter(F.col("prev_plan").isNotNull() & (F.col("plan") != F.col("prev_plan")))
)
changes.orderBy("user_id", "month").show()
Output
+-------+----------+----+---------+
|user_id|     month|plan|prev_plan|
+-------+----------+----+---------+
|     u1|2024-03-01| pro|     free|
|     u2|2024-02-15|team|      pro|
+-------+----------+----+---------+

Only two of the six rows survive. u1 moved from free to pro on 2024-03-01, and u2 moved from pro to team on 2024-02-15. The rows where the plan stayed the same (u1 on 2024-02-01, u2 on 2024-03-15) fail the != test, and the isNotNull() check drops each user's first row, where prev_plan is NULL. (Those rows would drop anyway, because != against NULL gives NULL, but the explicit check makes the intent clear.)

lag() without partitionBy

When the whole DataFrame is one series, such as a daily total, you can build the window with only orderBy.

from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("lag-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("prev_total", F.lag("total_sales").over(w)).show()
Output
+----------+-----------+----------+
|      date|total_sales|prev_total|
+----------+-----------+----------+
|2024-01-01|        300|      NULL|
|2024-01-02|        330|       300|
|2024-01-03|        340|       330|
|2024-01-04|        310|       340|
+----------+-----------+----------+

Only the first date, 2024-01-01, gets NULL, and every later row reads the row before it: 2024-01-03 gets 330 and 2024-01-04 gets 340. Without partitionBy, 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. That is fine for a small table like this one but slow on large data, so partition by a key whenever one exists.

lag() and lead() side by side

lead() is the mirror image of lag(): it looks forward instead of back. Putting both on the same window gives each row its previous and next value.

df.select(
    "store", "date", "sales",
    F.lag("sales").over(w).alias("prev_day"),
    F.lead("sales").over(w).alias("next_day"),
).orderBy("store", "date").show()
Output
+-----+----------+-----+--------+--------+
|store|      date|sales|prev_day|next_day|
+-----+----------+-----+--------+--------+
|   S1|2024-01-01|  100|    NULL|     120|
|   S1|2024-01-02|  120|     100|      90|
|   S1|2024-01-03|   90|     120|    NULL|
|   S2|2024-01-01|  200|    NULL|     210|
|   S2|2024-01-02|  210|     200|     250|
|   S2|2024-01-03|  250|     210|    NULL|
+-----+----------+-----+--------+--------+

For S1 on 2024-01-02, prev_day is 100 and next_day is 90. The NULLs sit at opposite ends: lag() has nothing before the first day of each store, and lead() has nothing after the last day.

Common errors and fixes

requires window to be ordered, please add ORDER BY clause

Python
Run it
# The window has partitionBy but no orderBy
w = Window.partitionBy("store")
df.withColumn("prev_sales", F.lag("sales").over(w)).show()
Error
AnalysisException: Window function lag(sales, -1, null) requires window to be ordered, please add ORDER BY clause. For example SELECT lag(sales, -1, null)(value_expr) OVER (PARTITION BY window_partition ORDER BY window_ordering) from table.

Why it happens: "Previous row" only has a meaning once the rows have an order. A window built with partitionBy alone groups the rows but does not order them, so Spark refuses to evaluate lag(). This message prints the call as lag(sales, -1, null) while the OVER-clause error below prints lag(sales, 1, NULL); both describe the same one-row look-back.

Fix: Add orderBy to the window, usually on a date or timestamp column.

Python
# Add orderBy to the window
w = Window.partitionBy("store").orderBy("date")
df.withColumn("prev_sales", F.lag("sales").over(w)).orderBy("store", "date").show()
Output
+-----+----------+-----+----------+
|store|      date|sales|prev_sales|
+-----+----------+-----+----------+
|   S1|2024-01-01|  100|      NULL|
|   S1|2024-01-02|  120|       100|
|   S1|2024-01-03|   90|       120|
|   S2|2024-01-01|  200|      NULL|
|   S2|2024-01-02|  210|       200|
|   S2|2024-01-03|  250|       210|
+-----+----------+-----+----------+

WINDOW_FUNCTION_WITHOUT_OVER_CLAUSE: lag requires an OVER clause

Python
Run it
# lag() used without .over(window)
df.withColumn("prev_sales", F.lag("sales")).show()
Error
AnalysisException: [WINDOW_FUNCTION_WITHOUT_OVER_CLAUSE] Window function "lag(sales, 1, NULL)" requires an OVER clause.

Why it happens: F.lag("sales") on its own only describes the lookup. Spark needs .over(window) to know how to group and order the rows before it can find a previous one.

Fix: Call .over() with a window that has both partitionBy (if you need groups) and orderBy.

Python
# Call .over() with an ordered window
w = Window.partitionBy("store").orderBy("date")
df.withColumn("prev_sales", F.lag("sales").over(w)).orderBy("store", "date").show()
Output
+-----+----------+-----+----------+
|store|      date|sales|prev_sales|
+-----+----------+-----+----------+
|   S1|2024-01-01|  100|      NULL|
|   S1|2024-01-02|  120|       100|
|   S1|2024-01-03|   90|       120|
|   S2|2024-01-01|  200|      NULL|
|   S2|2024-01-02|  210|       200|
|   S2|2024-01-03|  250|       210|
+-----+----------+-----+----------+

Cannot specify window frame for lag function

Python
Run it
# lag() does not accept a rowsBetween / rangeBetween frame
w = Window.partitionBy("store").orderBy("date").rowsBetween(-1, 0)
df.withColumn("prev_sales", F.lag("sales").over(w)).show()
Error
AnalysisException: Cannot specify window frame for lag function.

Why it happens: lag() always reads a fixed number of rows back, set by offset, so it brings its own one-row frame. A different frame, such as rowsBetween(-1, 0) here or rangeBetween(-1, 0), is rejected with this error. Frames are for aggregates such as running sums, covered in the rowsBetween tutorial.

Fix: Drop the frame and keep only partitionBy and orderBy. Use offset to control how far back lag() looks.

Python
# Drop the frame: lag() always looks a fixed number of rows back
w = Window.partitionBy("store").orderBy("date")
df.withColumn("prev_sales", F.lag("sales").over(w)).orderBy("store", "date").show()
Output
+-----+----------+-----+----------+
|store|      date|sales|prev_sales|
+-----+----------+-----+----------+
|   S1|2024-01-01|  100|      NULL|
|   S1|2024-01-02|  120|       100|
|   S1|2024-01-03|   90|       120|
|   S2|2024-01-01|  200|      NULL|
|   S2|2024-01-02|  210|       200|
|   S2|2024-01-03|  250|       210|
+-----+----------+-----+----------+
  • lead()

    the forward-looking twin of lag(), returning the value offset rows after the current row.

  • last()

    with ignorenulls=True over a window that ends at the previous row, it returns the most recent non-null value instead of strictly the previous row.

  • coalesce()

    replaces the NULL that lag() returns on the first row, or the NULL that arithmetic produces from it, with a fallback value.

  • row_number()

    another window function that needs an ordered window, numbering rows 1, 2, 3 within each partition.

  • WindowSpec.orderBy()

    sets the row order that decides which row counts as "previous" for lag().

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 lag and lead in PySpark?

lag() returns a value from a row before the current row, and lead() returns a value from a row after it. Both take the same arguments (col, offset, default) and both need .over() with an ordered window. With offset=1, lag() is NULL on the first row of each partition and lead() is NULL on the last.

Why does lag return null for the first row?

The first row of each partition has no row before it, so lag() returns its default, which is None and shows as NULL. Because partitionBy restarts the look-back per group, this happens once per partition, not once per DataFrame. Pass a default such as F.lag("sales", 1, 0) if you want a value instead.

How do I get the previous non-null value instead of the previous row?

lag() returns whatever sits in the previous row, including NULL. To carry forward the last non-null value, use last(col, ignorenulls=True) over a window ordered the same way whose frame ends at the previous row (rowsBetween(Window.unboundedPreceding, -1)). Unlike lag(), last() is an aggregate and does accept a frame.

How do I use lag in Spark SQL?

The SQL form uses an OVER clause, for example LAG(sales) OVER (PARTITION BY store ORDER BY date). You can pass an offset and default as extra arguments, such as LAG(sales, 2, 0). The same rule applies as in the DataFrame API: the window needs ORDER BY. Spark SQL also accepts IGNORE NULLS, as in LAG(x) IGNORE NULLS OVER (ORDER BY id), which skips null values and returns the last non-null one before the current row.