Selecting the first positive event

user_11518133 0
Viewed 90

I'm struggling on how can I create a subsample of a dataframe using just the first positive test basing on the date. I'll show a toy example. Suppose I have the folowing;

df = data.frame(guy = c("A", "B", "A", 'B', "C", "C"),
  test1 = c(1, 1, 0, 0, 1, 0),
                test2 = c(0, 1, 0, 1, 0, 0),
                test3 = c(0, 0, 1, 0, 0, 1),
                date = as.Date(c('1999-10-20', '1999-10-21', '1999-10-22', '1999-10-23', '1999-10-24', '1999-10-25')));df
   #guy test1 test2 test3       date
#1   A     1     0     0 1999-10-20
#2   B     1     1     0 1999-10-21
#3   A     0     0     1 1999-10-22
#4   B     0     1     0 1999-10-23
#5   C     1     0     0 1999-10-24
#6   C     0     0     1 1999-10-25

Now, I want to filter, selecting just the first positive test, (i.e test1|test2|test3 = 1) based on the oldest date. In my example I'd get the following:

   #guy test1 test2 test3       date
#1   A     1     0     0 1999-10-20
#2   B     1     1     0 1999-10-21
#3   C     1     0     0 1999-10-24

Data frame:

df = data.frame(guy = c("A", "B", "A", 'B', "C", "C"),
  test1 = c(1, 1, 0, 0, 1, 0),
                test2 = c(0, 1, 0, 1, 0, 0),
                test3 = c(0, 0, 1, 0, 0, 1),
                date = as.Date(c('1999-10-20', '1999-10-21', '1999-10-22', '1999-10-23', '1999-10-24', '1999-10-25')));df

Any hint on how can I do that?

dataframe filter r select database
6 Answers

And using dplyr::top_n another option would be:

df = data.frame(guy = c("A", "B", "A", 'B', "C", "C"),
                test1 = c(1, 1, 0, 0, 1, 0),
                test2 = c(0, 1, 0, 1, 0, 0),
                test3 = c(0, 0, 1, 0, 0, 1),
                date = as.Date(c('1999-10-20', '1999-10-21', '1999-10-22', '1999-10-23', '1999-10-24', '1999-10-25')))

library(dplyr)

df %>% 
  filter(test1 | test2 | test3) %>% 
  group_by(guy) %>% 
  top_n(-1, date)
#> # A tibble: 3 x 5
#> # Groups:   guy [3]
#>   guy   test1 test2 test3 date      
#>   <chr> <dbl> <dbl> <dbl> <date>    
#> 1 A         1     0     0 1999-10-20
#> 2 B         1     1     0 1999-10-21
#> 3 C         1     0     0 1999-10-24
user_129938610

A base R option using subset + ave + max.col

subset(
  df,
  as.logical(
    ave(
      max.col(df[grepl("test\\d+", names(df))], "first"),
      guy,
      FUN = function(x) x == min(x)
    )
  ) & (test1|test2|test3)
)

which gives

  guy test1 test2 test3       date
1   A     1     0     0 1999-10-20
2   B     1     1     0 1999-10-21
5   C     1     0     0 1999-10-24
user_121587570

Using dplyr 1.0.0 for any number of test columns you can do :

library(dplyr)

df %>%
  group_by(guy) %>%
  slice(which.max(rowSums(select(cur_data(), starts_with('test'))) > 0))


#   guy   test1 test2 test3 date      
#  <chr> <dbl> <dbl> <dbl> <date>    
#1 A         1     0     0 1999-10-20
#2 B         1     1     0 1999-10-21
#3 C         1     0     0 1999-10-24

The above assumes you would have atleast one row in each guy which has 1 in it. If it is not the case meaning you can have a guy without any 1 at all you can use match.

df %>%
  group_by(guy) %>%
  slice(match(TRUE, rowSums(select(cur_data(), starts_with('test'))) > 0))
user_39629140

Another approach can be done with inner_join() and reshaping data to long in order to identify old dates and values of one. Here the code:

library(tidyverse)
#Code
dfout <- df %>% inner_join(df %>% pivot_longer(-c(guy,date)) %>% group_by(guy,name) %>%
  filter(date==min(date) & value==1) %>% ungroup() %>%
  group_by(guy) %>%
  filter(!duplicated(value)) %>% select(-c(name,value)))

Output:

  guy test1 test2 test3       date
1   A     1     0     0 1999-10-20
2   B     1     1     0 1999-10-21
3   C     1     0     0 1999-10-24
user_20808480

An option with base R

subset(df, seq_len(nrow(df)) == ave(seq_len(nrow(df)) *
       (test1|test2|test3), guy, FUN = min))
#   guy test1 test2 test3       date
#1   A     1     0     0 1999-10-20
#2   B     1     1     0 1999-10-21
#5   C     1     0     0 1999-10-24
user_37322710

Try:

library(dplyr)
df %>% filter(test1 | test2 | test3 ) %>% 
       arrange(date) %>% group_by(guy) %>% 
       summarize(first(date),first(test1),first(test2),first(test3)) %>%
       ungroup

# A tibble: 3 x 5
  guy   `first(date)` `first(test1)` `first(test2)` `first(test3)`
  <chr> <date>                 <dbl>          <dbl>          <dbl>
1 A     1999-10-20                 1              0              0
2 B     1999-10-21                 1              1              0
3 C     1999-10-24                 1              0              0
user_135133280
Related