Convert a datetime with zeroes for the time and a separate time column into datetime in R-CodePudding

There's a table with the following form:

ID | Date                          | Time
A    2021-07-26T00:00:00.000 0000   1:00AM
b    2021-08-13T00:00:00.000 0000   10:00PM

data.frame(ID = c("A", "B"), date = c("2021-07-26T00:00:00.000 0000", " 2021-08-13T00:00:00.000 0000"), time = c("1:00AM", "10:00PM"))

Like the dataframe above except Date is a datetime format. I would like to add the time to datetime given a vector of datetimes where all the time part is currently 0.

Expected output:

ID | new_date_time                          
A    2021-07-26 01:00:00  
b    2021-08-13 22:00:00

CodePudding user response：

We can convert with lubridate/str_replace i.e use str_replace to replace the substring starting from 'T' with 'time' column and use ymd_hm from lubridate to do the conversion

library(dplyr)
library(lubridate)
library(stringr)
df1 %>% 
     transmute(ID, new_date_time = ymd_hm(str_replace(date, "T.*", 
         str_c(' ', str_pad(time, width = 7, pad = '0')))))

-output

 ID       new_date_time
1  A 2021-07-26 01:00:00
2  B 2021-08-13 22:00:00

Or may also do

library(parsedate)
df1 %>%
    mutate(date = ymd_hms(date), time = format(parse_date(time), 
      '%H:%M:%S')) %>%
    transmute(ID, new_date_time = ymd_hms(str_c(date, ' ', time)))
  ID       new_date_time
1  A 2021-07-26 01:00:00
2  B 2021-08-13 22:00:00