R: Replace NA with other variables in the df using tidyverse

user_12620237 0
Viewed 237

I want to replace the NA values in my df using tidyverse, the values I want should be caculated from other cols:

input:

ID,    X1,    X2,    X3,
"A",  0.96,   NA,    0.97,
"B",  1.00,   NA,    1.01,
"C",  0.98,   0.03,  NA,
"A",  1.00,   NA,    1.00,
"D",  NA,     0.05,  0.99,

My wish is to find in each row which variable of all three X1, X2, X3 is NA and to calcualte it with the sum of the other two variables

output:

ID,    X1,    X2,    X3,
"A",  0.96,   1.93,  0.97,
"B",  1.00,   2.01,  1.01,
"C",  0.98,   0.03,  1.01,
"A",  1.00,   2.00,   1.00,
"D",  1.04,   0.05,  0.99,

Thanks!

tidyverse r
4 Answers

Replace NA value with sum of other non-NA value in the row.

This can be done using base R apply -

df[-1] <- t(apply(df[-1], 1, function(x) 
             replace(x, is.na(x), sum(x, na.rm = TRUE))))
df

#  ID   X1   X2   X3
#1  A 0.96 1.93 0.97
#2  B 1.00 2.01 1.01
#3  C 0.98 0.03 1.01
#4  A 1.00 2.00 1.00
#5  D 1.04 0.05 0.99

and similarly with purrr::pmap_df -

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

Very nice question. Thank you!

Here we use:

  1. rowSums to add a column with the row sums of X1-X3
  2. then we mutate across all X and
  3. coalesce each X with rowSum1
  4. surprisingly rowSum1 column is gone away because not needed ->
  5. this is due to the fantastic .keep="unused" argument of mutate
library(tidyverse)
df %>% 
  mutate(rowsum1 = rowSums(select(., starts_with("X")), na.rm=TRUE)) %>% 
  mutate(across(starts_with("X"), ~coalesce(.,rowsum1)),.keep="unused")

Output:

  ID       X1    X2    X3
  <chr> <dbl> <dbl> <dbl>
1 A      0.96  1.93  0.97
2 B      1     2.01  1.01
3 C      0.98  0.03  1.01
4 A      1     2     1   
5 D      1.04  0.05  0.99
user_133216470

Similar to Ronak Shah's purrr version, but keeping the ID column is this:

library(purrr)

pmap_df(df, \(...) {
  vars <- list(...)
  map(vars, ~ ifelse(is.na(.), do.call(sum, c(vars[-1], na.rm = T)), .))
})
user_70100560

One more approach using dplyr::cur_data()

df %>% rowwise() %>%
  mutate(replace(cur_data()[-1], is.na(cur_data()[-1]), sum(cur_data()[-1], na.rm = T)))

# A tibble: 5 x 4
# Rowwise: 
  ID       X1    X2    X3
  <chr> <dbl> <dbl> <dbl>
1 A      0.96  1.93  0.97
2 B      1     2.01  1.01
3 C      0.98  0.03  1.01
4 A      1     2     1   
5 D      1.04  0.05  0.99

OR without using rowwise and using across instead

df %>% mutate(across(starts_with('X'), ~ ifelse(is.na(.), rowSums(cur_data()[-1], na.rm = T), .)))

  ID   X1   X2   X3
1  A 0.96 1.93 0.97
2  B 1.00 2.01 1.01
3  C 0.98 0.03 1.01
4  A 1.00 2.00 1.00
5  D 1.04 0.05 0.99
user_28848590
Related