Updating values in a column based on criteria

Question

I need to update x_eligible to 1, for all corresponding records of same study_id & site, where x == 1. This is to sort of to overwrite the values of x for the same records as study_id & site, whenever x_eligible is set to 1

Here is a reproducible code:

library(tidyverse)
newdata = data.frame(site = c('A','A','A','B','B','B','B'),
                     study_id = c(1,1,2,1,1,1,2),
                     x = c(0,1,0,0,NA,1,0),
                     x_eligible = c(0,1,0,0,0,1,0))

xEligibility2 <- function (x, siteid, studyID){

  el = newdata %>% filter(site == siteid & study_id ==studyID & x_eligible==1)

  if(exists("el"))
    return(ifelse(nrow(el)>=1,1,0))
  else
    return(0)
}

newdata = newdata %>% mutate(
  x_eligible = ifelse(apply(newdata, 1, xEligibility2, siteid=site, studyID=study_id) == 1, 1, 0) 
)

And this is what I am getting as results on the x_eligible column:

x_eligible = c(1,1,1,1,1,1,1)

x_eligible is all being set to 1.

This is my expected output:

x_eligible = c(1,1,0,1,1,1,0)

I would appreciate any help to point out what I could be doing wrong.

Can you provide some code in a `dput` or somehting? https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — william3031, Jun 26 '19 at 22:31
As @heds1 has mentioned, what would you like the output to look like? Which values should be 1s? It isn't exactly clear from your explanation. — william3031, Jun 26 '19 at 22:48
Okay, let me edit the question to make it reproducible and include the expected output. — Narshion M. Ngao, Jun 26 '19 at 22:50

score 1 · Accepted Answer · answered Jun 26 '19 at 23:08

You can group by site and study_id and test if any of the values in x equal 1:

library(dplyr)

newdata %>%
  group_by(site, study_id) %>%
  mutate(x_eligible = +any(x == 1))

# A tibble: 7 x 4
# Groups:   site, study_id [4]
  site  study_id     x x_eligible
  <fct>    <dbl> <dbl>      <int>
1 A            1     0          1
2 A            1     1          1
3 A            2     0          0
4 B            1     0          1
5 B            1    NA          1
6 B            1     1          1
7 B            2     0          0

Updating values in a column based on criteria

1 Answers1