calculating delta time between records in dataframe
<p>I have an interesting problem, I am trying to calculate the delta time between records done at different locations.</p>

<pre><code>id x y time
1  x1 y1 10
1  x1 y1 12
1  x2 y2 14
2  x4 y4 8
2  x5 y5 12
</code></pre>

<p>I am trying to get some thing like</p>

<pre><code>id x y time delta
1 x1 y1 10   4
1 x2 y2 14   0
2 x4 y4 8    4
2 x5 y5 12   0
</code></pre>

<p>I have done this type of processing with HiveQL by using custom UDTF but was thinking how can I achieve this with DataFrame in general (may it be in R, Pandas, PySpark). Ideally, I am trying to find a solution for Python pandas and pyspark.</p>

<p>Any hint is appreciated, thank you for your time !</p>

<p>@jezrael thank you for the hints, it was very useful, here is the code </p>

<pre><code>import pandas as pd
df = pd.read_csv("sampleInput.txt", header=None,usecols=[0,1,2,3], names=['id','x','y','time'],sep="\t")
delta = df.groupby(['id','x','y']).first().reset_index()
delta['delta'] = delta.groupby('id')['time'].diff().shift(-1).fillna(0)
</code></pre>

<p>That takes </p>

<pre><code>1   x1  y1  10
1   x1  y1  12
1   x2  y2  14
2   x4  y4  8
2   x5  y5  12
</code></pre>

<p>and gives,</p>

<pre><code>   id   x   y  time  delta
0   1  x1  y1    10      4
1   1  x2  y2    14      0
2   2  x4  y4     8      4
3   2  x5  y5    12      0
</code></pre>

python pandas dataframe spark-dataframe pyspark-sql