CODEWITHSUNDEEP
pythonpandasdatetimediff
ankur .k
ankur .k

Reputation: 17

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 correct, it should rather be 453 min (+1 day).

Upvotes: 1

Views: 257

Answers (1)

mozway
mozway

Reputation: 260825

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

Upvotes: 2

Related Questions