Convert dd/mm/yy and dd/mm/yyyy to Dates

Question

I have some a character vector with dates in various formats like this

dates <- c("23/11/12", "20/10/2012", "22/10/2012" ,"23/11/12")

I want to convert these to Dates. I have tried the very good dmy from the lubridate package, but this does not work:

    dmy(dates)
[1] "0012-11-23 UTC" "2012-10-20 UTC" "2012-10-22 UTC" "0012-11-23 UTC"

It is treating the /12 year as if it is 0012.

So I now am trying regular expression to select each type and individually convert to dates using as.Date(). However the regular expression I have tried to select the dd/mm/yy only does not work.

dates[grep('[0-9]{2}/[0-9]{2}/[0-9]{2,2}', dates)]

returns

[1] "23/11/12"   "20/10/2012" "22/10/2012" "23/11/12"

I thought that the {2,2} should get a exactly 2 numbers and not all of them. I'm not very good at regular expression so any help will be appreciated.

Thanks

EDIT

What I actually have are three different types of date as below

dates <- c("23-Jul-2013", "23/11/12", "20/10/2012", "22/10/2012" ,"23/11/12")

And I want to convert these to dates

parse_date_time(dates,c('dmy'))

gives me

[1] "2013-07-23" "0012-11-23" "2012-10-20" "2012-10-22" "0012-11-23"

However, this is wrong and 0012 should be 2012. I would like (a fairly simple) solution to this.

One solution I now have (thanks to @plannapus)is to use regular expressions I actually ended up creating this function as I was still getting some cases where the lubridate approach was turning 12 into 0012

    asDateRegex <- function(dates, 
        #selects strings from the vector dates using regexes and converts these to Dates
        regexes = c('[0-9]{2}/[0-9]{2}/[0-9]{4}', #dd/mm/yyyy
            '[0-9]{2}/[0-9]{2}/[0-9]{2}$', #dd/mm/yy
            '[0-9]{2}-[[:alpha:]]{3}-[0-9]{4}'), #dd-mon-yyyy
        orders = 'dmy',
        ...){
        require(lubridate)
        new_dates <- as.Date(rep(NA, length(dates)))
        for(reg in regexes){
            new_dates[grep(reg, dates)] <- as.Date(parse_date_time(dates[grep(reg, dates)], order = orders))
        }
        new_dates
    }

asDateRegex (dates)
[1] "2012-10-20" "2013-07-23" "2012-11-23" "2012-10-22" "2012-11-23"

But this is not very elegant. Any better solutions?

You may have a look here: http://stackoverflow.com/questions/19127095/parse-dates-in-format-dmy-together-with-dmy-using-parse-date-time-in-package-lub — Henrik, Oct 17 '13 at 12:31

agstudy · Accepted Answer · 2013-10-17T13:09:09.007

16

You can use parse_date_time from lubridate:

some.dates <- c("23/11/12", "20/10/2012", "22/10/2012" ,"23/11/12")
parse_date_time(some.dates,c('dmy'))
[1] "2012-11-23 UTC" "2012-10-20 UTC" "2012-10-22 UTC" "2012-11-23 UTC"

But , Note that the order of format is important :

some.dates <- c("20/10/2012","23/11/12",  "22/10/2012" ,"23/11/12")
parse_date_time(some.dates,c('dmY','dmy'))

[1] "2012-10-20 UTC" "2012-11-23 UTC" "2012-10-22 UTC" "2012-11-23 UTC"

EDIT

Internally parse_date_time is using guess_formats (which I guess uses some regular expressions):

guess_formats(some.dates,c('dmy'))
       dmy        dmy        dmy        dmy 
"%d/%m/%Y" "%d/%m/%y" "%d/%m/%Y" "%d/%m/%y"

As mentioned in the comment you can use parse_date_time like this:

as.Date(dates, format = guess_formats(dates,c('dmy')))

edited Oct 17 '13 at 13:09

answered Oct 17 '13 at 11:22

agstudy

119,832
17
199
261

1

Me neither. +1 I need to spend more time with lubridate – Simon O'Hanlon Oct 17 '13 at 11:27
1

Just out of interest, what version of lubridate are you using? Under lubridate `1.3.0` , R `2.15.2` (yeah, out of date, I know), Win 7, I still get `0012` years when I copy & paste your `parse_date_time` code. – Richie Cotton Oct 17 '13 at 12:10
1

Also note that `parse_date_time` returns a `POSIXlt` vector, not a `Date`, (`dmy` returns `POSIXct`). – Richie Cotton Oct 17 '13 at 12:17
I also still get 0012 using this code. I am using R version 3.0.1 and lubridate_1.3.0 – Tom Liptrot Oct 17 '13 at 12:21
1

I described a similar phenomenon in [my last question](http://stackoverflow.com/questions/19127095/parse-dates-in-format-dmy-together-with-dmy-using-parse-date-time-in-package-lub). It is also posted as an issue on [lubridate github](https://github.com/hadley/lubridate/issues/206). – Henrik Oct 17 '13 at 12:24
@RichieCotton R version `3.0.0 (2013-04-03) Platform: x86_64-w64-mingw32/x64 (64-bit) lubridate_1.3.0` – agstudy Oct 17 '13 at 12:32
1

`as.Date(dates, format = guess_formats(dates,c('dmy')))` works for me. I'm not sure why this works and not `parse_date_time`. @agstudy if you add that to your answer i'll mark it as question answered (i'm not quite sure of the correct etiquette here...) – Tom Liptrot Oct 17 '13 at 12:58
@agstudy, in [my question](http://stackoverflow.com/questions/19127095/parse-dates-in-format-dmy-together-with-dmy-using-parse-date-time-in-package-lub) (which you answered), I concluded that the order of the date components did not differ. It seems to still be the case in lubridate 1.3.0. I don't know why you get different results here... – Henrik Oct 17 '13 at 13:35
@Henrik I see your point. I will investigate ( I am far from a pc right now). – agstudy Oct 17 '13 at 13:46
@agstudy, no stress at all. Just curious. The function seems slightly shaky. Cheers! – Henrik Oct 17 '13 at 13:48
I actually ended up creating this function as I was still getting some cases where the lubridate approach was turning 12 into 0012 – Tom Liptrot Oct 18 '13 at 07:26

score 7 · Answer 2 · answered Oct 17 '13 at 12:14

7

You can choose the format based upon input length of date.

y <- ifelse(nchar(dates) == 8, "y", "Y")
as.Date(dates, format = paste0("%d/%m/%", y))

answered Oct 17 '13 at 12:14

Richie Cotton

118,240
47
247
360

score 1 · Answer 3 · answered Oct 17 '13 at 11:14

1

You can use strsplit and nchar to get a subvector of dates where the year is two characters long:

> dates[sapply(strsplit(dates,"/"),function(x)nchar(x)[3]==2)]
[1] "23/11/12" "23/11/12"

answered Oct 17 '13 at 11:14

mrip

14,913
4
40
58

score 1 · Answer 4 · answered Oct 17 '13 at 11:26

Following your original attempt at regex based solutions, you may try gsub using this regexp, then converting to any date-time format you wish...

#  Replace 4 digit years with two digit years
short <- gsub( "([0-9]{2})([0-9]{2})$" , "\\2" , dates )
#[1] "23/11/12" "20/10/12" "22/10/12" "23/11/12"


as.Date( short , format = "%d/%m/%y" )
#[1] "2012-11-23" "2012-10-20" "2012-10-22" "2012-11-23"

score 1 · Answer 5 · edited May 23 '17 at 11:45

1

If you really wanted to do it in regexp you should have used $ to signify that there was nothing (i.e. end of string) after the last two-digits numbers:

dates[grep('[0-9]{2}/[0-9]{2}/[0-9]{2}$', dates)]
[1] "23/11/12" "23/11/12"

Otherwise, in addition to the other answers you can have a look here and here for other ways of handling multiple date formats.

edited May 23 '17 at 11:45

Community

1
1

answered Oct 17 '13 at 11:27

plannapus

18,529
4
72
94

score 1 · Answer 6 · answered Oct 17 '13 at 13:21

Here's a base R way for the more general case not (yet) addressed in the unaccepted answers.

dates <- c("23-Jul-2013", "23/11/12", "20/10/2012", "22/10/2012" ,"23/11/12")
fmts <- list('%d-%b-%Y', '%d/%m/%y', '%d/%m/%Y')
d <- mapply(as.Date, list(dates), fmts, SIMPLIFY=FALSE)
max.d <- do.call(function(...) pmax(..., na.rm=TRUE), d)
min.d <- do.call(function(...) pmin(..., na.rm=TRUE), d)
max.d[max.d > Sys.Date()] <- min.d[max.d > Sys.Date()]
max.d
# [1] "2012-11-23" "2012-10-20" "2012-10-22" "2012-11-23"

Convert dd/mm/yy and dd/mm/yyyy to Dates

6 Answers6

Linked

Related