unexpected error - format datetime into mutate

Question

I was following the clue: Mutate and format multiple date columns

The solution results in an error

M601_M_multi.2 <- M601_M_multi %>% 
  mutate (
    across(ends_with(' Time'), ~format(as.POSIXct(.), "%d.%m.%Y %H:%M:%S")),
                 .names = "{.col}.used")   
  )

Error in `mutate()`:
ℹ In argument: `across(contains(" Time"), ~format(as.POSIXct(.), "%d.%m.%Y %H:%M:%S"))`.
Caused by error in `across()`:
! Can't compute column `C3_S1_O2_PV Time`.
Caused by error in `as.POSIXlt.character()`

In df I have numerous columns where the name ends with " Time". I want to convert all to date and time (as.POSIXct)

The single column solution works though

M601_M_multi.2 <- M601_M_multi %>% 
  mutate (
    test = as.POSIXct(`C3_S1_O2_PV Time`, format ="%d.%m.%Y %H:%M:%S")    
  )

Any suggestion and answer will be appreciated

The single column operation is different ... Can you please post some data e.g. by using `dput(head(M601_M_multi))` — Andre Wildberg, Aug 03 '23 at 19:29

score 2 · Accepted Answer · answered Aug 03 '23 at 19:40

Your code blocks are not equivalent.

  mutate (
    across(ends_with(' Time'), ~format(as.POSIXct(.), "%d.%m.%Y %H:%M:%S")),
                 .names = "{.col}.used")   
  )

The code sequence:

as.POSIXct(x), using the default format=, which is to try some unambiguous formats embedded within the function. Notably missing from the list of tryFormats= is anything including "%d.%m.%Y ...".
Convert back to a string with format(y, format="%d.%m.%Y %H:%M:%S"). This assumes that y here already inherits from POSIXt, so that this makes sense. This expression should result in strings (character class), not number-like timestamps (POSIXt class).

In your second block:

  mutate (
    test = as.POSIXct(`C3_S1_O2_PV Time`, format ="%d.%m.%Y %H:%M:%S")    
  )

The code sequence:

as.POSIXct(x, format="%d.%m.%Y %H:%M:%S") is correct (I think) in that you know that x here is not in an unambiguous format, so you are telling as.POSIXct how to find specific components of the timestamp. This should result in a number-like POSIXt-class object.

I think you need your code to be:

  mutate(
    across(ends_with(' Time'), ~ as.POSIXct(., format="%d.%m.%Y %H:%M:%S"),
           .names = "{.col}.used")   
  )

which results in new columns (with .used appended to the names) filled with POSIXt-class values.

unexpected error - format datetime into mutate

1 Answers1