dplyr's rowwise + replace_NAs: replacing NAs in multiple columns with value from other column

user_5976462 0
Viewed 140

I am interested in replacing all NAs in a row with a reference value from that same row. The following code works, but too slowly for my actual use case:

df = data.frame(ref = c(1,3,4,2,5), var1 = c(NA,3,4,2,1), var2 = c(1,3,NA,5,5))
for (i in 1:nrow(df)) {
  ref_value = as.character(df$ref[i])
  df[i,] = df[i,] %>% mutate_all(replace_na, replace = ref_value) }

I tried to speed up the process with the following modification and got the error below.

df = data.frame(ref = c(1,3,4,2,5), var1 = c(NA,3,4,2,1), var2 = c(1,3,NA,5,5))
df %>% rowwise() %>% mutate_all(replace_na, replace = ref)

error:

Error: Problem with mutate() input ref. x Replacement for data is length 2, not length 1 ℹ Input ref is (function (data, replace, ...) .... ℹ The error occured in row 1.

Any suggestions?

dplyr r
2 Answers

We can use coalesce. Loop over the 'var' columns and use coalesce with the looped column and 'ref', so that wherever there is NA, it gets replaced by the corresponding element from 'ref', while the other non-NA element remain as such

library(dplyr) #>= 1.0.0
df %>% 
     mutate(across(starts_with('var'), ~ coalesce(., ref)))
#   ref var1 var2
#1   1    1    1
#2   3    3    3
#3   4    4    4
#4   2    2    5
#5   5    1    5
user_37322710

This can also works:

library(dplyr)

df %>%
  mutate(across(c(var1:var2),~ ifelse(is.na(.), ref, .)))

Output:

  ref var1 var2
1   1    1    1
2   3    3    3
3   4    4    4
4   2    2    5
5   5    1    5
user_20808480
Related