Subset groups with conditions on number of NA

Question

Part of my data is as below:

      CUSIP  yearmon datafqtr PRIMEXCH       date   PRC  VOL       RET
1: 00003210 Nov 1970  1970 Q4        A 1970-11-16 9.875 3400 -0.091954
2: 00003210 Nov 1970  1970 Q4        A 1970-11-17 8.750 4100 -0.113924
3: 00003210 Nov 1970  1970 Q4        A 1970-11-18 9.125 5400  0.042857
4: 00003210 Nov 1970  1970 Q4        A 1970-11-19 9.375 3600  0.027397
5: 00003210 Nov 1970  1970 Q4        A 1970-11-20 9.625 3100  0.026667
6: 00003210 Nov 1970  1970 Q4        A 1970-11-23 9.250 1500 -0.038961
   SHROUT NUMTRD    vwretd   ceqq        S          A           A0
1:   2655     NA -0.001385 10.544 24558.75 0.05144521 2.094781e-06
2:   2655     NA  0.000824 10.544 24558.75 0.05144521 2.094781e-06
3:   2655     NA -0.007519 10.544 24558.75 0.05144521 2.094781e-06
4:   2655     NA  0.001180 10.544 24558.75 0.05144521 2.094781e-06
5:   2655     NA  0.009683 10.544 24558.75 0.05144521 2.094781e-06
6:   2655     NA  0.006372 10.544 24558.75 0.05144521 2.094781e-06
        Aplus     Aminus Aplus.market Aminus.market          BTM
1: 0.03421433 0.06293247   0.05269694    0.04643831 0.0004293378
2: 0.03421433 0.06293247   0.05269694    0.04643831 0.0004293378
3: 0.03421433 0.06293247   0.05269694    0.04643831 0.0004293378
4: 0.03421433 0.06293247   0.05269694    0.04643831 0.0004293378
5: 0.03421433 0.06293247   0.05269694    0.04643831 0.0004293378
6: 0.03421433 0.06293247   0.05269694    0.04643831 0.0004293378
    RET.month MOM1 MOM2 MOM3 MOM4
1: -0.1724146   NA   NA   NA   NA
2: -0.1724146   NA   NA   NA   NA
3: -0.1724146   NA   NA   NA   NA
4: -0.1724146   NA   NA   NA   NA
5: -0.1724146   NA   NA   NA   NA
6: -0.1724146   NA   NA   NA   NA

The combination of CUSIP and yearmon makes each individual group, the observations are in daily frequency. I want to subset all the observations in groups that have no more than 5 missing values in the variable VOL. That means for a specific CUSIP, in a specific month (yearmon), when there are more than 5 missing values in VOL, then the observations of this CUSIP in this month (yearmon) will be deleted from the data.

You've not provided a *minimal* and *reproducible* example here. Please trim this down significantly so that it only includes the pertinent parts: `CUSIP`, `yearmon` (is this just character?), and `VOL`, and *make sure you include data that triggers your logic*. After that, just to be certain, include what you expect the output to look like. References: [reproducible examples](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example), [SO help](http://stackoverflow.com/help/mcve). — r2evans, Nov 26 '16 at 00:19

r2evans · Accepted Answer · 2016-11-26T20:52:45.027

I offer dplyr and base-R methods.

`dplyr`

I'll provide an example using dplyr, though this is also easily done with other data.frame-managing methods (e.g., base R, data.table).

Since your data isn't usable (yet), I'll make some:

n <- 50
set.seed(42)
dat <- data_frame(
  CUSIP = sample(c("0001", "0002"), size = n, replace = TRUE),
  yearmon = sample(c("Nov 1970", "Dec 1970"), size = n, replace = TRUE),
  VOL = sample(10000, size = n, replace = TRUE)
)
dat$VOL <- ifelse(runif(n) < 0.2, NA, dat$VOL)
str(dat)
# Classes 'tbl_df', 'tbl' and 'data.frame': 50 obs. of  3 variables:
#  $ CUSIP  : chr  "0002" "0002" "0001" "0002" ...
#  $ yearmon: chr  "Nov 1970" "Nov 1970" "Nov 1970" "Dec 1970" ...
#  $ VOL    : int  6263 2172 2166 3890 9425 9627 NA NA NA 23 ...

This includes two CUSIP and two yearmons each, with variable numbers of invalid VOL fields. (Though this generates a group with 5, I'll take some liberty here and say that you want "no more than 4 NAs". This liberty is taken for simplicity of a contrived example, it should have no impact on your actual data and the code's execution.)

# demonstrate at least one group with >= 5 NAs
dat %>%
  arrange(CUSIP, yearmon) %>%
  group_by(CUSIP, yearmon) %>%
  summarize(n = sum(is.na(VOL)))
# Source: local data frame [4 x 3]
# Groups: CUSIP [?]
#   CUSIP  yearmon     n
#   <chr>    <chr> <int>
# 1  0001 Dec 1970     4
# 2  0001 Nov 1970     2
# 3  0002 Dec 1970     5
# 4  0002 Nov 1970     4

By your logic, we should completely remove the data for 0002 and Dec 1970.

# same code with the new filter added
dat %>%
  arrange(CUSIP, yearmon) %>%
  group_by(CUSIP, yearmon) %>%
  filter(sum(is.na(VOL)) < 5) %>%
  summarize(n = sum(is.na(VOL)))
# Source: local data frame [3 x 3]
# Groups: CUSIP [?]
#   CUSIP  yearmon     n
#   <chr>    <chr> <int>
# 1  0001 Dec 1970     4
# 2  0001 Nov 1970     2
# 3  0002 Nov 1970     4

This code was merely to demonstrate; the code for your use should be something as simple as:

VOL_NA_limit <- 5
newdat <- dat %>%
  group_by(CUSIP, yearmon) %>%
  filter(sum(is.na(VOL)) <= VOL_NA_limit)

Base R

If you'd prefer to not use dplyr, the same can be done with by and rbind:

do.call("rbind", by(dat, list(dat$CUSIP, dat$yearmon), function(df) {
  if (sum(is.na(df$VOL)) < VOL_NA_limit) df else NULL
}))

or split and Filter:

do.call("rbind",
        Filter(function(df) sum(is.na(df$VOL)) < VOL_NA_limit,
               split(dat, list(dat$CUSIP, dat$yearmon))))

Both of these base methods are faster than the dplyr method, though not so much as to be impressive.

i am more comfortable with data.table, could you describe how this could be done in data.table? — BobLiu, Nov 26 '16 at 20:36
No, I don't use `data.table`. See my edit for non-`dplyr` options. — r2evans, Nov 26 '16 at 20:53

Subset groups with conditions on number of NA

1 Answers1

dplyr

Base R

`dplyr`