Find out if there is any non-NA value for each id

user_13807049 0
Viewed 263

In the following data frame in long format (i.e., one row per measurement point), I would like to find out which person (variable ID) has only NA's on both Var1 and Var2 and which person has at least one non-NA value on either Var1 or Var2. Those with only NA values should be in Group 0 and those that have at least one non-NA value should be in Group 1.

df <- read.table(text=
"ID       Var1   Var2      
800057    NA    NA               
800057    NA    NA           
800057    NA    NA          
800057    NA    1         
800057    NA    NA 
800119    NA    NA 
800119    NA    NA          
800119    NA    NA      
834011     1    NA
834011     3    NA  
834341    NA    NA
834341    5     NA  
834341    NA    NA   
834341    NA    NA   
834341    3     NA", header=TRUE)

My result would then look like this:

df <- read.table(text=
"ID       Var1  Var2  Group    
800057    NA    NA   1            
800057    NA    NA   1        
800057    NA    NA   1       
800057    NA    1    1     
800057    NA    NA   1
800119    NA    NA   0
800119    NA    NA   0       
800119    NA    NA   0   
834011     1    NA   1
834011     3    NA   1
834341    NA    NA   1
834341    5     NA   1   
834341    NA    NA   1
834341    NA    2    1
834341    3     NA   1", header=TRUE)

I want to try out a solution using group_by(ID) and mutate() to create the Group variable.

dplyr grouping dataframe r
3 Answers

You can use all :

library(dplyr)
df %>%
  group_by(ID) %>%
  mutate(Group = as.integer(!all(is.na(Var1) & is.na(Var2))))

#       ID  Var1  Var2 Group
#    <int> <int> <int> <int>
# 1 800057    NA    NA     1
# 2 800057    NA    NA     1
# 3 800057    NA    NA     1
# 4 800057    NA     1     1
# 5 800057    NA    NA     1
# 6 800119    NA    NA     0
# 7 800119    NA    NA     0
# 8 800119    NA    NA     0
# 9 834011     1    NA     1
#10 834011     3    NA     1
#11 834341    NA    NA     1
#12 834341     5    NA     1
#13 834341    NA    NA     1
#14 834341    NA    NA     1
#15 834341     3    NA     1

Or can change the last line to use any :

mutate(Group = as.integer(any(!is.na(Var1) | !is.na(Var2))))
user_39629140

Another dplyr solution would be comparing the count of NA rows, is.na(), to the count of entries in each group, n(). Since we have two variables, n() needs to be multiplied by two. Plus sign + before the comparison converts the boolean to numeric.

library(dplyr)

df %>% 
  group_by(ID) %>% 
  mutate(group = +(sum(is.na(Var1), is.na(Var2)) < 2 * n()))

Last line can be replaced with the following to avoid doing any comparisons.

  mutate(group = +(as.logical(sum(is.na(Var1), is.na(Var2)) - 2 * n())))

#> # A tibble: 15 x 4
#> # Groups:   ID [4]
#>        ID  Var1  Var2 group
#>     <int> <int> <int> <int>
#>  1 800057    NA    NA     1
#>  2 800057    NA    NA     1
#>  3 800057    NA    NA     1
#>  4 800057    NA     1     1
#>  5 800057    NA    NA     1
#>  6 800119    NA    NA     0
#>  7 800119    NA    NA     0
#>  8 800119    NA    NA     0
#>  9 834011     1    NA     1
#> 10 834011     3    NA     1
#> 11 834341    NA    NA     1
#> 12 834341     5    NA     1
#> 13 834341    NA    NA     1
#> 14 834341    NA    NA     1
#> 15 834341     3    NA     1
user_64614620

You can use a simple ifelse() function in base.

df$group <- ifelse(is.na(df$Var1) & is.na(df$Var2), 1, 2) 

       ID Var1 Var2 group
1  800057   NA   NA     1
2  800057   NA   NA     1
3  800057   NA   NA     1
4  800057   NA    1     2
5  800057   NA   NA     1
6  800119   NA   NA     1
7  800119   NA   NA     1
8  800119   NA   NA     1
9  834011    1   NA     2
10 834011    3   NA     2
11 834341   NA   NA     1
12 834341    5   NA     2
13 834341   NA   NA     1
14 834341   NA   NA     1
15 834341    3   NA     2

Data:

df <- read.table(text=
"ID       Var1   Var2      
800057    NA    NA               
800057    NA    NA           
800057    NA    NA          
800057    NA    1         
800057    NA    NA 
800119    NA    NA 
800119    NA    NA          
800119    NA    NA      
834011     1    NA
834011     3    NA  
834341    NA    NA
834341    5     NA  
834341    NA    NA   
834341    NA    NA   
834341    3     NA", header=TRUE)
user_141635330
Related