How to mutate NA on multiple rows (rowwise) in tibble

Question

I spend sometime try to figure out how to mutate NA values on multiple rows on row perspective in tibble, the tibble has 3 observations and 6 variables, generate below:

df <- data.frame(ID = c(1, 2, 3),
                 Score1 = c(90, 80, 70),
                 Score2 = c(66, 78, 86),
                 Score3 = c(NA, 86, 96),
                 Score4 = c(84, 76, 72),
                 Score5 = c(92, NA, 74))
sample_tibble <- as_tibble(df)

The tibble looks as

# A tibble: 3 x 6
     ID Score1 Score2 Score3 Score4 Score5
  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1     1     90     66     NA     84     92
2     2     80     78     86     76     NA
3     3     70     86     96     72     74

I have to use functions from tidyverse (e.g mutate, mutate_at, rowwise.. etc.), the target is to replace the NA on row 1 (in Score3 column) and row 2 (in Score5 column) with the mean of row 1 and row 2 respectively (mean calculated with other values on row rather than NA), so the ideal result should be after mutate

# A tibble: 3 x 6
     ID Score1 Score2 Score3 Score4 Score5
  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
1     1     90     66     83     84     92
2     2     80     78     86     76     80
3     3     70     86     96     72     74

The first NA replace by mean(c(90, 66, NA, 84, 92), na.rm = TRUE) as 83
The second NA replace by mean(c(80, 78, 86, 76, NA), na.rm = TRUE) as 80

Tried some code like below, and also check previous doc as Apply a function to every row of a matrix or a data frame or dplyr - using mutate() like rowmeans(), but the code never work since I am able to figure out body of mutate function

sample_tibble[, -1] %>% rowwise() %>% mutate(...)

Not limited on rowwise or mutate (such as mutate_at also good), is there any solution able to mutate row 1 and row 2 to reach the target format (Its great to mutate at same time, not as use for loop to mutate twice), appreciate any solutions !

r2evans · Accepted Answer · 2020-02-08T21:04:54.803

A slightly inefficient way would be to gather and group_by it:

sample_tibble %>%
  tidyr::gather(k, v, -ID) %>%
  group_by(ID) %>%
  mutate(v = if_else(is.na(v), mean(v, na.rm = TRUE), v)) %>%
  ungroup() %>%
  tidyr::spread(k, v)
# # A tibble: 3 x 6
#      ID Score1 Score2 Score3 Score4 Score5
#   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
# 1     1     90     66     83     84     92
# 2     2     80     78     86     76     80
# 3     3     70     86     96     72     74

As RonakShah also reminded me, gather/spread can be replaced with the newer (and more featureful) cousins: pivot_longer/pivot_wider.

Another technique uses apply:

sample_tibble %>%
  mutate(mu = apply(.[,-1], 1, mean, na.rm = TRUE)) %>%
  ### similarly, and faster, thanks RonakShah
  # mutate(mu = rowMeans(.[,-1], na.rm = TRUE)) %>%
  mutate_at(vars(starts_with("Score")), ~ if_else(is.na(.), mu, .)) %>%
  select(-mu)

A caveat with this: the .[,-1] is explicitly using every column except the first; if you have other columns that were not mentioned in the question, then this will certainly use more data than you intend. Unfortunately, I don't know of a way to use :-ranging in this solution, as that would be clearer.

score 1 · Answer 2 · answered Feb 08 '20 at 08:31

One approach utilizing a little bit of maths could be:

df %>%
 mutate_at(vars(-1), 
           ~ pmax(is.na(.)*rowMeans(select(df, -1), na.rm = TRUE), 
                  (!is.na(.))*., 
                  na.rm = TRUE))


  ID Score1 Score2 Score3 Score4 Score5
1  1     90     66     83     84     92
2  2     80     78     86     76     80
3  3     70     86     96     72     74

How to mutate NA on multiple rows (rowwise) in tibble

2 Answers2