How to apply excel_numeric_to_date inside a customized function?

user_10292638 0
Viewed 168

I am trying to apply a customized function to a column of excel imported dates.

I apply the next function:

library(janitor)

fun_excel_date <- function(x){
  if(is.numeric(x)){
  excel_numeric_to_date(as.numeric(
    as.character(x)
  ), date_system = "modern")}
  
  else {
    return(NA)
  }
  
}

do.call(rbind, lapply(some_dummy_dates$date, fun_excel_date))

Console output:

#     [,1]
#[1,] 3967
#[2,] 7783
#[3,] 6028
#[4,] 4479

When applied over one element the function works fine, say excel_numeric_to_date(as.numeric(as.character(29536)), date_system = "modern") as it returns "1980-11-11".

However when applied over an entire column the function returns an unexplained numeric output. The problem persists even changing x argument to x <- as.Date(x, origin="1899-12-30") inside the function.

data

some_dummy_dates <-structure(list(date = c(29536, 33352, 31597, 30048)), class = "data.frame", row.names = c(NA, 
-4L))

Am I missing something inside the function? Is there any other approach?

janitor r excel date function
1 Answers

Date class is internally stored as integer. So, if we want to avoid the coercion to integer, use c

do.call(c, lapply(some_dummy_dates$date, fun_excel_date))
#[1] "1980-11-11" "1991-04-24" "1986-07-04" "1982-04-07"

Also, conversion to matrix will drop the Date class and changes it to integer

matrix(do.call(c, lapply(some_dummy_dates$date, fun_excel_date)))
#     [,1]
#[1,] 3967
#[2,] 7783
#[3,] 6028
#[4,] 4479

If we want to store it in an object, either store as a vector or create a data.frame/tibble/data.table which can have attributes

data.frame(Date = do.call(c, lapply(some_dummy_dates$date, fun_excel_date)))
#     Date
#1 1980-11-11
#2 1991-04-24
#3 1986-07-04
#4 1982-04-07

Or use the function with rowwise

library(dplyr)
some_dummy_dates %>%
   rowwise %>%
   mutate(Date = fun_excel_date(date)) %>%
   ungroup

The OP's function uses if/else which are not vectorized and that is the reason it takes only a single element and convert it. This can be vectorized by simulatenously checking whether the column is numeric (within across) and then creating/modifying the column by applying the excel_numeric_to_date

library(janitor)
some_dummy_dates %>% 
    mutate(across(where(is.numeric), 
       ~ excel_numeric_to_date(., date_system = 'modern'), .names = "{.col}_new"))
#   date   date_new
#1 29536 1980-11-11
#2 33352 1991-04-24
#3 31597 1986-07-04
#4 30048 1982-04-07
user_37322710
Related