R fill missing values with the sum of other values using tidyverse

I used toy data from Anil Goyal (Thanks!)

There was a similar question today please see here: R: Replace NA with other variables in the df using tidyverse

Here we:

1. sum the rows
2. count the NA's
3. and with across with apply the desired condition on the col1-4
4. and the most part I love is .keep = "unused" which removes the "helper" columns.

df %>% 
  mutate(rowsum1 = rowSums(., na.rm=TRUE)) %>%
  mutate(count_na = rowSums(is.na(select(.,everything())))) %>% 
  mutate(across(starts_with("col"), ~case_when(count_na ==1 ~coalesce(.,rowsum1),
                                               TRUE ~ as.numeric(.))
                ), .keep ="unused"
         )

Output:

 col_1 col_2 col_3 col_4
1    35   421  1223   767
2    43    54   435    78
3   234    NA    NA    65
4   784     8   687    89
5    23    45    78   146

Two tidyverse approaches

#toy data
df <- data.frame(
  col_1 = c(35L, 43L, 234L, NA, 23L),
  col_2 = c(421L, 54L, NA, 8L, 45L),
  col_3 = c(NA, 435L, NA, 687L, 78L),
  col_4 = c(767L, 78L, 65L, 89L, NA)
)
df
#>   col_1 col_2 col_3 col_4
#> 1    35   421    NA   767
#> 2    43    54   435    78
#> 3   234    NA    NA    65
#> 4    NA     8   687    89
#> 5    23    45    78    NA

#load libraries
library(tidyverse)

#1st approach
df %>% mutate(across(everything(), ~ ifelse(rowSums(is.na(cur_data())) == 1 & is.na(.), rowSums(cur_data(), na.rm = T), .)))
#>   col_1 col_2 col_3 col_4
#> 1    35   421  1223   767
#> 2    43    54   435    78
#> 3   234    NA    NA    65
#> 4   784     8   687    89
#> 5    23    45    78   146

#2nd approach
df %>% rowwise() %>%
  mutate(replace(cur_data(), is.na(cur_data()) & rowSums(is.na(cur_data())) == 1, rowSums(cur_data(), na.rm = T)))
#> # A tibble: 5 x 4
#> # Rowwise: 
#>   col_1 col_2 col_3 col_4
#>   <int> <int> <int> <int>
#> 1    35   421  1223   767
#> 2    43    54   435    78
#> 3   234    NA    NA    65
#> 4   784     8   687    89
#> 5    23    45    78   146

^{Created on 2021-06-05 by the reprex package (v2.0.0)}

We can use coalesce with rowSums so as to make this more efficient

library(dplyr)
v1 <- rowSums(df, na.rm = TRUE) * NA^(rowSums(is.na(df)) != 1)
df[] <- coalesce(unlist(df),v1[row(df)])

-output

 df
  col_1 col_2 col_3 col_4
1    35   421  1223   767
2    43    54   435    78
3   234    NA    NA    65
4   784     8   687    89
5    23    45    78   146

Or may also write the above as

df %>%
     mutate(rS = rowSums(cur_data(), na.rm = TRUE) * 
        NA^(rowSums(is.na(cur_data())) != 1), 
        across(-rS, ~ coalesce(., rS)), rS = NULL)
#  col_1 col_2 col_3 col_4
#1    35   421  1223   767
#2    43    54   435    78
#3   234    NA    NA    65
#4   784     8   687    89
#5    23    45    78   146

You can also use the following solution, it is an alternative to replace function:

library(dplyr)
library(purrr)

df %>% 
  pmap_df(., ~ if(sum(is.na(c(...))) == 1) {
    coalesce(c(...), sum(c(...), na.rm = TRUE)) 
  } else {
    c(...)
  }
)

# A tibble: 2 x 4
  col_1 col_2 col_3 col_n
  <dbl> <dbl> <dbl> <dbl>
1    35    42     5     2
2   123    90    NA    NA

Data

structure(list(col_1 = c(35, 123), col_2 = c(NA, 90), col_3 = c(5, 
NA), col_n = c(2, NA)), class = "data.frame", row.names = c(NA, 
-2L))

A slight modification in the previous answer here to check only for 1 NA in a row -

df<- t(apply(df, 1, function(x) {
  if(sum(is.na(x)) == 1)  replace(x, is.na(x), sum(x, na.rm = TRUE))
  else x
}))

Or with purrr::pmap_df :

df <- purrr::pmap_df(df, ~{
  x <- c(...)
  if(sum(is.na(x)) == 1)  replace(x, is.na(x), sum(x, na.rm = TRUE))
  else x
})

An alternative solution that uses two intermediate variables:

• q_na number of NA per row
• s_row sum of the row values excluding NA.

library(tidyverse)

df <- tribble(
~col_1, ~col_2, ~col_3,
    NA,      1,      3,
    NA,     NA,      2,
     1,      5,      6,
    NA,     NA,     NA,
     3,     NA,      2)

df %>% 
  rowwise() %>% 
  mutate(q_na = sum(is.na(c_across(col_1:col_3))), 
    s_row = sum(c_across(col_1:col_3), na.rm = TRUE)) %>% 
  ungroup() %>% 
  filter(q_na == 1) %>% 
  mutate(across(col_1:col_3, ~if_else(is.na(.x), s_row, .x))) %>% 
  dplyr::select(col_1:col_3)

#> # A tibble: 2 x 3
#>   col_1 col_2 col_3
#>   <dbl> <dbl> <dbl>
#> 1     4     1     3
#> 2     3     5     2

If you want to get all rows, just delete the filter and include it in the if_else:

df %>% 
  rowwise() %>% 
  mutate(q_na = sum(is.na(c_across(col_1:col_3))), 
    s_row = sum(c_across(col_1:col_3), na.rm = TRUE)) %>% 
  ungroup() %>% 
  filter() %>% 
  mutate(across(col_1:col_3,
    ~if_else(q_na == 1 & is.na(.x), s_row, .x))) %>% 
  dplyr::select(col_1:col_3)

#> # A tibble: 5 x 3
#>   col_1 col_2 col_3
#>   <dbl> <dbl> <dbl>
#> 1     4     1     3
#> 2    NA    NA     2
#> 3     1     5     6
#> 4    NA    NA    NA
#> 5     3     5     2

^{Created on 2021-06-05 by the reprex package (v0.3.0)}