Home > database >  In R how do you combine date and time character columns into a a single column?
In R how do you combine date and time character columns into a a single column?

Time:04-22

I have two separate columns with date and time, respectively. I just want to combine them into a a single column. For example;

# Example data
Time<- as.character(c("11:15", "12:23", "15:42"))
Date<- as.character(c("2022-02-14", "2022-03-01", "2022-03,01"))                       
df<-data.frame(Time, Date)
class(df$Date)
class(df$Time)

The question posted here is similar but I don't have a double with "24" I need to deal with. I tried the following to no avail;

df$DateTime<-as.POSIXlt(paste0(df$Date, " ", df$Time, tz = "HST"))

Any help is greatly appreciated. Using mutate() would be fine too.

CodePudding user response:

Try this:

#I think you might have put a comma instead of a dash in you example data. Corrected by gsub:    
df$Date <- gsub(",", "-",df$Date)

#Create a new combined date and time column:
df$DateTime <- paste(df$Date,df$Time)

#Convert the new column into a POSIXlt class object:
df$DateTime <- as.POSIXlt(df$DateTime,  c("%Y-%m-%d %H:%M"), tz = "HST")

Another approach using piping actions from the dplyr package:

library(dplyr)

df %>% 
  mutate(Date = gsub(",","-",Date)) %>% 
  mutate(DateTime = paste(Date,Time)) %>% 
  mutate(DateTime = as.POSIXlt(DateTime,  c("%Y-%m-%d %H:%M"), tz = "HST")) -> df

CodePudding user response:

We can use tidyverse and since the date and time are in the correct format/order, we don't necessarily need to specify the format of DateTime.

library(tidyverse)

df %>% 
  mutate(Date = str_replace_all(Date, ",", "-"),
         DateTime = as.POSIXlt(paste(Date, Time, sep = " "), tz = "HST"))

Or we can also use lubridate:

library(lubridate)

df %>% 
  mutate(Date = str_replace_all(Date, ",", "-"),
         DateTime = ymd_hm(paste(Date, Time, sep = " "), tz = "HST"))

Output

   Time       Date            DateTime
1 11:15 2022-02-14 2022-02-14 11:15:00
2 12:23 2022-03-01 2022-03-01 12:23:00
3 15:42 2022-03-01 2022-03-01 15:42:00
  •  Tags:  
  • r
  • Related