Converting Data factors to Date

2
votes

I know this has been asked and answered here previously, but I am not getting the provided solution to work for me. Same as the OP, I am reading an output from SQL into R, and it autmoatically recognises the Date column as a factor, as follows:

Sample.Time..Trend.2.: Factor w/ 101 levels "","2013/10/24 00:19:00",..: 2 3 4 5 6 7 8 9 10 11 ...

As per the original solution, I have trued converting the factor to a Date using the as.Date function, with no success:

as.Date(df[1], format = "%Y/%m/%d %H:%M:%S")

Have I missed something here?

EDIT: Reproducible code (dput(df))

structure(list(Sample.Time..Trend.1. = structure(c(2L, 3L, 4L, 
5L, 6L, 7L, 8L, 9L, 10L, 11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 
19L, 20L, 21L, 22L, 23L, 24L, 25L, 26L, 27L, 28L, 29L, 30L, 31L, 
32L, 33L, 34L, 35L, 36L, 37L, 38L, 39L, 40L, 41L, 42L, 43L, 44L, 
45L, 46L, 47L, 48L, 49L, 50L, 51L, 52L, 53L, 54L, 55L, 56L, 57L, 
58L, 59L, 60L, 61L, 62L, 63L, 64L, 65L, 66L, 67L, 68L, 69L, 70L, 
71L, 72L, 73L, 74L, 75L, 76L, 77L, 78L, 79L, 80L, 81L, 82L, 83L, 
84L, 85L, 86L, 87L, 88L, 89L, 90L, 91L, 92L, 93L, 94L, 95L, 96L, 
97L, 98L, 99L, 100L, 101L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = c("", "2013/10/24 00:19:00", 
"2013/10/24 00:49:00", "2013/10/24 01:18:59", "2013/10/24 01:48:59", 
"2013/10/24 02:18:59", "2013/10/24 02:48:59", "2013/10/24 03:18:59", 
"2013/10/24 03:48:59", "2013/10/24 04:18:58", "2013/10/24 04:48:58", 
"2013/10/24 05:18:57", "2013/10/24 05:48:57", "2013/10/24 06:18:57", 
"2013/10/24 06:48:57", "2013/10/24 07:18:58", "2013/10/24 07:48:58", 
"2013/10/24 08:18:57", "2013/10/24 08:48:57", "2013/10/24 09:18:57", 
"2013/10/24 09:48:57", "2013/10/24 10:18:57", "2013/10/24 10:48:57", 
"2013/10/24 11:18:57", "2013/10/24 11:48:57", "2013/10/24 12:18:57", 
"2013/10/24 12:48:57", "2013/10/24 13:18:56", "2013/10/24 13:48:56", 
"2013/10/24 14:18:57", "2013/10/24 14:48:57", "2013/10/24 15:18:56", 
"2013/10/24 15:48:56", "2013/10/24 16:18:56", "2013/10/24 16:48:56", 
"2013/10/24 17:18:56", "2013/10/24 17:48:56", "2013/10/24 18:18:56", 
"2013/10/24 18:48:56", "2013/10/24 19:18:55", "2013/10/24 19:48:55", 
"2013/10/24 20:18:55", "2013/10/24 20:48:55", "2013/10/24 21:18:54", 
"2013/10/24 21:48:54", "2013/10/24 22:18:55", "2013/10/24 22:48:55", 
"2013/10/24 23:18:55", "2013/10/24 23:48:55", "2013/10/25 00:18:54", 
"2013/10/25 00:48:54", "2013/10/25 01:18:53", "2013/10/25 01:48:53", 
"2013/10/25 02:18:53", "2013/10/25 02:48:53", "2013/10/25 03:18:53", 
"2013/10/25 03:48:53", "2013/10/25 04:18:53", "2013/10/25 04:48:53", 
"2013/10/25 05:18:53", "2013/10/25 05:48:53", "2013/10/25 06:18:53", 
"2013/10/25 06:48:53", "2013/10/25 07:18:53", "2013/10/25 07:48:53", 
"2013/10/25 08:18:52", "2013/10/25 08:48:52", "2013/10/25 09:18:52", 
"2013/10/25 09:48:52", "2013/10/25 10:18:51", "2013/10/25 10:48:51", 
"2013/10/25 11:18:51", "2013/10/25 11:48:51", "2013/10/25 12:18:52", 
"2013/10/25 12:48:52", "2013/10/25 13:18:52", "2013/10/25 13:48:52", 
"2013/10/25 14:18:52", "2013/10/25 14:48:52", "2013/10/25 15:18:51", 
"2013/10/25 15:48:51", "2013/10/25 16:18:51", "2013/10/25 16:48:51", 
"2013/10/25 17:18:51", "2013/10/25 17:48:51", "2013/10/25 18:18:51", 
"2013/10/25 18:48:51", "2013/10/25 19:18:51", "2013/10/25 19:48:51", 
"2013/10/25 20:18:51", "2013/10/25 20:48:51", "2013/10/25 21:18:51", 
"2013/10/25 21:48:51", "2013/10/25 22:18:50", "2013/10/25 22:48:50", 
"2013/10/25 23:18:50", "2013/10/25 23:48:50", "2013/10/26 00:18:49", 
"2013/10/26 00:48:49", "2013/10/26 01:18:49", "2013/10/26 01:48:49"
), class = "factor"), AHU.DJ_SATemp = c(23.5765, 23.5814, 23.5814, 
23.5814, 23.5814, 23.5814, 23.5814, 23.5814, 23.5814, 23.5814, 
23.5814, 23.5814, 23.5814, 23.5814, 23.5814, 23.5814, 22.92, 
19.0991, 18.7197, 17.9586, 19.1917, 19.3438, 18.3791, 17.2845, 
18.8174, 17.2029, 16.2401, 18.6221, 16.6308, 20.3029, 17.1108, 
16.4354, 16.4104, 18.8174, 17.4799, 17.8511, 17.5776, 17.3822, 
18.9923, 17.9586, 18.7197, 19.3769, 20.0901, 20.5982, 20.8141, 
21.006, 21.0896, 21.1019, 21.1963, 21.1963, 21.1963, 21.1963, 
21.1963, 21.1963, 21.1963, 21.1963, 21.1963, 21.1963, 21.1963, 
21.1963, 21.1963, 21.1963, 21.1813, 21.1728, 20.8275, 18.3291, 
17.9965, 18.3105, 17.391, 16.1228, 17.1886, 16.1424, 16.2401, 
16.6308, 18.5116, 18.8174, 16.7254, 17.5142, 19.2843, 16.5331, 
17.265, 17.3731, 20.5269, 20.91, 21.1775, 21.2907, 21.2907, 21.2907, 
21.2907, 21.2907, 21.2314, 21.1963, 21.1963, 21.1171, 21.1019, 
21.0642, 21.006, 21.006, 20.9973, 20.958, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), .Names = c("Sample.Time..Trend.1.", 
"AHU.DJ_SATemp"), row.names = c(NA, -200L), class = "data.frame")

Results of Conversion methods:

as.Date(df[1], format = "%Y/%m/%d %H:%M:%S")

Error in as.Date.default(df1, format = "%Y/%m/%d %H:%M:%S") : do not know how to convert 'df1' to class "Date"

as.Date(as.character(df[1],"%Y/%m/%d %H:%M:%S"))

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

as.Date(factor(df[1]), format = "%Y/%m/%d %H:%M:%S")

Error in sort.list(y) : 'x' must be atomic for 'sort.list' Have you called 'sort' on a list?

2
r
Works for me, but do note that the first level is "", which suggests some empty strings or other wierdness, which clearly doesn't match the "%Y/%m/%d %H:%M:%S" pattern. Try as.Date(factor("2013/10/24 00:19:00"), format = "%Y/%m/%d %H:%M:%S") to see that this works. – Gavin Simpson
@Jilber "factor" is a valid input for as.Date() – Gavin Simpson
Could you also post the evidence by which you conclude "no success"? That would help nail down the issue. What does your last line of code produce in R? – Gavin Simpson
Thanks for the replies, I have added a reproducible version of my inputs, as well as the result of the suggested conversion methods. I think you are correct @Gavin Simpson, in that there are blank values which are causing the issue. – Daniel Coakely
@Daniel Coakely, please avoid double posting. In your previous question today (albeit with a different title) one part of the question concerned "trouble converting the current datestamps to dates as they are stored as factors". I provided an answer in that question. – Henrik

2 Answers

5
votes

Note that your error has nothing to do with as.Date(·), it has to do with miss indexig in df[1] it should be df[, 1], then you can use as.Date(·)

as.Date(df[, 1], format = "%Y/%m/%d %H:%M:%S")

Take a look at ?"["

-1
votes

Function to automatically transform date into desired format. Original date is a factor data type.

date_conv=function(date)
{
  mydate = date
  mydate = as.Date(mydate, format = "%d-%B-%Y") #changes format 16-Mar-17 to 
  0017-03-16
  new_date=format(mydate, "20%y-%m-%d") #0017-03-16 to 2017-03-16
  return(new_date)
}