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>I think you need <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop_duplicates.html" rel="nofollow"><code>drop_duplicates</code></a> with <code>groupby</code> with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.diff.html" rel="nofollow"><code>DataFrameGroupBy.diff</code></a>, <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.core.groupby.Series.shift.html" rel="nofollow"><code>shift</code></a> and <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.fillna.html" rel="nofollow"><code>fillna</code></a>:</p>

<pre><code>df1 = df.drop_duplicates(subset=['id','x','y']).copy()

df1['delta'] = df1.groupby(['id'])['time'].diff().shift(-1).fillna(0)
</code></pre>

<p>Final 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><strong>Timings</strong>:</p>

<pre><code>In [111]: %timeit df.groupby(['id','x','y']).first().reset_index()
100 loops, best of 3: 2.42 ms per loop

In [112]: %timeit df.drop_duplicates(subset=['id','x','y']).copy()
1000 loops, best of 3: 658 Âµs per loop
</code></pre>

python pandas dataframe spark-dataframe pyspark-sql