1

I am importing a data frame into R, but R is not recognizing the columns with the dates as being in dates format.

> mydata[1,1] [1] 1/1/2003 0:00 216332 Levels: 1/1/2003 0:00 1/1/2003 0:15 1/1/2003 0:30 ... 9/9/2007 9:55

I tried: 

> as.Date(mydata[1,1], format = "%m/%d/%Y %H:%M")
[1] "2003-01-01"

But then I miss the time.

If I do 

> strptime(mydata[2,1], format = "%m/%d/%Y %H:%M")
[1] "2003-01-01 00:15:00 EST"

I get what I need. However it does not work when I assign this result to my variable

> mydata[,1] <- strptime(mydata[,1], format = "%m/%d/%Y %H:%M")
Warning message:
In `[<-.data.frame`(`*tmp*`, , 1, value = list(sec = c(0, 0, 0,  :
  provided 11 variables to replace 1 variables

My question is similar to the question at Set time value into data frame cell

Although, it is well explained, after spending some time reading and trying I could not figure that out on my own.

Community
  • 1
  • 1
Alan Alves
  • 61
  • 1
  • 2
  • 9
  • 2
    I might miss something here, but why not go for `as.POSIXct`? `mydata[ , 1] <- as.POSIXct(mydata[ , 1], format = "%m/%d/%Y %H:%M")` – Henrik Jun 30 '14 at 20:03
  • It return an warning message: `Warning message: In `[<-.factor`(`*tmp*`, iseq, value = 1191212100) : invalid factor level, NA generated`. I could solve the problem using the lubridate package as suggested by rrs. Thank you for your comment though. – Alan Alves Jul 02 '14 at 00:19
  • Then you have some peculiarities in your data not shown in your example. This works fine on consistently formatted date-times: `mydata <- data.frame(time = factor(c("1/14/2003 0:30", "9/19/2007 9:55")))`; `str(mydata)`; `mydata[, 1] <- as.POSIXct(mydata[,1], format = "%m/%d/%Y %H:%M")`; `str(mydata)`. – Henrik Jul 02 '14 at 06:38

4 Answers4

2

The levels mean you have a factor. You need to convert to character with as.character():

 dt <- as.POSIXct(as.character(mydata[ ,1]) format = "%m/%d/%Y %H:%M")

The first item with time = 0:00 will not show the time when printed but the others will. The error is occuring because the POSIXlt object is a list of 11 item lists. Generally it is better to use as.POSIXct than to use strptime because strptime returns a POSIXlt object and they are a bit of a mess to work with.:

d <- factor("1/1/2003 0:01")
as.POSIXct( as.character(d), format = "%m/%d/%Y %H:%M")
[1] "2003-01-01 00:01:00 PST"
IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • Although I found out about lubridate (which solved the problem) before seeing your post, I tested it and it worded perfectly: `> mydata[,1] <- as.POSIXct(as.character(mydata[ ,1]), format = "%m/%d/%Y %H:%M") > mydata[1:10,1] [1] "2007-10-01 00:00:00 EDT" "2007-10-01 00:15:00 EDT" [3] "2007-10-01 00:30:00 EDT" "2007-10-01 00:45:00 EDT" [5] "2007-10-01 01:00:00 EDT" "2007-10-01 01:15:00 EDT" [7] "2007-10-01 01:30:00 EDT" "2007-10-01 01:45:00 EDT" [9] "2007-10-01 02:00:00 EDT" "2007-10-01 02:15:00 EDT"`. Thank you ! – Alan Alves Jul 02 '14 at 00:40
0

If you are using read.table, read.csv or similar functions to read in the data then you could look at this solution for a way to specify which columns will be dates and have them automatically converted as they are read in. This will do the conversion on the character strings without any conversion to factor (which may be part of your problem).

Community
  • 1
  • 1
Greg Snow
  • 48,497
  • 6
  • 83
  • 110
0

When dealing with dates, I find lubridate can be very helpful:

library(lubridate)
mydata[, 1] <- mdy_hm(mydata[, 1])
rrs
  • 9,615
  • 4
  • 28
  • 38
0

If you don't want to deal with Levels, try this:

First convert your data into character:

data<- as.character(mydata[1,1])

Then give the date format you need, for example:

date<- format(as.POSIXct(data, tz="EST"),"%Y-%m-%d %H")

Edith Cortes
  • 1
  • 1
  • It still does not work: `>data<- as.character(mydata[2,1])` `>data [1] "10/1/2007 0:15"` `> date<- format(as.POSIXct(data, tz="EST"),"%Y-%m-%d %H:%M") > date [1] "0010-01-20 00:00"` The package lubridate as suggested by rrs worked well. – Alan Alves Jul 02 '14 at 00:24