R: How to create a Date object using intervals?

Question

I am trying to create a Date object with specific features. My original Data set the following:

Year Month Day FivMin   A   B   C   D
2000   1    1    1      1   2   3   4
2000   1    1    2      2   3   0   1
2000   1    1    3      3   4   1   2
2000   1    1    4      0   1   2   3
2000   1    2    1      1   2   3   4
2000   1    2    2      5   3   4   1
2000   1    2    3      3   0   1   2
2000   1    2    4      4   1   9   3
2000   1    3    1      1   2   3   4
2000   1    3    2      0   1   7   1
2000   1    3    3      3   4   1   2
2000   1    3    4      1   -2   2   3
2000   1    4    1      0   2   3   4
2000   1    4    2      2   1   4   1
2000   1    4    3      3   0   1   2
2000   1    4    4      0   2   2   3
2000   1    5    1      1   2   3   4
2000   1    5    2      2   3   4   1
2000   1    5    3      0   -1   1   2
2000   1    5    4      9   1   2   3

The variable FiveMin represents a time interval, it means that 1 is equal to 00:05, 2 is equal to 00:10, and so on. My first idea was to use

Date <- df%>%mutate(date = str_c(year,"/",month,"/",day,"/",fivemin),
         date = ymd_hm(date)%>%
         select(-c(1:8)))

But the result is the following

2020-09-07 01:01:00
2020-09-07 01:02:00
2020-09-07 01:02:00
2020-09-07 01:02:00
NA
NA
NA
NA
2020-09-07 04:01:00
2020-09-07 04:02:00
2020-09-07 04:02:00
2020-09-07 04:02:00
NA
NA
NA
NA

Is there a possibility to use a start for setting the initial hour and the time interval.

Thanks for your help.

Answer 1

Using base R, we can paste the components required to get date and time and then use as.POSIXct to convert it.

df$DateTime <- with(df, as.POSIXct(paste(Year, Month, Day, FivMin * 5), 
                        format = "%Y %m %d %M", tz = "UTC"))
df$DateTime

#[1] "2000-01-01 00:05:00 UTC" "2000-01-01 00:10:00 UTC"
#[3] "2000-01-01 00:15:00 UTC" "2000-01-01 00:20:00 UTC"
#[5] "2000-01-02 00:05:00 UTC" "2000-01-02 00:10:00 UTC"
#[7] "2000-01-02 00:15:00 UTC" "2000-01-02 00:20:00 UTC"
#....

data

df <- structure(list(Year = c(2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 2000L, 
2000L, 2000L, 2000L, 2000L, 2000L), Month = c(1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L
), Day = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 4L, 
4L, 4L, 4L, 5L, 5L, 5L, 5L), FivMin = c(1L, 2L, 3L, 4L, 1L, 2L, 
3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L), A = c(1L, 
2L, 3L, 0L, 1L, 5L, 3L, 4L, 1L, 0L, 3L, 1L, 0L, 2L, 3L, 0L, 1L, 
2L, 0L, 9L), B = c(2L, 3L, 4L, 1L, 2L, 3L, 0L, 1L, 2L, 1L, 4L, 
-2L, 2L, 1L, 0L, 2L, 2L, 3L, -1L, 1L), C = c(3L, 0L, 1L, 2L, 
3L, 4L, 1L, 9L, 3L, 7L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L
), D = c(4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 
1L, 2L, 3L, 4L, 1L, 2L, 3L)), class = "data.frame", row.names = c(NA, -20L))

Answer 2

library(dplyr)
library(lubridate)

df %>%
    mutate(Hour = FivMin * 5 %/% 60
       , Minute = FivMin * 5 %% 60) %>%
    mutate(datetime = paste0(Year, "/", Month, "/", Day, "/", Hour, "/", Minute)) %>%
    mutate(datetime = ymd_hm(datetime))

Output:

   Year Month Day   FivMin     A     B     C     D Hour  Minute datetime           
  <dbl> <chr> <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <chr>  <dttm>             
1  2000 01    01         1     1     2     3     4 00    05     2000-01-01 00:05:00
2  2000 01    01         2     2     3     0     1 00    10     2000-01-01 00:10:00
3  2000 01    01         3     3     4     1     2 00    15     2000-01-01 00:15:00
4  2000 01    01         4     0     1     2     3 00    20     2000-01-01 00:20:00
5  2000 01    02         1     1     2     3     4 00    05     2000-01-02 00:05:00
6  2000 01    02         2     5     3     4     1 00    10     2000-01-02 00:10:00