I have a pandas Dataframe with an ID column and a date column (YYYY-MM-DD),

There may be gaps in the date field, as shown. I would like to have a new column, "occurrences_last_month" where it counts the number of occurrences for each ID in the last month (30 days).

The idea would be to add a new column with the following output,

For example, in the case of ID 001:

• January 1st: substracts 1 month to December 2nd, so 0 occurrences
• January 2nd: goes from December 3rd to January 1st, so 1 occurrence
• February 7th: goes from January 8th, so 0 occurrences

I tried to use the datetime.timedelta to compute a new column "date_previous_month", but I am unable to compute what I need from there, I tried using count() but I failed to obtain what I need.

CodePudding user response：

First idea is per grouops use Rolling.count with remove first level created by ID:

df = df.set_index('Date')
df['Ocurrences_last_month'] = (df.groupby('ID')
                                 .rolling('30D')
                                 .count().sub(1).droplevel(0).astype(int))
print (df)
            ID  Ocurrences_last_month
Date                                 
2022-01-01   1                      0
2022-01-04   1                      1
2022-02-07   1                      0
2022-01-02   2                      0
2022-01-03   2                      1
2022-01-28   2                      2

EDIT: If possible duplciated values create Series and assign to original DataFrame by DataFrame.join:

s = df.groupby('ID').rolling('30D', on='Date')['Date'].count().sub(1).astype(int)

df = df.join(s.rename('Ocurrences_last_month'), on=['ID','Date'])
print (df)
   ID       Date  Ocurrences_last_month
0   1 2022-01-01                      0
1   1 2022-01-04                      1
2   1 2022-02-07                      0
3   2 2022-01-02                      0
4   2 2022-01-03                      1
5   2 2022-01-28                      2