sorting records with in grouped item in a dataframe
<p>Objective : I am trying to sort rows within a group in DataFrame [in python and pyspark]</p>

<p>This is what I have,</p>

<pre><code>id time 
    1  10 
    1  8  
    1  1
    1  5
    2  1
    2  8
</code></pre>

<p>This is what I am looking for,</p>

<pre><code>id time delta
1  1    7
1  8    2
1  10   0
1  1    4
2  5    3
2  8    0
</code></pre>

<p>There is a solution to this <a href="http://stackoverflow.com/questions/39826720/calculating-delta-time-between-records-in-dataframe">here</a>, but sorting within grouped object was not possible, any hints on this would be helpful, I looking to work with python pandas and also in pyspark.</p>

<p>You can use lag over a window:</p>

<pre><code>from pyspark.sql.functions import lag
from pyspark.sql.window import Window

df = sc.parallelize([
    (1,  10), (1,  8 ), (1,  1), (1,  5), (2,  1), (2,  8),
]).toDF(["id", "time"])

df.withColumn("delta", 
    df["time"] - lag("time", 1).over(Window().partitionBy("id").orderBy("time")))
</code></pre>

<p>where <code>Window().partitionBy("id").orderBy("time")</code> creates a frame with records:</p>

<ul>
<li>Grouped by <code>id</code>.</li>
<li>Sorted by <code>time</code>.</li>
</ul>

<p>and <code>lag("time", 1)</code> emits a previous value of time according to the frame definition.</p>

python pandas dataframe pyspark spark-dataframe