Various times that are in 24 hours but they become negative time when it jumps to the next day

Question

I am using python to calculate the time difference between start and last times. Each event has a start time and end time. I have found the difference between the columns labelled 'Interval' but they have negative values when the beginning and end time are on different days

For instance the last time 23:57:25 and the difference between the start time is 00:02:05 gives -86120.0

     StartTime  LastTime    Interval
     23:37:24   23:37:24    300.0
     23:42:24   23:42:24    301.0
     23:47:25   23:47:25    300.0
     23:52:25   23:52:25    300.0
     23:57:25   23:57:25    -86120.0
     00:02:05   00:02:05    300.0
     00:07:05   00:07:05    301.0
     00:12:06   00:12:06    300.0
     00:17:06   00:17:06    301.0

Here is what I am using to find the difference between the last time and start time

      DNS['DNS_interval']=(pd.to_datetime(DNS['LastTime'].shift(-1), format='%H:%M:%S')-pd.to_datetime(DNS['StartTime'], format='%H:%M:%S'))//timedelta(seconds=1) 
        NTP['NTP_interval']=(pd.to_datetime(NTP['LastTime'].shift(-1), format='%H:%M:%S')-pd.to_datetime(NTP['StartTime'], format='%H:%M:%S'))//timedelta(seconds=1)

I have tried this to fix the negative

 df['Interval']=df['Interval'].apply(lambda x: x + Timedelta(days=1) if    x < 0 else x)

But I get the error message

NameError: name 'Timedelta' is not defined I have also tried timedelta and imported the rights packages so I dont know why it wont work

If there is a better solution that would also be great

What are DNS and NTP? I guess the data shown is `df`? – Stop harming Monica Aug 30 '19 at 10:20 — Stop harming Monica, Aug 30 '19 at 10:20
Yes they are part of the dataframes – Tina Aug 30 '19 at 11:50 — Tina, Aug 30 '19 at 11:50

score 0 · Answer 1 · answered Aug 30 '19 at 10:12

We can select the subset of the frame where Interval is negative and add one day of seconds (86400).

df.loc[df['Interval'] < 0] += 60*60*24 # 86400

Example:

import pandas as pd

df = pd.DataFrame({
    'Interval': [-1,0,1]
})

df.loc[df['Interval'] < 0] += 100

print(df)

Result:

   Interval
0        99      # Correctly got +100
1         0
2         1

Various times that are in 24 hours but they become negative time when it jumps to the next day

1 Answers1