12

My aggregation needs vary among columns / data.frames. I would like to pass the "list" argument to the data.table dynamically.

As a minimal example:

require(data.table)
type <- c(rep("hello", 3), rep("bye", 3), rep("ok",3))
a <- (rep(1:3, 3))
b <- runif(9)
c <- runif(9)
df <- data.frame(cbind(type, a, b, c), stringsAsFactors=F)
DT <-data.table(df)

This call:

DT[, list(suma = sum(as.numeric(a)), meanb = mean(as.numeric(b)), minc = min(as.numeric(c))), by= type]

will have result similar to this:

    type suma     meanb      minc
1: hello    6 0.1332210 0.4265579
2:   bye    6 0.5680839 0.2993667
3:    ok    6 0.5694532 0.2069026

Future data.frames will have more columns that I will want to summarize differently. But for the sake of working with this small example: Is there a way to pass the list programatically?

I naïvely tried:

# create a different list
mylist <- "list(lengtha = length(as.numeric(a)), maxb = max(as.numeric(b)), meanc = mean(as.numeric(c)))"
# new call
DT[, mylist, by=type]

With the following error:

1: hello
2:   bye
3:    ok
mylist
1: list(lengtha = length(as.numeric(a)), maxb = max(as.numeric(b)), meanc = mean(as.numeric(c)))
2: list(lengtha = length(as.numeric(a)), maxb = max(as.numeric(b)), meanc = mean(as.numeric(c)))
3: list(lengtha = length(as.numeric(a)), maxb = max(as.numeric(b)), meanc = mean(as.numeric(c)))

Any hints appreciated! Best regards!

PS sorry about these as.numeric(), I could not quite figure out why, but I needed them for the example to run.

Minor edit inserted columns / before data.frame in initial sentence to clarify my needs.

Arun
  • 116,683
  • 26
  • 284
  • 387
jjap
  • 189
  • 2
  • 9
  • 2
    if you provide vector inputs to `cbind`, the result would be a `matrix`. And since matrix can not hold both `character` and `numeric` arguments, every vector other than `type` will be converted to `character`. Instead you should do: `data.frame(type,a,b,c, stringsAsFactors=F)`. Even better, you can directly use `data.table(type, a, b, c)`, – Arun Feb 06 '13 at 08:30
  • 1
    you mean to say your `aggregation needs` vary **amongst the columns** of `data.table/data.frame`? What you say and what you show aren't quite the same. Because later you talk about "future data.frames will have more columns". – Arun Feb 06 '13 at 08:37
  • 1
    @Arun The answer is yes. It is more useful/accurate to characterize them as varying amongst the columns. Will make a small edit about that. Thanks for pointing out the needless/counterproductive use of cbind as well ! – jjap Feb 06 '13 at 14:55

4 Answers4

10

This is explained FAQ 1.6 what you are looking for is quote and eval

something like

 mycall <- quote(list(lengtha = length(as.numeric(a)), maxb = max(as.numeric(b)), meanc = mean(as.numeric(c))))

 DT[, eval(mycall)]

After a bit of head-banging, here is a very ugly way of constructing the call for ddply using .()

myplyrcall <- .(lengtha = length(as.numeric(a)), maxb = max(as.numeric(b)), meanc = mean(as.numeric(c)))

do.call(ddply,c(.data = quote(DF), .variables = 'type',.fun = quote(summarise),myplyrcall))

You could also use as.quoted which has an as.quoted.character method to construct using paste0

myplc <-as.quoted(c("lengtha" = "length(as.numeric(a))", "maxb" = "max(as.numeric(b))", "meanc" = "mean(as.numeric(c))"))

This can be used with data.table as well!

dtcall <- as.quoted(mylist)[[1]]


DT[,eval(dtcall), by = type]

data.table all the way.

mnel
  • 113,303
  • 27
  • 265
  • 254
  • 1
    +1 Yes this is the idiomatic way. The `eval` here is treated specially by `data.table` and `j` is optimized even though it's dynamic. – Matt Dowle Feb 06 '13 at 10:15
  • OMG! You are right... right there in the FAQ! Thanks for the solutions. Nice to see one that work under both plyr and data.table. – jjap Feb 06 '13 at 14:50
  • How can I maintain preexistent cols with DT[, eval(mycall)] ? – Captain Tyler Mar 30 '21 at 18:39
7

Another method (supporting the use of paste or paste0 to build the expression):

expr <- parse(text=mylist)
DT[, eval( expr ), by=type]
#-------
    type lengtha      maxb     meanc
1: hello       3 0.8265407 0.5244094
2:   bye       3 0.4955301 0.6289475
3:    ok       3 0.9527455 0.5600915
IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • 3
    +1 Small comment that `parse` will be evaluated here on each iteration (the same `parse` each time, wastefully). So it's faster to take that outside the loop and use the pre parsed expression in `j`. Either `q=parse(text=...)` or `q=quote(...)` before the DT query are ok. That then allows `data.table` to _optimize_ the `j` query. It only optimizes (changes the `j` expression) once before grouping; it can't optimize if the `parse` is inside `j`. – Matt Dowle Feb 06 '13 at 10:09
  • 1
    Edited to incorporate this improvement. – IRTFM Feb 06 '13 at 17:41
7

Another way is to use .SDcols to group the columns for which you'd like to perform the same operations together. Let's say that you require columns a,d,e to be summed by type where as, b,g should have mean taken and c,f its median, then,

# constructing an example data.table:
set.seed(45)
dt <- data.table(type=rep(c("hello","bye","ok"), each=3), a=sample(9), 
                 b = rnorm(9), c=runif(9), d=sample(9), e=sample(9), 
                 f = runif(9), g=rnorm(9))

#     type a          b         c d e         f          g
# 1: hello 6 -2.5566166 0.7485015 9 6 0.5661358 -2.2066521
# 2: hello 3  1.1773119 0.6559926 3 3 0.4586280 -0.8376586
# 3: hello 2 -0.1015588 0.2164430 1 7 0.9299597  1.7216593
# 4:   bye 8 -0.2260640 0.3924327 8 2 0.1271187  0.4360063
# 5:   bye 7 -1.0720503 0.3256450 7 8 0.5774691  0.7571990
# 6:   bye 5 -0.7131021 0.4855804 6 9 0.2687791  1.5398858
# 7:    ok 1 -0.4680549 0.8476840 2 4 0.5633317  1.5393945
# 8:    ok 4  0.4183264 0.4402595 4 1 0.7592801  2.1829996
# 9:    ok 9 -1.4817436 0.5080116 5 5 0.2357030 -0.9953758

# 1) set key
setkey(dt, "type")

# 2) group col-ids by similar operations
id1 <- which(names(dt) %in% c("a", "d", "e"))
id2 <- which(names(dt) %in% c("b","g"))
id3 <- which(names(dt) %in% c("c","f"))

# 3) now use these ids in with .SDcols parameter
dt1 <- dt[, lapply(.SD, sum), by="type", .SDcols=id1]
dt2 <- dt[, lapply(.SD, mean), by="type", .SDcols=id2]
dt3 <- dt[, lapply(.SD, median), by="type", .SDcols=id3]

# 4) merge them.
dt1[dt2[dt3]]

#     type  a  d  e          b          g         c         f
# 1:   bye 20 21 19 -0.6704055  0.9110304 0.3924327 0.2687791
# 2: hello 11 13 16 -0.4936211 -0.4408838 0.6559926 0.5661358
# 3:    ok 14 11 10 -0.5104907  0.9090061 0.5080116 0.5633317

If/when you have many many column, making a list like the one you've might be cumbersome.

Arun
  • 116,683
  • 26
  • 284
  • 387
  • 2
    This approach will actually mesh perfectly with my workflow. Thanks for reading _around my words and through my mind_. – jjap Feb 06 '13 at 14:39
  • 1
    For those reading this far, who may not be familiar with the `.SD` symbol (or data.table for that matter): datatable-faq 2.1 has a very good explanation. `.SDcols` is documented in the data.table help file. – jjap Feb 06 '13 at 15:50
5

I find it worrysome that apparently eval is part of the answer. From your question it is not clear to me, if and why you really want to do what you claim to want. Thus I demonstrate here that you can also use a function:

fun <- function(a,b,c) {
  list(lengtha = length(as.numeric(a)), 
          maxb = max(as.numeric(b)), 
         meanc = mean(as.numeric(c)))  
}

DT[, fun(a,b,c), by=type]

    type lengtha      maxb     meanc
1: hello       3 0.8792184 0.3745643
2:   bye       3 0.8718397 0.4519999
3:    ok       3 0.8900764 0.4511536
Roland
  • 127,288
  • 10
  • 191
  • 288
  • 1
    @Arun Your second statement is true, but I don't know if it makes sense to keep playing Guess-What-OP-Needs ... – Roland Feb 06 '13 at 08:35
  • 3
    +1 But the function call will create its own environment for each call, iiuc. The `eval` approach may be preferred for speed because `data.table` evals the expression directly in a static environment which `data.table` creates internally. The `eval` call itself isn't `eval`'d. That is stripped off leaving the expression itself, which is then optimized before being `eval`'d by a C level eval by the grouping code. How much difference it makes of course depends. The function call is a nice syntax and that can often trump speed. – Matt Dowle Feb 06 '13 at 10:23
  • @MatthewDowle Thank you for the clarification. I'm always learning something new about data.table. – Roland Feb 06 '13 at 10:28