CODEWITHSUNDEEP
pythonpandas
JeffTheKiller
JeffTheKiller

Reputation: 153

Pandas, selecting rows with conditions depend on column date value

I begin with my dataframe:

ID Alarm          Date
1   1    2017-09-06 13:41:36.0
1   2    2017-09-06 15:50:20.0
2   3    2016-12-12 13:30:30.0
2   1    2017-09-03 18:24:12.0
2   2    2017-09-06 12:01:51.0
3   4    2017-08-10 13:11:11.0
3   2    2017-09-06 14:43:33.0

So for each ID it is sorted ascending by Date. Now I would like to select only rows, which have date maximum one week earlier than the date from the last record in each ID (it is always with Alarm = 2). It should be done for each ID separately. The output should look like this:

ID Alarm          Date
1   1    2017-09-06 13:41:36.0
1   2    2017-09-06 15:50:20.0
2   1    2017-09-03 18:24:12.0
2   2    2017-09-06 12:01:51.0
3   2    2017-09-06 14:43:33.0

I tried grouping by ID, but I don't know how to connect Alarms and Dates. What is the simplest way to it, maybe something with external 'time' libraries?

Upvotes: 2

Views: 280

Answers (1)

jezrael
jezrael

Reputation: 862471

You can use groupby with filtering by compare subtracted last day with Timedelta:

print (df.groupby('ID')['Date'].apply(lambda x: x >= x.iat[-1] - pd.Timedelta(7, unit='d')))
0     True
1     True
2    False
3     True
4     True
5    False
6     True
Name: Date, dtype: bool

df = df[df.groupby('ID')['Date'].apply(lambda x: x >= x.iat[-1] - pd.Timedelta(7, unit='d'))]
print (df)
   ID  Alarm                Date
0   1      1 2017-09-06 13:41:36
1   1      2 2017-09-06 15:50:20
3   2      1 2017-09-03 18:24:12
4   2      2 2017-09-06 12:01:51
6   3      2 2017-09-06 14:43:33

Upvotes: 1

Related Questions