dplyr filter with condition on multiple columns

user_4887832 0
Viewed 99080

I'd like to remove rows corresponding to a particular combination of variables from my data frame.

Here's a dummy data :

father<- c(1, 1, 1, 1, 1)
mother<- c(1, 1, 1, NA, NA) 
children <- c(NA, NA, 2, 5, 2) 
cousins   <- c(NA, 5, 1, 1, 4) 


dataset <- data.frame(father, mother, children, cousins)  
dataset


father  mother  children cousins
1      1       NA      NA
1      1       NA       5
1      1        2       1
1     NA        5       1
1     NA        2       4

I want to filter this row :

  father  mother  children cousins
    1      1       NA      NA

I can do it with :

test <- dataset %>% 
filter(father==1 & mother==1) %>%
filter (is.na(children)) %>%
filter (is.na(cousins))
test

My question : I have many columns like grand father, uncle1, uncle2, uncle3 and I want to avoid something like that:

  filter (is.na(children)) %>%
  filter (is.na(cousins)) %>%
  filter (is.na(uncle1)) %>%
  filter (is.na(uncle2)) %>%
  filter (is.na(uncle3)) 
  and so on...

How can I use dplyr to say filter all the column with na (except father==1 & mother==1)

dplyr dataframe r
6 Answers

dplyr >= 1.0.4

If you're using dplyr version >= 1.0.4 you really should use if_any or if_all, which specifically combines the results of the predicate function into a single logical vector making it very useful in filter. The syntax is identical to across, but these verbs were added to help fill this need: if_any/if_all.

library(dplyr)

dataset %>% 
  filter(if_all(-c(father, mother), ~ is.na(.)), if_all(c(father, mother), ~ !is.na(.)))

Here I have written out the variable names, but you can use any tidy selection helper to specify variables (e.g., column ranges by name or location, regular expression matching, substring matching, starts with/ends with, etc.).

Output

  father mother children cousins
1      1      1       NA      NA
user_63824340

This answer builds on @Feng Jiangs answer using the dplyr::left_joint() operation, and is more like a reprex. In addition, it ensures the proper order of columns is restored in case the order of variables in df_filter differs from the order of the variables in the original dataset. Also, the dataset was expanded for a duplicate combination to show these are part of the filtered output (df_out).

library(dplyr)

father<- c(1, 1, 1, 1, 1,1)
mother<- c(1, 1, 1, NA, NA,1) 
children <- c(NA, NA, 2, 5, 2,NA) 
cousins   <- c(NA, 5, 1, 1, 4,NA)

dataset <- data.frame(father, mother, children, cousins) 
df_filter <- data.frame( father = 1, mother = 1, children = NA, cousins = NA)
                         
test  <- df_filter  %>% 
  left_join(dataset) %>% 
  relocate(colnames(dataset))
user_172979260
Related