How to calculate time difference in between rows using loop in panda python

Question

I have a CSV file with columns date, time. I want to calculate row-by-row the time difference time_diff in the time column. I wrote the following code but it's incorrect. Here is my code and at bottom, my CSV file:

#def time_diff(x):

date_array = []
date_array.append(pd.to_datetime(data['date'][0]).date())
start = []
end = []
temp_date  = pd.to_datetime(data['date'][0]).date()
start.append(pd.to_datetime(data['time'][0], format='%H:%M:%S').time())
for i in range(len(data['date'])):
    cur_date = pd.to_datetime(data['date'][i]).date()
    if( cur_date > temp_date):
        end.append(pd.to_datetime(data['time'][i-1], format='%H:%M:%S').time())
        start.append(pd.to_datetime(data['time'][i], format='%H:%M:%S').time())
        date_array.append(cur_date)
        temp_date = cur_date
end.append(pd.to_datetime(data['time'][len(data['date'])-1], format='%H:%M:%S').time())
if start <= end:
    return end - start
else: 
    end += timedelta(1) # +day
    assert end > start
    return end - start


for i in range(len(date_array)):
    s_time = datetime.datetime.combine(date_array[i],start_time[i])
    e_time = datetime.datetime.combine(date_array[i],  end_time[i])
    timediff = (e_time - s_time)

My CSV file:

First column and second column are date and time. Third column shows expected time_diff in hours.

Answer 1

A little bit messy aproach ( but it works atleast), but running difference wasnt working for me, so here you go:

df = pd.read_csv('test.csv', sep = ";")
df['concant_time'] =  df['date'] + " " +  df['time'] # concat time to take day into account
df['concant_time'] =  pd.to_datetime(df['concant_time'], format = "%m/%d/%Y %H:%M:00") # transfrom string to datetime
df['time_diff'] = 0 #initialize column to assign running time diff.

And the loop to get time difference:

for i in range(df.shape[0] - 1):
    df['time_diff'][i+1] = (datetime.datetime.min +  (df['concant_time'][i+1] - df['concant_time'][i])).time()

# .time() gets the time from datetime object, if you need both days and hours, simply use this line instead:
df['time_diff'][i+1] = df['concant_time'][i+1] - df['concant_time'][i]

the code outputs next:

Hope it helps!

Answer 2

I realize that this has already been answered, but I thought I would propose another solution that takes advantage of vectorization. It should perform better if there are a lot of records by avoiding iteration.

First, I'll create a test data set:

import pandas as pd
import datetime
from tabulate import tabulate
import numpy as np

start_date = datetime.datetime(2018, 1, 1, 00, 0, 0)
end_date = datetime.datetime(2018, 12, 31, 00, 0, 0)

duration = (end_date - start_date).total_seconds()
custom_index = range(0, 20)

duration_df = pd.DataFrame(columns=['Random Duration'], index=custom_index)
duration_df['Random Duration'] = duration

duration_df['Random Duration'] = duration_df['Random Duration'].apply(lambda x: np.random.randint(0, x))
duration_df['Random Date'] = duration_df['Random Duration'].apply(lambda x: start_date + datetime.timedelta(seconds=x))
duration_df = duration_df.drop(columns='Random Duration')
duration_df = duration_df.sort_values('Random Date')
duration_df = duration_df.reset_index(drop=True)

Now we can create the new column with this one liner:

duration_df['Hours Since Previous'] = (duration_df['Random Date'] -
                                       duration_df['Random Date'].shift(1)).astype('timedelta64[h]')

This will give you the following results:

    Random Date            Hours Since Previous
--  -------------------  ----------------------
 0  2018-01-16 21:56:47                     nan
 1  2018-01-24 17:54:12                     187
 2  2018-02-13 19:30:24                     481
 3  2018-02-21 04:18:23                     176
 4  2018-03-05 02:07:50                     285
 5  2018-03-13 20:18:30                     210
 6  2018-04-16 09:05:13                     804
 7  2018-04-17 20:30:46                      35
 8  2018-05-17 02:39:36                     702
 9  2018-05-20 16:43:20                      86

How this works

duration_df['Random Date'].shift(1) creates a new series of dates that are offset by one row.

(duration_df['Random Date'] - duration_df['Random Date'].shift(1)) creates a pandas.Timedelta() object and .astype('timedelta64[h]') converts the resulting Timedelta to hours.

Answer 3

I know this question is a bit old, but I haven't seen any other answers talking about rolling windows, with are perfect for this kind of problem:

from io import StringIO
import pandas as pd 

csvData="""date,time
10/3/18,16:00:00
10/3/18,17:00:00
10/3/18,18:00:00
10/3/18,21:30:00
10/4/18,6:00:00"""

df = pd.read_csv(StringIO(csvData), encoding='utf8', sep=",")
df['datetime'] = pd.to_datetime(df['date'] + '-' + df['time'])
df['time_diff'] = [x.iloc[-1] - x.iloc[0] for x in df['datetime'].rolling(2) if len(x)>0]
                                                                       
print(df)

Output:

      date      time            datetime       time_diff
0  10/3/18  16:00:00 2018-10-03 16:00:00 0 days 00:00:00
1  10/3/18  17:00:00 2018-10-03 17:00:00 0 days 01:00:00
2  10/3/18  18:00:00 2018-10-03 18:00:00 0 days 01:00:00
3  10/3/18  21:30:00 2018-10-03 21:30:00 0 days 03:30:00
4  10/4/18   6:00:00 2018-10-04 06:00:00 0 days 08:30:00

Answer 4

I just want to add here if you want to get cumulative time. 00:00:00 00:00:01 00:00:02 and so on ....

for i in range(df.shape[0] - 1): df['time_diff'][i+1] = (datetime.datetime.min + (df['concant_time'][i+1] - df['concant_time'][0])).time()

just replace i with 0 so that it will take index 0 as reference.