Format multiple Time columns based on one Date column in RStudio-CodePudding

I could achieve the desired results with a series of paste() and as.Date() functions for each column individually, but is there a more efficient approach?

Reprex

Data

df1 <- data.frame(Column = as.numeric(c(1:6)),
                  Headers = c("1989-01-01", "1990-01-01", "1991-01-01", "1992-01-01", "1993-01-01", "1994-01-01"),
                  Are = c("7:08:26 am", "7:08:34 am", "7:08:41 am", "7:08:45 am", "7:08:47 am", "7:08:48 am"),
                  Separated = c("Apples", "Bananas", "Cherries", "Dates", "Elderberries", "Figs"),
                  `And They` = c("7:36:48 am", "7:36:54 am", "7:36:59 am", "7:37:01 am", "7:37:01 am", "7:36:59 am"), 
                  Have = c("5:08:11 pm", "5:09:00 pm", "5:09:51 pm", "5:10:43 pm", "5:11:37 pm", "5:12:32 pm"),
                  Different = c("5:36:33 pm", "5:37:21 pm", "5:38:09 pm", "5:39:00 pm", "5:39:51 pm", "5:40:44 pm"),
                  Names = c("Red", "Orange", "Yellow", "Green", "Blue", "Violet"),
                  check.names = F)

Data Frame

> df
  Column    Headers        Are    Separated   And They       Have  Different  Names
1      1 1989-01-01 7:08:26 am       Apples 7:36:48 am 5:08:11 pm 5:36:33 pm    Red
2      2 1990-01-01 7:08:34 am      Bananas 7:36:54 am 5:09:00 pm 5:37:21 pm Orange
3      3 1991-01-01 7:08:41 am     Cherries 7:36:59 am 5:09:51 pm 5:38:09 pm Yellow
4      4 1992-01-01 7:08:45 am        Dates 7:37:01 am 5:10:43 pm 5:39:00 pm  Green
5      5 1993-01-01 7:08:47 am Elderberries 7:37:01 am 5:11:37 pm 5:39:51 pm   Blue
6      6 1994-01-01 7:08:48 am         Figs 7:36:59 am 5:12:32 pm 5:40:44 pm Violet

Desired Output

  Column    Headers                 Are    Separated            And They                Have           Different  Names
1      1 1989-01-01 1989-01-01 07:08:26       Apples 1989-01-01 07:36:48 1989-01-01 17:08:11 1989-01-01 17:36:33    Red
2      2 1990-01-01 1990-01-01 07:08:34      Bananas 1990-01-01 07:36:54 1990-01-01 17:09:00 1990-01-01 17:37:21 Orange
3      3 1991-01-01 1991-01-01 07:08:41     Cherries 1991-01-01 07:36:59 1991-01-01 17:09:51 1991-01-01 17:38:09 Yellow
4      4 1992-01-01 1992-01-01 07:08:45        Dates 1992-01-01 07:37:01 1992-01-01 17:10:43 1992-01-01 17:39:00  Green
5      5 1993-01-01 1993-01-01 07:08:47 Elderberries 1993-01-01 07:37:01 1993-01-01 17:11:37 1993-01-01 17:39:51   Blue
6      6 1994-01-01 1994-01-01 07:08:48         Figs 1994-01-01 07:36:59 1994-01-01 17:12:32 1994-01-01 17:40:44 Violet

CodePudding user response：

We may loop across the columns of interest paste the 'Column' with the individual columns and convert to datetime class with ymd_hms from lubridate

library(dplyr)
library(lubridate)
library(stringr)
df1 <- df1 %>% 
   mutate(across(Headers:Names, ~ ymd_hms(str_c(Column, ' ', .))))

-output

df1
 Column             Headers                Have           Different               Names
1 1989-01-01 1989-01-01 07:08:26 1989-01-01 07:36:48 1989-01-01 17:08:11 1989-01-01 17:36:33
2 1990-01-01 1990-01-01 07:08:34 1990-01-01 07:36:54 1990-01-01 17:09:00 1990-01-01 17:37:21
3 1991-01-01 1991-01-01 07:08:41 1991-01-01 07:36:59 1991-01-01 17:09:51 1991-01-01 17:38:09
4 1992-01-01 1992-01-01 07:08:45 1992-01-01 07:37:01 1992-01-01 17:10:43 1992-01-01 17:39:00
5 1993-01-01 1993-01-01 07:08:47 1993-01-01 07:37:01 1993-01-01 17:11:37 1993-01-01 17:39:51
6 1994-01-01 1994-01-01 07:08:48 1994-01-01 07:36:59 1994-01-01 17:12:32 1994-01-01 17:40:44

With the OP's new dput edit, the Headers is now the Date column

df %>%
     mutate(across(c(Are, `And They`, Have, Different),
         ~ ymd_hms(str_c(Headers, " ", .))))

-output

  Column    Headers                 Are    Separated            And They                Have           Different  Names
1      1 1989-01-01 1989-01-01 07:08:26       Apples 1989-01-01 07:36:48 1989-01-01 17:08:11 1989-01-01 17:36:33    Red
2      2 1990-01-01 1990-01-01 07:08:34      Bananas 1990-01-01 07:36:54 1990-01-01 17:09:00 1990-01-01 17:37:21 Orange
3      3 1991-01-01 1991-01-01 07:08:41     Cherries 1991-01-01 07:36:59 1991-01-01 17:09:51 1991-01-01 17:38:09 Yellow
4      4 1992-01-01 1992-01-01 07:08:45        Dates 1992-01-01 07:37:01 1992-01-01 17:10:43 1992-01-01 17:39:00  Green
5      5 1993-01-01 1993-01-01 07:08:47 Elderberries 1993-01-01 07:37:01 1993-01-01 17:11:37 1993-01-01 17:39:51   Blue
6      6 1994-01-01 1994-01-01 07:08:48         Figs 1994-01-01 07:36:59 1994-01-01 17:12:32 1994-01-01 17:40:44 Violet

data

df1 <- structure(list(Column = c("1989-01-01", "1990-01-01", "1991-01-01", 
"1992-01-01", "1993-01-01", "1994-01-01"), Headers = c("7:08:26 am", 
"7:08:34 am", "7:08:41 am", "7:08:45 am", "7:08:47 am", "7:08:48 am"
), Have = c("7:36:48 am", "7:36:54 am", "7:36:59 am", "7:37:01 am", 
"7:37:01 am", "7:36:59 am"), Different = c("5:08:11 pm", "5:09:00 pm", 
"5:09:51 pm", "5:10:43 pm", "5:11:37 pm", "5:12:32 pm"), Names = c("5:36:33 pm", 
"5:37:21 pm", "5:38:09 pm", "5:39:00 pm", "5:39:51 pm", "5:40:44 pm"
)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6"))

CodePudding user response：

library(dplyr); library(lubridate)
df %>% 
  mutate(across(Headers:Names, ~ymd_hms(paste(Column, .x))))