calculate percentile of column over window in pyspark

user_1450410 0
Viewed 2140

i have use case where i need to do a calculation of a percentile on a column (let's call it X) over a sliding window. so the window definition is chronological - last 120 days:

days = lambda i: i * 86400
w = Window.partitionBy("entityId").orderBy(F.col("trn_time").cast("long").asc())
    .rangeBetween(-days(120),-days(1))

i thought on using approxQuantile but it is a Dataframe function . second option is using :

percent_rank().over(w)

but i need to sort the window by the numeric column (X) that i want to do the percentile on , and the window is already sorted by time. when i try to add X to the orderBY in the window definition :

w = Window.partitionBy("entityId").orderBy(F.col("trn_time").cast("long").asc(),"X")\
    .rangeBetween(-days(120),-days(1))

i get the following error : "A range window frame with value boundaries cannot be used in a window specification with multiple order by expressions"

how can i implement this logic ?

pyspark apache-spark-sql apache-spark
1 Answers

You need to write it as a inbuilt SQL expression:

# This is like a UDF
magic_percentile = F.expr('percentile_approx(X, 0.5)')
# Define your window
w = Window.partitionBy("entityId").orderBy(F.col("trn_time").cast("long").asc())
    .rangeBetween(-days(120),-days(1))

df = df.withColumn("rolling_percentile", magic_percentile.over(w))

When calculating percentile, you always order the values from smallest to largest and then take the quantile value, so the values within your window will be sorted.

Reference: Median / quantiles within PySpark groupBy

user_88053150
Related