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

user_5278205 0
Viewed 47

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
lubridate r time date datetime
2 Answers

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
user_37322710

Here is a base R option -

transform(df, new_date_time = as.POSIXct(paste(sub('T.*', '', date), time), 
              format = '%Y-%m-%d %I:%M%p', tz = 'UTC'))

#  ID                         date    time       new_date_time
#1  A 2021-07-26T00:00:00.000+0000  1:00AM 2021-07-26 01:00:00
#2  B 2021-08-13T00:00:00.000+0000 10:00PM 2021-08-13 22:00:00
user_39629140
Related