CODEWITHSUNDEEP
rexcel
Sia
Sia

Reputation: 37

How to combine time and date variables in R on Mac

Similar to this topic might have previously been asked. But, I have searched and tried most of the similar materials before, however, I could not resolve my problem. I work with R on Mac. I have a dataset (DF1), in which there are one Date column and one Time column with the class of "POSIXct" "POSIXt". I want to merge this dataset with another dataset DF2 (with the same class columns) by "Date_Time", which is the combination of Date and Time columns. First of all, when I import my datasets from Excel to R (on Mac), all the values in the Time column get a value of 1899-12-31 beside the time, for example, 1899-12-31 08:33:00. How should I remove this while keeping the class of Time variable (POSIXct" "POSIXt)? Then, to merge the Time and Date column, I used paste() as the following:

DF1$Date_Time <- paste(DF1$DATE , DF1$TIME)

Nevertheless, after I merge Date and Time columns, the class of the new Date_Time column becomes "character" not "POSIXct" "POSIXt". So, I tried different methods to change the class of Date_Time column, such as 

strftime(DF1$Date_Time , format = "%Y-%m-%d %H:%M:%S" , tz="UTC")
strptime(DF1$Date_Time , format = "%Y-%m-%d %H:%M:%S" , tz="UTC")
as.POSIXct(DF1$Date_Time , format = "%Y-%m-%d %H:%M:%S" , tz="UTC")
ymd_hms(DF1$Date_Time , tz = "UTC")

However, none of them worked. To sum up, what I need is a Date_Time variable with the class of "POSIXct" "POSIXt", because after this step I need to calculate the time differences in minutes and as you know the classes of character and factor do not work for that. I also tried another way. I made the Date_Time column in Excel (before importing to R) and changed the format to time format in excel. Then, I imported that excel file in R and noticed that the class of Date_Time is still "character". Thanks for your help.

Edit: Here is a sample of DF1:

DF1 <- structure(list(No. = c(1, 2, 3, 4), Time = 
structure(c(-2209044420, 
-2209044360, -2209044300, -2209044240), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), Date = structure(c(1559433600, 1559433600, 
1559433600, 1559433600), class = c("POSIXct", "POSIXt"), tzone = 
"UTC")), row.names = c(NA, 
-4L), class = c("tbl_df", "tbl", "data.frame"))

Upvotes: 1

Views: 382

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388982

We can extract Date and Time, paste the values together and convert it into POSIXct format. 

new_data <- transform(transform(DF1, Date = as.Date(Date), 
                                     Time = format(Time, "%T")),
                         DateTime = as.POSIXct(paste(Date, Time), tz = "UTC"))
new_data

#  No.     Time       Date            DateTime
#1   1 08:33:00 2019-06-02 2019-06-02 08:33:00
#2   2 08:34:00 2019-06-02 2019-06-02 08:34:00
#3   3 08:35:00 2019-06-02 2019-06-02 08:35:00
#4   4 08:36:00 2019-06-02 2019-06-02 08:36:00

Using dplyr and lubridate

library(dplyr)
library(lubridate)

DF1 %>%
  mutate(Date = as.Date(Date), Time = format(Time, "%T"), 
         DateTime = ymd_hms(paste(Date, Time)))

Upvotes: 0

Related Questions