Hello all,
i've this use case where i would like to group transactions per person into groups based on the difference in dates between transactions. See table below on example dataset:
| Person ID | Transaction ID | Transaction_Item | Transacted Date | Grouping |
|---|
| A | T_1 | 1 | 01/01/2018 | 1 |
| A | T_1 | 2 | 01/01/2018 | 1 |
| A | T_2 | 1 | 31/01/2018 | 1 |
| A | T_2 | 2 | 31/01/2018 | 1 |
| A | T_3 | 1 | 02/03/2018 | - |
| A | T_3 | 1 | 02/03/2018 | - |
| A | T_4 | 1 | 15/10/2018 | 2 |
| A | T_5 | 1 | 17/10/2018 | 2 |
| B | T_6 | 1 | 21/01/2018 | 3 |
| B | T_7 | 1 | 24/01/2018 | 3 |
| B | T_8 | 1 | 27/01/2018 | 3 |
The grouping column is what i wish to achieve. Basically transactions are sorted in ascending transacted date and if they are within 30 days of each other, they will be grouped together. If not they are not grouped together. Currently i'm able to get the number of days between each transaction and used a for loop to achieve that. However i'm wondering if there is a more efficient way of doing this as this method takes too long when dealing with large number of transactions.
Hopefully someone here can give me advice, thanks.
