How to count values between a date time and make a new column out of it

Question

In Python Pandas I need to create a new column in a dataframe (number of encounters in last year) in the below dataframe with the following logic.

I want to count the number of encounteruniqueid that customeruniqueid had in the prior 365 days and write those in a new column called number_of_unique_encouters_in_last_year in the dataframe.

Can you help?

Column 1: encounteruniqueid

Column 2: datetimeofencounter

Column 3: customeruniqueid

Column 4: Numberofencounters_in_last_yr

sample dataframe

Answer 1

Something like this might work:

def occurrences(df, cust_id, date):
    # filter dataframe by customer id and date
    x = df[(df["uniquecustomerid"] == cust_id) &
           (df["datetime"].between((date - pd.offsets.Day(365)), date))]
    # counter the number of unique ecnounters (should all be unique, so no need for nunique)
    unique_in_last_year = x["uniqueencounter"].count()
    # return the count
    return unique_in_last_year

# apply the function to your dataframe
your_df.apply(lambda x: occurrences(your_df, x["uniquecustomerid"], x["datetime"]), axis=1)