Pandas fill column based on date and time data of entire frame

user_17579922 0
Viewed 153

I have a python data frame like:

Timestamp (UTC) App Status Start Time End Time
11/18/2021 17:13:01 App 1 passing 17:13:01
11/18/2021 17:07:28 App 1 failing 17:07:28
11/18/2021 16:31:11 App 1 failing 16:31:11
11/18/2021 16:15:22 App 1 passing 16:15:22
11/18/2021 16:07:51 App 1 failing 16:07:51
11/22/2021 13:56:18 App 2 passing 13:56:18
11/22/2021 03:43:33 App 2 failing 03:43:33
11/22/2021 02:48:06 App 2 failing 02:48:06
11/19/2021 10:30:21 App 3 passing 10:30:21
11/17/2021 13:42:11 App 3 failing 13:42:11

This is a sample of the data, the one I will use will look the same only with more records. I need it to calculate the downtime for each app from the first failing event to the first passing event. If there is multiple passing status I need it to calculate the individual downtime sequences and the total downtime of the application in time format and place these values in different columns.

I am using Pandas for csv manipulation.

So the final Data Frame looks like.

Timestamp (UTC) App Status Start Time End Time Downtime Downtime Minutes
11/18/2021 17:13:01 App 1 passing 17:13:01 41:50 49:21
11/18/2021 17:07:28 App 1 failing 17:07:28 41:50 49:21
11/18/2021 16:31:11 App 1 failing 16:31:11 41:50 49:21
11/18/2021 16:15:22 App 1 passing 16:15:22 07:31 49:21
11/18/2021 16:07:51 App 1 failing 16:07:51 07:31 49:21
11/22/2021 13:56:18 App 2 passing 13:56:18 11:08:12 668.12
11/22/2021 03:43:33 App 2 failing 03:43:33 11:08:12 668.12
11/22/2021 02:48:06 App 2 failing 02:48:06 11:08:12 668.12
11/19/2021 10:30:21 App 3 passing 10:30:21 44:48:10 2688.10
11/17/2021 13:42:11 App 3 failing 13:42:11 44:48:10 2688.10

Any help would be appreciated.

I know the tables are not easy to read but I had to format is as code before Stack Overflow would post it

Here is the code for the sample df


import pandas as pd

data = {'TimeStamp': ['11/18/2021 17:13:01','11/18/2021 17:07:28','11/18/2021 16:31:11','11/18/2021 16:15:22',
              '11/18/2021 16:07:51','11/22/2021 13:56:18','11/22/2021 03:43:33','11/22/2021 02:48:06',
                      '11/19/2021 10:30:21','11/17/2021 13:42:11'],
'App': ['App1','App1','App1','App1','App1','App2','App2','App2','App3','App3'],
'Status': ['Passing','Failing','Failing','Passing','Failing','Passing','Failing','Failing','Passing','Failing']}

df = pd.DataFrame(data)

print(df)
pandas time date python
1 Answers

You could do it like this:

# Setup
df = pd.DataFrame(data).sort_values(
    by=["App", "TimeStamp", "Status"], ignore_index=True
)
df["TimeStamp"] = pd.to_datetime(df["TimeStamp"])

# Calculate difference between rows and deal with first one, convert values
df["Downtime"] = df["TimeStamp"].diff().fillna(method="bfill").dt.total_seconds()

# Iterate to deal with change of sequences
df["group"] = 0
for i in df.index:
    if i == 0:
        df.loc[i, "Downtime"] = 0
        continue
    if df.loc[i - 1, "Status"] == "Passing":
        df.loc[i, "Downtime"] = 0
        df.loc[i:, "group"] += 1

# Add cumulative sums by app
cum_sums = df.groupby(["App"]).sum()
for app in df["App"].unique():
    df.loc[df["App"] == app, "Total Downtime"] = cum_sums.loc[app, "Downtime"]

# Add cumulative sums by group
cum_sums = df.groupby(["group"]).sum()
for group in df["group"].unique():
    df.loc[df["group"] == group, "Downtime"] = cum_sums.loc[group, "Downtime"]

# Cleanup
df = df.drop(columns="group")
df["Downtime"] = df["Downtime"].apply(
    lambda x: f"{int(x // 3600):02}:{int((x % 3600) // 60):02}:{int(x % 60):02}"
)
df["Total Downtime"] = df["Total Downtime"].apply(
    lambda x: f"{int(x // 3600):02}:{int((x % 3600) // 60):02}:{int(x % 60):02}"
)

print(df)
# Outputs
            TimeStamp   App   Status  Downtime Total Downtime
0 2021-11-18 16:07:51  App1  Failing  00:07:31       00:49:21
1 2021-11-18 16:15:22  App1  Passing  00:07:31       00:49:21
2 2021-11-18 16:31:11  App1  Failing  00:41:50       00:49:21
3 2021-11-18 17:07:28  App1  Failing  00:41:50       00:49:21
4 2021-11-18 17:13:01  App1  Passing  00:41:50       00:49:21
5 2021-11-22 02:48:06  App2  Failing  11:08:12       11:08:12
6 2021-11-22 03:43:33  App2  Failing  11:08:12       11:08:12
7 2021-11-22 13:56:18  App2  Passing  11:08:12       11:08:12
8 2021-11-17 13:42:11  App3  Failing  44:48:10       44:48:10
9 2021-11-19 10:30:21  App3  Passing  44:48:10       44:48:10
user_112460560
Related