I have a panel,dfL, where I am trying to identify series consecutive numbers within id, id, and segment, shift in the variables PM. I am looking for series consecutive numbers that contain the numbers -1 and 1 and has the length of 4 or more.
Below my illustration of the situation with data,
# install.packages(c("tidyverse"), dependencies = TRUE)
library(tibble)
I initially have the data in wide format like this,
dfa <- tibble(id = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1,
1, 1, 1, 1, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7),
PM01 = c(NA, -3, NA, -2, -1, 1, 2, NA, NA, -2, -1, NA, -3, -2, -1,
1, 2, 3, NA, NA, -2, -1, 1, 2, 3, NA, NA, NA, NA, NA),
PM02 = c(1, -2, NA, NA, NA, -3, -2, -1, NA, 1, 2, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, -1, 1, 2, NA, NA, NA, NA, NA),
PM03 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, -3, -2, -1, 1, 2, 3, NA, NA)
);dfa
#> # A tibble: 30 x 4
#> id PM01 PM02 PM03
#> <dbl> <dbl> <dbl> <dbl>
#> 1 0 NA 1 NA
#> 2 0 -3 -2 NA
#> 3 0 NA NA NA
#> 4 0 -2 NA NA
#> 5 0 -1 NA NA
#> 6 0 1 -3 NA
#> 7 0 2 -2 NA
#> 8 0 NA -1 NA
#> 9 0 NA NA NA
#> 10 0 -2 1 NA
#> # ... with 20 more rows
In this this PM01 row 4-7 would be a match.
I've tidyr::gather the data to long to only have one vector I have to look through. Like this,
# install.packages(c("tidyverse"), dependencies = TRUE)
library(tidyr)
dfL <- dfa %>% select(id, PM01:PM03) %>% gather(shift, PM, PM01:PM03, na.rm = FALSE) %>% arrange(id, shift) %>% group_by(id, shift)
I tried explaining what I am looking for, but found out it might be clearer if I simply show my desired outcome. Like this,
cbind(dfL, TF = c(FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE,
TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE))
# A tibble: 90 x 4
# Groups: id, shift [9]
id shift PM TF
<dbl> <chr> <dbl> <lgl>
1 0 PM01 NA FALSE
2 0 PM01 -3 FALSE
3 0 PM01 NA FALSE
4 0 PM01 -2 FALSE
5 0 PM01 -1 FALSE
6 0 PM01 1 FALSE
7 0 PM01 NA FALSE
8 0 PM01 NA FALSE
9 0 PM01 NA FALSE
10 0 PM01 -2 FALSE
# ... with 80 more rows
Regardless of efficiency, you might do this; Starting from dfL, create a new group variable that identify consecutive NA or non-NAs chunks, and then add the condition column by checking the conditions within each chunk:
dfL %>%
group_by(g = cumsum(is.na(PM) != lag(is.na(PM), default=0)), add=T) %>%
mutate(TF = n() >= 4 && all(c(-1,1) %in% PM)) %>%
ungroup() %>% select(-g)
# A tibble: 90 x 4
# id shift PM TF
# <dbl> <chr> <dbl> <lgl>
# 1 0 PM01 NA FALSE
# 2 0 PM01 -3 FALSE
# 3 0 PM01 NA FALSE
# 4 0 PM01 -2 TRUE
# 5 0 PM01 -1 TRUE
# 6 0 PM01 1 TRUE
# 7 0 PM01 2 TRUE
# 8 0 PM01 NA FALSE
# 9 0 PM01 NA FALSE
#10 0 PM01 -2 FALSE
# ... with 80 more rows