Replacing missing values by group and identifying mutual exclusiveness

user_13884754 0
Viewed 75

I am working with the grouped data in R.

In the following data example, I would like to fill the missing values in "sex" variable, and keep as is if there was no corresponding data (i.e. for id=6).

In the "diagnosis" variable, some had only one diagnosis and some had multiple diagnosis. So, I also would like to group the variable "diagnosis" into "wanted" to identify mutual exclusiveness.

The example data is;

d.f <- tribble (
~id, ~sex, ~diagnosis,
1, "M", "A",
1, NA, "B",
1, NA, "C",
2, NA, "A",
2, "F", NA,
2, NA, "A",
3, NA, NA,
3, "M", "A",
3, "M", "B",
4, "F", "C",
5, "F", "B",
6, NA, "A",
7, "M", NA
)

The desired data is ;

wanted <- tribble (
~id, ~sex, ~diagnosis,~wanted,
1, "M", "A", "ABC group",
1, "M", "B", "ABC group",
1, "M", "C", "ABC group",
2, "F", "A", "Only A",
2, "F", NA, "Only A",
2, "F", "A", "Only A",
3, "M", NA, "AB group",
3, "M", "A", "AB group",
3, "M", "B", "AB group",
4, "F", "C", "Only C",
5, "F", "B", "Only B",
6, NA, "A", "Only A",
7, "M", NA, "Missing"
)
dplyr dataframe r
3 Answers
  • mutate sex column by using first(na.omit(sex)) first is just an aggregating function which is safe to use here
  • another column say wanted can be mutated in two steps.
    • paste all strings together in the group using paste(unique(na.omit(diagnosis)), collapse = '')
    • thereafter use case_when to mutate strings as per your choice
library(tidyverse)

d.f %>%
  group_by(id) %>%
  mutate(sex = first(na.omit(sex)),
         wanted = { x <- paste(unique(na.omit(diagnosis)), collapse = ''); 
         case_when(nchar(x) == 1 ~  paste0('Only ', x),
                   nchar(x) == 0 ~ 'Missing',
                   TRUE ~ paste(x, ' Group'))})
#> # A tibble: 13 x 4
#> # Groups:   id [7]
#>       id sex   diagnosis wanted    
#>    <dbl> <chr> <chr>     <chr>     
#>  1     1 M     A         ABC  Group
#>  2     1 M     B         ABC  Group
#>  3     1 M     C         ABC  Group
#>  4     2 F     A         Only A    
#>  5     2 F     <NA>      Only A    
#>  6     2 F     A         Only A    
#>  7     3 M     <NA>      AB  Group 
#>  8     3 M     A         AB  Group 
#>  9     3 M     B         AB  Group 
#> 10     4 F     C         Only C    
#> 11     5 F     B         Only B    
#> 12     6 <NA>  A         Only A    
#> 13     7 M     <NA>      Missing
user_28848590 
library(dplyr)
library(tidyr)
library(stringr)

df1 %>% 
  group_by(id) %>% 
  drop_na(diagnosis) %>% 
  summarise(wanted = str_c(c(unique(diagnosis)), collapse = "")) %>% 
  full_join(df1, . , by = "id") %>% 
  group_by(id) %>% 
  fill(sex, .direction = "updown")

#> # A tibble: 13 x 4
#> # Groups:   id [7]
#>       id sex   diagnosis wanted
#>    <dbl> <chr> <chr>     <chr> 
#>  1     1 M     A         ABC   
#>  2     1 M     B         ABC   
#>  3     1 M     C         ABC   
#>  4     2 F     A         A     
#>  5     2 F     <NA>      A     
#>  6     2 F     A         A     
#>  7     3 M     <NA>      AB    
#>  8     3 M     A         AB    
#>  9     3 M     B         AB    
#> 10     4 F     C         C     
#> 11     5 F     B         B     
#> 12     6 <NA>  A         A     
#> 13     7 M     <NA>      <NA>
user_64614620

This can also be used:

library(dplyr)

d.f %>%
  group_by(id) %>%
  mutate(sex = coalesce(sex, sex[!is.na(sex)][1]), 
         wanted = across(diagnosis, ~ {x <- unique(diagnosis[!is.na(diagnosis)])
         if_else(length(x) > 1, paste(paste(x, collapse = ""), "Group"), 
                 if_else(length(x) == 1, paste("Only", x[1]), "Missing")
         )}))

# A tibble: 13 x 4
# Groups:   id [7]
      id sex   diagnosis wanted$diagnosis
   <dbl> <chr> <chr>     <chr>           
 1     1 M     A         ABC Group       
 2     1 M     B         ABC Group       
 3     1 M     C         ABC Group       
 4     2 F     A         Only A          
 5     2 F     NA        Only A          
 6     2 F     A         Only A          
 7     3 M     NA        AB Group        
 8     3 M     A         AB Group        
 9     3 M     B         AB Group        
10     4 F     C         Only C          
11     5 F     B         Only B          
12     6 NA    A         Only A          
13     7 M     NA        Missing
user_143145200
Related