CODEWITHSUNDEEP
r
MT32
MT32

Reputation: 677

Convert two columns into date and time in r

I know this question has been asked over and over again. But this time, the problem is a little different.

a<-matrix(c("01-02-2014", "02-02-2014", "03-02-2014", 
"04-02-2014","05-02-2014","0 1", "0 2", "0 3", "0 4","0 5"),nrow=5)

a<-data.frame(a)

names(a)<-c("date","time")

a$date<-as.Date(a$date, format="%d-%m-%Y")

So now I get this data frame.

    date       time
1 2014-02-01   0 1
2 2014-02-02   0 2
3 2014-02-03   0 3
4 2014-02-04   0 4
5 2014-02-05   0 5

As you can see, the time is the minute of the day, but it is not in typical 00:00 form so the R doesnt recognize it as time, so my question is how do i tranform the time column into a 00:00 format so i can merge with date column to form %Y%m%d %H:%M ??

Upvotes: 1

Views: 387

Answers (2)

Onyambu
Onyambu

Reputation: 79198

you do not need to do anything. Just use it the way it is:

strptime(do.call(paste,a),"%Y-%m-%d %H %M","UTC")
[1] "2014-02-01 00:01:00 UTC" "2014-02-02 00:02:00 UTC"
[3] "2014-02-03 00:03:00 UTC" "2014-02-04 00:04:00 UTC"
[5] "2014-02-05 00:05:00 UTC"

or just even

strptime(paste(a$date,a$time),"%Y-%m-%d %H %M")
[1] "2014-02-01 00:01:00 PST" "2014-02-02 00:02:00 PST"
[3] "2014-02-03 00:03:00 PST" "2014-02-04 00:04:00 PST"
[5] "2014-02-05 00:05:00 PST"

Upvotes: 1

akrun
akrun

Reputation: 886938

We can use sprintf after splitting the 'time' column by space (" ") to get the required format

a$time <- sapply(strsplit(as.character(a$time), " "), 
    function(x) do.call(sprintf, c(fmt = "%02d:%02d", as.list(as.numeric(x)))))
a$time
#[1] "00:01" "00:02" "00:03" "00:04" "00:05"

Then, paste the columns and convert to POSIXct

as.POSIXct(paste(a$date, a$time))
#[1] "2014-02-01 00:01:00 EST" "2014-02-02 00:02:00 EST"
#[3] "2014-02-03 00:03:00 EST" "2014-02-04 00:04:00 EST"
#[5] "2014-02-05 00:05:00 EST"

Or using lubridate we can directly convert it to POSIXct without formatting the 'time' column

library(lubridate)
ymd_hm(paste(a$date, a$time), tz = "EST")
#[1] "2014-02-01 00:01:00 EST" "2014-02-02 00:02:00 EST"
#[3] "2014-02-03 00:03:00 EST" "2014-02-04 00:04:00 EST"
#[5] "2014-02-05 00:05:00 EST"

Upvotes: 2

Related Questions