How to select all id's rows where all sequence is present?

user_7934206 0
Viewed 75

I have a data table like below :

DT1 <- data.table(
  id=c(1,1,1,2,2,2,1,1),
  sensor_id=c(1,2,3,1,2,3,2,3),
  time=c("2017-01-01 00:00:05","2017-01-01 00:06:35","2017-01-01 00:23:44","2017-01-02 22:00:20","2017-01-02 22:01:09","2017-01-02 22:28:02","2017-01-03 22:23:01","2017-01-03 22:50:52")
  )

From this, I want to select all the customer_id rows which have all the sequences of sensor id's (1,2,3) in this case.

So I want to label these like below :

DT1 <- data.table(
  id=c(1,1,1,2,2,2,1,1),
  sensor_id=c(1,2,3,1,2,3,2,3),
  time=c("2017-01-01 00:00:05","2017-01-01 00:06:35","2017-01-01 00:23:44","2017-01-02 22:00:20","2017-01-02 22:01:09","2017-01-02 22:28:02","2017-01-03 22:23:01","2017-01-03 22:50:52"),
  group=c(1,1,1,2,2,2,3,3),
  seq_boolean=c(1,1,1,1,1,1,0,0)
)

where I can identify the row is having all the sequences or not. One id can appear multiple times and also not have all the sequences.

So for this, I tried the below to group first

DT1[,group:=rleid(id),by=list(id,rleid(sensor_id))]

But this just groups all as one. I am not sure where I am doing it wrong. Any help is appreciated.

EDIT I can also have data tables like in the below format.

DT1 <- data.table(
       id=c(1,2,1,2,1,2,1,1),
       sensor_id=c(1,1,2,2,3,3,2,3),
       time=c("2017-01-01 00:00:05","2017-01-01 00:06:35","2017-01-01 00:23:44","2017-01-02 00:00:20","2017-01-02 00:35:09","2017-01-02 00:28:02","2017-01-03 00:45:01","2017-01-03 00:50:52")
   )

Here the group is defined by sensor_id.sensor_id 1 is start of the session and sensor_id 2 is the middle session and sensor_id 3 is the end of the session. An id can have multiple sessions and some sensors might fail to capture the id. So my purpose is to identify all the sessions captured by all 3 sensor ids and see how much is being captured.

data.table dplyr r
2 Answers

We can check if all the uniq_sensor is present for each group.

uniq_sensor <- unique(DT1$sensor_id)
DT1[, seq_boolean := +(all(uniq_sensor %in% sensor_id)), rleid(id)]
DT1

#   id sensor_id                time seq_boolean
#1:  1         1 2017-01-01 00:00:05           1
#2:  1         2 2017-01-01 00:06:35           1
#3:  1         3 2017-01-01 00:23:44           1
#4:  2         1 2017-01-02 22:00:20           1
#5:  2         2 2017-01-02 22:01:09           1
#6:  2         3 2017-01-02 22:28:02           1
#7:  1         2 2017-01-03 22:23:01           0
#8:  1         3 2017-01-03 22:50:52           0

We can also count the length of unique sensors for each group :

DT1[, seq_boolean := +(uniqueN(sensor_id) == length(uniq_sensor)), rleid(id)]

EDIT

For the additional condition where we want to make sure that the sensor_id occurs in exact same order we can do :

library(dplyr)

DT1 %>%
  group_by(id) %>%
  group_by(grp = cumsum(c(TRUE, diff(sensor_id) <= 0)), .add = TRUE) %>%
  #Use add = TRUE for old dplyr < 1.0.0
  mutate(seq_boolean = +(all(uniq_sensor %in% sensor_id)))
user_39629140 
DT1[,seq_boolean := +setequal(sensor_id, DT1[, sensor_id]), by = rleid(id)]
DT1
   id sensor_id                time seq_boolean
1:  1         1 2017-01-01 00:00:05           1
2:  1         2 2017-01-01 00:06:35           1
3:  1         3 2017-01-01 00:23:44           1
4:  2         1 2017-01-02 22:00:20           1
5:  2         2 2017-01-02 22:01:09           1
6:  2         3 2017-01-02 22:28:02           1
7:  1         2 2017-01-03 22:23:01           0
8:  1         3 2017-01-03 22:50:52           0
user_83802720
Related