Drop overlapping periods less than 6 months in pandas dataframe

user_11641908 0
Viewed 117

I have the following Pandas dataframe and I want to drop the rows for each customer where the difference between Dates is less than 6 month per customer. For example, I want to keep the following dates for customer with ID 1 - 2017-07-01, 2018-01-01, 2018-08-01

Customer_ID    Date
1          2017-07-01
1          2017-08-01
1          2017-09-01
1          2017-10-01
1          2017-11-01
1          2017-12-01
1          2018-01-01
1          2018-02-01
1          2018-03-01
1          2018-04-01
1          2018-06-01
1          2018-08-01
2          2018-11-01     
2          2019-02-01
2          2019-03-01
2          2019-05-01
2          2020-02-01
2          2020-05-01
pandas dataframe python
1 Answers

Define the following function to process each group of rows (for each customer):

def selDates(grp):
    res = []
    while grp.size > 0:
        stRow = grp.iloc[0]
        res.append(stRow)
        grp = grp[grp.Date >= stRow.Date + pd.DateOffset(months=6)]
    return pd.DataFrame(res)

Then apply this function to each group:

result = df.groupby('Customer_ID', group_keys=False).apply(selDates)

The result, for your data sample, is:

    Customer_ID       Date
0             1 2017-07-01
6             1 2018-01-01
11            1 2018-08-01
12            2 2018-11-01
15            2 2019-05-01
16            2 2020-02-01
user_73884770
Related