Select rows based on column condition and date time

user_13317119 0
Viewed 62

From below picture, we see that serial C was failed on 3rd January, and A failed on 5th January within 6 days period. I am interested to take samples for 3 days before the failure of each serial number.

enter image description here

My codes:

from pickle import TRUE
import pandas as pd
import numpy as np
import datetime
from datetime import date, timedelta
df = pd.read_csv('https://gist.githubusercontent.com/JishanAhmed2019/e464ca4da5c871428ca9ed9264467aa0/raw/da3921c1953fefbc66dddc3ce238dac53142dba8/failure.csv',sep='\t')

df['date'] = pd.to_datetime(df['date'])
#df.drop(columns=df.columns[0], axis=1, inplace=True)

df = df.sort_values(by="date")
d = datetime.timedelta(days = 3)

df_fail_date = df[df['failure']==1].groupby(['serial_number'])['date'].min()

df_fail_date = df_fail_date - d
df_fail_date

I was not not able to move further to sample my data. I am interested to get the following data, that is 3 days before the failure. Serial C had only 1 day available before failure so I wanna keep that one as well. It would be nice to add duration column to count the days before failure occurred. I appreciate your suggestions. Thanks!

Expected output dataframe: enter image description here

python-datetime pandas dataframe python
2 Answers

You can use a groupby.rolling to get the dates/serials to keep, then merge to select:

df['date'] = pd.to_datetime(df['date'])

N = 3

m = (df.sort_values(by='date')
     .loc[::-1]
     .groupby('serial_number', group_keys=False)
     .rolling(f'{N+1}d', on='date')
     ['failure'].max().eq(1)
     .iloc[::-1]
     )

out = df.merge(m[m], left_on=['serial_number', 'date'],
               right_index=True, how='right')

Output:

         date serial_number  failure_x  smart_5_raw  smart_187_raw  failure_y
2  2014-01-01             C          0            0             80       True
8  2014-01-02             C          0            0            200       True
4  2014-01-03             C          1            0            120       True
7  2014-01-02             A          0            0            180       True
5  2014-01-03             A          0            0            140       True
9  2014-01-04             A          0            0            280       True
14 2014-01-05             A          1            0            400       True
user_163434640

Another possible solution:

N = 4

df['date'] = pd.to_datetime(df['date'])

(df[df.groupby('serial_number')['failure'].transform(sum) == 1]
 .sort_values(by=['serial_number', 'date'])
 .groupby('serial_number')
 .apply(lambda g:
   g.assign(duration=1+np.arange(min(0, min(N, len(g))-len(g)), min(N, len(g)))))
 .loc[lambda x: x['duration'] > 0]
 .reset_index(drop=True))

Output:

        date serial_number  failure  smart_5_raw  smart_187_raw  duration
0 2014-01-02             A        0            0            180         1
1 2014-01-03             A        0            0            140         2
2 2014-01-04             A        0            0            280         3
3 2014-01-05             A        1            0            400         4
4 2014-01-01             C        0            0             80         1
5 2014-01-02             C        0            0            200         2
6 2014-01-03             C        1            0            120         3
user_115644870
Related