CODEWITHSUNDEEP
rdata.table
Chris
Chris

Reputation: 313

Number of non-NA records by column, grouped

I have a data.table that looks something like this:

> dt <- data.table(
  group1 = c("a", "a", "a", "b", "b", "b", "b"),
  group2 = c("x", "x", "y", "y", "z", "z", "z"),
  data1 = c(NA, rep(T, 3), rep(F, 2), "sometimes"),
  data2 = c("sometimes", rep(F,3), rep(T,2), NA))

> dt

   group1 group2     data1     data2
1:      a      x        NA sometimes
2:      a      x      TRUE     FALSE
3:      a      y      TRUE     FALSE
4:      b      y      TRUE     FALSE
5:      b      z     FALSE      TRUE
6:      b      z     FALSE      TRUE
7:      b      z sometimes        NA

My goal is to find the number of non-NA records in each data column, grouped by group1 and group2.

   group1 group2     data1     data2
1:      a      x         1         2
3:      a      y         1         1
4:      b      y         1         1
5:      b      z         3         2

I have this code left over from dealing with another part of the dataset, which had no NAs and was logical:

dt[
  ,
  lapply(.SD, sum),
  by = list(group1, group2),
  .SDcols = c("data3", "data4")
]

But it won't work with NA values, or non-logical values.

Upvotes: 4

Views: 129

Answers (3)

David Arenburg
David Arenburg

Reputation: 92300

Another alternative is to melt/dcast in order to avoid by column operation. This will remove the NAs and use the length function by default 

dcast(melt(dt, id = c("group1", "group2"), na.rm = TRUE), group1 + group2 ~ variable) 
# Aggregate function missing, defaulting to 'length'
#    group1 group2 data1 data2
# 1:      a      x     1     2
# 2:      a      y     1     1
# 3:      b      y     1     1
# 4:      b      z     3     2

Upvotes: 4

DatamineR
DatamineR

Reputation: 9628

Using dplyr (with some help from David Arenburg & eddi):

library(dplyr)
dt %>% group_by(group1, group2) %>% summarise_each(funs(sum(!is.na(.))))
Source: local data table [4 x 4]
Groups: group1

  group1 group2 data1 data2
1      a      x     1     2
2      a      y     1     1
3      b      y     1     1
4      b      z     3     2

Upvotes: 3

eddi
eddi

Reputation: 49448

dt[, lapply(.SD, function(x) sum(!is.na(x))), by = .(group1, group2)]
#   group1 group2 data1 data2
#1:      a      x     1     2
#2:      a      y     1     1
#3:      b      y     1     1
#4:      b      z     3     2

Upvotes: 8

Related Questions