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.