Using filter() with across() to keep all rows of a data frame that include a missing value for any variable

user_4882822 0
Viewed 10042

Sometimes I want to view all rows in a data frame that will be dropped if I drop all rows that have a missing value for any variable. In this case, I'm specifically interested in how to do this with dplyr 1.0's across() function used inside of the filter() verb.

Here is an example data frame:

df <- tribble(
  ~id, ~x, ~y,
  1, 1, 0,
  2, 1, 1,
  3, NA, 1,
  4, 0, 0,
  5, 1, NA
)

Code for keeping rows that DO NOT include any missing values is provided on the tidyverse website. Specifically, I can use:

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~ !is.na(.x)
    )
  )

Which returns:

# A tibble: 3 x 3
     id     x     y
  <dbl> <dbl> <dbl>
1     1     1     0
2     2     1     1
3     4     0     0

However, I can't figure out how to return the opposite -- rows with a missing value in any variable. The result I'm looking for is:

# A tibble: 2 x 3
     id     x     y
  <dbl> <dbl> <dbl>
1     3    NA     1
2     5     1    NA

My first thought was just to remove the !:

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~ is.na(.x)
    )
  )

But, that returns zero rows.

Of course, I can get the answer I want with this code if I know all variables that have a missing value ahead of time:

df %>% 
  filter(is.na(x) | is.na(y))

But, I'm looking for a solution that doesn't require me to know which variables have a missing value ahead of time. Additionally, I'm aware of how to do this with the filter_all() function: 

df %>% 
  filter_all(any_vars(is.na(.)))

But, the filter_all() function has been superseded by the use of across() in an existing verb. See https://dplyr.tidyverse.org/articles/colwise.html

Other unsuccessful attempts I've made are:

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~any_vars(is.na(.x))
    )
  )

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~!!any_vars(is.na(.x))
    )
  )

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~!!any_vars(is.na(.))
    )
  )

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~any(is.na(.x))
    )
  )

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~any(is.na(.))
    )
  )
r4epi dplyr r
8 Answers

It's now possible with dplyr 1.0.4. The new if_any() replaces across() for the filtering use-case.

library(dplyr)

df <- tribble(~ id, ~ x, ~ y,
              1, 1, 0,
              2, 1, 1,
              3, NA, 1,
              4, 0, 0,
              5, 1, NA)

df %>% 
  filter(if_any(everything(), is.na))
#> # A tibble: 2 x 3
#>      id     x     y
#>   <dbl> <dbl> <dbl>
#> 1     3    NA     1
#> 2     5     1    NA

Created on 2021-02-10 by the reprex package (v0.3.0)

See here for more details: https://www.tidyverse.org/blog/2021/02/dplyr-1-0-4-if-any/

user_61052590

We can use reduce

library(dplyr)
library(purrr)
df %>% 
      filter(across(everything(), is.na) %>% reduce(`|`))
# A tibble: 2 x 3
#     id     x     y
#  <dbl> <dbl> <dbl>
#1     3    NA     1
#2     5     1    NA
user_37322710

HanOostdijk replied on the RStudio Community website with a solution that uses the across() function. He writes:

"In the same article you mention tidyverse website there is 'a trick' with the rowSums function. You can use that as:"

rowAny <- function(x) {
  rowSums(x) > 0
} 

df %>% 
  filter(
    rowAny(
      across(
       .cols = everything(),
       .fns = ~ is.na(.x)
      )
    )
  )

@akrun points out in the comments below that this can be made more compact as:

df %>% 
  filter(rowSums(across(everything(), ~ is.na(.))) > 0)
user_48828220

Here is my take on this. My understanding of the new across() function is that it operates on columns, not rows. So when you run this code you don't get anything becuase

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~ is.na(.x)
    )
  )

# A tibble: 0 x 3
# … with 3 variables: id <dbl>, x <dbl>, y <dbl>

it essentially create three logical columns in the background to check the presence of element-wise NA, it then probably compute a final logical column on which it operates the filter command. Now this final column will have TRUE only if all the columns are TRUE for is.na(). To test my hypothesis, I added another row in your data that has all NA in all three columns. And when I run your code as is, I get that row as an output because now the corresponding row will generate a TRUE in that final logical vector. Now I don't know if this is how across() works exactly but this makes sense to me.

library(tidyverse)
df <- tribble(
  ~id, ~x, ~y,
  1, 1, 0,
  2, 1, 1,
  3, NA, 1,
  4, 0, 0,
  5, 1, NA,
  NA,NA,NA
)

df %>% 
  filter(
    across(
      .cols = everything(),
      .fns = ~ is.na(.x)
    )
  )
#> # A tibble: 1 x 3
#>      id     x     y
#>   <dbl> <dbl> <dbl>
#> 1    NA    NA    NA

so in order for this to work with your original data, I would use rowwise() and c_across() (rowwise version of across()) as follows

df %>% rowwise() %>% 
  filter(
    is.na(sum(c_across(everything())))
  ) %>% 
  ungroup()
#> # A tibble: 3 x 3
#>      id     x     y
#>   <dbl> <dbl> <dbl>
#> 1     3    NA     1
#> 2     5     1    NA

df %>% rowwise() %>% 
  filter(
    any(is.na(c_across(everything())))
  ) %>% 
  ungroup()
#> # A tibble: 3 x 3
#>      id     x     y
#>   <dbl> <dbl> <dbl>
#> 1     3    NA     1
#> 2     5     1    NA

Created on 2020-06-02 by the reprex package (v0.3.0)

user_37464270

or

df %>% anti_join(df %>% drop_na())
user_150192890

I found a way to do this using only base R:

df[apply(is.na.data.frame(df), 1, any), ]

I hope that helps you.

user_97132710

I think that it might help to use

setdiff(df, df %>% drop_na())

to get the result you want.

This does not use across(), but gets the job done in a simple way.

user_109732250 
> df %>% filter(!complete.cases(.))

# A tibble: 2 x 3
     id     x     y
  <dbl> <dbl> <dbl>
1     3    NA     1
2     5     1    NA

or simply

df[!complete.cases(df), ]

Unfortunately, it's not possible just invert the output of across(). Otherwise it would be as easy as

df %>% 
  filter( !across( everything(), ~!is.na(.) ) )

This reminds me, in JMP there is an option "Invert row selection". Maybe in R we need an inverted filter() too? Something like this:

anti_filter <- function(.data, ...){
  anti_join(.data, filter(.data, ...))
}

and then - a miracle:

df %>% anti_filter(across(everything(), ~!is.na(.)))
# A tibble: 2 x 3
     id     x     y
  <dbl> <dbl> <dbl>
1     3    NA     1
2     5     1    NA
user_150500290
Related