Create interval of dates for my existing data in R-CodePudding

I am trying to get my existing observations to 10 min intervals in R.

I did this:

data3$date= ceiling_date(as.POSIXct(data3$betdate), unit = "10 minutes")

data3 %>% group_by(date, prov) %>% 
  summarise(cant=n())

But the problem with this code it is that if there is no observation for one interval, the interval will not appear in the output data, which have a lot of sense because there are no observations with the date in that interval. So i need to capture the information about that intervals that does not have observations registred. Any ideas? Already thanks to all of you.

CodePudding user response：

See a simplified example of @Limey's comment, using just months and data.table

# set up fake data
set.seed(1000)
library(lubridate)
# create sequence, and save it as a data.frame so it has a header
months <- seq(ymd("2022-01-01"), ymd("2022-06-01"), by = "month")
# create fake data, and remove some rows
dat <- data.frame(month = months, values = sample(100:200, length(months)))
dat <- dat[-sample(1:length(months),3),]
dat
#       month values
#1 2022-01-01    167
#4 2022-04-01    150
#6 2022-06-01    128

here we perform the merge and see the NAs representing missing observations

library(data.table)
setDT(dat)
months_listed <- data.frame(month = seq(min(dat$month), max(dat$month), by = "month"))
setDT(months_listed)
merge.data.table(months_listed, dat, by = "month", all.x = T)
#        month values
#1: 2022-01-01    167
#2: 2022-02-01     NA
#3: 2022-03-01     NA
#4: 2022-04-01    150
#5: 2022-05-01     NA
#6: 2022-06-01    128