CODEWITHSUNDEEP
r
Hakki
Hakki

Reputation: 1472

R replace missing values if all are missing within a group

I would like to replace missing value from value in another column, if all values within a group are missing. Here is example and something I thought would work. There can be unlimited amount of groups.

library(tidyverse)

df <- tibble(ID = c("A", "A", "A", "B", "B", "B"),
       val1 = c(1,2,3,4,5,6),
       val2 = c(NA, NA, NA, NA, 2, 3))

df %>%
group_by(ID) %>%
mutate(val2 = ifelse(all(is.na(val2)), val1, val2))

# Groups:   ID [2]
  ID     val1  val2
  <chr> <dbl> <dbl>
1 A         1     1
2 A         2     1
3 A         3     1
4 B         4    NA
5 B         5    NA
6 B         6    NA

What I would like to get is val2 should get values from val1, if all val2 values are missing within group. Now it seems that it is giving me the first value. Nothing should happen if all are not missing.

Result:
# A tibble: 6 x 3
  ID     val1  val2
  <chr> <dbl> <dbl>
1 A         1     1
2 A         2     2
3 A         3     3
4 B         4    NA
5 B         5     2
6 B         6     3

Upvotes: 0

Views: 422

Answers (2)

Karthik S
Karthik S

Reputation: 11546

Does this work:

library(dplyr)
df %>% group_by(ID) %>% mutate(val2 = case_when(all(is.na(val2)) ~ val1, TRUE ~ val2))
# A tibble: 6 x 3
# Groups:   ID [2]
  ID     val1  val2
  <chr> <dbl> <dbl>
1 A         1     1
2 A         2     2
3 A         3     3
4 B         4    NA
5 B         5     2
6 B         6     3

Upvotes: 2

MonJeanJean
MonJeanJean

Reputation: 2906

You almost had it. I create an indicator which is used to replace the values:

df %>% 
  group_by(ID) %>% 
  mutate(val3 = ifelse(all(is.na(val2)),1,0)) %>%
  ungroup() %>% 
  mutate(val2 = ifelse(val3 == 1, val1, val2)) %>% 
  select(-val3)

Output:

# A tibble: 6 x 3
  ID     val1  val2
  <chr> <dbl> <dbl>
1 A         1     1
2 A         2     2
3 A         3     3
4 B         4    NA
5 B         5     2
6 B         6     3

Upvotes: 1

Related Questions