how to convert df character columns inside a list to an specific date format?-CodePudding

Suppose the next df with character date columns inside a list:

df<- data.frame(dates=c("2021-12-31 UTC", "2021-12-27 UTC", "2021-12-26 UTC", NA),
                another_column_with_dates=c("2021-11-21 UTC", 
                                                 "2021-12-12", "2021-11-01 UTC", NA))

list_of_df <- list(df=df)

Then I got a function that removes "UTC" and also whitespaces, then it converts the dates into an specific format: "%d/%m/%Y:

convert_columns_to_date <- function(x){
   
   if(is.na(x)){
     
     return(NA)
   }
   
   else if(!is.na(x)){
   # remove white-spaces and extra strings
   x<- trimws(gsub("UTC", "", x))
   
   # date format: %d/%m/%Y
   x<- format(as.Date(x), "%d/%m/%Y")
   
   return(x)
  
   
   }
   
  else {
    return(NA)
  }
   
   
 }

The function works well with a single input, say convert_columns_to_date("2021-11-01 UTC") returns the right format "01/11/2021".

However when the function is applied over all columns in the df inside a list:

 
 date_columns_input <- c('dates', 'another_column_with_dates')
 

 for(i in 1:length(list_of_df[["df"]][date_columns_input])){
   
   
   list_of_df[["df"]][i]<- convert_columns_to_date(list_of_df["df"][i])
   
 }

The next error arises:

Error in charToDate(x) : character string is not in a standard unambiguous format

Why is this error happening? And is there any way to solve it?

CodePudding user response：

1) lapply Use format_vec as shown and then lapply over the ix columns within df. Note that as.Date removes junk at the end already and it is usually better not to overwrite objects so that it is easy to re-run without regenerating the input. ix should be specified as a vector of column numbers or names.

ix <- 1:2
format_vec <- function(x) format(as.Date(x), format = "%d/%m/%Y")
L <- list_of_df
L$df[ix] <- lapply(L$df[ix], format_vec)
L

giving:

$df
       dates another_column_with_dates
1 31/12/2021                21/11/2021
2 27/12/2021                12/12/2021
3 26/12/2021                01/11/2021
4       <NA>                      <NA>

2) loop If you prefer to use a loop then:

ix <- 1:2
L <- list_of_df
for(i in ix) L$df[[i]]<- format_vec(L$df[[i]])

3) dplyr

library(dplyr)

L <- list_of_df
L$df <- L$df %>% mutate(across(1:2, format_vec))

4) collapse

library(collapse)

L <- list_of_df
L$df <- ftransformv(L$df, 1:2, format_vec)