Reputation: 4500
Input DF:
main_id sub_id time
1 . 11 . 12:00
1 . 12 1:00
1 . 12 . 3:00
1 . 12 . 5:00
1 . 13 . 7:00
1 . 13 . 8:00
2 . 21 . 12:00
2 . 21 5:00
I am trying to find the running timestamp difference with respect to the main_id alone
Output DF:
main_id sub_id . time diff
1 . 11 . 12:00 null
1. 12 . 1:00 . 1
1 . 12 . 3:00 . 2
1 . 12 . 5:00 . 2
1 . 13 . 7:00 . 2
1 . 13 . 8:00 . 1
2 . 21 . 12:00 . null
2 . 21 . 5:00 . 5
Code Tried:
val needed_window = Window.partitionBy($"main_id").orderBy($"main_id")
val diff_time = diff($"time").over(partitionWindow)
df.select($"*", diff_time as "time_diff").show
I am getting error in the diff function, is there a way to implement this. Any suggestions please.
Upvotes: 2
Views: 5193
Reputation: 22449
Assuming your time
column is of type Timestamp
, you can calculate the time
difference between the current row and previous row using unix_timestamp
along with the lag Window function.
import java.sql.Timestamp
import org.apache.spark.sql.functions._
import org.apache.spark.sql.expressions.Window
val df = Seq(
(1, 11, Timestamp.valueOf("2018-06-01 12:00:00")),
(1, 12, Timestamp.valueOf("2018-06-01 13:00:00")),
(1, 12, Timestamp.valueOf("2018-06-01 15:00:00")),
(1, 12, Timestamp.valueOf("2018-06-01 17:00:00")),
(1, 13, Timestamp.valueOf("2018-06-01 19:00:00")),
(1, 13, Timestamp.valueOf("2018-06-01 20:00:00")),
(2, 21, Timestamp.valueOf("2018-06-01 12:00:00")),
(2, 21, Timestamp.valueOf("2018-06-01 17:00:00"))
).toDF("main_id", "sub_id", "time")
val window = Window.partitionBy($"main_id").orderBy($"main_id")
df.withColumn("diff",
(unix_timestamp($"time") - unix_timestamp(lag($"time", 1).over(window))) / 3600.0
).show
// +-------+------+-------------------+----+
// |main_id|sub_id| time|diff|
// +-------+------+-------------------+----+
// | 1| 11|2018-06-01 12:00:00|null|
// | 1| 12|2018-06-01 13:00:00| 1.0|
// | 1| 12|2018-06-01 15:00:00| 2.0|
// | 1| 12|2018-06-01 17:00:00| 2.0|
// | 1| 13|2018-06-01 19:00:00| 2.0|
// | 1| 13|2018-06-01 20:00:00| 1.0|
// | 2| 21|2018-06-01 12:00:00|null|
// | 2| 21|2018-06-01 17:00:00| 5.0|
// +-------+------+-------------------+----+
Upvotes: 3