Conditionally fill cells with values from different row

user_17125683 0
Viewed 32

Suppose I have the following data frame:

df <- data.frame(cbind("Method" = rep(c("A","B"), each = 3),
                       "Sub" = rep(c("A1", "A2", "A3"), times = 2),
                       "Value1" = c(2, 3, NA, 4, 2, 3),
                       "Value2" = c(1, 2, NA, 2, 3, 3),
                       "Value3" = c(2, 2, 3, 1, 2, 2)))

#   Method Sub Value1 Value2 Value3
# 1      A  A1      2      1      2
# 2      A  A2      3      2      2
# 3      A  A3   <NA>   <NA>      3
# 4      B  A1      4      2      1
# 5      B  A2      2      3      2
# 6      B  A3      3      3      2

Values for Value1 and Value2 will systematically show a missing value whenever Method == A and Sub == A3. I want these values to be replaced by those that appear at Method == Aand Sub == A2. In this case, the desired output is

#   Method Sub Value1 Value2 Value3
# 1      A  A1      2      1      2
# 2      A  A2      3      2      2
# 3      A  A3      3      2      3
# 4      B  A1      4      2      1
# 5      B  A2      2      3      2
# 6      B  A3      3      3      2

How can I achieve this? Note that in reality, my df is much more complex, with more columns and rows and more possible methods and values. I tried playing around with for loops, but perhaps there is a more efficient way (e.g., using dplyr).

Thank you in advance!

dplyr r
2 Answers

How about this:

library(tidyverse)  
df <- data.frame(cbind("Method" = rep(c("A","B"), each = 3),
                       "Sub" = rep(c("A1", "A2", "A3"), times = 2),
                       "Value1" = c(2, 3, NA, 4, 2, 3),
                       "Value2" = c(1, 2, NA, 2, 3, 3),
                       "Value3" = c(2, 2, 3, 1, 2, 2)))

df %>% 
  group_by(Method) %>% 
  mutate(across(c(Value1, Value2), 
                ~case_when(is.na(.x) & Method == "A" & Sub == "A3" ~ .x[which(Sub == "A2")], 
                           TRUE ~ .x)))
#> # A tibble: 6 × 5
#> # Groups:   Method [2]
#>   Method Sub   Value1 Value2 Value3
#>   <chr>  <chr> <chr>  <chr>  <chr> 
#> 1 A      A1    2      1      2     
#> 2 A      A2    3      2      2     
#> 3 A      A3    3      2      3     
#> 4 B      A1    4      2      1     
#> 5 B      A2    2      3      2     
#> 6 B      A3    3      3      2

Created on 2022-05-20 by the reprex package (v2.0.1)

user_82064340

Another solution using coalesce:

library(dplyr)

df <- data.frame(cbind("Method" = rep(c("A","B"), each = 3),
                       "Sub" = rep(c("A1", "A2", "A3"), times = 2),
                       "Value1" = c(2, 3, NA, 4, 2, 3),
                       "Value2" = c(1, 2, NA, 2, 3, 3),
                       "Value3" = c(2, 2, 3, 1, 2, 2)))

df <- df %>%
  dplyr::mutate(dplyr::across(Value1:Value2, ~dplyr::coalesce(.x, .x[Method == "A" & Sub == "A2"])))

df
#>   Method Sub Value1 Value2 Value3
#> 1      A  A1      2      1      2
#> 2      A  A2      3      2      2
#> 3      A  A3      3      2      3
#> 4      B  A1      4      2      1
#> 5      B  A2      2      3      2
#> 6      B  A3      3      3      2
Created on 2022-05-20 by the reprex package (v2.0.1)
user_100433230
Related