7
day   month     year    hour
01     05      2017     00
05     12      2017     01
10     07      2017     23

i don't have column minute and seconds now i am trying to get a single variable as date_time in the format of 

2017-05-01 00:00:00
2017-12-05 01:00:00
2017-07-10 23:00:00

getting the error while using the below code

df$date <- as.Date(with(df, paste(year, month, day,hour,sep="-")),
           "%Y-%m-%d %H:%M:%S")

thanks in advance

Adam Quek
  • 6,973
  • 1
  • 17
  • 23
snr999
  • 105
  • 1
  • 1
  • 7
  • `paste(year, month, day,hour,sep="-")` would give you `"2017-05-01-00"` which doesnt match the format you have given. Also, `as.Date` won't give you the format you require. – Adam Quek May 24 '17 at 06:28

3 Answers3

6

We can try paste the columns together with sprintf, then convert to datetime with as.POSIXct

as.POSIXct(do.call(sprintf, c(df1, fmt = c("%02d-%02d-%4d %02d"))), format = "%d-%m-%Y %H")
#[1] "2017-05-01 00:00:00 IST" "2017-12-05 01:00:00 IST" "2017-07-10 23:00:00 IST"

Or use lubridate

library(lubridate)
with(df1, ymd_h(paste(year, month, day, hour, sep= ' ')))
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Thanks @akrun , how can it consider the year,month,day,... to get o/p – snr999 May 24 '17 at 06:41
  • @snr999 Isn't the one in the answer the expected output you showed – akrun May 24 '17 at 06:43
  • do we need to convert to character from factor for above variable to get output by using lubridate > – snr999 May 24 '17 at 06:43
  • @snr999 Okay, I thought your columns are `numeric` as it was numbers. So, if you have `factor` class. try `df1[] <- lapply(df1, function(x) as.numeric(as.character(x)))` before attempting the solution. Just changing to `character` would also work with `lubridate` – akrun May 24 '17 at 06:44
  • Hi akrun after converting them all into "Num" format am getting the Error in paste(year, month, day, hour, sep = " ") : cannot coerce type 'closure' to vector of type 'character' – snr999 May 24 '17 at 06:54
  • @snr99 That is a strange error. I couldn't reproduce it though – akrun May 24 '17 at 10:10
2

since year, month, day, hour are stored in different columns, why not just use ISOdate function? for instance:

> ISOdate("2017","12", "05", "01")
[1] "2017-12-05 01:00:00 GMT"

if you don't want to see time zone info, just wrap ISOdate with as.Date

> as.Date(ISOdate("2017","12", "05", "01"))
[1] "2017-12-05"

But this changes the class of object from POSIXct to Date and (correct me if I'm wrong) does not really remove time zone info.

This was discussed in another question How to convert in both directions between year,month,day and dates in R?

Xu Shaoyang
  • 71
  • 5
1

You can do this directly without making a string using lubridate.

library(lubridate)

df1 %>%
  mutate(date = make_datetime(year, month, day, hour))

This is probably more reliable than using string parsing.

Mhairi McNeill
  • 1,951
  • 11
  • 20