How to aggregate data within a time window to a specific date in a dataframe-CodePudding

I have a dataset like:

New_ID     application_start_date  is_approved
1234       2022-03-29              1
2345       2022-01-29              1
1234       2021-02-28              0
567        2019-07-03              1
567        2018-09-01              0

And I want to create new attributes N_App_3M which would be sum of is_approved to that application_start_date within 3 month time frame.

Expected output would be:

New_ID     application_start_date  is_approved  N_App_3M
1234       2022-03-29              1            2
2345       2022-01-29              0            0
1234       2022-02-28              1            1
567        2019-07-03              1            1
567        2018-09-01              0            0

CodePudding user response：

Compute the rolling 3-month and 7-day sums and then use pd.merge_asof to generate your columns:

df["application_start_date"] = pd.to_datetime(df["application_start_date"])
df = df.set_index("application_start_date").sort_index()
app_3M = df.resample("M")["is_approved"].sum().rolling(3).sum().rename("N_App_3M").fillna(0)
app_7D = df.rolling("7D")["is_approved"].sum().rename("N_App_7D").fillna(0)

output = pd.merge_asof(df,app_3M,direction="nearest",left_index=True,right_index=True)
output = pd.merge_asof(output,app_7D,direction="nearest",left_index=True,right_index=True)

>>> output
                        New_ID  is_approved  N_App_3M  N_App_7D
application_start_date                                         
2018-09-01                 567            0       0.0       0.0
2019-07-03                 567            1       0.0       1.0
2021-02-28                1234            0       0.0       0.0
2022-01-29                2345            1       1.0       1.0
2022-03-29                1234            1       2.0       1.0