dplyr: how to filter ignoring empty values

user_16488952 0
Viewed 245

I want to filter with a tibble ignoring empty values.

Example, when the variables tibble have not empty values the filter works fine:

library(tidyverse)
library(datasets)

variables <- tibble(manufacturer = 'audi',
                    model = 'a4',
                    cyl = '6'
                    )
filtered <- mpg %>%
  filter(manufacturer == variables$manufacturer,
         model == variables$model,
         cyl == variables$cyl)

# A tibble: 3 x 11
  manufacturer model displ  year   cyl trans    drv     cty   hwy fl   
  <chr>        <chr> <dbl> <int> <int> <chr>    <chr> <int> <int> <chr>
1 audi         a4      2.8  1999     6 auto(l5) f        16    26 p    
2 audi         a4      2.8  1999     6 manual(~ f        18    26 p    
3 audi         a4      3.1  2008     6 auto(av) f        18    27 p    
# ... with 1 more variable: class <chr>

When I put an '' (or NA) value on variables tibble, dplyr finds no matches and outputs a tibble with 0 obs.

# A tibble: 0 x 11
# ... with 11 variables: manufacturer <chr>, model <chr>, displ <dbl>,
#   year <int>, cyl <int>, trans <chr>, drv <chr>, cty <int>,
#   hwy <int>, fl <chr>, class <chr>

I want dplyr to ignore the empty values ​​of variables and filter the others, is there any way to do it? Greetings!

dplyr r
1 Answers

An option is to select the columns of 'variables' to create new dataset without the NA or blank ("") and then use filter with across

library(dplyr)
library(ggplot2)
variables$model <- "" # changing some columns to blank, NA for testing
variables$cyl <- NA_integer_

variables2 <- variables %>%
      select(where(~ !all(is.na(.)| . == '')))
variables2
# A tibble: 1 x 1
#  manufacturer
#  <chr>       
#1 audi      

mpg %>%
     filter(across(all_of(names(variables2)), 
            ~ . == variables2[[cur_column()]]))

-output

# A tibble: 18 x 11
   manufacturer model      displ  year   cyl trans      drv     cty   hwy fl    class  
   <chr>        <chr>      <dbl> <int> <int> <chr>      <chr> <int> <int> <chr> <chr>  
 1 audi         a4           1.8  1999     4 auto(l5)   f        18    29 p     compact
 2 audi         a4           1.8  1999     4 manual(m5) f        21    29 p     compact
 3 audi         a4           2    2008     4 manual(m6) f        20    31 p     compact
 4 audi         a4           2    2008     4 auto(av)   f        21    30 p     compact
 5 audi         a4           2.8  1999     6 auto(l5)   f        16    26 p     compact
 6 audi         a4           2.8  1999     6 manual(m5) f        18    26 p     compact
 7 audi         a4           3.1  2008     6 auto(av)   f        18    27 p     compact
 8 audi         a4 quattro   1.8  1999     4 manual(m5) 4        18    26 p     compact
 9 audi         a4 quattro   1.8  1999     4 auto(l5)   4        16    25 p     compact
10 audi         a4 quattro   2    2008     4 manual(m6) 4        20    28 p     compact
11 audi         a4 quattro   2    2008     4 auto(s6)   4        19    27 p     compact
12 audi         a4 quattro   2.8  1999     6 auto(l5)   4        15    25 p     compact
13 audi         a4 quattro   2.8  1999     6 manual(m5) 4        17    25 p     compact
14 audi         a4 quattro   3.1  2008     6 auto(s6)   4        17    25 p     compact
15 audi         a4 quattro   3.1  2008     6 manual(m6) 4        15    25 p     compact
16 audi         a6 quattro   2.8  1999     6 auto(l5)   4        15    24 p     midsize
17 audi         a6 quattro   3.1  2008     6 auto(s6)   4        17    25 p     midsize
18 audi         a6 quattro   4.2  2008     8 auto(s6)   4        16    23 p     midsize

If we use the OP's code, this returns 0 rows as some columns doesn't have any matches

> mpg %>%
+   filter(manufacturer == variables$manufacturer,
+          model == variables$model,
+          cyl == variables$cyl)
# A tibble: 0 x 11
# … with 11 variables: manufacturer <chr>, model <chr>, displ <dbl>, year <int>, cyl <int>, trans <chr>, drv <chr>, cty <int>, hwy <int>, fl <chr>, class <chr>
user_37322710
Related