R: Convert date column of data table

Question

I have a CSV table that includes a date column containing different formatted date strings. I'll want to write a small function that tries to convert those strings into date objects. The idea is to try different date formats until the date can be parsed:

parseDate <- function(date){
  formats <- c("%d.%m.%y %H:%M", "%Y-%m-%d %H:%M:%S", "%m-%d-%Y-%H:%M:%S")

  for(format in formats){
    newDate <- strptime(date, format=format)
    if(!is.na(newDate)){
      return(newDate)
    }
  }

  print(paste0("Invalid input: ", date))
  return(NA)
}

column1 <- c("2014-11-14 14:20:16", "24.11.14 9:48", "24.11.14 9:49")
table <- data.frame(column1)

table$column1 = lapply(table$column1, FUN = parseDate)

The problem is that I can't find the correct way to apply this function to the column. I tried lapply, apply and by, and non of those functions delivers the desired output. Could you help me here?

score 2 · Answer 1 · answered Feb 21 '15 at 14:29

You could try parse_date_time which can take multiple "formats"

library(lubridate)
parse_date_time(table$column1, c("%d.%m.%y %H:%M", "%Y-%m-%d %H:%M:%S", 
           "%m-%d-%Y-%H:%M:%S"))
#[1] "2014-11-14 14:20:16 UTC" "2014-11-24 09:48:00 UTC"
#[3] "2014-11-24 09:49:00 UTC"

score 2 · Accepted Answer · answered Feb 21 '15 at 14:40

2

Maybe this:

> do.call("c", lapply(table$column1, FUN = parseDate))
[1] "2014-11-14 14:20:16 AEDT" "2014-11-24 09:48:00 AEDT"
[3] "2014-11-24 09:49:00 AEDT"

answered Feb 21 '15 at 14:40

Peter Diakumis

3,942
2
28
26

R: Convert date column of data table

2 Answers2