Filter pandas dataframe, row value must be higher than an offset max value of the column, per customer

Viewed 37

I have a dataframe of orders containing Customer ID, Order ID, Revenue and Order Date Like so:

Customer ID Order ID Revenue Order Date
A 1 10 05-08-2022
B 2 10 04-07-2022
C 3 10 05-02-2022

And so forth. I am trying to copy this dataframe but only keeping each row where a given customers order date, is between the latest order date and three months prior to it. I.e. the condition is variable for each row.

I've tried something like this

df_filtered = df.loc[df['Order Date']>=(df.max(['Date Order']- DateOffset(months=3)))]

But get the error "TypeError: unsupported operand type(s) for -: 'list' and 'DateOffset'"

I've also tried to create a separate dataframe where I've grouped by Customer ID and calculated the date 3 months prior to latest purchase.

Like this:

Customer ID Last_purchase_3M
A 05-05-2022
B 04-04-2022
C 05-12-2021

With the intention to do something like this:

df_filtered = df.loc[df['Order Date']>=df_list['last_purchase_3M'] & df['Customer ID'] == df_list['Customer ID']]

But this gives me this error "TypeError: unsupported operand type(s) for &: 'int' and 'str'".

I clearly don't know what I am doing here (also I'm new to this;))

Am I on the right track or is this completely wrong?

1 Answers

Three is a dtype issue here

df_filtered = df.loc[df['Order Date']>=(df.max(['Date Order']- DateOffset(months=3)))]

Try

df['Order Date'] = pd.to_datetime(df['Order Date'])
df_filtered = df.loc[df['Order Date'].ge(df['Order Date'].max()- DateOffset(months=3))]
Related