Time interval between rows in a column, Python

Input

New Time
11:59:57
12:42:10
12:48:45
18:44:53 
18:49:06
21:49:54
21:54:48
5:28:20 

Below I wrote code to create interval in min.

import pandas as pd
import numpy as np

df = pd.read_csv(r"D:\test\test1.csv")

df['Interval in min'] = (pd.to_timedelta(df['New Time'].astype(str)).diff(1).dt.floor('T').dt.total_seconds().div(60))
print(df)

Output

New Time  Interval in min
11:59:57              NaN
12:42:10             42.0
12:48:45              6.0
18:44:53            356.0
18:49:06              4.0
21:49:54            180.0
21:54:48              4.0
5:28:20            -987.0

Last interval in min i.e. -987 min is not right. Please suggest.

>Solution :

Assuming you want to consider a negative difference to be a new day, you could use:

s = pd.to_timedelta(df['New Time']).diff()
df['Interval in min'] = (s
  .add(pd.to_timedelta(s.lt('0').cumsum(), unit='d'))
  .dt.floor('T').dt.total_seconds().div(60)
)

output:

   New Time  Interval in min
0  11:59:57              NaN
1  12:42:10             42.0
2  12:48:45              6.0
3  18:44:53            356.0
4  18:49:06              4.0
5  21:49:54            180.0
6  21:54:48              4.0
7   5:28:20            453.0

Leave a Reply