Return rows with both NA and a value at once

Question

I have a df

ID <- c(101,102,103,104) 
Status <- c('P','F_Avg','F_Sig',NA) 
df <- data.frame(ID,Status)

I am trying to filter the failed ones and return both Pass and NA but I am not able to do so. I know it's a basic question but please bear with me and help me out.

I tried the following

df1 <-  sqldf("SELECT * FROM df
              WHERE Status NOT LIKE 'F%'")

and it returns only one observation and that is row1 which is 'P' but I also need row4 'NA'.

This is just an example. I have a bigger datset that has NA's and I want to apply the learning from this example to the bigger one. I don't want to declare as string — Sharath, Jul 17 '15 at 16:34
can you do `or is.na(status) = TRUE` in the `where` clause in R? — Vamsi Prabhala, Jul 17 '15 at 16:37
is usage of `sqldf` mandatory? It's somewhat trivial to get your desired output in base R. — erasmortg, Jul 17 '15 at 16:52
`df[df$Status %in% c("P",NA),]` would get you your desired result in base R — erasmortg, Jul 17 '15 at 17:01

mpalanco · Accepted Answer · 2015-07-17T17:38:38.153

2

df1 <-  sqldf("SELECT * FROM df
              WHERE (Status NOT LIKE 'F%'
              OR Status IS null)")

Output:

   ID Status
1 101      P
2 104   <NA>

Using dplyr:

library(dplyr)
filter(df, !grepl("^F", Status))

edited Jul 17 '15 at 17:38

answered Jul 17 '15 at 17:06

mpalanco

12,960
2
59
67

score 1 · Answer 2 · answered Jul 17 '15 at 16:45

1

Your sql must be this:

SELECT * FROM df
WHERE Status NOT LIKE 'F%'
or status = 'NA'

I'm not sure how you achieve that in r.

answered Jul 17 '15 at 16:45

Dan Bracuk

20,699
4
26
43

Return rows with both NA and a value at once

2 Answers2