How to convert irregular date time series to regular one in R?-CodePudding

I have a dataframe 'CHARD' that includes a date-time column, followed by a few variables. The date-time column includes an irregular series of dates and times, and I would like to convert it to a continuous, five-minute interval series. So that something like this:

29.08.2021 14:51:03
29.08.2021 14:57:09
29.08.2021 15:03:45

becomes this:

 29.08.2021 14:55:00
 29.08.2021 15:00:00
 29.08.2021 15:05:00

Therefore, I would need to round the times forward in time (14:51 becomes 14:55) and fill in the gaps if there is one five-minute increment missing.

Does anyone have a method for doing this?

Thank you for all your help!

CodePudding user response：

First, convert to dates to POSIXlt (date-time) format, then use seq and lubridate::ceiling_date to create the desired sequence:

library(lubridate)

date <-c("29.08.2021 14:51:03","29.08.2021 14:57:09","29.08.2021 15:03:45")
date <- as.POSIXlt(date, format = "%d.%m.%Y %H:%M:%S")
# [1] "2021-08-29 14:51:03 CEST" "2021-08-29 14:57:09 CEST" "2021-08-29 15:03:45 CEST"

seq(from = ceiling_date(min(date), unit = "5 minutes"), 
    to = ceiling_date(max(date), unit = "5 minutes"), 
    by = "5 min")
# [1] "2021-08-29 14:55:00 CEST" "2021-08-29 15:00:00 CEST" "2021-08-29 15:05:00 CEST"

CodePudding user response：

A pure base R solution:

dates <- c("29.08.2021 14:51:03","29.08.2021 14:57:09","29.08.2021 15:03:45")

# convert to datetime format
dates <- as.POSIXct(dates, format = "%d.%m.%Y %H:%M:%S")

# round up to 5 minutes
dates <- as.POSIXct(300*ceiling(as.integer(dates)/300), origin = "1970-01-01 00:00.00")

# create regular series
seq.POSIXt(from = min(dates), to = max(dates), by = "5 mins")