0

I have a dataset containing a date in CHAR format that I want to convert to POSIXct. The dates are always in one of five formats, but for this example two will suffice:

test <- c("15/03/19 17:16", "15/03/19,17:16", "15/03/19,17:16")
formats <- c(
    "%d/%m/%y,%H:%M",
    "%d/%m/%y %H:%M",
    "%d/%m/%Y,%H:%M",
    "%d/%m/%Y %H:%M",
    "%Y%m%d%H%M%S"
)

as.POSIXct(test[1], tz = "GMT", tryFormats = formats) # works
as.POSIXct(test[2:3], tz = "GMT", tryFormats = formats) # works
as.POSIXct(test, tz = "GMT", tryFormats = formats)    # fails

Individually, the two dates convert without issue. When the vector (or in my case, the datatable via mutate) is put through as.POSIXct the following error is generated:

Error in as.POSIXct.character(x, tz, ...) : character string is not in a standard unambiguous format

Presumably as.POSIXct is picking only one of the formats in tryFormat and trying to apply that to the whole set, which won't work.

Is my only option here creating a blank column and manually looping through each individual row to populate it?

Example dates:

15/03/19 17:16
15/03/19,17:16
15/03/2019 17:16
15/03/2019,17:16
20190315171600GS
Deadly-Bagel
  • 1,612
  • 1
  • 9
  • 21

1 Answers1

2

Try lubridate::dmy_hm which works for both. Also your formats needs correction I believe.

formats <- c("%d/%m/%y,%H:%M", "%d/%m/%y %H:%M")

lubridate::dmy_hm(test)
#[1] "2019-03-15 17:16:00 UTC" "2019-03-15 17:16:00 UTC" "2019-03-15 17:16:00 UTC"

Or with anytime

anytime::addFormats(formats)
anytime::anytime(test)

If we need to specify formats explicitly, we may also use parse_date_time

lubridate::parse_date_time(test, formats)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Need to be able to specify the formats because one is a little obscure, eg `20181130223406GS` which is formatted `"%Y%m%d%H%M%S"`. I removed all the existing formats in anytime and added just the five I have, but it's still parsing wrong. Interestingly the other dates also come up as 18:16, but that is likely a setting somewhere. – Deadly-Bagel Aug 21 '19 at 11:41
  • @Deadly-Bagel Can you check the update with `parse_date_time`. Also I think you need `%y` instead of `%Y` in your `formats` since it is only 2 digit year. – Ronak Shah Aug 21 '19 at 11:45
  • Some are two digits and some are four digits, between that and the comma it accounts for four of the formats (added example of each to question). Yes, the parse_date_time does indeed do the trick, overlooked that somehow. Thanks! – Deadly-Bagel Aug 21 '19 at 11:48