Ultimately, I want to aggregate the amount column in my data over modular groups that do not yet exist but that are the composite of two existing groups and a time difference dimension.

I have a dataset that is structured like this:

data <- tibble( date = as.POSIXct(c('2023-07-01 12:00:00', '2023-07-01 13:00:00', '2023-07-02 12:00:00', '2023-07-03 14:00:00', '2023-07-03 16:00:00', '2023-07-04 12:00:00', '2023-07-04 14:00:00','2023-07-05 10:00:00', '2023-07-05 12:00:00')),
transaction_id = c(1, 1, 2, 3, 3, 4, 4, 5, 5),
seller_ID = c(201, 202, 201, 201, 204, 205, 206, 201, 207),
amount = c(100, 150, 200, 300, 350, 400, 450, 500, 550)
)

I want to add to this a time difference, which is the duration between the occurrence of a seller_ID and its previous occurrence.

data <- data %>%
  group_by(seller_ID) %>%
  arrange(seller_ID, date) %>%
  mutate(time_diff_hours = as.numeric(difftime(date, lag(date, default = first(date)), units = "hours"))) %>%
  ungroup()

Conceptually, there are a few next steps (and this is where I am struggling):

First, I want to group together all transactionIDs regardless of seller_IDs (so all transaction_ID 1 rows should be aggregated together). BUT, I also want to include in this aggregation repeated sellerIDs if those sellerIDs appear within 24 hours (inclusive) of the previous (lag) appearance of that sellerID. So in the example data, all transactionID 1s get aggregated together by transactionID, but tranactionID2 which includes sellerID 201 is also included in the aggregation because it happened within 24s of the first appearance of sellerID 201, who was a part to transactionID1. In other words, simply aggregating by transactionID would not be sufficient.

However, I also don’t want to include sellerIDs in an aggregation if they happen too far from the previous appearance. For example, the two other instances of sellerID 201 (transactionIDs 3 and 5) are not counted with transaction ID 1 because they happen too long (>24 hours) afterward the previous appearance of sellerID 201 and are not lumped together either for the same reason.

In short, I have groups, transactionID and sellerID, but I want to sometimes combine transactionIDs together based on the temporal relationship between sellerIDs.

The ideal groups would look like this:

data_with_ideal_groups <- tibble(
  date = as.POSIXct(c('2023-07-01 12:00:00', '2023-07-01 13:00:00', '2023-07-02 12:00:00',  
                      '2023-07-03 14:00:00', '2023-07-03 16:00:00', '2023-07-04 12:00:00', '2023-07-04 14:00:00', 
                      '2023-07-05 10:00:00', '2023-07-05 12:00:00')),
  transaction_id = c(1, 1, 2, 3, 3, 4, 4, 5, 5),
  seller_ID = c(201, 202, 201, 201, 204, 205, 206, 201, 207),
  amount = c(100, 150, 200, 300, 350, 400, 450, 500, 550),
  ideal_groups = c(1,1,2,3,1,2,4,4,5)
)

Help would be very much appreciated.

G