0
$\begingroup$

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:

enter image description here

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

$\endgroup$
1
  • $\begingroup$ Why was your code not working? How specifically was the output wrong? $\endgroup$
    – smci
    Nov 16 '19 at 2:13
1
$\begingroup$

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:

enter image description here

Hope it helps!

$\endgroup$
3
  • $\begingroup$ I guess your code will work for my code. Thank you very much for your response an I will try your method. $\endgroup$
    – awa
    Dec 5 '18 at 17:22
  • $\begingroup$ Your code seems work for me. But here in your code df = pd.read_csv('test.csv', sep = ";") gave me an error. So then I delete the sep =";" Then it worked properly. Thank you very much $\endgroup$
    – awa
    Dec 6 '18 at 4:32
  • $\begingroup$ I have another problem, I want to convert that time_diff into minutes. Then How I can do that by using this code? $\endgroup$
    – awa
    Dec 6 '18 at 4:50
6
$\begingroup$

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.

$\endgroup$
8
  • $\begingroup$ Your code is very advantage. But there is a problem for me. Because here I attached the subset of my csv. I can't specify exactly end date. Because this is continuously going to the next day ,next to next day. Then I can't specify this is the end day. Then wht should I have to do? $\endgroup$
    – awa
    Dec 6 '18 at 4:08
  • $\begingroup$ I’m not sure what you’re saying. Does the .csv file get appended to periodically? If that is the case, you can rerun this code when new data is appended. Be sure to sort the records on the date / time before applying the code. If that’s not what you mean, maybe you could explain a little more. $\endgroup$
    – Skiddles
    Dec 6 '18 at 4:14
  • $\begingroup$ yes csv file get appended to periodically. So can't I write that start time is depend on csv file first row and end time is [i-1](for i in range (len(data['time)) . This code can I wite using loop inside in the class.(def time_diff()) $\endgroup$
    – awa
    Dec 6 '18 at 4:27
  • $\begingroup$ TBH, I’d be surprised if you could produce a different result. What do you expect the first row to contain for your time difference? Depending on how few rows are added at a time, the loop may be functionally about as fast. You could possibly modify the code if you don’t want to overwrite existing values. Perhaps it’s clearer to you because you know how you plan to use the data. Best of luck. $\endgroup$
    – Skiddles
    Dec 6 '18 at 4:53
  • $\begingroup$ @Skiddles.Thank you for the response. I just posted the subset of my csv file. But start time is same as I mentioned. But end time I can't decide yet. That is the problem $\endgroup$
    – awa
    Dec 6 '18 at 4:56
0
$\begingroup$

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
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.