scala - Apply functions on each group in Data Frame

Question

I'm new to Stack Overflow and new to programming. I am learning Scala. I have the following data frame:

id	From	To
James	2021-01-09	2021-01-15
James	2021-01-14	2198-01-01
James	2021-01-22	null
Sara	2021-01-16	2198-01-01
Sara	2021-02-23	null

question from:https://stackoverflow.com/questions/65925020/apply-functions-on-each-group-in-data-frame

Answer 1

You can use when to mask the irrelevant values, and get the max From date where To is null:

import org.apache.spark.sql.expressions.Window

val df2 = df.withColumn(
    "To",
    when(
        $"to" === lit("2198-01-01"),
        max(when($"To".isNull, $"From")).over(Window.partitionBy("id"))
    ).otherwise($"to")
)

df2.show
+-----+----------+----------+
|   id|      From|        To|
+-----+----------+----------+
|James|2021-01-09|2021-01-15|
|James|2021-01-14|2021-01-22|
|James|2021-01-22|      null|
| Sara|2021-01-16|2021-02-23|
| Sara|2021-02-23|      null|
+-----+----------+----------+