Complex conditional mutating: Create new variable with conditional mutating using only past observations for a given participant?

Question

I have a dataframe (fbwb) with multiple assessments of bullying (1-6) using multiple measures (1-3) in a group of participants. The df looks like this:

fbwb <- read.table(text="id year bully1 bully2 bully3 cbully bully_ever 
100 1 NA 1 NA 1 1
100 2 1 1 NA 1 1
100 3 NA 0 NA 0 1
101 1 NA NA 1 1 1
102 1 NA 1 NA 1 1
102 2 NA NA NA NA 1
102 3 NA 1 1 1 1
102 4 0 0 0 0 1
103 1 NA 1 NA 1 1
103 2 NA 0 0 0 1", header=TRUE)

Where bully1, bully2, and bully3 are binary variables that each = 1 if bullying was reported on the respective measure. cbully is binary and = 1 if any of the 3 bullying variables = 1 for a given year. bully_ever is binary and = 1 if bullying was reported on any measure in any year for a given participant.

I want to create a new binary variable in my df called bully_past. bully_past represents the case when cbully = 1 in ANY PAST YEAR. This is subtly different from bully_ever. For example, if a participant has been assessed 4 times:

• bully_past should use info from years 3, 2, and 1 AT YEAR 4.
• bully_past should use info from years 2 and 1 AT YEAR 3.
• bully_past should use info from year 1 AT YEAR 2.
• bully_past should be NA at year 1.

I have tried quite a few things, but the most recent rendition is the following:

fbwb <- fbwb %>%
  dplyr::group_by(id) %>%
  dplyr::mutate(bully_past = case_when(cbully == 1 & year == (year - 1) |
                                         cbully == 1 & year == (year - 2) |
                                         cbully == 1 & year == (year - 3) |
                                         cbully == 1 & year == (year - 4) |
                                         cbully == 1 & year == (year - 5) ~ 1,
                                       (is.na(cbully) & year == (year - 1) &
                                         is.na(cbully) & year == (year - 2) &
                                         is.na(cbully) & year == (year - 3) &
                                         is.na(cbully) & year == (year - 4) &
                                         is.na(cbully) & year == (year - 5)) ~ NA_real_,
                                       TRUE ~ 0)) %>%
  dplyr::ungroup()

This does not work because the syntax for indicating which years to use is not correct - so it generates a column of NA values. I have made other attempts, but I have not been able to manage to take into account observations from ALL PREVIOUS YEARS.

It can be done in Stata using this code:

gen bullyingever = bullying
sort iid time
replace bullyingever = 1 if bullying[_n - 1]==1 & iid[_n - 1]==iid
replace bullyingever = 1 if bullying[_n - 2]==1 & iid[_n - 2]==iid
replace bullyingever = 1 if bullying[_n - 3]==1 & iid[_n - 3]==iid
replace bullyingever = 1 if bullying[_n - 4]==1 & iid[_n - 4]==iid
replace bullyingever = 1 if bullying[_n - 5]==1 & iid[_n - 5]==iid

I appreciate any input on how to accomplish this in R, preferably using dplyr.

Answer 1

Here we can write a helper function that can look at previous events both using cumsum (to keep a cumulative account of events which lets you look into the past) and lag() in order to look exclusively behind the current value. So we have

had_previous_event <- function(x) {
  lag(cumsum(!is.na(x) & x==1)>0)
}

You can then use that with your dplyr chain

fbwb %>%
  arrange(id, year) %>% 
  group_by(id) %>%
  mutate(bully_past = had_previous_event(cbully))

This returns TRUE/FALSE but if you want zero/one you can change that to

  mutate(bully_past = as.numeric(had_previous_event(cbully)))

Answer 2

One solution can be using dplyr and ifelse as:

library(dplyr)

  fbwb  %>% group_by(id) %>%
  arrange(id, year) %>%
  mutate(bully_past_year = ifelse(is.na(lag(cbully)), 0L, lag(cbully))) %>%
  mutate(bully_past = ifelse(cumsum(bully_past_year)>0L, 1L, 0 )) %>%
  select(-bully_past_year) %>% as.data.frame()

  #    id   year bully1 bully2 bully3 cbully bully_ever bully_past
  # 1  100    1     NA      1     NA      1          1          0
  # 2  100    2      1      1     NA      1          1          1
  # 3  100    3     NA      0     NA      0          1          1
  # 4  101    1     NA     NA      1      1          1          0
  # 5  102    1     NA      1     NA      1          1          0
  # 6  102    2     NA     NA     NA     NA          1          1
  # 7  102    3     NA      1      1      1          1          1
  # 8  102    4      0      0      0      0          1          1
  # 9  103    1     NA      1     NA      1          1          0
  # 10 103    2     NA      0      0      0          1          1  

Answer 3

There is an alternative approach which aggregates in a non-equi self-join. This approach has the benefit that it works even with unordered data.

library(data.table)
# coerce to data.table
bp <- setDT(fbwb)[
  # non equi self-join and aggregate within the join
  fbwb, on = .(id, year < year), as.integer(any(cbully)), by = .EACHI][]
# append new column
fbwb[, bully_past := bp$V1][]

     id year bully1 bully2 bully3 cbully bully_ever bully_past
 1: 100    1     NA      1     NA      1          1         NA
 2: 100    2      1      1     NA      1          1          1
 3: 100    3     NA      0     NA      0          1          1
 4: 101    1     NA     NA      1      1          1         NA
 5: 102    1     NA      1     NA      1          1         NA
 6: 102    2     NA     NA     NA     NA          1          1
 7: 102    3     NA      1      1      1          1          1
 8: 102    4      0      0      0      0          1          1
 9: 103    1     NA      1     NA      1          1         NA
10: 103    2     NA      0      0      0          1          1

The non-equi join condition considers only previous years. So, the first year for each id is NA as requested by the OP.

The any() function returns TRUE if at least one of the values is TRUE (after coersion to type logical). In R, the integer value 1L corresponds to the logical value TRUE.