Convert dates with different formats in data frame over multiple columns

Question

I have data frame (test) that looks like this:

    abx   start      stop       abx2    start2     stop2     abx3 start3 stop3
    cipro 07/10/12   07/10/12   flagyl  07/10/12   07/17/12   n/a   n/a   n/a
    vanco 07/12/2012 07/15/2012 levo    07/20/2012 07/27/2012 n/a  n/a    n/a

This continues until start9/stop9. I'd like to convert all the dates to standard date format.

I wrote a function to convert the start dates depending on the number of characters in the date. Plan to write a similar function for the stops.

    dateconv <- function(x) { 
    as.character(x)
    z <- ifelse(nchar(x) == 8, "y","Y")
    date <- as.Date(x, format =paste0("%m/%d/%", z))
    rm(z)
    }
    test[,grep("^start", names(test))] <- dateconv(test[,grep("^start",         
   names(test))])

Any ideas what I'm doing wrong? Getting this error: Error in as.Date.default(x, format = paste0("%m/%d/%", z)) : do not know how to convert 'x' to class “Date”

Update (Feb 20, 2015): Thanks to Richard's comment I got this to work (really like the grep("st(art|op)" :

    g <- grep("st(art|op)", names(test), value = TRUE)

    test[g] <- lapply(test[g], function(x) {
     x <- as.character(x)
      as.Date(x, format = paste0("%m/%d/%", ifelse(nchar(x) == 8, "y",       "Y")))})

the last line in an r function is its return value. you should move the `rm(z)` somewhere else or just delete it altogether since it is unnecessary. And you're calling `dateconv` incorrectly for the input. You should use `lapply` or similar, like `lapply(test[, grep("^start", names(test))], dateconv)` — rawr, Feb 19 '15 at 22:22
possible duplicate of [How to change multiple Date formats in same column](http://stackoverflow.com/questions/13764514/how-to-change-multiple-date-formats-in-same-column) — Metrics, Feb 19 '15 at 22:44

score 1 · Accepted Answer · edited Feb 20 '15 at 13:51

Here's one way to change the test data frame.

g <- grep("st(art|op)", names(test), value = TRUE)

test[g] <- lapply(test[g], function(x) {
    x <- as.character(x)
    as.Date(x, format = paste0("%m/%d/%", ifelse(nchar(x) == 8, "y", "Y")))
}))

which gives

#     abx      start       stop   abx2     start2      stop2 abx3 start3 stop3
# 1 cipro 2012-07-10 2012-07-10 flagyl 2012-07-10 2012-07-17  n/a   <NA>  <NA>
# 2 vanco 2012-07-12 2012-07-15   levo 2012-07-20 2012-07-27  n/a   <NA>  <NA>

Where test is

test <- read.table(text = "abx   start      stop       abx2    start2     stop2     abx3 start3 stop3\n    cipro 07/10/12   07/10/12   flagyl  07/10/12   07/17/12   n/a   n/a   n/a\n    vanco 07/12/2012 07/15/2012 levo    07/20/2012 07/27/2012 n/a  n/a    n/a", header = TRUE, stringsAsFactors=FALSE)

Thanks Richard. Got it to work with a slight modificaiton.g <- grep("st(art|op)", names(test), value = TRUE) test[g] <- lapply(test[g], function(x) { x <- as.character(x) as.Date(x, format = paste0("%m/%d/%", ifelse(nchar(x) == 8, "y", "Y"))) }) — Tom O, Feb 20 '15 at 13:42

score 0 · Answer 2 · answered Feb 19 '15 at 22:41

0

Your easiest bet is to use mdy() from the lubridate package:

library(lubridate)
dateFields <- grep('^(start|stop)',names(test),value=T)
test[,dateFields] <- lapply(test[,dateFields],mdy)

answered Feb 19 '15 at 22:41

Jthorpe

9,756
2
49
64

Convert dates with different formats in data frame over multiple columns

2 Answers2

Linked