Subset of rows containing NA (missing) values in a chosen column of a data frame

Question

We have a data frame from a CSV file. The data frame DF has columns that contain observed values and a column (VaR2) that contains the date at which a measurement has been taken. If the date was not recorded, the CSV file contains the value NA, for missing data.

Var1  Var2 
10   2010/01/01
20   NA
30   2010/03/01

We would like to use the subset command to define a new data frame new_DF such that it only contains rows that have an NA' value from the column (VaR2). In the example given, only Row 2 will be contained in the new DF.

The command

new_DF<-subset(DF,DF$Var2=="NA")

does not work, the resulting data frame has no row entries.

If in the original CSV file the Value NA are exchanged with NULL, the same command produces the desired result: new_DF<-subset(DF,DF$Var2=="NULL").

How can I get this method working, if for the character string the value NA is provided in the original CSV file?

score 160 · Answer 1 · edited Jan 20 '16 at 10:03

160

Never use =='NA' to test for missing values. Use is.na() instead. This should do it:

new_DF <- DF[rowSums(is.na(DF)) > 0,]

or in case you want to check a particular column, you can also use

new_DF <- DF[is.na(DF$Var),]

In case you have NA character values, first run

Df[Df=='NA'] <- NA

to replace them with missing values.

edited Jan 20 '16 at 10:03

JelenaČuklina

2,839
2
19
32

answered Nov 02 '11 at 13:02

Joris Meys

98,937
27
203
258

2

Thanks for your fast answer (this was quick)! Indeed, due to the csv-delivery of the data, the 'NA' are character values and your second statement might be very useful. Can you also clarify your first statement? The use of rowSums() is not clear for me, since I will only check a particular column (there are plenty of columns). If that particular column (in the example it would be column Var2) has there an 'NA' character string (I will replace it with your second statement), then I would like to choose the entire row to be part of the new data frame. – John Nov 02 '11 at 14:29
@John : updated. THe point is to use is.na, I wrongly interpreted you wanted to check all variables. – Joris Meys Nov 02 '11 at 21:34
3

should that be `new_DF – PatrickT Jul 24 '13 at 19:01

score 40 · Answer 2 · answered Nov 02 '11 at 13:32

40

NA is a special value in R, do not mix up the NA value with the "NA" string. Depending on the way the data was imported, your "NA" and "NULL" cells may be of various type (the default behavior is to convert "NA" strings to NA values, and let "NULL" strings as is).

If using read.table() or read.csv(), you should consider the "na.strings" argument to do clean data import, and always work with real R NA values.

An example, working in both cases "NULL" and "NA" cells :

DF <- read.csv("file.csv", na.strings=c("NA", "NULL"))
new_DF <- subset(DF, is.na(DF$Var2))

answered Nov 02 '11 at 13:32

maressyl

943
6
11

1

Thanks for your answer. If I understand it correctly the first statement would do the same as Df[Df=='NA'] – John Nov 02 '11 at 14:42
Exactly. Joris suggested to replace "NA" strings by NA values manually, here i only suggest to use the "na.strings" feature of read.table() to achieve the same purpose. – maressyl Nov 02 '11 at 15:33
Joris' answer is actually the "preferred" way to accomplish this feat (if you are writing this in a script). See: http://stackoverflow.com/questions/9860090/in-r-why-is-better-than-subset – Jonathan Feb 22 '13 at 17:30
@Jonathan : Two distinct ideas here, the topic you cite says "[" should be prefered on "subset", but we were talking about the "na.strings" argument in read.table(), my subset was here only to visualize the effects. – maressyl Aug 01 '13 at 08:02

score 38 · Answer 3 · answered Nov 03 '17 at 09:35

38

complete.cases gives TRUE when all values in a row are not NA

DF[!complete.cases(DF), ]

answered Nov 03 '17 at 09:35

user3226167

2,029
2
25
29

score 14 · Answer 4 · edited Jul 27 '18 at 12:22

14

new_data <- data %>% filter_all(any_vars(is.na(.)))

This should create a new data frame (new_data) with only the missing values in it.

Works best to keep a track of values that you might later drop because they had some columns with missing observations (NA).

edited Jul 27 '18 at 12:22

Dmitriy

5,347
12
23
37

answered Jul 27 '18 at 12:01

Ronak Pol

141
1
3

score 3 · Answer 5 · edited Nov 22 '17 at 01:06

3

Try changing this:

new_DF<-dplyr::filter(DF,is.na(Var2))

edited Nov 22 '17 at 01:06

csilk

1,986
2
19
37

answered Nov 21 '17 at 23:57

drhnis

63
2

1

Could you explain why this works, what this does etc.? – csilk Nov 22 '17 at 00:19
new_DF – drhnis Nov 28 '17 at 19:56
1

More nicely expressed as `DF %>% filter(is.na(Var2))` after `library(dplyr)`. – Joe Feb 22 '18 at 13:15

score -1 · Answer 6 · edited May 29 '16 at 09:25

-1

Prints all the rows with NA data:

tmp <- data.frame(c(1,2,3),c(4,NA,5));
tmp[round(which(is.na(tmp))/ncol(tmp)),]

edited May 29 '16 at 09:25

Manfred Radlwimmer

12,469
13
47
56

answered May 29 '16 at 06:28

jstar

597
7
9

@ZheyuanLi If you don't like the answer, simply down-vote it. Editing the answer to recommend flagging is NOT the appropiate action. Leave a comment if you feel the need to. – Manfred Radlwimmer May 29 '16 at 09:25

Subset of rows containing NA (missing) values in a chosen column of a data frame

6 Answers6

Linked

Related