Spark Dataframe sliding window over pair of rows
<p>I have an eventlog in csv consisting of three columns <code>timestamp</code>, <code>eventId</code> and <code>userId</code>.</p>

<p>What I would like to do is append a new column <code>nextEventId</code> to the dataframe.</p>

<p>An example eventlog:</p>

<pre><code>eventlog = sqlContext.createDataFrame(Array((20160101, 1, 0),(20160102,3,1),(20160201,4,1),(20160202, 2,0))).toDF("timestamp", "eventId", "userId")
eventlog.show(4)

|timestamp|eventId|userId|
+---------+-------+------+
| 20160101|      1|     0|
| 20160102|      3|     1|
| 20160201|      4|     1|
| 20160202|      2|     0|
+---------+-------+------+
</code></pre>

<p>The desired endresult would be:</p>

<pre><code>|timestamp|eventId|userId|nextEventId|
+---------+-------+------+-----------+
| 20160101|      1|     0|          2|
| 20160102|      3|     1|          4|
| 20160201|      4|     1|        Nil|
| 20160202|      2|     0|        Nil|
+---------+-------+------+-----------+
</code></pre>

<p>So far I've been messing around with sliding windows but can't figure out how to compare 2 rows...</p>

<pre><code>val w = Window.partitionBy("userId").orderBy(asc("timestamp")) //should be a sliding window over 2 rows...
val nextNodes = second($"eventId").over(w) //should work if there are only 2 rows
</code></pre>