Filter dataframe within a group with one column meeting an AND condition in R-CodePudding

I have the following dataframe for which I need to filter only those rows that have both an "intake" and "discharge" per group (id). The result should go from looking like this:

> df <- tibble(id = c(1, 1, 2, 3, 3, 4, 4, 5, 6, 7, 7),
               type = c("intake", "discharge", "intake", "intake", "discharge",
                        "intake", "discharge", "intake", "intake", "intake", "discharge"))
> df
      id type     
   <dbl> <chr>    
 1     1 intake   
 2     1 discharge
 3     2 intake   
 4     3 intake   
 5     3 discharge    
 6     4 intake   
 7     4 discharge
 8     5 intake   
 9     6 intake   
10     7 intake   
11     7 discharge

To this:

      id type     
   <dbl> <chr>    
 1     1 intake   
 2     1 discharge   
 3     3 intake   
 4     3 discharge    
 5     4 intake   
 6     4 discharge 
 7     7 intake   
 8     7 discharge

So that groups (ids) that do not have both an intake AND a discharge are removed (and only those that do have both are kept).

I hope that makes sense... sorry it has been a long day.

CodePudding user response：

Here's a way to select groups that have both "intake" and "discharge".

library(dplyr)

values <- c('intake', 'discharge')

df %>%
  group_by(id) %>%
  filter(all(values %in% type) & type %in% values) %>%
  ungroup

#     id type     
#  <dbl> <chr>    
#1     1 intake   
#2     1 discharge
#3     3 intake   
#4     3 discharge
#5     4 intake   
#6     4 discharge
#7     7 intake   
#8     7 discharge

all(values %in% type) selects the complete group which has both the values whereas type %in% values would select within those groups rows which has either of the two values.

CodePudding user response：

library(dplyr)
df %>%
  group_by(id) %>%
  filter(sum(type == "intake") >= 1,
         sum(type == "discharge") >= 1) %>%
  # add below if we only want intake/discharge lines
  # filter(type %in% c("intake", "discharge")) %>% 
  ungroup()

Result (varies due to addition of "other" in OP, unclear desired behavior)

# A tibble: 9 x 2
     id type     
  <dbl> <chr>    
1     1 intake   
2     1 discharge
3     3 intake   
4     3 discharge
5     3 other    
6     4 intake   
7     4 discharge
8     7 intake   
9     7 discharge