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?