How to change multiple Date formats in same column?

date, format, r

Solution

Since MattBagg's answer in 2012, `lubridate` has added the `parse_date_time` function which is designed for exactly this situation and can resolve this problem in a single line:

library(lubridate) 

data <- data.frame(initialDiagnose = c("14.01.2009", "9/22/2005", 
        "4/21/2010", "28.01.2010", "09.01.2009", "3/28/2005", 
        "04.01.2005", "04.01.2005", "Created on 9/17/2010", "03 01 2010"))

parse_date_time(data$initialDiagnose, orders = c('mdy', 'dmy'))

 [1] "2009-01-14 UTC" "2005-09-22 UTC" "2010-04-21 UTC" "2010-01-28 UTC" "2009-01-09 UTC"
 [6] "2005-03-28 UTC" "2005-01-04 UTC" "2005-01-04 UTC" "2010-09-17 UTC" "2010-03-01 UTC"

The `orders=` argument is a character vector containing the possible date-time parsing formats in the order they should be tested. So by giving `c('mdy', 'dmy')`, lubridate will try to parse all strings as `Month, Date, Year` format. If it can't do that successfully (for example, the date `14.01.2009` won't work as there is no 14th month), it will try the next in the list.

The order in which it tries formats is a bit complicated, but you generally don't have to worry about it. `parse_date_time2` simply tries them in the order provided. `parse_date_time` takes a subset of the input strings and uses that as a training set to find the best performing set formats, which orders based on the function given by the `select_formats` argument, which by default prioritizes the most complex formats (ie, those with the most format tokens)

Problem

What I've got so far is a dataframe column with dates in different character formats. A few appear in the `%d.%m.%Y` pattern, some in `%m/%d/%Y` : ``` data$initialDiagnose = as.character(data$initialDiagnose) data$initialDiagnose[1:10] [1] "14.01.2009" "9/22/2005" "4/21/2010" "28.01.2010" "09.01.2009" "3/28/2005" "04.01.2005" "04.01.2005" "9/17/2010" "03.01.2010" ``` I want them as Date() in one format, but R refuses of course. So I tried at first to change them by the separator: ``` data$initialDiagnose[grep('/', data$initialDiagnose)] = as.character.Date(data$initialDiagnose[grep('/', data$initialDiagnose)], format = '%m/%d/%Y') ``` Analog to the '.' dates. But it didn't work. How can I change them all to one format, that I can work with them?

Original source