How to create a new column based on if any of a subset of columns are NA with the dplyr

user_2572423 0
Viewed 168

This feels like this should be possible with mutate_at or mutate(across(...)), but I'm not understanding something...

Suppose we have the following. I am including the desired output desired which is an indicator column based on whether any of the columns containing the word "test" have an NA value:

library(tidyverse)

df <- tibble::tribble(
  ~id,    ~name, ~test_col, ~is_test, ~another_test, ~desired,
   1L, "mickey",        NA,      13L,           12L,       1L,
   2L, "donald",       19L,       NA,            NA,       1L,
   3L,  "daisy",       15L,      20L,           20L,       0L,
   4L,  "goofy",       18L,      14L,           10L,       0L,
   5L,  "pluto",       16L,      10L,            NA,       1L,
   6L, "minnie",       19L,      15L,           16L,       0L
  )

df
#> # A tibble: 6 x 6
#>      id name   test_col is_test another_test desired
#>   <int> <chr>     <int>   <int>        <int>   <int>
#> 1     1 mickey       NA      13           12       1
#> 2     2 donald       19      NA           NA       1
#> 3     3 daisy        15      20           20       0
#> 4     4 goofy        18      14           10       0
#> 5     5 pluto        16      10           NA       1
#> 6     6 minnie       19      15           16       0

But in reality we start without the desired column: df_start <- df %>% select(-desired).

I can successfully use fiter_at to get only the observations where one or more of the columns containing "test" are NA:

df_start %>% 
  filter_at(vars(contains("test")), any_vars(is.na(.)))
#> # A tibble: 3 x 5
#>      id name   test_col is_test another_test
#>   <int> <chr>     <int>   <int>        <int>
#> 1     1 mickey       NA      13           12
#> 2     2 donald       19      NA           NA
#> 3     5 pluto        16      10           NA

I could save this subset and then do use bind_rows, but I would like to create the desired column in one pipeline. Again, this feels like this should be doable with mutate_at or mutate(across(...)) but I have yet to be successful.

Question: How do you create the indicator column desired in one pipeline with dplyr?

Examples created on 2021-08-29 by the reprex package (v2.0.0)

dplyr r
4 Answers

You could use

library(dplyr)

df %>% 
  mutate(desired = +if_any(contains("test"), is.na))

to get

# A tibble: 6 x 6
     id name   test_col is_test another_test desired
  <int> <chr>     <int>   <int>        <int>   <int>
1     1 mickey       NA      13           12       1
2     2 donald       19      NA           NA       1
3     3 daisy        15      20           20       0
4     4 goofy        18      14           10       0
5     5 pluto        16      10           NA       1
6     6 minnie       19      15           16       0
user_125052510

Here is a way how we could do it with across:

Update: With the help of genius Martin Gal the short version:

df %>% 
    select(-desired) %>% 
    mutate(desired = +(rowSums(is.na(across(test_col:another_test))) > 0))

The long version, :-)

library(dplyr)
df %>% select(-desired) %>% 
    mutate(across(test_col:another_test, ~ case_when(is.na(.) ~ 1, 
                                                     TRUE ~ 0), .names ="New_{.col}")) %>% 
    mutate(desired = ifelse(New_test_col == 1 | 
                            New_is_test == 1 |
                            New_another_test == 1, 1, 0), .keep="unused")

Output:

     id name   test_col is_test another_test desired
  <int> <chr>     <int>   <int>        <int>   <dbl>
1     1 mickey       NA      13           12       1
2     2 donald       19      NA           NA       1
3     3 daisy        15      20           20       0
4     4 goofy        18      14           10       0
5     5 pluto        16      10           NA       1
6     6 minnie       19      15           16       0
user_133216470

An option with map/reduce

library(dplyr)
library(purrr)
df %>%
    mutate(desired = map(select(., contains('test')),is.na ) %>% 
          reduce(`|`) %>% 
       as.integer )

-output

# A tibble: 6 x 6
     id name   test_col is_test another_test desired
  <int> <chr>     <int>   <int>        <int>   <int>
1     1 mickey       NA      13           12       1
2     2 donald       19      NA           NA       1
3     3 daisy        15      20           20       0
4     4 goofy        18      14           10       0
5     5 pluto        16      10           NA       1
6     6 minnie       19      15           16       0
user_37322710

Interestingly, every now and then the same answers always show up for these kinds of rowwise logical operations - MartinGals's if_any() , TarJae's RowSums(across()) and akrun's map%>%reduce.

For completeness' sake, another common favourite, which is the most verbose and less elegant (and one of the least efficient ones), is the pmapapproach:

output<-df%>%mutate(desired=pmap(across(contains('test')), ~{
        vector<-c(...)
        +any(is.na(vector))})

# OR simply

output<-df%>%mutate(desired=pmap(across(contains('test')), ~{
        +any(is.na(c(...)))})
user_139723330
Related