1

I have a column list of dates in data frame with date format 201001011200 as %Y%m%d%H%M. I wanted to split them as %Y%m%d and %H%M as Date and Time.

I tried to as.Date(data$Date,origin = "1970-01-01") but I got an error message

Error in charToDate(x) : character string is not in a standard unambiguous format

The class of the date is numeric. So I tried to convert it to characterand applied the above as.Date function but was not helpful.

Any idea? Thank you in advance.

EDIT

Here is a sample of my data:

Index Date          rank amount
81211 201004090000  11 4.9
81212 201004090100  11 4.6
81213 201004090200  11 3.3
81214 201004090300  11 2.7
81215 201004090400  11 3.1
81216 201004090500  11 3.7
81217 201004090600  11 4.0
81218 201004090700  11 4.2
81219 201004090800  11 4.2
81220 201004090900  11 4.0
Rich Scriven
  • 97,041
  • 11
  • 181
  • 245
G1124E
  • 407
  • 1
  • 10
  • 20

1 Answers1

3

Updated Answer: Beginning with your example data, you can do 

data$Date <- as.POSIXct(as.character(data$Date), format =  "%Y%m%d%H%M")

to change the column to a POSIX datetime value. Then, to extract the date and time into two separate columns, you can do

data$date <- as.character(as.Date(data$Date))
data$time <- format(data$Date, "%T")

This gives the following updated data frame data

   Index                Date rank amount       date     time
1  81211 2010-04-09 00:00:00   11    4.9 2010-04-09 00:00:00
2  81212 2010-04-09 01:00:00   11    4.6 2010-04-09 01:00:00
3  81213 2010-04-09 02:00:00   11    3.3 2010-04-09 02:00:00
4  81214 2010-04-09 03:00:00   11    2.7 2010-04-09 03:00:00
5  81215 2010-04-09 04:00:00   11    3.1 2010-04-09 04:00:00
6  81216 2010-04-09 05:00:00   11    3.7 2010-04-09 05:00:00
7  81217 2010-04-09 06:00:00   11    4.0 2010-04-09 06:00:00
8  81218 2010-04-09 07:00:00   11    4.2 2010-04-09 07:00:00
9  81219 2010-04-09 08:00:00   11    4.2 2010-04-09 08:00:00
10 81220 2010-04-09 09:00:00   11    4.0 2010-04-09 09:00:00

Original Answer: If you are starting with a numeric value, wrap it in as.character() then run it through as.POSIXct() to get a POSIX date-time value.

data$Date <- as.POSIXct(as.character(data$Date), format = "%Y%m%d%H%M")

As an example I will use 201001011200 as you gave.

(x <- as.POSIXct(as.character(201001011200), format = "%Y%m%d%H%M"))
# [1] "2010-01-01 12:00:00 PST"

Then to separate out the date and time you can do the following.

list(as.Date(x), format(x, "%T"))
# [[1]]
# [1] "2010-01-01"
# 
# [[2]]
# [1] "12:00:00"

That gives Date and character classed items, respectively. For a plain old character vector, just use format() twice.

c(format(x, "%m-%d-%Y"), format(x, "%T"))
# [1] "01-01-2010" "12:00:00"

or

c(as.character(as.Date(x)), format(x, "%T"))
# [1] "2010-01-01" "12:00:00"
Rich Scriven
  • 97,041
  • 11
  • 181
  • 245
  • 1
    beat me to it .. the OP says they're starting with numeric, but they already know how to use `as.character()`, so your answer should get them the rest of the way ... – Ben Bolker Jun 26 '16 at 00:19
  • @ Richard Scriven The above date `201001011200` is just one of the dates in the column `Date`. The dates change by incrementing the hour. So, I have around 100K rows. The above solution didn't work. – G1124E Jun 26 '16 at 00:35
  • I also used `data$Date<-as.POSIXct("data$Date",format="%Y%m%d%H%M")` , all the dates were changed to NA's – G1124E Jun 26 '16 at 00:37
  • @RichardScriven your solution in the comment section worked fine. From that I used the following for splitting it in to Date and Time: `data$Date <- as.Date(data$Date)` `data$Time <- format(data$Date,"%H:%M")` but as for the data frame for Time it only listed 0.00 for the whole column. why is that? – G1124E Jun 26 '16 at 00:58
  • @RichardScriven why is `data$Time <- format(data$Date,"%H:%M")` is not working? I also tried with `data$Time <- format(data$Date,"%T") as per your above solution but it didn't work. It just displayed a column with no time in it. – G1124E Jun 26 '16 at 01:06
  • @G1124E - I don't really know why that is. It works fine when I run it on your example data. – Rich Scriven Jun 26 '16 at 01:26