2

So I have a data set containing of 4 individuals. Each individual is measured for different time period. In R:

df = data.frame(cbind("id"=c(1,1,1,2,2,3,3,3,3,4,4), "t"=c(1,2,3,1,2,1,2,3,4,1,2), "x1"=c(0,1,0,1,0,0,1,0,1,0,0)))

and I want to create variable x2 indicating whether there already was 1 in variable x1 for given individual, ie it will look like this:

"x2" = c(0,1,1,1,1,0,1,1,1,0,0)

... ideally with dplyr package. So far I have came here:

new_df = df %>% dplyr::group_by(id) %>% dplyr::arrange(t)

but can not move from this point... The desired result is on picture.

enter image description here

pikachu
  • 690
  • 1
  • 6
  • 17

1 Answers1

1

Here is one approach using dplyr:

df %>% 
  arrange(id, t) %>%
  group_by(id) %>% 
  mutate(x2 = ifelse(row_number() >= min(row_number()[x1 == 1]), 1, 0))

This will add a 1 if the row number is greater or equal to the first row number where x1 is 1; otherwise, it will add a 0.

Note, you will get warnings, as at least one group does not have a value of x1 which equals 1.

Also, another alternative, including if you want NA where no id has a x1 value of 1 (e.g., where id is 4):

df %>% 
  arrange(id, t) %>%
  group_by(id) %>% 
  mutate(x2 = +(row_number() >= which(x1 == 1)[1]))

Output

      id     t    x1    x2
   <dbl> <dbl> <dbl> <dbl>
 1     1     1     0     0
 2     1     2     1     1
 3     1     3     0     1
 4     2     1     1     1
 5     2     2     0     1
 6     3     1     0     0
 7     3     2     1     1
 8     3     3     0     1
 9     3     4     1     1
10     4     1     0     0
11     4     2     0     0
Ben
  • 28,684
  • 5
  • 23
  • 45
  • Thanks. Meanwhile I came up with this solution: new_df = df %>% dplyr::arrange(id, t) %>% dplyr::group_by(id) %>% dplyr::mutate("x2"=ifelse(cumsum(x1)>0,1,0)) ... but I am afraid there might pop up some special cases when it will have problems. what do you think ? – pikachu Jan 14 '21 at 12:51
  • 1
    @pikachu That should work. I edited with another alternative that would add `NA` if no value of `x1` is equal to 1 as well just in case. – Ben Jan 14 '21 at 12:56