Numbers of missing values by group in R

Question

I have a data-frame with several missing values (NAs), grouped into a number of groups (A,B,C,D,E,F) in a column named Group. I am trying to analyse it in R.

I want to tabulate the number of rows/records belonging to each group, or better still the proportion of rows/records within each group (ie out of the total number of rows/records within that group), which have: >= 1 NA (i.e. at least 1 missing val), >=2 NAs, >=3 NAs, >=4 NAs, so forth (up to n NAs, which I would predefine).

I want to do it with just base R and dplyr. I guess a solution would start with df %>% group_by(Group) but I'm not sure where to go from there.

Any ideas?

Edit: Let's take iris for data:

iris0 <- iris
set.seed(101)
iris0[sample(150,40),1] <- NA
iris0[sample(150,40),2] <- NA
iris0[sample(150,40),3] <- NA
iris0[sample(150,40),4] <- NA

At this point I would like (after supplying say max_n=7) a table somewhat like the following

                1 NAs 2 NAs 3 NAs 4 NAs
1   setosa      30    13    3   0   0   0   0
2   versicolor  40    18    5   0   0   0   0
3   virginica   36    13    2   0   0   0   0
4   Total      106    44    10  0   0   0   0
5   (%)       70.67 29.33   6.67    0.00    0.00    0.00    0.00

Actually I came up with a method that does it using rowsums and lapply but I wonder if anyone can help with a more efficient method (using base R and dplyr) e.g. with aggregate or something. Unboxing the lapply results into a table like the above is also a major pain.

Also, better still would be something that divides the numbers by the total records in each class (for the proportion in each class).

Answer 1

Although you did not make data available, I suppose you have the following kind of (simplified) problem where

Z = data.frame(Group = c("A", "A", "B", "B", "C", "C", "C"), 
               value = c(1, NA, 1, 1, NA, NA, 1))
  Group value
1     A     1
2     A    NA
3     B     1
4     B     1
5     C    NA
6     C    NA
7     C     1

and now you want to count the proportion of entries for each group that are not NA, correct?

You can do this with

aggregate(value ~ Group, 
          data = Z, 
          function(x) {
                       sum(!is.na(x))/length(x)
                       }, 
          na.action = NULL)

and obtain the outcome

  Group     value
1     A 0.5000000
2     B 1.0000000
3     C 0.3333333 

EDIT:

After the provision of data, we need to slightly tweak the code to arrive at the result.

First do

Test = iris0 %>% 
  dplyr::mutate(NAS = rowSums(is.na(.))) %>% 
  dplyr::group_by(Species) 

and then we do

aggregate(NAS ~ Species, 
          data = Test, 
          function(x) {
            table(x)
          }
)

to arrive at

     Species NAS.0 NAS.1 NAS.2 NAS.3
1     setosa    20    17    10     3
2 versicolor    10    22    13     5
3  virginica    14    23    11     2

EDIT II:

To address your further questions in the comments:

(1) You can index and extract the usual way, e.g.

Test[Test$NAS == 3 & Test$Species == "virginica"]

(2) We can also get a self-made cumulative table for the example by means of

Z = aggregate(NAS ~ Species, 
      data = Test, 
      function(x) {
        table(x)
      }
)
(t(
   apply(Z[,2:dim(Z)[2]], 1, cumsum)
                                    ) 
                                      - 50)*(-1)

with the result

      0  1 2 3
[1,] 30 13 3 0
[2,] 40 18 5 0
[3,] 36 13 2 0

This is a quick fix, of course, but working. Note that in the general case you need to replace 50 with the vectors of group counts.

(3) It stops at 3 NAs because there are simply no more columns to hold more NAs.