I am trying to compute the cumulative sum per class. Code is working fine by using sum(df.value).over(Window.partitionBy('class').orderBy('time'))
df = sqlContext.createDataFrame( [(1,10,"a"),(3,2,"a"),(1,2,"b"),(2,5,"a"),(2,1,"b"),(9,0,"b"),(4,1,"b"),(7,8,"a"),(3,8,"b"),(2,5,"a"),(0,0,"a"),(4,3,"a")],
["time", "value", "class"] )
time|value|class|
+----+-----+-----+
| 1| 10| a|
| 3| 2| a|
| 1| 2| b|
| 2| 5| a|
| 2| 1| b|
| 9| 0| b|
| 4| 1| b|
| 7| 8| a|
| 3| 8| b|
| 2| 5| a|
| 0| 0| a|
| 4| 3| a|
df.withColumn('cumsum_value', sum(df.value).over(Window.partitionBy('class').orderBy('time'))).show()
time|value|class|cumsum_value|
+----+-----+-----+------------+
| 1| 2| b| 2|
| 2| 1| b| 3|
| 3| 8| b| 11|
| 4| 1| b| 12|
| 9| 0| b| 12|
| 0| 0| a| 0|
| 1| 10| a| 10|
| 2| 5| a| 20|
| 2| 5| a| 20|
| 3| 2| a| 22|
| 4| 3| a| 25|
| 7| 8| a| 33|
+----+-----+-----+------------+
But its not working with duplicate rows. Desired output should be:
time|value|class|cumsum_value|
+----+-----+-----+------------+
| 1| 2| b| 2|
| 2| 1| b| 3|
| 3| 8| b| 11|
| 4| 1| b| 12|
| 9| 0| b| 12|
| 0| 0| a| 0|
| 1| 10| a| 10|
| 2| 5| a| 15|
| 2| 5| a| 20|
| 3| 2| a| 22|
| 4| 3| a| 25|
| 7| 8| a| 33|
+----+-----+-----+------------+
.orderBy('time', 'value')
(i.e. you have to define how to break ties in the case that the times are the same) – pault