CODEWITHSUNDEEP
r
snr999
snr999

Reputation: 125

how to convert many date-time formats to one format

I have a column as dt in my Dataframe as below

dt

01-Jan-2017 12:00:00
12/01/2017   01:15:00
13-Sep-97    21:00:00
20 August 2017 22:00:00
12/19/17   01:15:00
2/4/2017    05:18:00

How to get R date time format in R_dt column

   dt                         R_dt
01-Jan-2017 12:00:00         2017-01-01 12:00:00
12/01/2017   01:15:00        2017-12-01 01:15:00
13-Sep-97    21:00:00        1997-09-13 21:00:00
20 August 2017 22:00:00      2017-08-20 22:00:00
12/19/17   01:15:00          2017-12-19 01:15:00
2/4/2017    05:18:00         2017-02-04 05:18:00

I have tried using - strftime(mydf$date,"%d/%m/%Y")- which throws error.

Upvotes: 0

Views: 62

Answers (1)

Dirk is no longer here
Dirk is no longer here

Reputation: 368599

You have multiple formats, you need a function that automagically tries multiple formats. anytime() from my anytime package is one such function:

R> dvec <- c("01-Jan-2017 12:00:00", "12/01/2017 01:15:00", "13-Sep-97 21:00:00", 
=            "20 August 2017 22:00:00", "12/19/17 01:15:00", "2/4/2017 05:18:00")
R> dvec
[1] "01-Jan-2017 12:00:00"    "12/01/2017 01:15:00"    
[3] "13-Sep-97 21:00:00"      "20 August 2017 22:00:00"
[5] "12/19/17 01:15:00"       "2/4/2017 05:18:00"      
R> anytime(dvec)
[1] "2017-01-01 12:00:00 CST" "2017-12-01 01:15:00 CST"
[3] NA                        "2017-08-20 22:00:00 CDT"
[5] NA                        NA                       
R>

You see that three fail: two use a two-digit year (format %y) which we don't support by default -- but for which we offer the ability to add formats via addFormat() so one can address this:

R> addFormats(c("%d-%b-%y %H:%M:%S", "%m/%d/%y %H:%M:%S"))
R> anytime(dvec)
[1] "2017-01-01 12:00:00 CST" "2017-12-01 01:15:00 CST"
[3] "2097-09-13 21:00:00 CDT" "2017-08-20 22:00:00 CDT"
[5] "2017-12-19 01:15:00 CST" NA                       
R>

The last one fails because of single digit month and day -- and that is not parsed by the Boost code we use so I have no (easy) fix for it.

However, for reasons related to testing and comparison, we now also allow use of R's internal parser (via my package RApiDatetime) so you possible can post-process those:

R> anytime(dvec[6], useR=TRUE)
[1] "2017-02-04 05:18:00 CST"
R>

Upvotes: 2

Related Questions