How to remove row if it has a NA value in one certain column [duplicate]

Question

My data called "dat":

A   B   C
NA  2   NA
1   2   3
1   NA  3
1   2   3

I want to be all rows to be removed if it has an NA in column B:

A   B   C
NA  2   NA
1   2   3
1   2   3

na.omit(dat) removes all rows with an NA not just the ones where the NA is in column B.

Also I'd like to know how to this for NA value in two columns.

I appreciate all advice!

Flied

Try with is.na i.e. dat[!is.na(dat$B),] or subset(dat, !is.na(B)) or library(dplyr);dat %>% filter(!is.na(B)) or data.table setDT(dat)[!is.na(B)] – Hull

Answer 1

The easiest solution is to use is.na():

df[!is.na(df$B), ]

which gives you:

   A B  C
1 NA 2 NA
2  1 2  3
4  1 2  3

Midkiff

Why do you need the comma "," after "(df$B)" ? – Transport

when you subset a data.frame with square brackets, the element before the comma applies to rows, the element after the comma to columns. if you don't use the comma, R will assume you are selecting columns. – Midkiff

This answer provides a big speed improvement over the tidyverse option! Average speed of 200us (base) vs 1400ms (tidyverse) – Sororate

Answer 2

there is an elegant solution if you use the tidyverse!

it contains the library tidyr that provides the method drop_na which is very intuitive to read.

So you just do:

library(tidyverse)

dat %>% drop_na("B")

OR

dat %>% drop_na(B)

if B is a column name

Elmore

I'd add that this answer is useful if passing strings to functions which are intended to be used as column names. – Unctuous

Answer 3

try this:

df<-data.frame(A=c(NA,1,1,1),B=c(2,2,NA,2),C=c(NA,3,3,3))
df<-df[-which(is.na(df$B)),]
df
   A B  C
1 NA 2 NA
2  1 2  3
4  1 2  3

Cheeky

Answer 4

This should work

dat <- dat[dat['B'].notnull()]  

Tushy