Replace NAs with missing values in sequence (R)

Question

I have a DF like

Now I want to replace The Col B = NA with 15 since that is the missing value. Col C first NA with 14 and second NA with 15. Col D first NA with 13, second NA with 14 and third NA with 15. So the numbers follow a sequence up to down or down to up.

Reproducible Sample Data

structure(list(`Col A` = c(11, 12, 13, 14, 15), `Col B` = c(NA, 
11, 12, 13, 14), `Col C` = c(NA, NA, 11, 12, 13), `Col D` = c(NA, 
NA, NA, 11, 12)), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

Answer 1

I think you can use the following solution in tidyverse:

library(dplyr)
library(purrr)

df[1] %>%
  bind_cols(map_dfc(2:length(df), function(x) {
    df[[x]][which(is.na(df[[x]]))] <- setdiff(df[[1]], df[[x]][!is.na(df[[x]])])
    df[x]
  }))

# A tibble: 5 x 4
  `Col A` `Col B` `Col C` `Col D`
    <dbl>   <dbl>   <dbl>   <dbl>
1      11      15      14      13
2      12      11      15      14
3      13      12      11      15
4      14      13      12      11
5      15      14      13      12

Or in base R we could do:

do.call(cbind, Reduce(function(x, y) {
  i <- which(is.na(df[[y]]))
  df[[y]][i] <- sort(setdiff(x, df[[y]]))
  df[[y]]
}, init = df[[1]], 2:length(df), accumulate = TRUE)) |>
  as.data.frame() |>
  setNames(paste0("Col", LETTERS[1:length(df)]))

  ColA ColB ColC ColD
1   11   15   14   13
2   12   11   15   14
3   13   12   11   15
4   14   13   12   11
5   15   14   13   12