lubridate::as_date() inside dplyr::

[abrahamazar]

Hey,

I am facing a weird situation with lubridate::as_date() inside a mutate Dplyr function.
I am trying to change dates from an Excel format to a date format.
The format I have is "45369", and I am trying to change the today column to "yyyy-mm-dd".
Taking into consideration the column can be any column in the data, the user will pick the name of the column through use input and be saved as [date_dc <- "user_input"].
Now, trying this format:
raw.main <- raw.main %>%
dplyr::mutate_at(vars(date_dc), ~ifelse(is.na(.), NA,lubridate::as_date(as.numeric(.), origin = "1899-12-30")))
return 19800 in all the column
Through a direct assignment, it returns:
raw.main[[date_dc]] <- lubridate::as_date(as.numeric(raw.main[[date_dc]]), origin = "1899-12-30")

Any idea what is the issue here?
Abraham
Sorry, was trying to upload more images, but I am a new user and posit didn't let me.

[abrahamazar]

xx <- readxl::read_excel("test_raw_main.xlsx")
library(dplyr)
 
y <- function(raw.main, date_dc) {
  raw.main[[date_dc]] <- lubridate::as_date(as.numeric(raw.main[[date_dc]]), origin = "1899-12-30")
  return(raw.main)
}
 
y2 <- function(raw.main, date_dc){
  raw.main <- raw.main %>%
    mutate(!!rlang::sym(date_dc) := ifelse(is.na(.),NA,lubridate::as_date(as.numeric(.), origin = "1899-12-30")))
  return(raw.main)
}
 
test1 <- y(xx, "today")
test2 <- y2(xx,"today")
 
y3 <- function(raw.main, date_dc){
  raw.main <- raw.main %>%
    mutate(!!rlang::sym(date_dc) := if_else(is.na(!!rlang::sym(date_dc)),
                                           NA,
                                           lubridate::as_date(as.numeric(!!rlang::sym(date_dc)), origin = "1899-12-30")))
  return(raw.main)
}
test3 <- y3(xx, "today")
 
all.equal(test1$today, test3$today)
test1$today
test3$today
 
y4 <- function(raw.main, date_dc){
  raw.main[[date_dc]] <- ifelse(is.na(raw.main[[date_dc]]),NA,lubridate::as_date(as.numeric(raw.main[[date_dc]]), origin = "1899-12-30"))
  return(raw.main)
}
 
test4 <- y4(xx, "today")
test4$today

Just to add, we found that the issue was coming from ifelse (base).
Using the dplyr::if_else was fine

[dromano]

Hi Abraham,

@abrahamazar

Sorry, was trying to upload more images, but I am a new user and posit didn't let me.

Thanks OK, and it's actually better not to post images, but post the data you're working with instead, between a pair of triple backticks, like this:

``` r
<-- paste output of dput(head(raw.main)) here
```

[dromano]

So do you still have an issue, or have you figured it out?

[abrahamazar]

I guess we figured it out, but was wondering why ifelse (base) was throwing this error.

[dromano]

Could you share the data you were using, as I suggested here?

[abrahamazar]

I am scared the data I am working with I cannot share.
However, here is a quick mock data for the sake of the test.

library(dplyr)
library(lubridate)
mock_data <- data.frame(start = c(45369,45369,45369,45369),
                        end = c(45369,45369,45369,45369),
                        today = c(45369,45369,45369,45369))

## test_v1
test_v1 <- mock_data %>%
  mutate(today = ifelse(is.na(today), NA, lubridate::as_date(today, origin = "1899-12-30")))

view(test_v1)

## test_v2
test_v2 <- mock_data %>%
  mutate(today = if_else(is.na(today), NA, lubridate::as_date(today, origin = "1899-12-30")))

view(test_v2)

[dromano]

@abrahamazar

I am scared the data I am working with I cannot share.

That's fine — sharing mock data that replicates the issue is actually better since it can help to highlight both misunderstanding and what might be going wrong. Thanks for sharing it.

[dromano]

It turns out that ifelse() strips the true and false vectors of their attributes, so that in the case of dates, we see the equivalent of this:

lubridate::as_date("1899-12-30") |> 
  print() |> 
  as.numeric()
#> [1] "1899-12-30"
#> [1] -25569

^{Created on 2024-05-06 with reprex v2.0.2}