Replace NA value in column with modified date in other column

Question

I have the following dataset:

  A          B
2007-11-22 2004-11-18
      <NA> 2004-11-10

when the value of column A is NA, I want this value to be replaced by the date in B, except with an additional 25 days added. Here is what the outcome should look like:

   A          B
2007-11-22 2004-11-18
2004-12-05 2004-11-10

So far, I have tried the following if else formula, but with no success.

library(lubridate)
data$A<- ifelse(is.na(data$A),data$B+days(25),data$A)

Could anyone tell me what's wrong with it or give me an alternate solution? The code to build my dataset is below.

A<-c("2007-11-22 01:00:00", NA)
B<-c("2004-11-18","2004-11-10")
data<-data.frame(A,B)
data$A<-as.Date(data$A);data$B<-as.Date(data$B)

ThomasIsCoding · Accepted Answer · 2021-01-23T00:15:23.977

The reason of the issue can be traced back from the source code of ifelse. When you type View(ifelse), you will see some lines in the bottom of the source code as below

    ans <- test
    len <- length(ans)
    ypos <- which(test)
    npos <- which(!test)
    if (length(ypos) > 0L)
        ans[ypos] <- rep(yes, length.out = len)[ypos]
    if (length(npos) > 0L)
        ans[npos] <- rep(no, length.out = len)[npos]
    ans

where test is logic array, and ans is initialized as a copy of test. When running ans[ypos] <- rep(yes, length.out = len)[ypos], the class of ans is coerced to numeric, rather than Date. That's why you have integers on A column after using ifelse.

You can try the code below

data$A <- as.Date(ifelse(is.na(data$A), data$B + days(25), data$A), origin = "1970-01-01")

which gives

> data
           A          B
1 2007-11-22 2004-11-18
2 2004-12-05 2004-11-10

score 0 · Answer 2 · answered Jan 23 '21 at 00:01

Assuming the data given reproducibly in the Note at the end -- in particular we assume both columns are of Date class -- compute a logical vector is_na which indicates which entries are NA and then set those from B.

is_na <- is.na(data$A)
data$A[is_na] <- data$B[is_na] + 25

This would also work and has the advantage that it does not overwrite data:

transform(data, A = replace(A, is.na(A), B[is.na(A)] + 25))

Note

Lines <- "
         A          B
2007-11-22 2004-11-18
        NA 2004-11-10"
data <- read.table(text = Lines, header = TRUE)
data[] <- lapply(data, as.Date)  # convert to Date class

score 0 · Answer 3 · answered Jan 23 '21 at 00:03

Instead of ifelse you could use coalesce

library(tidyverse)
library(lubridate)

A <- c("2007-11-22 01:00:00", NA)
B <- c("2004-11-18","2004-11-10")
data <-data.frame(A,B)

data <- data %>%
  mutate(A = as_date(A),
         B = as_date(B),
         A = coalesce(A,B+days(25)))

Replace NA value in column with modified date in other column

3 Answers3

Note