How can I ask R to flag a specific pattern (i.e., column value changes from 1 to 0) each time it occurs within a group of observations?

Question

The below reprex mimics my data: for each person, I have different values of 'res' at different times. I need an indicator variable ('flag') to tell me each time that 'res' changes from 1 to 0 within a given person, and I want 'flag' to equal 1 at the first time (and the first time only) that 'res'= 0 after 'res' = 1. Lastly, I want to count the number of times 'flag' = 1 for each person.

My code has two problems:

It flags every time after 'res'= 1 that 'res' = 0 (but I need 'flag'= 1 only the first time 'res'=0).
Counting the number of times 'flag' = 1 does not work.

Note: The last 'res_next_time' is inevitably NA. By definition in my data, I would never have 'flag'=1 here, so it's okay that it defaults to 0.

Thanks for your help!

#Load packages
library(Hmisc)
#> Loading required package: lattice
#> Loading required package: survival
#> Loading required package: Formula
#> Loading required package: ggplot2
#> 
#> Attaching package: 'Hmisc'
#> The following objects are masked from 'package:base':
#> 
#>     format.pval, units
library(dplyr)
#> Warning: package 'dplyr' was built under R version 4.0.4
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:Hmisc':
#> 
#>     src, summarize
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(tidyr)
#> Warning: package 'tidyr' was built under R version 4.0.5

#Create data set
person <- c(1, 1, 1, 2, 3, 3, 3, 3, 3, 3)
time <- c(1, 2, 3, 1, 2, 1, 2, 3, 4, 5)
res <- c(1, 0, 1, 1, 1, 0, 0, 1, 0, 1)

#Populate data frame
d <- cbind(person, time, res)
d <- as.data.frame(d)

#Create new variable equal to 'res' at the person's next time point
d$res_next_time <- Lag(d$res, -1)

#Group times by person
d %>% 
  group_by(person) %>% 
#Create a new variable 'flag' = 1 when a person's 'res' changes from 1 to 0, and 'flag' = 0 otherwise
  mutate(flag = case_when(res_next_time < 1 ~ 1, TRUE ~ 0)) %>%
#Because 'flag'= 1 is at the time of 'res'= 1 before 'res'= 0, we lag it to have 'flag' = 1 at 'res' = 0
  mutate(flag_res0 = Lag(flag, +1)) %>%
#Replace the NAs in 'flag_res0' with 0
  replace_na(list(flag_res0 = 0)) %>%
  #mutate(flag_res0 = as.numeric(flag_res0 & cumsum(flag_res0) <= 1)) %>%
#Count number of flags per person
  mutate(mig_freq = sum(flag_res0)) %>%
#Limit the data to only include the final indicator
  select('person', 'time', 'res', 'flag_res0')
#> # A tibble: 10 x 4
#> # Groups:   person [3]
#>    person  time   res flag_res0
#>            
#>  1      1     1     1         0
#>  2      1     2     0         1
#>  3      1     3     1         0
#>  4      2     1     1         0
#>  5      3     2     1         0
#>  6      3     1     0         1
#>  7      3     2     0         1
#>  8      3     3     1         0
#>  9      3     4     0         1
#> 10      3     5     1         0

^{Created on 2021-04-15 by the reprex package (v0.3.0)}

user_5329073 · Answer

You won't need the column res_next_time with my solution. I think that @Paul PR's is more concise.

# using your data d
d %>% 
  group_by(person) %>% 
  mutate(flag2 = if_else(lag(res) == 1 & res == 0 &  
                           !(duplicated(lag(res) == 1 & res == 0)),1, 0, 0))

You could add ungroup() at the end. That might be important depending on what's next. This basically is 'if TRUE TRUE and not duplicated, then...'

Your comment indicated that you aren't looking for the first occurrence but any occurrence within the group.

That's actually much simpler.

(d %>% 
  group_by(person) %>% 
  mutate(flag = if_else(lag(res) == 1 & res == 0, 1, 0, 0)))

The output looks like this. (I added data at the end of your example data to show my occurrences.)

# # A tibble: 13 x 4
# # Groups:   person [3]
#    person  time   res  flag
#        
#  1      1     1     1     0
#  2      1     2     0     1
#  3      1     3     1     0
#  4      2     1     1     0
#  5      3     2     1     0
#  6      3     1     0     1
#  7      3     2     0     0
#  8      3     3     1     0
#  9      3     4     0     1
# 10      3     5     1     0
# 11      3     6     0     1
# 12      1     7     1     0
# 13      1     8     0     1

user_12439281 · Answer

Here's a solution that solves the problem in two steps:

Use dplyr's lag function to calculate the previous value of res, rather than the next value of res. We do this in the grouped data frame so the first value of res_last_time is NA for each person.
Use cumsum in the grouped data frame to only keep the first flag = 1 for each person.

d %>% 
    group_by(person) %>% 
    mutate(res_last_time = lag(res, 1)) %>%
    mutate(flag = res == 0 & res_last_time == 1) %>%
    mutate(flag = as.numeric(flag & cumsum(flag) <= 1))

Using your same d data.frame, here are the results I get:

#> # A tibble: 10 x 5
#> # Groups:   person [3]
#>    person  time   res res_last_time  flag
#>                 
#>  1      1     1     1            NA     0
#>  2      1     2     0             1     1
#>  3      1     3     1             0     0
#>  4      2     1     1            NA     0
#>  5      3     2     1            NA     0
#>  6      3     1     0             1     1
#>  7      3     2     0             0     0
#>  8      3     3     1             0     0
#>  9      3     4     0             1     0
#> 10      3     5     1             0     0

^{Created on 2021-04-15 by the reprex package (v1.0.0)}

How can I ask R to flag a specific pattern (i.e., column value changes from 1 to 0) each time it occurs within a group of observations?

2 Answers