calculating delta time between records in dataframe
Question: I have an interesting problem, I am trying to calculate the delta time between
records done at different locations.

    
    
    id x y time
    1  x1 y1 10
    1  x1 y1 12
    1  x2 y2 14
    2  x4 y4 8
    2  x5 y5 12
    

I am trying to get some thing like

    
    
    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
    

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.

Any hint is appreciated, thank you for your time !


Answer: I think you need [`drop_duplicates`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.DataFrame.drop_duplicates.html) with `groupby`
with [`DataFrameGroupBy.diff`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.core.groupby.DataFrameGroupBy.diff.html),
[`shift`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.core.groupby.Series.shift.html) and
[`fillna`](http://pandas.pydata.org/pandas-
docs/stable/generated/pandas.Series.fillna.html):

    
    
    df1 = df.drop_duplicates(subset=['id','x','y']).copy()
    
    df1['delta'] = df1.groupby(['id'])['time'].diff().shift(-1).fillna(0)
    

Final 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)
    

**Timings** :

    
    
    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
    

