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$
  • $\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!

|improve this answer|||||
$\endgroup$
  • $\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.

|improve this answer|||||
$\endgroup$
  • $\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

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.