Thanks for catching that. I think this is working now. I added the lubridate package to manage the 30-minute intervals properly with the floor_date() function.
```{r}
library(data.table)
library(lubridate)
#---- fake data ----
times <- seq.POSIXt(
from = as.POSIXct("2021-03-12 08:00:00"),
to = as.POSIXct("2021-03-12 12:00:00"),
by = "sec"
)
accounts <- seq(1111, 9999, by = 1000)
amounts <- seq(1, 5000, by = 1)
dt <- data.table(
datetime = sample(times, 1500, replace = TRUE),
account = sample(accounts, 1500, replace = TRUE),
amount = sample(amounts, 1500, replace = TRUE)
)
# order by account and datetime
setkeyv(dt, c("account", "datetime"))
# separate into date and time columns
dt[, `:=`(date = as.IDate(datetime), time = as.ITime(datetime))]
dt[, datetime := NULL]
#---- counts and sums ----
# new time by each half hour
dt[, half_hour := floor_date(as.POSIXct(date) + time, "30 minutes")]
# count and sum by half hour
dt[,`:=`(n = seq_len(.N), cumulative = cumsum(amount)) , by = .(account, half_hour)]