thelatemail thelatemail - 11 months ago 70
R Question

data.table grouping separately on numeric and text variables

I'm trying to simplify this

two-stage process which acts on both numeric and character variables. E.g. - take the first element of
each of the numeric variables. Consider this small example:

dt <- data.table(grpvar=letters[c(1,1,2)], textvar=c("one","two","one"),
numvar=1:3, othernum=2:4)
# grpvar textvar numvar othernum
#1: a one 1 2
#2: a two 2 3
#3: b one 3 4

Now my first thought was to nest
to drop the one variable out of the
call, but I thought that was a bit complicated:

dt[, c(textvar=textvar[1], .SD[, lapply(.SD, sum), .SDcols=-c("textvar")]), by=grpvar]
# grpvar textvar numvar othernum
#1: a one 3 5
#2: b one 3 4

Then I thought maybe I could do each grouping separately and join them, but that seems even worse:

dt[, .(textvar=textvar[1]), by=grpvar][
dt[, lapply(.SD, sum), by=grpvar, .SDcols=-c("textvar")], on="grpvar"
# grpvar textvar numvar othernum
#1: a one 3 5
#2: b one 3 4

Is there a simpler construction that would get around the nesting of
or the joining? I feel like I'm overlooking something elementary.

Answer Source

The j-argument in data.table is (deliberately) quite flexible. All we need to remember is that:

As long as j returns a list, each element of the list will become a column in the resulting data.table.

Using the fact that c(list, list) is a list, we can construct the expression as follows:

dt[, c(textvar = textvar[1L], lapply(.SD, sum)), # select/compute all cols necessary
      .SDcols = numvar:othernum,                 # provide .SD's columns 
      by = grpvar]                               # group by 'grpvar'
#    grpvar textvar numvar othernum
# 1:      a     one      3        5
# 2:      b     one      3        4

Here, I've not wrapped the first expression with list() since textvar[1L] returns a length=1 vector.. i.e., identical(c(1, list(2, 3)), c(list(1), list(2,3))) is TRUE.

Note that this is only possible from v1.9.7. The bug was just recently fixed in the current development version.