How do I calculate time difference of rows passed on column value

user_17579922 0
Viewed 72

I have a pandas dataframe like

Status Time Stamp
Passing 2021-11-25 15:15:36
Failing 2021-11-25 00:46:23
Failing 2021-11-25 00:16:03
Failing 2021-11-24 23:45:08
Passing 2021-11-25 15:15:13
Failing 2021-11-25 00:46:47
Failing 2021-11-25 00:16:09
Failing 2021-11-24 23:44:59

I need to get the time of the first passing event to the first instance of when it failed for that sequence. So the difference row 0 and row 3, and add it to a new column.

Then I need it to calculate the next sequence and add it to the value in the new column.

So the difference between row 4 and row 7 and add the difference to the previous time so I get the total time it was failing.

This is what the df should look like at the end

Status Time Stamp Downtime Total Downtime
Passing 2021-11-25 15:15:36 15:30:38 31:00:52
Failing 2021-11-25 00:46:23 15:30:38 31:00:52
Failing 2021-11-25 00:16:03 15:30:38 31:00:52
Failing 2021-11-24 23:45:08 15:30:38 31:00:52
Passing 2021-11-25 15:15:13 15:30:14 31:00:52
Failing 2021-11-25 00:46:47 15:30:14 31:00:52
Failing 2021-11-25 00:16:09 15:30:14 31:00:52
Failing 2021-11-24 23:44:59 15:30:14 31:00:52

Note that this is example data and the index's of passing and failing events are at different index each time.

Here is my code

import pandas as pd

data = {'Status': ['Passing','Failing','Failing','Failing','Passing','Failing','Failing','Failing'],

'TimeStamp': ['2021-11-25 15:15:36','2021-11-25 00:46:23','2021-11-25 00:16:03','2021-11-24 23:45:08','2021-11-25 15:15:13','2021-11-25 00:46:47','2021-11-25 00:16:09','2021-11-24 23:44:59']}

df = pd.DataFrame(data)

I'm self taught in Python and pandas and have no idea how to achieve what I need. Any help would be appreciated.

pandas dataframe timestamp python
1 Answers

below, you build up the column "Downtime":

from datetime import datetime as dt,timedelta as td

df.loc[:,'Downtime'] = dt.now()
prevPassIdx = 0
prevOldest = dt.now()
timestamps = []

for i in range(1, len(df['TimeStamp'])):
    if df['Status'][i] == 'Passing':
        if i!=0:
            timestamps.append(dt.strptime(df.iloc[prevPassIdx,1],"%Y-%m-%d %H:%M:%S")- prevOldest)
            df.iloc[prevPassIdx:i,2]=timestamps[-1]
        prevPassIdx = i
        prevOldest = dt.now()
    else:
        if dt.strptime(df['TimeStamp'][i],"%Y-%m-%d %H:%M:%S") <prevOldest:
            prevOldest = dt.strptime(df['TimeStamp'][i],"%Y-%m-%d %H:%M:%S")
if df['Status'][i] != ('Passing'):
    timestamps.append(dt.strptime(df.iloc[prevPassIdx,1],"%Y-%m-%d %H:%M:%S") - prevOldest)
    df.iloc[prevPassIdx:i+1,2]= timestamps[-1]

below, you build up the column "Total Downtime":

delta = td()
for t in timestamps:
    delta = delta+ t
seconds = delta.total_seconds()
hours = seconds//3600
minutes = (seconds//60)%60
seconds = seconds %60
df.loc[:, 'Total Downtime'] = '{:02d}:{:02d}:{:02d}'.format(int(hours),int(minutes),int(seconds))
user_142711450
Related