How to leave out rows with missing values when total no. of values crosses a given value in R

Question

I have a dataset which contains 45% of Missing values:

I would like to remove the rows which has NA's values for a given period. for example, if there are rows continuously has missing values ,for almost an hour or more than 50 values missing continuously , i want to remove that rows alone. And i don't want to leave the rows with missing values less than 15 or 25.

In short, 1) I don't want to remove all rows that has got NA value's. 2) I want to remove rows that continuously has NA values in a column

example data: pic

Welcome to StackOverflow! Please read the info about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). This will make it much easier for others to help you. — Sotos, Sep 12 '17 at 09:34
Ideas involving `is.na()` & `cumsum()` come to mind. But without a clearer description of the desired state / a sample dataset to try out, they'll remain ideas. — Z.Lin, Sep 12 '17 at 09:42
I have added how data looks, It has many NA values. I dont want to remove all NA values. I just want to remove NA values with rows counting more than 50 — Jonreyan, Sep 12 '17 at 09:53

CPak · Accepted Answer · 2017-09-13T14:14:18.173

Discard columnwise contiguous NAs

Try this, which uses rle(is.na...)) to determine runs of NAs. If any are > num_runs then it is discarded (Data at bottom)

myfun <- function(x, num_runs) {
              # x is vector column of df
              require(dplyr)
              runs <- cumsum(rle(is.na(x))$lengths)
              vals <- rle(is.na(x))$values
              start <- dplyr::lag(runs)+1
              start <- replace(start, is.na(start), 1)
              M <- rbind(start[vals], runs[vals])
              seqruns <- apply(M, 2, function(x) if ((x[2]-x[1]+1) > num_runs) { seq(x[1],x[2]) })
              ans <- unlist(seqruns)
              return(ans)
         }

library(purrr)
library(dplyr)
num_runs <- 4
discard <- unlist(map(1:ncol(df), ~myfun(df[,.x, num_runs])))
df[-discard,]

Output

                   mpg cyl  disp  hp drat    wt  qsec vs am gear carb
Mazda RX4         21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
Mazda RX4 Wag     21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
Datsun 710        22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
Hornet 4 Drive    21.4   6 258.0 110 3.08 3.215 19.44  1  0   NA    1
Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0   NA    2
Valiant           18.1   6 225.0 105 2.76 3.460 20.22  1  0   NA    1
Duster 360        14.3   8 360.0 245 3.21 3.570 15.84  0  0   NA    4
Merc 240D         24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
Merc 230          22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
Merc 280          19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
Merc 280C         17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
Fiat 128          32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
Honda Civic       30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
Toyota Corolla    33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
Fiat X1-9         27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
Porsche 914-2     26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
Lotus Europa      30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
Ford Pantera L    15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
Ferrari Dino      19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
Maserati Bora     15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
Volvo 142E        21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

Discard rowwise contiguous NAs

Try this, which uses rle(is.na...)) to determine runs of NAs. If any are > num_runs then it is discarded (Data at bottom)

library(purrr)
num_runs <- 1    # number of contiguous NAs
keep <- map_lgl(1:nrow(df), ~!any(rle(is.na(unlist(df[.x,])))$lengths[rle(is.na(unlist(df[.x,])))$values] > num_runs))
df[keep,]

Output

                     mpg cyl  disp  hp drat    wt  qsec vs am gear carb
Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0   NA    1
Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0   NA    2
Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0   NA    1
Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0   NA    4
Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0   NA    4
Lincoln Continental 10.4   8 460.0 215   NA 5.424 17.82  0  0   NA    4
Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0   NA    4
Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0   NA    1
Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0   NA    2
AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0   NA    2
Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0   NA    4
Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0   NA    2
Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

Data

library(dplyr)
df <- mtcars %>% replace(.==3, NA)

Hi thanks. here in the column **gear** in **df** , there are continuous NA values (6 NA's) i.e, in rows Merc 450SE, Merc 450SL, Merc 450SLC , Cadillac Fleetwood, Lincoln Continental, Chrysler Imperial. But the output has dropped only the three rows. I want that whole continuous rows should be dropped including "Cadillac Fleetwood, Lincoln Continental, Chrysler Imperial" — Jonreyan, Sep 12 '17 at 10:46
I thought you wanted continous NAs in rows. I'll revise for continuous NAs in columns. — CPak, Sep 12 '17 at 10:49
I have a small issue. The above function works only for two consecutive NA's in a column. For example, i have added small example. In column 'x', you can see data missing at 1:3, 14:16 and 19:20 (added picture). This has three consecutive NA's and When i use the above function it throws error `Error in if (x[2] - x[1] + 1 > num_runs) { : missing value where TRUE/FALSE needed` . Is it possible to add 'n' no of consecutive columns in the function? — Jonreyan, Sep 13 '17 at 05:50
Try the updated answer. I cleaned up the code by placing the operations in a function. — CPak, Sep 13 '17 at 14:15

How to leave out rows with missing values when total no. of values crosses a given value in R

1 Answers1