Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to find the time difference between first row and the row when the condition is met?

Tags:

python

pandas

I need to find the time difference (in minutes) between the beginning and the moment when col1 exceeds the value 20.

For the data below, the answer should be 72 minutes (from 20:00:19 till 21:12:00).

df:

date_time            col1
2018-03-04 20:00:19  9
2018-03-04 21:10:00  13
2018-03-04 21:12:00  21
2018-03-04 21:15:00  25

How can I do it? This is my current snippet:

df.index = pd.to_datetime(df['date_time'])
start = df.index[0]
row_id = df.index[df['col1'] > 20]
time_val = start - df.index[row_id]
like image 432
Tatik Avatar asked Dec 22 '22 23:12

Tatik


2 Answers

One liner:

ans = pd.to_datetime(df.groupby(df.col1>20).first().date_time).diff().dt.total_seconds()/60

output:

ans[True]:

71.68333333333334
like image 101
Yuca Avatar answered Dec 25 '22 11:12

Yuca


Assuming 'date_time' is dtype datetime. We can use diff to get Timedelta and cumsum to get cumulative Timedelta. Then we can use idxmax on df.col1.gt(20)

df.date_time.diff().fillna(0).cumsum()[df.col1.gt(20).idxmax()]

Timedelta('0 days 01:11:41')

Timedelta has a total_seconds method that you can divide by 60

df.date_time.diff().fillna(0).cumsum()[df.col1.gt(20).idxmax()].total_seconds() / 60

71.68333333333334

Or you can divide by another Timedelta

df.date_time.diff().fillna(0).cumsum()[df.col1.gt(20).idxmax()] / pd.Timedelta(1, unit='m')

71.68333333333334
like image 25
piRSquared Avatar answered Dec 25 '22 11:12

piRSquared