How to modify the values in a group based on the presance of some other value in the group using dplyr?

user_12296712 0
Viewed 41

This is my data

structure(list(group = c("A", "A", "A", "B", "B", "B", "C", "C", 
"C", "D", "D", "D"), type = c("T1", "T2", "T3", "T1", "T2", "T3", 
"T1", "T2", "T3", "T1", "T2", "T3"), value = c("MC", "C", NA, 
"C", "C", NA, "MA", "A", "A", NA, "MA", "MA")), row.names = c(NA, 
-12L), class = c("tbl_df", "tbl", "data.frame"))

# A tibble: 12 x 3
   group type  value
   <chr> <chr> <chr>
 1 A     T1    MC   
 2 A     T2    C    
 3 A     T3    NA   
 4 B     T1    C    
 5 B     T2    C    
 6 B     T3    NA   
 7 C     T1    MA   
 8 C     T2    A    
 9 C     T3    A    
10 D     T1    NA   
11 D     T2    MA   
12 D     T3    MA

For each group there are 3 types. When there is a 'M' value in the group (MC or MA) then the rest of the values in the group should be changed to that value but the NA should remain as NA.

This is my expected output:

# A tibble: 12 x 3
   group type  value
   <chr> <chr> <chr>
 1 A     T1    MC   
 2 A     T2    MC    
 3 A     T3    NA   
 4 B     T1    C    
 5 B     T2    C    
 6 B     T3    NA   
 7 C     T1    MA   
 8 C     T2    MA    
 9 C     T3    MA    
10 D     T1    NA   
11 D     T2    MA   
12 D     T3    MA
dplyr tidyverse r
2 Answers
df1 <- structure(list(group = c("A", "A", "A", "B", "B", "B", "C", "C", "C", "D", "D", "D"), 
               type = c("T1", "T2", "T3", "T1", "T2", "T3", "T1", "T2", "T3", "T1", "T2", "T3"), 
               value = c("MC", "C", NA, "C", "C", NA, "MA", "A", "A", NA, "MA", "MA")), 
          row.names = c(NA, -12L), class = c("tbl_df", "tbl", "data.frame"))

Solution using dplyr and an ifelse in mutate (for illustrative purpose I did not overwrite value but assign to a new variable...)

library(dplyr)
df1 %>% 
  group_by(group) %>% 
  mutate(newValue = ifelse(!is.na(value) & any(grepl("^M", value)), value[grepl("M", value)][1], value))

Returns:

   group type  value newValue
   <chr> <chr> <chr> <chr>   
 1 A     T1    MC    MC      
 2 A     T2    C     MC      
 3 A     T3    NA    NA      
 4 B     T1    C     C       
 5 B     T2    C     C       
 6 B     T3    NA    NA      
 7 C     T1    MA    MA      
 8 C     T2    A     MA      
 9 C     T3    A     MA      
10 D     T1    NA    NA      
11 D     T2    MA    MA      
12 D     T3    MA    MA

A comment: Just be aware that right now the behaviour is somewhat undefined if multiple different "M" values appear within a group. But I assume that this is prevented by the data structure/logic...

user_53555670

Using dplyr you can try this:

df %>%
  group_by(group) %>%
  mutate(value = case_when(any(grepl("M", value)) & value %in% c("C", "A") ~ paste0("M", value),
                           TRUE ~ value)) %>%
  ungroup()

Which results in:

# A tibble: 12 x 3
   group type  value
   <chr> <chr> <chr>
 1 A     T1    MC   
 2 A     T2    MC   
 3 A     T3    NA   
 4 B     T1    C    
 5 B     T2    C    
 6 B     T3    NA   
 7 C     T1    MA   
 8 C     T2    MA   
 9 C     T3    MA   
10 D     T1    NA   
11 D     T2    MA   
12 D     T3    MA
user_94228710
Related