Aggregate by NA in R

Question

Does anybody know how to aggregate by NA in R.

If you take the example below

a <- matrix(1,5,2)
a[1:2,2] <- NA
a[3:5,2] <- 2
aggregate(a[,1], by=list(a[,2]), sum)

The output is:

Group.1 x
2       3

But is there a way to get the output to include NAs in the output like this:

Group.1 x
2       3
NA      2

Thanks

Answer 1

Instead of aggregate(), you may want to consider rowsum(). It is actually designed for this exact operation on matrices and is known to be much faster than aggregate(). We can add NA to the factor levels of a[, 2] with addNA(). This will assure that NA shows up as a grouping variable.

rowsum(a[, 1], addNA(a[, 2]))
#      [,1]
# 2       3
# <NA>    2

If you still want to use aggregate(), you can incorporate addNA() as well.

aggregate(a[, 1], list(Group = addNA(a[, 2])), sum)
#   Group x
# 1     2 3
# 2  <NA> 2

And one more option with data.table -

library(data.table)
as.data.table(a)[, .(x = sum(V1)), by = .(Group = V2)]
#    Group x
# 1:    NA 2
# 2:     2 3

Answer 2

Use summarize from dplyr

library(dplyr)

a %>%
  as.data.frame %>%
  group_by(V2) %>%
  summarize(V1_sum = sum(V1))

Answer 3

Using sqldf:

a <- as.data.frame(a)
sqldf("SELECT V2 [Group], SUM(V1) x 
      FROM a 
      GROUP BY V2")

Output:

  Group x
1    NA 2
2     2 3

stats package

A variation of AdamO's proposal:

data.frame(xtabs( V1 ~ V2 , data = a,na.action = na.pass, exclude = NULL))

Output:

    V2 Freq
1    2    3
2 <NA>    2