Drop rows based on date condition using python

Question

I have a dataframe with, among others, a date and an ID column. Below is an example frame just for the purpose of this question. But the real data includes many more rows and columns.

from datetime import date, timedelta
import pandas as pd

date = datetime.datetime(2020, 1, 1)
delta_1 = 5
delta_2 = 15
delta_3 = 18

data = {
    'A': [date, date - timedelta(delta_1), date - timedelta(delta_2), date, date - timedelta(delta_3)], 
    'B': ['a', 'a', 'a', 'b', 'b']
}
df = pd.DataFrame(data)
print(df)

           A  B
0 2020-01-01  a
1 2019-12-27  a
2 2019-12-17  a
3 2020-01-01  b
4 2019-12-14  b

What I want to achieve is to, for each unique id (column B in the example), start with the most recent row, and drop rows based on a date condition: if a row with an existing id is inserted within 10 days from the most recent row with that id, it is only the latest row that is valid. So in this example, with 10 days as the limit, I would end up with this result:

           A  B
0 2020-01-01  a
2 2019-12-17  a
3 2020-01-01  b
4 2019-12-14  b

Any idea would be appreciated!

BENY · Accepted Answer · 2020-01-22 14:05:00Z

5

Here is one way , use diff with cumsum , get the day diff sum , then we get the divisor by //

s=df.groupby('B').A.apply(lambda x : x.diff().dt.days.cumsum().fillna(0).abs()//10)
df=df.groupby([df.B,s]).head(1)
           A  B
0 2020-01-01  a
2 2019-12-17  a
3 2020-01-01  b
4 2019-12-14  b

answered Jan 22, 2020 at 14:05

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CHRD Over a year ago

Thank you! This seems to work, but would you mind explaining the logic a bit more in depth? Why we use groupby, fillna(0), head(1) etc.

BENY Over a year ago

@CHRD first we need split the group into sub-group , that is use the first groupby , after that we group by the above and B to get the first item every ten days

Collectives™ on Stack Overflow

Drop rows based on date condition using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related