Caused by error in `as.POSIXlt.character()`: ! character string is not in a standard unambiguous format. How can I change CHR to POSIXCT?

Question

I have a huge data frame called 'cyclist_trip_data_all'

head(cyclist_trip_data_all)

The most important columns of dates have chr class and I need it to be converted to POSIXct for calculations.

combined_ctd <-
  mutate(cyclist_trip_data_all, ride_length =
           difftime(ended_at,started_at,units='mins'))

The above throws the unambiguous format error at us and I'm pretty sure because the two columns are chr class.

cyclist_trip_data_all %>%
    transmute(ended_at = as.POSIXct(ended_at,tz="",tryFormats=
                                    c("%Y-%m-%d %H:%M:%OS",
                                      "%Y/%m/%d %H:%M:%OS",
                                      "%Y-%m-%d %H:%M",
                                      "%Y/%m/%d %H:%M",
                                      "%Y-%m-%d",
                                      "%Y/%m/%d")))
head(cyclist_trip_data_all$ended_at)

Using the above code throws similar unambiguous format error:

Error in `transmute()`:
! Problem while computing `ended_at = as.POSIXct(...)`.
Caused by error in `as.POSIXlt.character()`:
! character string is not in a standard unambiguous format
Traceback:

1. cyclist_trip_data_all %>% transmute(ended_at = as.POSIXct(ended_at, 
 .     tz = "", tryFormats = c("%Y-%m-%d %H:%M:%OS", "%Y/%m/%d %H:%M:%OS", 
 .         "%Y-%m-%d %H:%M", "%Y/%m/%d %H:%M", "%Y-%m-%d", "%Y/%m/%d")))
2. transmute(., ended_at = as.POSIXct(ended_at, tz = "", tryFormats = c("%Y-%m-%d %H:%M:%OS", 
 .     "%Y/%m/%d %H:%M:%OS", "%Y-%m-%d %H:%M", "%Y/%m/%d %H:%M", 
 .     "%Y-%m-%d", "%Y/%m/%d")))
3. transmute.data.frame(., ended_at = as.POSIXct(ended_at, tz = "", 
 .     tryFormats = c("%Y-%m-%d %H:%M:%OS", "%Y/%m/%d %H:%M:%OS", 
 .         "%Y-%m-%d %H:%M", "%Y/%m/%d %H:%M", "%Y-%m-%d", "%Y/%m/%d")))
4. mutate_cols(.data, dots, caller_env = caller_env())
5. withCallingHandlers({
 .     for (i in seq_along(dots)) {

I've tried as.POSIXct(as.numeric(as.character())) function instead but it changes every date values in the column into NA introduced by coersion.

Also tried:

cyclist_trip_data_all$ended_at <- as.POSIXct(cyclist_trip_data_all$ended_at,format="%Y-%m-%d %H:%M:%S",tz="UTC")
cyclist_trip_data_all$started_at <- as.POSIXct(cyclist_trip_data_all$ended_at,format="%Y-%m-%d %H:%M:%S",tz="UTC")

And it also changes every value to NA.

How do I actually change it to a POSIXct class without it becoming NA?

To note, this error doesn't happen in RStudio but it's happening in my Kaggle notebook for R.

Answer 1

There are alternatives -- at some point I found having to chase (known) formats to be too repetitive and boring and wrote a package that does it for me (and fast):

> library(anytime)
> datevec <- c("5/30/2021 11:58", "5/30/2021 12:10", "5/30/2021 11:29")
> anytime(datevec)
[1] "2021-05-30 11:58:00 CDT" "2021-05-30 12:10:00 CDT" "2021-05-30 11:29:00 CDT"
> 

The example just shows the first three of your (non-reproducibly presented) dates. The package has other functions too for converting dates, or specific timezones as well as formatters. Take a look: anytime at CRAN -- and yes it of course also works in pipes and with other packages and whatnot. It "just" aims to take care of converting 'any time or date in any format' to POSIXct or Date.