CODEWITHSUNDEEP
rclassdatedatetime
Hafeez
Hafeez

Reputation: 95

R - Merge data and time when time column already has current system date in it

For data set, I have a Date and a Time column, both with class 'Character'. The source of the data is https://d396qusza40orc.cloudfront.net/exdata%2Fdata%2Fhousehold_power_consumption.zip

There are 9 variables/columns in the data set. Here is what the data looks like in Date and Time columns.

Date Time
2007-02-01 00:01:00
2007-02-01 00:02:00
2007-02-01 18:22:00
2007-02-02 04:15:00
2007-02-02 13:43:00

Both the Date and Time are of class 'character'

My objective is to convert both date and time from class 'Character' to classes 'Date' and 'Time' respectively. Ultimately I want to be able to sort the data points by date and time, and prepare histograms by various time intervals.

I converted Date to class 'Date' as follows:

newdata$Date <- as.Date(newdata$Date, "%d/%m/%Y")

I converted time as follows:

newdata$Time <- strptime(newdata$Time, format="%H:%M:%S")

Date worked out fine. However, for time it adds the current date as shown in the illustration below.

Date Time
2007-02-01 2020-12-13 00:01:00
2007-02-01 2020-12-13 00:02:00
2007-02-01 2020-12-13 18:22:00
2007-02-02 2020-12-13 04:15:00
2007-02-02 2020-12-13 13:43:00

How can I replace the current date in column 'Time' with corresponding date in column 'Date', so that I end up with a DateTime object in column 'Time'? I can then use this column to sort and bucket the data points.

Thank you for helping out!

Upvotes: 0

Views: 32

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388982

In base R, you could do :

data$date_time <- as.POSIXct(paste(data$Date, data$Time), tz = 'UTC')

#  Date       Time     date_time          
#  <chr>      <chr>    <dttm>             
#1 2007-02-01 00:01:00 2007-02-01 00:01:00
#2 2007-02-01 00:02:00 2007-02-01 00:02:00
#3 2007-02-01 18:22:00 2007-02-01 18:22:00
#4 2007-02-02 04:15:00 2007-02-02 04:15:00
#5 2007-02-02 13:43:00 2007-02-02 13:43:00

Upvotes: 1

Paul
Paul

Reputation: 2959

You can use a combination of paste and lubridate::ymd_hms() to do this:

library(dplyr)
library(lubridate)
   
mutate(data, Time = ymd_hms(paste(Date, Time)))

# A tibble: 5 x 2
  Date       Time               
  <chr>      <dttm>             
1 2007-02-01 2007-02-01 00:01:00
2 2007-02-01 2007-02-01 00:02:00
3 2007-02-01 2007-02-01 18:22:00
4 2007-02-02 2007-02-02 04:15:00
5 2007-02-02 2007-02-02 13:43:00

Upvotes: 3

Related Questions