Mark IDs that have a specific value across columns-CodePudding

I am trying to create a variable 'check' with values 1/0. These should be assigned based on whether across columns V1 to V3 there is at least one value = 1 for each ID.

DF <- data.frame (ID= c(1,1,1,2,3,3,4,5,5,6), V1= c(1,0,0,1,1,0,0,1,0,0), 
V2= c(1,1,0,0,1,0,1,1,0,0), V3= c(0,1,0,1,0,0,0,0,1,0))

This is the code I am using but group by doesn't seem to work. It does seem to go across columns and mark as 1 all of those having at least one value of 1 but not by ID.

DF %>% dplyr::group_by(ID) %>% 
dplyr::mutate(Check= case_when(if_any('V1':'V3',~.x!=0)~1,TRUE ~0)) %>% 
dplyr::ungroup()

So the output I am looking for is this one:

ID	V1	V2	V3	check
1	1	1	0	1
1	0	1	1	1
1	0	0	0	1
2	1	0	1	0
3	1	1	0	0
3	0	0	0	0
4	0	1	0	0
5	1	1	0	1
5	0	0	1	1
6	0	0	0	0

Could you help?

Many thanks!

Edit: apologies, I have noticed a mistake in the output, it should be fine now.

CodePudding user response：

Please check the below code

these are steps i followed

after grouping by ID column, derive new columns where if column is equal to 0 then change the value to NA, replace the 0 with NA
then retain the previous values to all the other rows so if the value is 1 it will be retained to other rows within the by group
then sum the values of all the three variables and if the sum of all 3 variable is 3 then derive check variable and update the value to 1
retain the 1 to other rows within the by group else set to zero

DF %>% group_by(ID) %>% 
mutate(across(starts_with('V'), ~ ifelse(.x==0, NA, .x), .names = 'new_{col}')) %>% 
fill(starts_with('new')) %>% 
mutate(check=ifelse(rowSums(across(starts_with('new')))==3,1,0)) %>% 
fill(check, .direction = 'downup') %>% mutate(check=replace_na(check,0)) %>% 
  select(-starts_with('new'))

^{Created on 2023-02-03 with reprex v2.0.2}

# A tibble: 10 × 5
# Groups:   ID [6]
      ID    V1    V2    V3 check
   <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1     1     1     0     1
 2     1     0     1     1     1
 3     1     0     0     0     1
 4     2     1     0     1     0
 5     3     1     1     0     0
 6     3     0     0     0     0
 7     4     0     1     0     0
 8     5     1     1     0     1
 9     5     0     0     1     1
10     6     0     0     0     0