Best way to convert to date in R when month is a 3 character variable?

user_12851323 0
Viewed 184

I have some odd looking variables which I need to convert into a Date class:

 dates <- c("  26JUL2018:23:59:59", "  02APR2018:23:59:59", "  02MAY2018:23:59:59", 
    "  22APR2018:23:59:59", "  27MAY2018:23:59:59", "  04MAR2018:23:59:59", 
    "  10APR2018:23:59:59", NA, "  04SEP2018:23:59:59", "  21APR2017:23:59:59"
    )

> dates
 [1] "  26JUL2018:23:59:59" "  02APR2018:23:59:59" "  02MAY2018:23:59:59"
 [4] "  22APR2018:23:59:59" "  27MAY2018:23:59:59" "  04MAR2018:23:59:59"
 [7] "  10APR2018:23:59:59" NA                     "  04SEP2018:23:59:59"
[10] "  21APR2017:23:59:59"

I tried the following which just produces NAs:

as.POSIXct(dates, format="  %d%m%Y:%H:%M:%S")
as.POSIXct(dates, format="  %d%mm%Y:%H:%M:%S")

I also tried removing the spaces using gsub, then using format="%d%m%Y:%H:%M:%S" and format="%d%mm%Y:%H:%M:%S" but neither worked.

What is the correct way to format this vector into class Date?

r
2 Answers

You can use %b as Abbreviated month name in the current locale on this platform. (Also matches full name on input: in some locales there are no abbreviations of names.)

as.POSIXct(dates, format="%d%b%Y:%H:%M:%S")
# [1] "2018-07-26 23:59:59 CEST" "2018-04-02 23:59:59 CEST"
# [3] "2018-05-02 23:59:59 CEST" "2018-04-22 23:59:59 CEST"
# [5] "2018-05-27 23:59:59 CEST" "2018-03-04 23:59:59 CET" 
# [7] "2018-04-10 23:59:59 CEST" NA                        
# [9] "2018-09-04 23:59:59 CEST" "2017-04-21 23:59:59 CEST"
user_104885040

A lubridate one-liner using dmy_hms (day-month-year/hour-minute-second)

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

dmy_hms(dates)
#>  [1] "2018-07-26 23:59:59 UTC" "2018-04-02 23:59:59 UTC"
#>  [3] "2018-05-02 23:59:59 UTC" "2018-04-22 23:59:59 UTC"
#>  [5] "2018-05-27 23:59:59 UTC" "2018-03-04 23:59:59 UTC"
#>  [7] "2018-04-10 23:59:59 UTC" NA                       
#>  [9] "2018-09-04 23:59:59 UTC" "2017-04-21 23:59:59 UTC"

If you want a different timezone, use the tz argument.

user_84854030
Related