[R] Converting Multiple Columns of Data Frame to Date

[Uwe Ligges]

John Bodley wrote:
> Hi,
> 
> I have a data frame which has a subset of columns containing character
> representations of dates. The data frame is obtained via the
> DBI::fetch(rs, ...) method. I would like to convert all the columns
> which were originally dates in the database to R dates. I can obtain a
> logical index of columns which are dates via
> 
>> idx <- dbColumnInfo(rs)$type == "DATE".
> 
> I'm having trouble converting these columns to R Date objects, A mock
> example is:
> 
>> df <- data.frame(blah = c(1, 2), start = c("01-Jun-09", "02-Jun-09"), end = c("01-Jul-09", "02-Jul-09"))
>> idx <- c(FALSE, TRUE, TRUE)
>> tmp <- as.Date(as.matrix(df[, idx]), "%d-%b-%y")


In the step above you do not want to make it a matrix but keep with a 
list like structure, hence I suggest o replace it by:

tmp <- lapply(df[, idx], as.Date, "%d-%b-%y")

Uwe Ligges


>> print(tmp)
> [1] "2009-06-01" "2009-06-02" "2009-07-01" "2009-07-02"
> 
> which seems correct, however when I assign the values back to the
> original data frame:
> 
>> df[, idx] <- tmp
>> print(df)
>   blah start   end
> 1    1 14396 14426
> 2    2 14397 14427
> 
> the associated dates are converted to numeric. Note that this approach
> only seems to fail if idx contains two or more columns, i.e., using
> the original definition of df and selecting one date column
> 
>> idx <- c(FALSE, TRUE, FALSE)
>> df[, idx] <- as.Date(as.matrix(df[, idx]), "%d-%b-%y")
>> print(df)
>    blah      start       end
> 1    1 2009-06-01 01-Jul-09
> 2    2 2009-06-02 02-Jul-09
> 
> Returns the correct format for df$start. I don't know a-priori which
> columns are SQL dates, so I need to work will column indices rather
> than:
> 
>> df <- transform(df, start = as.Date(start, "%d-%b-%y")
> 
> Any ideas?
> 
> Thanks
> -John
> 
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.