Spark Dataframe/ Dataset: Generic Conditional cumulative sum

Question

I have a dataframe which has a few attributes (C1 to C2), an offset (in days) and a few values (V1, V2).

val inputDF= spark.sparkContext.parallelize(Seq((1,2,30, 100, -1),(1,2,30, 100, 0), (1,2,30, 100, 1),(11,21,30, 100, -1),(11,21,30, 100, 0), (11,21,30, 100, 1)), 10).toDF("c1", "c2", "v1", "v2", "offset")
inputDF: org.apache.spark.sql.DataFrame = [c1: int, c2: int ... 3 more fields]

scala> inputDF.show
+---+---+---+---+------+
| c1| c2| v1| v2|offset|
+---+---+---+---+------+
|  1|  2| 30|100|    -1|
|  1|  2| 30|100|     0|
|  1|  2| 30|100|     1|
| 11| 21| 30|100|    -1|
| 11| 21| 30|100|     0|
| 11| 21| 30|100|     1|
+---+---+---+---+------+

What I need to do is, calculate the cumulative sum for V1, V2 for (c1,c2) across offset.

I tried this but that's far away from a generic solution that could work on any data frame.

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

val groupKey = List("c1", "c2").map(x => col(x.trim))
val orderByKey = List("offset").map(x => col(x.trim))

val w = Window.partitionBy(groupKey: _*).orderBy(orderByKey: _*)

val outputDF = inputDF
  .withColumn("cumulative_v1", sum(inputDF("v1")).over(w))
  .withColumn("cumulative_v2", sum(inputDF("v2")).over(w))

+---+---+---+---+------+----------------------------
| c1| c2| v1| v2|offset|cumulative_v1| cumulative_v2|
+---+---+---+---+------+-------------|--------------|
|  1|  2| 30|100|    -1|30           | 100          |
|  1|  2| 30|100|     0|60           | 200          |
|  1|  2| 30|100|     1|90           | 300          |
| 11| 21| 30|100|    -1|30           | 100          |
| 11| 21| 30|100|     0|60           | 200          |
| 11| 21| 30|100|     1|90           | 300          |
+---+---+---+---+------+-----------------------------

The challenge is [a] I need to do this across multiple and varying offset windows (-1 to 1), (-10 to 10), (-30 to 30) or any others [b] I need to use this function across multiple dataframes/ datasets, so I'm hoping for a generic function that could either work in RDD/ Dataset.

Any thoughts on how I could achieve this in Spark 2.0?

Help is much appreciated. Thanks!

Welcome to Stack Overflow! We are a question-and-answer site, not a coders-for-hire service. Please explain what you have tried so far and why it hasn't worked. See: Why is "Can someone help me?" not an actual question? — Joe C
Thanks. I arrived at the above resultset with my solution. Adding it now. — Yash

Yash Yash · Accepted Answer · 2017-02-20T08:40:03

Here's a primitive take using just data frames.

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

val groupKey = List("c1", "c2").map(x => col(x.trim))
val orderByKey = List("offset").map(x => col(x.trim))

val w = Window.partitionBy(groupKey: _*).orderBy(orderByKey: _*)

val inputDF= spark
  .sparkContext
  .parallelize(Seq((1,2,30, 100, -1),(1,2,3, 100, -2),(1,2,140, 100, 2),(1,2,30, 100, 0), (1,2,30, 100, 1),(11,21,30, 100, -1),(11,21,30, 100, 0), (11,21,30, 100, 1)), 10)
  .toDF("c1", "c2", "v1", "v2", "offset")

val outputDF = inputDF
  .withColumn("cumulative_v1", sum(when($"offset".between(-1, 1), inputDF("v1")).otherwise(0)).over(w))
  .withColumn("cumulative_v3", sum(when($"offset".between(-2, 2), inputDF("v1")).otherwise(0)).over(w))
  .withColumn("cumulative_v2", sum(inputDF("v2")).over(w))

This produces a cumulative sum over a single 'value' for different windows.

scala> outputDF.show
+---+---+---+---+------+-------------+-------------+-------------+              
| c1| c2| v1| v2|offset|cumulative_v1|cumulative_v3|cumulative_v2|
+---+---+---+---+------+-------------+-------------+-------------+
|  1|  2|  3|100|    -2|            0|            0|          100|
|  1|  2| 30|100|    -1|           30|           30|          200|
|  1|  2| 30|100|     0|           60|           60|          300|
|  1|  2| 30|100|     1|           90|           90|          400|
|  1|  2|140|100|     2|           90|           90|          500|
| 11| 21| 30|100|    -1|           30|           30|          100|
| 11| 21| 30|100|     0|           60|           60|          200|
| 11| 21| 30|100|     1|           90|           90|          300|
+---+---+---+---+------+-------------+-------------+-------------+

A couple of drawbacks of this approach - [1] for each conditional window (-1,1), (-2,2) or any (from_offset, to_offset), sum() needs to be called separately. [2] this isn't a generic function.

I know spark accepts a variable list of columns for aggregate functions like this -

val exprs = Map("v1" -> "sum", "v2" -> "sum")

But I'm unsure of how to extend this for window functions with variable conditions. I'm still very curious to know if there is a better and modular/ reusable function that we can write to solve this.

Spark Dataframe/ Dataset: Generic Conditional cumulative sum

2 Answers