R: filter non missing data on many (but not all) columns

user_11343736 0
Viewed 921

have the following data frame lets call it df, with the following observations

id b c f e_7 ic_107 d g j
1 23 3 66 97 8 5 7 0
2 1 1 5 7 NA NA NA NA
3 NA 2 79 5 5 4 9 0
4 0 2 32 1 6 6 1 0
5 36 6 9 49 9 NA NA NA
6 0 2 32 1 6 7 8 9
7 36 NA NA 49 9 0 0 1

I want to retain only the records which do not have NA in many, but not all, columns. Let's say, column b, c, d, g, and j.

I am currently using filter with pipes, but I would like to avoid coding like:

df_new <- df %>%
  filter(!is.na(b))%>%
  filter(!is.na(c))%>%
  filter(!is.na(d))%>%
  filter(!is.na(g))%>%
  filter(!is.na(j))

Is there an easier way to write the code?

In this example, I have 5 columns for the filtering condition. In my real dataset, I have 17. Therefore, I would like to avoid the coding above.

Also, instead of simple column names a, b, c, d..., the columns of my real dataset have long names, such as lighteningdate, depression,anxiety..., so I'd like to use a vector of column numbers (c(3:9, 13:21))rather than a list of column names in the coding.

missing-data na filter r pipe
5 Answers

We can use filter with if_all

library(dplyr)
library(purrr)
df %>%
    filter(if_all(c(b, c, d, g, j), negate(is.na)))
#   id  b c  f e_7 ic_107 d g j
#1  1 23 3 66  97      8 5 7 0
#2  4  0 2 32   1      6 6 1 0
#3  6  0 2 32   1      6 7 8 9

Or specify the index

df %>%
    filter(if_all(c(2:3, 7:9),  ~ !is.na(.)))

Or with complete.cases

df %>%
   filter(if_all(c(2:3, 7:9),  complete.cases))

which would be similar to base R option

df[complete.cases(df[c(2:3, 7:9)]),]

data

df <- structure(list(id = 1:7, b = c(23L, 1L, NA, 0L, 36L, 0L, 36L), 
    c = c(3L, 1L, 2L, 2L, 6L, 2L, NA), f = c(66L, 5L, 79L, 32L, 
    9L, 32L, NA), e_7 = c(97L, 7L, 5L, 1L, 49L, 1L, 49L), ic_107 = c(8L, 
    NA, 5L, 6L, 9L, 6L, 9L), d = c(5L, NA, 4L, 6L, NA, 7L, 0L
    ), g = c(7L, NA, 9L, 1L, NA, 8L, 0L), j = c(0L, NA, 0L, 0L, 
    NA, 9L, 1L)), class = "data.frame", row.names = c(NA, -7L
))
user_37322710

Another base R option using subset + is.na + rowMeans

> subset(df, !is.na(rowMeans(cbind(b, c, d, g, j))))
  id  b c  f e_7 ic_107 d g j
1  1 23 3 66  97      8 5 7 0
4  4  0 2 32   1      6 6 1 0
6  6  0 2 32   1      6 7 8 9
user_121587570

You can use rowSums to count number of NA values in each row and select only those rows which have no NA's.

cols <- c(2, 3, 7:9)
subset(df, rowSums(is.na(df[cols])) == 0)

#  id  b c  f e_7 ic_107 d g j
#1  1 23 3 66  97      8 5 7 0
#2  4  0 2 32   1      6 6 1 0
#3  6  0 2 32   1      6 7 8 9
user_39629140

You can try this:

library(dplyr)
library(purrr)

df <- tribble(~id,  ~b, ~c, ~f, ~e_7,   ~ic_107,    ~d, ~g, ~j,
              1,    23, 3,  66, 97, 8,  5,  7,  0,
              2,    1,  1,  5,  7,  NA, NA, NA, NA,
              3,    NA, 2,  79, 5,  5,  4,  9,  0,
              4,    0,  2,  32, 1,  6,  6,  1,  0,
              5,    36, 6,  9,  49, 9,  NA, NA, NA,
              6,    0,  2,  32, 1,  6,  7,  8,  9,
              7,    36, NA, NA, 49, 9,  0,  0,  1)


df %>% filter(map_int(split(is.na(select(df,b,c,d,g,j)),1:nrow(df)),sum)<=2L)
user_146695450

You can use across(c(.,.,.)):

library(dplyr)
df %>%
  filter(across(c(a,b,c), ~ !is.na(.)))
  a b  c  d
1 2 5 10 NA
2 3 6  7  3

Mock data:

df <- data.frame(
  a = c(NA,1,2,3,NA),
  b = c(4,NA,5,6,7),
  c = c(8,9,10,7,6),
  d = c(NA,NA,NA,3,7)
)
user_80399780
Related