Spark Dataframe Window lag function based on multiple columns

Question

val df = sc.parallelize(Seq((201601, 100.5),
  (201602, 120.6),
  (201603, 450.2),
  (201604, 200.7),
  (201605, 121.4))).toDF("date", "volume")

val w = org.apache.spark.sql.expressions.Window.orderBy("date")    
val leadDf = df.withColumn("new_col", lag("volume", 1, 0).over(w))
leadDf.show()

+------+------+-------+
|  date|volume|new_col|
+------+------+-------+
|201601| 100.5|    0.0|
|201602| 120.6|  100.5|
|201603| 450.2|  120.6|
|201604| 200.7|  450.2|
|201605| 121.4|  200.7|
+------+------+-------+

This is working fine.

But if I have one more column as territory like below.

val df = sc.parallelize(Seq((201601, ter1, 10.1),
  (201601, ter2, 10.6),
  (201602, ter1, 10.7),
  (201603, ter3, 10.8),
  (201603, ter4, 10.8),
  (201603, ter3, 10.8),
  (201604, ter4, 10.9))).toDF("date", "territory", "volume")

My requirement is for the same territory, I want to find the volume of previous month(if exists) if not exists just assign a value 0.0

val w = org.apache.spark.sql.expressions.Window.orderBy("date", "territory") val leadDf = df.withColumn("new_col", lag("volume", 1, 0).over(w)) But doesnt work — Ramesh
– Ramesh, Commented Jan 23, 2017 at 16:57
I just included territory in orderBy clause....doesnt give the correct results — Ramesh
– Ramesh, Commented Jan 23, 2017 at 16:57

Assaf Mendelson · Accepted Answer · 2017-01-23 18:50:20Z

1

If I understand correctly you want the value of the previous date for the same territory.

If so then just add partitionBy i.e. redefine your window spec as follows:

val w = org.apache.spark.sql.expressions.Window.partitionBy("territory").orderBy("date")

answered Jan 23, 2017 at 18:50

Assaf Mendelson

13k5 gold badges51 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spark Dataframe Window lag function based on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related