1

I am aware of the all the question regarding the filter multiple conditions with very comprehensive answers such as Q1, Q2, or even for removing NA values Q3, Q4.

But I have a different question, How I can do filter using dplyr or even data.table functions to keep both NA values and a conditional parameters?

as an example in the following I'd like to keep all of the values in Var3 which is >5 PLUS NA values.

library(data.table)
library(dplyr)

 Var1<- seq(1:5)
 Var2<- c("s", "a", "d", NA, NA)
 Var3<- c(NA, NA, 2, 5, 2) 
 Var4<- c(NA, 5, 1, 3,4)
 DT <- data.table(Var1,Var2,Var3, Var4) 
 DT
   Var1 Var2 Var3 Var4
1:    1    s   NA   NA
2:    2    a   NA    5
3:    3    d    2    1
4:    4   NA    5    3
5:    5   NA    2    4

The Expected results: 

       Var1 Var2 Var3 Var4
    1:    1    s   NA   NA
    2:    2    a   NA    5
    3:    3    d    2    1
    4:    5   NA    2    4

I have tried followings but not successful:

##Using dplyr::filter
 DT %>%  filter(!Var3 ==5)
  Var1 Var2 Var3 Var4
1    3    d    2    1
2    5 <NA>    2    4

# or

DT %>%  filter(Var3 <5 & is.na(Var3))
[1] Var1 Var2 Var3 Var4
<0 rows> (or 0-length row.names)

## using data.table 

 DT[DT[,.I[Var3 <5], Var1]$V1]
   Var1 Var2 Var3 Var4
1:   NA   NA   NA   NA
2:   NA   NA   NA   NA
3:    3    d    2    1
4:    5   NA    2    4

Any help with explanation is highly appreciated!

Daniel
  • 1,202
  • 2
  • 16
  • 25

2 Answers2

3

I think this will work. Use | to indicate or for the filters. dt2 is the expected output.

library(dplyr)

Var1 <- seq(1:5)
Var2 <- c("s", "a", "d", NA, NA)
Var3 <- c(NA, NA, 2, 5, 2) 
Var4 <- c(NA, 5, 1, 3, 4)

dt <- data_frame(Var1, Var2, Var3, Var4)

dt2 <- dt %>% filter(Var3 < 5 | is.na(Var3))
www
  • 38,575
  • 12
  • 48
  • 84
2

With data.table, we use the following logic to filter the rows where 'Var3' is less than 5 and not an NA (!is.na(Var3)) or (|) if it is an NA

DT[(Var3 < 5& !is.na(Var3)) | is.na(Var3)]
#   Var1 Var2 Var3 Var4
#1:    1    s   NA   NA
#2:    2    a   NA    5
#3:    3    d    2    1
#4:    5   NA    2    4

If we need the dplyr, just use the same logic in filter

DT %>%
   filter((Var3 <5  & !is.na(Var3)) | is.na(Var3))

As @ycw mentioned the & !is.na(Var3) is not really needed but if we remove the is.na(Var3), it becomes important

DT[, Var3 < 5 ]
#[1]    NA    NA  TRUE FALSE  TRUE

DT[, Var3 < 5  & !is.na(Var3)]
#[1] FALSE FALSE  TRUE FALSE  TRUE
akrun
  • 874,273
  • 37
  • 540
  • 662
  • Is `Var3 < 5 | is.na(Var3)` the same as `Var3 <5 & !is.na(Var3) | is.na(Var3)`? – www Aug 25 '17 at 15:07
  • @ycw It would be the same. I am a bit cautious when dealing with NAs – akrun Aug 25 '17 at 15:12
  • 1
    Thanks for your explanation. It is indeed a good idea to be cautious when dealing with `NA`. – www Aug 25 '17 at 15:21
  • 1
    Re your first line, I'd prefer to see parentheses. I have no idea how `x & y | z` gets evaluated. – Frank Aug 25 '17 at 15:34