Select rows with all or less than n number NAs in specific column in R

user_7996904 0
Viewed 79

I want to filter my data frame according to a specific conditions. I want to keep all records that have only NA for variable x1 per id Or with less then n valid variables. let's say I want to keep id = bb (all records that have only NA for variable x1) and also all records for id=aa and id==cc which have less than 3 valid values 

df <- tibble::tribble(
   ~id,    ~date, ~x1, ~x2, ~x3, ~x4,
  "aa", 20200309, 204,  NA,  NA, 233,
  "aa", 20200310,  NA, 227,  NA, 178,
  "aa", 20200311,  NA, 144,   1, 532,
  "aa", 20200312,  NA,  72,  NA, 478,
  "bb", 20200313,  NA,  NA,  NA, 396,
  "bb", 20200314,  NA, 185,   3, 417,
  "bb", 20200315,  NA, 106,  NA, 292,
  "bb", 20200316,  NA,  63,  NA, 439,
  "bb", 20200317,  NA,  37,   4, 461,
  "bb", 20200318,  NA,  33,   1, 458,
  "cc", 20200314,  NA, 185,   3, 417,
  "cc", 20200315,  56, 106,  NA, 292,
  "cc", 20200316,  NA,  63,  NA, 439,
  "cc", 20200317,  NA,  37,   4, 461,
  "cc", 20200318,  105,  33,   1, 458,
  "dd", 20200316,  NA,  63,  NA, 439,
  "dd", 20200317,  NA,  37,   4, 461,
  "dd", 20200318,  NA,  33,   1, 458,
  "dd", 20200314,  NA, 185,   3, 417,
  "dd", 20200315,  56, 106,  NA, 292,
  "dd", 20200316,  NA,  63,  NA, 439,
  "dd", 20200317,  15,  37,   4, 461,
  "dd", 20200318,  105,  33,   1, 458 )

I am using filter_at for first condition (keep all records that have only NA for variable x1 per id) but not sure how to add the second condition

df %>% 
  group_by(id) %>% 
  filter_at(vars(x1), all_vars(all(is.na(.)) ))
dplyr tidyverse r
2 Answers

One option could be:

df %>%
 group_by(id) %>%
 filter(sum(!is.na(x1)) < 3)

   id        date    x1    x2    x3    x4
   <chr>    <dbl> <dbl> <dbl> <dbl> <dbl>
 1 aa    20200309   204    NA    NA   233
 2 aa    20200310    NA   227    NA   178
 3 aa    20200311    NA   144     1   532
 4 aa    20200312    NA    72    NA   478
 5 bb    20200313    NA    NA    NA   396
 6 bb    20200314    NA   185     3   417
 7 bb    20200315    NA   106    NA   292
 8 bb    20200316    NA    63    NA   439
 9 bb    20200317    NA    37     4   461
10 bb    20200318    NA    33     1   458
11 cc    20200314    NA   185     3   417
12 cc    20200315    56   106    NA   292
13 cc    20200316    NA    63    NA   439
14 cc    20200317    NA    37     4   461
15 cc    20200318   105    33     1   458
user_59645570

Another option is

library(dplyr)
df %>%
    group_by(id) %>%
    filter(length(which(!is.na(x1))) < 3)
user_37322710
Related