Find total IDs between two dates that satisfies a condition

user_18012581 0
Viewed 68

I have a dataset PosNeg like this. I need to find count of ID's who have a pattern like this- P N P P or N P N N P N - that is having at least one N (negative) between two P's(positive). If this pattern occurs at least once, then count that ID. Date is always in ascending order.

Eg: for ID 1, I have at least 1 N on 02/25 between two P's, Hence I'll count ID 1. ID 2 and 3 does not have N between 2 P's, so those are not counted. ID 4 also has one N between two P's on 03/18, hence I'll include 4. So the total number of ID's which satisfices the condition is 2(1 and 4)

My idea is to find min(date) of positive and max(date) of positive for each ID, and look for any negative in between these dates, but not sure how to implement it. Any suggestions in R/Python/SQL would be helpful.

ID Test Date
1 P 2021-01-02
1 P 2021-01-08
1 N 2021-02-25
1 P 2021-03-26
2 N 2021-02-05
2 P 2021-03-04
2 N 2021-03-30
3 N 2021-01-24
3 P 2021-02-10
4 N 2021-02-15
4 P 2021-02-28
4 N 2021-03-18
4 P 2021-04-11

Output:

Total
2

EDIT1: There could be multiple N's (at least 1) between two P's, not just 1, and I want to include it in my count.

EDIT2: I want this ID to be included but it is not included in the result dataframe. But, there are multiple N's in between 2 P's.

ID DATE TEST
1 2020-06-12 N
1 2020-08-20 N
1 2020-10-04 N
1 2020-12-09 N
1 2021-01-08 P
1 2021-02-05 P
1 2021-03-26 P
1 2021-05-26 P
1 2021-06-30 N
1 2021-07-21 N
1 2021-08-23 N
1 2021-09-16 N
1 2021-10-08 N
1 2021-10-18 N
1 2021-10-29 P

EDIT3: The ID in the previous edit is 1 while in my the output of my real data starts from 15. I think it should start from 1. Also, its is not N and P in my real data but 'Negative' and 'Positive'. My code is now this:

data4c %>% group_by(STUDY_ID) 
%>% summarise(isP = str_detect(str_c(TEST, collapse = ""), 
"PositiveNegative+Positive"), .groups = 'drop') 
 %>% filter(isP)

enter image description here

data-manipulation dataframe r sql python
2 Answers

Here is one option with str_c/str_detect - grouped by 'ID', paste the 'Test' elements and then check whether the pattern P followed by one or more N (N+) and then a P occurs

library(stringr)
library(dplyr)
df1 %>% 
  group_by(ID) %>% 
  summarise(isP = str_detect(str_c(substr(Test,1, 1) collapse = ""), "PN+P"), 
    .groups = 'drop') %>% 
 filter(isP)
# A tibble: 2 × 2
     ID isP  
  <int> <lgl>
1     1 TRUE 
2     4 TRUE

Using the OP's new data

> df2 %>%  group_by(ID) %>% 
   summarise(isP = str_detect(str_c(substr(TEST,1, 1), collapse = ""), "PN+P"), 
     .groups = 'drop') %>% 
  filter(isP)
# A tibble: 1 × 2
     ID isP  
  <int> <lgl>
1     1 TRUE

EDIT: added substr to extract the first letter in 'Test' column as the original data values are not 'P' or 'N' as showed in example

data

df2 <- structure(list(ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L), DATE = c("2020-06-12", "2020-08-20", "2020-10-04", 
"2020-12-09", "2021-01-08", "2021-02-05", "2021-03-26", "2021-05-26", 
"2021-06-30", "2021-07-21", "2021-08-23", "2021-09-16", "2021-10-08", 
"2021-10-18", "2021-10-29"), TEST = c("N", "N", "N", "N", "P", 
"P", "P", "P", "N", "N", "N", "N", "N", "N", "P")), 
class = "data.frame", row.names = c(NA, 
-15L))
user_37322710

Updated answer

library(dplyr)

dat %>%
  group_by(ID) %>%
  summarize(
    yourcond = grepl(pattern = "PN+P", x = paste(Test, collapse = "")))

result:

# A tibble: 5 x 2
     ID yourcond
  <dbl> <lgl>   
1     1 TRUE    
2     2 FALSE   
3     3 FALSE   
4     4 TRUE    
5     5 TRUE

new data:

dat <- structure(list(ID = c(1, 1, 1, 1, 2, 2, 2, 3, 3, 4, 4, 4, 4, 
                             5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5), Test = c("P", "P", 
                                                                                    "N", "P", "N", "P", "N", "N", "P", "N", "P", "N", "P", "N", "N", 
                                                                                    "N", "N", "P", "P", "P", "P", "N", "N", "N", "N", "N", "N", "P"
                             ), Date = c("2021-01-02", "2021-01-08", "2021-02-25", "2021-03-26", 
                                         "2021-02-05", "2021-03-04", "2021-03-30", "2021-01-24", "2021-02-10", 
                                         "2021-02-15", "2021-02-28", "2021-03-18", "2021-04-11", "2020-06-12", 
                                         "2020-08-20", "2020-10-04", "2020-12-09", "2021-01-08", "2021-02-05", 
                                         "2021-03-26", "2021-05-26", "2021-06-30", "2021-07-21", "2021-08-23", 
                                         "2021-09-16", "2021-10-08", "2021-10-18", "2021-10-29")), row.names = c(NA, 
                                                                                                                 -28L), class = "data.frame")

Previous answer

library(dplyr)

dat %>%
  group_by(ID) %>%
  summarize(
    yourcond = any((Test == "N") & (lag(Test) == "P") & (lead(Test) == "P")))

result:

# A tibble: 4 x 2
     ID yourcond
  <int> <lgl>   
1     1 TRUE    
2     2 NA      
3     3 NA      
4     4 TRUE

You could add count(yourcond) to the dplyr chain to return counts of each NA and TRUE.

data:

dat <- structure(list(ID = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 4L, 
4L, 4L, 4L), Test = c("P", "P", "N", "P", "N", "P", "N", "N", 
"P", "N", "P", "N", "P"), Date = c("2021-01-02", "2021-01-08", 
"2021-02-25", "2021-03-26", "2021-02-05", "2021-03-04", "2021-03-30", 
"2021-01-24", "2021-02-10", "2021-02-15", "2021-02-28", "2021-03-18", 
"2021-04-11")), class = "data.frame", row.names = c(NA, -13L))
user_100218760
Related