How to use unboundedPreceding, unboundedFollowing and currentRow in rowsBetween in PySpark

ONLINECAST

2 min read

·

05-10-2024

9

0

Share

Mastering Window Functions in PySpark: UnboundedPreceding, UnboundedFollowing and currentRow Explained

Window functions in PySpark are incredibly powerful tools for performing calculations across rows in a DataFrame. One of the most common and versatile window functions is rowsBetween, which allows you to define a range of rows to include in your calculation. This article dives deep into understanding how unboundedPreceding, unboundedFollowing, and currentRow work within rowsBetween to unlock its full potential.

The Scenario: Calculating Moving Averages

Imagine you have a DataFrame containing daily stock prices and you want to calculate a 5-day moving average. Here's a simplified example:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, avg, window

spark = SparkSession.builder.appName("WindowFunctions").getOrCreate()
data = [
    ("2023-01-01", 100),
    ("2023-01-02", 105),
    ("2023-01-03", 110),
    ("2023-01-04", 108),
    ("2023-01-05", 112),
    ("2023-01-06", 115),
    ("2023-01-07", 118)
]
df = spark.createDataFrame(data, ["date", "price"])

Now, to calculate the 5-day moving average, we'll use the rowsBetween function with unboundedPreceding, unboundedFollowing, and currentRow.

windowSpec = Window.partitionBy("date").orderBy("date").rowsBetween(-2, 2) # 5-day window
df_moving_avg = df.withColumn("moving_avg", avg(col("price")).over(windowSpec))
df_moving_avg.show()

Decoding the rowsBetween Parameters:

• unboundedPreceding: This parameter specifies that the window should start from the first row of the partition (in our case, the earliest date).
• unboundedFollowing: This parameter signifies that the window should continue until the last row of the partition (the latest date).
• currentRow: This parameter represents the current row being processed, serving as the center of the window.

In our example, rowsBetween(-2, 2) defines a 5-day window around each date. Here's a breakdown:

1. -2: This indicates we include the two rows preceding the current row (in this case, 2023-01-01 and 2023-01-02 for 2023-01-03).
2. 2: This means we include the two rows following the current row (2023-01-05 and 2023-01-06 for 2023-01-03).
3. currentRow: Finally, the current row (2023-01-03) itself is also included in the window.

Beyond Moving Averages:

The power of rowsBetween goes beyond calculating moving averages. You can use it for various scenarios like:

• Lagged Features: Calculate the price difference between the current day and the previous day (using rowsBetween(-1, -1)).
• Trend Analysis: Identify increasing or decreasing trends by comparing the current value with the previous few values (using rowsBetween(-2, -1) or rowsBetween(-3, -1)).
• Rank Calculations: Find the rank of a value within a specific window of rows (using rowsBetween with appropriate starting and ending points).

Key Points to Remember:

• The rowsBetween function works in conjunction with other window functions like avg, sum, min, max, etc.
• You can specify different window sizes and starting/ending points to achieve the desired results.
• Partitioning the DataFrame based on specific columns ensures that window calculations are performed within the correct groups.

Conclusion:

Mastering unboundedPreceding, unboundedFollowing, and currentRow in rowsBetween unlocks a world of possibilities within PySpark. By leveraging these concepts, you can perform complex calculations on your data, derive insightful trends, and gain a deeper understanding of your dataset. Start experimenting with different combinations of these parameters to unlock the full power of window functions in your PySpark workflows.