python Pandas how to remove lines from dataframe based on next lines (in a specific timeframe)

user_4842238 0
Viewed 109

I am having events in a dataframe of total of 3 columns - identificator, event status and datetime.

Identificator   Date    Status
ID1 2021-05-02 19:55:43 OK
ID2 2021-05-02 19:48:01 FAILED
ID3 2021-05-02 19:47:53 OK
ID1 2021-05-03 19:55:43 OK
ID2 2021-05-03 20:48:01 OK
ID3 2021-05-03 19:47:53 OK
ID1 2021-05-04 19:55:43 FAILED
ID1 2021-05-04 20:55:43 OK
ID2 2021-05-04 19:48:01 OK
ID3 2021-05-04 19:47:53 OK

I need to delete all lines where the status is failed, but there is another attempt in the next 24 for the same ID.

For example - line 2 should stay, because the next attempt is ~25 hours later. Line 7 should be deleted, because the next attempt for ID1 is only 1 hour later.

Thank you for any help or suggestions.

pandas python
2 Answers

You can do it using pandas.Grouper by grouping the Date and Identificator values within 24 hrs, and then picking last value for each Identificator within a group.

You can also specify a specific time from where to start the 24H groups by using the origin param. By default, it is set to "start_day", which is midnight (00:00:00). So to start the 24H intervals from say 3 PM, you can do it like:

df['Date'] = pd.to_datetime(df['Date'])
df.groupby([pd.Grouper(key='Date', freq='24H', origin='15:00:00'),'Identificator'], as_index=False).agg({'Date':'last', 'Status':'last'})

Now to only remove the rows with 'FAILED' status that later became 'OK' within 24 hrs, you can merge this grouped dataframe obtained above with the original, and then drop the rows where 'FAILED' status row was not present in the grouped dataframe.

So do it all as below:

df['Date'] = pd.to_datetime(df['Date'])
grouped_df = df.groupby([pd.Grouper(key='Date', freq='24H', origin='15:00:00'),'Identificator'], as_index=False).agg({'Date':'last', 'Status':'last'})
df = df.merge(grouped_df, on=['Identificator','Date'], suffixes=('','_grouped'), how="left")
df = df[~((df.Status=='FAILED') & (df.Status_grouped.isna()))].drop('Status_grouped', axis=1)

Output:

>>> df
  Identificator                Date  Status
0           ID1 2021-05-02 19:55:43      OK
1           ID2 2021-05-02 19:48:01  FAILED
2           ID3 2021-05-02 19:47:53      OK
3           ID1 2021-05-03 19:55:43      OK
4           ID2 2021-05-03 20:48:01      OK
5           ID3 2021-05-03 19:47:53      OK
7           ID1 2021-05-04 20:55:43      OK
8           ID2 2021-05-04 19:48:01      OK
9           ID3 2021-05-04 19:47:53      OK
user_93793900

First I copied and paste this data into an excel file, and convert its data to column as follows:

enter image description here

saved and then read the saved excel file with pandas.

import pandas as pd
df = pd.read_excel('excel path')

print(df)

  Identificator                Date  Status
0           ID1 2021-05-02 19:55:43      OK
1           ID2 2021-05-02 19:48:01  FAILED
2           ID3 2021-05-02 19:47:53      OK
3           ID1 2021-05-03 19:55:43      OK
4           ID2 2021-05-03 20:48:01      OK
5           ID3 2021-05-03 19:47:53      OK
6           ID1 2021-05-04 19:55:43  FAILED
7           ID1 2021-05-04 20:55:43      OK
8           ID2 2021-05-04 19:48:01      OK
9           ID3 2021-05-04 19:47:53      OK

following I filter the table by id values and calculations in the filtered table. Find the desired to be deleted row, remove that row from the dataframe that is first read.

unique_ids=df.Identificator.unique()

for id in unique_ids:
  df2=df[df.Identificator==str(id)]
  df2=df2.reset_index()
  for i in df2.index:
    if df2.Status[i]=="OK":
      pass
    if df2.Status[i]=="FAILED":
      print(df2.Date[i+1]-df2.Date[i])
      timediff=(df2.Date[i+1]-df2.Date[i])
      ashour=timediff.total_seconds()/60/60
      if ashour<24:
        df=df[~(df['Date'] == df2.Date[i])]

print(df)

The result is:

  Identificator                Date  Status
0           ID1 2021-05-02 19:55:43      OK
1           ID2 2021-05-02 19:48:01  FAILED
2           ID3 2021-05-02 19:47:53      OK
3           ID1 2021-05-03 19:55:43      OK
4           ID2 2021-05-03 20:48:01      OK
5           ID3 2021-05-03 19:47:53      OK
7           ID1 2021-05-04 20:55:43      OK
8           ID2 2021-05-04 19:48:01      OK
9           ID3 2021-05-04 19:47:53      OK
user_99116710
Related