Extract rows with consecutive values if they are preceded by different consecutive values in R

user_14102225 0
Viewed 75

I have a dataframe like this:

id <- c(rep(1234, 6), rep(5678, 10), rep(9101, 5))
date <- seq(as.Date("2020-01-01"), as.Date("2020-01-21"), by = "days")
mode <- c(1, 1, 1, 2, 2, 2, 1, 1, 2, 1, 1, 1, 2, 2, 2, 2, 1, 1, 2, 2, 2)
df <- data.frame(id, date, mode)

I want to group by id and extract all of the rows which have at least three consecutive 2s in the mode column IF those 2s are preceded by at least three consecutive 1s.

My expected output for dataframe above would be

enter image description here

Thank you.

conditional-statements dataframe if-statement r
4 Answers

This method keeps rows which have at least three consecutive 2's that follow at least three consecutive 1's.

library(dplyr)

df %>%
  group_by(id, grp = data.table::rleid(mode)) %>%
  semi_join(summarise(., mode = mode[1], n = n()) %>%
              filter(mode == 2 & n >= 3 & lag(mode) == 1 & lag(n) >= 3),
            by = c("id", "grp")) %>%
  ungroup() %>%
  select(-grp)

# # A tibble: 7 × 3
#      id date        mode
#   <dbl> <date>     <dbl>
# 1  1234 2020-01-04     2
# 2  1234 2020-01-05     2
# 3  1234 2020-01-06     2
# 4  5678 2020-01-13     2
# 5  5678 2020-01-14     2
# 6  5678 2020-01-15     2
# 7  5678 2020-01-16     2
user_100689850

For this dataset:

library(dplyr)

df %>% 
  mutate(counter= cumsum(mode!=lag(mode, default=FALSE))) %>% 
  group_by(id, counter) %>% 
  filter(n() >= 3 & mode==2) %>% 
  ungroup() %>% 
  select(-counter)

     id date        mode
  <dbl> <date>     <dbl>
1  1234 2020-01-04     2
2  1234 2020-01-05     2
3  1234 2020-01-06     2
4  5678 2020-01-13     2
5  5678 2020-01-14     2
6  5678 2020-01-15     2
7  5678 2020-01-16     2
user_133216470

Here is one option with rle

library(dplyr)
f1 <- function(x) {
   rl <- rle(x)
    any(rl$values == 2 & lag(rl$values) == 1 & 
     rl$lengths >= 3, na.rm = TRUE) & x == 2
    }
df %>% 
  group_by(id) %>%
  filter(f1(mode)) %>%
  ungroup
# A tibble: 6 × 3
     id date        mode
  <dbl> <date>     <dbl>
1  1234 2020-01-04     2
2  1234 2020-01-05     2
3  1234 2020-01-06     2
4  9101 2020-01-19     2
5  9101 2020-01-20     2
6  9101 2020-01-21     2
user_37322710 
> r=rle(mode)
> l=length(r$values)
> v1=r$values;v2=c(NA,v1[-l])
> l1=r$lengths;l2=c(NA,l1[-l])
> pick=v1==2&v2==1&l1>=2&l2>=3
> df[rep(pick,r$lengths),]
     id       date mode
4  1234 2020-01-04    2
5  1234 2020-01-05    2
6  1234 2020-01-06    2
13 5678 2020-01-13    2
14 5678 2020-01-14    2
15 5678 2020-01-15    2
16 5678 2020-01-16    2
user_46697480
Related