How to remove rows of dataframe after a particular string is detected, using filter and dplyr

user_5115721 0
Viewed 124

I have data like the example below. For each participant, if a particular string ("trial_end") is present in the my_strings column I would like to remove all the rows after its appearance.

library(dplyr)
library(stringr)
library(tibble)

df1 <- tibble::tribble(
  ~participant_id, ~timestamp,     ~my_strings,
  1L,        1L,  "other_string",
  1L,        2L,  "other_string",
  1L,        3L, "trial_end",
  1L,        4L,  "other_string",
  2L,        1L,  "other_string",
  2L,        2L,  "other_string",
  2L,        3L,  "other_string",
  2L,        4L,  "other_string",
  3L,        1L,  "other_string",
  3L,        2L, "trial_end",
  3L,        3L,  "other_string",
  3L,        4L,  "other_string"
)

My first attempt was to use str_detect to look for the presence of the string, which to provide the row number, then use filter to keep only that row and all those before it:

df2 <- df1 %>% 
  group_by(participant_id) %>%
        filter(row_number() < (which(str_detect(my_strings, "trial_end"))) + 1)

This seems to throw an error when the string is not detected (e.g. participant 2 in the example here).

My next attempt was to add a conditional if_else, trying to effectively say 'IF the target string is detected THEN remove all the rows after for that participant, ELSE if the string is not detected, do nothing'.

df3 <- df1 %>% 
  group_by(participant_id) %>%
  if_else(str_detect(my_strings, "trial_end"),
        filter(row_number() < (which(str_detect(my_strings, "trial_end"))) + 1),
        filter(timestamp < max(timestamp)))

This also returned an error: Error: condition must be a logical vector, not a grouped_df/tbl_df/tbl/data.frame object.

My final attempt tried to make use of another answer already here, by placing the conditional if else inside filter, but this too produced an error.

df4 <- df1 %>% 
  group_by(participant_id) %>%
  filter(if(str_detect(my_strings, "trial_end") < (which(str_detect(my_strings, "trial_end")) + 1)) 
            else < n())

Can anyone point out the best approach for this problem? Is filter the wrong way to go about it?

Many thanks.

For clarity, the desired outcome would look like this:

desired_output <- tibble::tribble(
                    ~participant_id, ~timestamp,    ~my_strings,
                                 1L,         1L, "other_string",
                                 1L,         2L, "other_string",
                                 1L,         3L,    "trial_end",
                                 2L,         1L, "other_string",
                                 2L,         2L, "other_string",
                                 2L,         3L, "other_string",
                                 2L,         4L, "other_string",
                                 3L,         1L, "other_string",
                                 3L,         2L,    "trial_end"
                    )
stringr dplyr subset filter r
4 Answers

You could count the cumulative sum of matches up to the prior row and filter to only include rows up to the first match per participant:

df1 %>%
  group_by(participant_id) %>%
  filter(lag(cumsum(my_strings == "trial_end"), default = 0) < 1) %>%
  ungroup()

# A tibble: 9 x 3
  participant_id timestamp my_strings  
           <int>     <int> <chr>       
1              1         1 other_string
2              1         2 other_string
3              1         3 trial_end   
4              2         1 other_string
5              2         2 other_string
6              2         3 other_string
7              2         4 other_string
8              3         1 other_string
9              3         2 trial_end
user_68518250

One option could be:

df1 %>%
    group_by(participant_id) %>%
    slice(if(all(my_strings != "trial_end")) 1:n() else 1:which(my_strings == "trial_end"))

  participant_id timestamp my_strings  
           <int>     <int> <chr>       
1              1         1 other_string
2              1         2 other_string
3              1         3 trial_end   
4              2         1 other_string
5              2         2 other_string
6              2         3 other_string
7              2         4 other_string
8              3         1 other_string
9              3         2 trial_end
user_59645570

You can write a small helper function to drop rows after a string is detected, if the string is not detected it does not drop anything.

library(dplyr)
drop_string_after <- function(string_vec, string) {
  i <- match(string, string_vec)
  if(is.na(i)) seq_along(string_vec) else seq_len(i)
}

and apply this function for each participant :

df1 %>%
  group_by(participant_id) %>%
  slice(drop_string_after(my_strings, 'trial_end')) %>%
  ungroup

#  participant_id timestamp my_strings  
#           <int>     <int> <chr>       
#1              1         1 other_string
#2              1         2 other_string
#3              1         3 trial_end   
#4              2         1 other_string
#5              2         2 other_string
#6              2         3 other_string
#7              2         4 other_string
#8              3         1 other_string
#9              3         2 trial_end

To use filter you need to change the return value of the function.

drop_string_after <- function(string_vec, string) {
  i <- match(string, string_vec)
  if(is.na(i)) TRUE else row_number() <= i
}

df1 %>%
  group_by(participant_id) %>%
  filter(drop_string_after(my_strings, 'trial_end')) %>%
  ungroup
user_39629140 
critpart <- 0
for (i in 1:nrow(df1)){
  if (is.na(df1$participant_id[i])) next
  if (df1$my_strings[i] == "trial_end"){
    critpart <- df1$participant_id[i]
    next
  }
  if (df1$participant_id[i] == critpart){
    df1[i,] <- NA
  }
}

df1 <- df1 %>% filter(!is.na(participant_id))
user_115721730
Related