Passing named lists to `.SDcols` / `.SD`

[grantmcdermott]

Is there any scope/appetite for supporting multiple .SD and .SDcols?

Motivation: I frequently encounter situations where I need to perform different aggregation tasks on distinct column groups. One group of columns will be aggregated as means, another group will be aggregated as medians, yet another group will be aggregated as sums, etc. In these cases, only one group can be passed through the convenience features of .SD(cols), while the other group(s) must all be aggregated manually.

Here's a simple (and somewhat ill-advised) example that illustrates the mechanics:

library(data.table)
d = as.data.table(iris)

d[, 
  c(lapply(.SD, sum),
    list(Petal.Length = mean(Petal.Length), Petal.Width = mean(Petal.Width))), 
 .SDcols = patterns("^Sepal"), 
 by = Species]
#>       Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#> 1:     setosa        250.3       171.4        1.462       0.246
#> 2: versicolor        296.8       138.5        4.260       1.326
#> 3:  virginica        329.4       148.7        5.552       2.026

Here the summed Sepal columns get the .SD convenience treatment, while I have to manually take the mean of the Petal columns separately (and name them; see also #1227 (comment)).

My proposal is to allow something like this instead:

d[, 
  c(lapply(.SD, sum), lapply(.SD2, mean)), 
  .SDcols = patterns("^Sepal"), .SDcols2 = patterns("^Petal"),
  by = Species]

I'm assuming here that you can match the relevant subsets based on the index (.SD2 => .SDcols2). If this is easy to do for one additional subset, then in principle it seems possible for any additional subsets (.SD3 => .SDcols3, etc). Of course, this may impose some small overhead that doesn't pass the cost-benefit test.

Feel free to close if this seems undesirable / too much work. Thanks for considering!

Update. Related: #1063 (comment) and possibly #4970

Proposed solution in the former is:

s_cols = grep("^Sepal", names(d), value = TRUE)
p_cols = grep("^Petal", names(d), value = TRUE)
d[, 
  {
   SD = unclass(.SD)
   c(lapply(SD[s_cols], sum), lapply(SD[p_cols], mean))
  },
  .SDcols = c(s_cols, p_cols),
  by = Species]
#>       Species Sepal.Length Sepal.Width Petal.Length Petal.Width
#> 1:     setosa        250.3       171.4        1.462       0.246
#> 2: versicolor        296.8       138.5        4.260       1.326
#> 3:  virginica        329.4       148.7        5.552       2.026

SessionInfo

> sessionInfo()
R version 4.1.0 (2021-05-18)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Arch Linux

Matrix products: default
BLAS/LAPACK: /usr/lib/libopenblas_haswellp-r0.3.13.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C              
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8   
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C            
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

[renkun-ken]

I have such use cases too.

If we don't want to introduce new parameters, it might make some sense to allow .SDcols to accept a list of character vectors so that .SD provides a list of data.tables for such purpose. Then an example code looks like

dt[, rowSums(.SD[[1]]) / rowSums(.SD[[2]]), .SDcols = list(c("a", "b"), c("x", "y", "z")]

or named list of .SDcols:

dt[, rowSums(.SD$a) / rowSums(.SD$b), .SDcols = list(a = c("m", "n"), b = c("x", "y", "z"))]

or a list of patterns:

dt[, rowSums(.SD$a) / rowSums(.SD$b), .SDcols = list(a = patterns("^m\\d+$"), b = patterns("^n\\d+$"))]

[grantmcdermott]

@renkun-ken Oh, I like that too.

[MichaelChirico]

thanks for raising. I've had something like @renkun-ken 's suggestion in mind for a long time, I would have sworn it's already an issue but I couldn't find anything searching.

unfortunately I don't think it can be so simple? since .SD[[I]] currently refers to the ith column of .SD, right?

[renkun-ken]

unfortunately I don't think it can be so simple? since .SD[[I]] currently refers to the ith column of .SD, right?

Yes, when .SDcols is a character vector. If .SDcols accepts a list, then it seems to require that the user know that .SD would also become a list of data.tables instead.

[avimallu]

This would work very well with what I proposed partially in #4970 (more like requested a feature). Building upon @renkun-ken's idea of .SDcols accepting a list, we can potentially do the following without much workaround and breaking changes (to existing implementation):

1. SDcols can accept a list of character, any function that returns logical or integer output, or an integer itself.
2. This is much like how the current .SDcols accept any of the individual type of any of the above argument classes.
3. When .SDcols is named, we can refer to it as so: .SD["name"] or we can always use their indexes.
4. We would still need the use to refer to the first/last or nth item of .SD with .SD["name"][1L] or for a specific column by .SD["name"][["column"]].
5. In the event that length(.SDcols) has a length of 1 or is a non-list class, we could revert to existing behaviour.

If my understanding of the code is right, we have the ability to currently differentiate between all the three different types of arguments to .SDcols above, so with some minor changes (which I'm guessing will be implementable in R itself), we should be able to add that feature. It would make codes like the following possible:

Lahman::Batting)[
  , .(
    lapply(.SD["run_types"], sum),
    lapply(.SD["stints"], uniqueN),
    lapply(.SD["patt"], \(x) sum(x)/uniqueN(yearID))
  ),
  playerID,
  .SDcols = list(
    run_types = c("R", "IBB", "SO"),
    stints = 3:4,
    patt = patterns("$R^|(X.B)|HR"))]

I think the potential syntactical inconsistency with .SD[["column"]] not referring to column in .SD can be forgiven, similar to how DT[x] is not filtering for values indicated by x if it is a data.table object (without bending our definition of a join to be a filter).

If/when #4970 and #4883 are implemented, it would allow for ridiculously flexible operations involving across, multiple functions and multiple .SD without breaking existing syntax.

[renkun-ken]

Thanks @avimallu for the thoughts on this.

In the event that length(.SDcols) has a length of 1 or is a non-list class, we could revert to existing behaviour.

If we are building consistent behavior for programming purposes, I guess we should not let length-1 list revert to existing behavior but a list of one data.table instead so that the following example could behave consistently.

dt_rowSums_sd <- function(data, col_groups) {
  d1 <- data[, lapply(.SD, rowSums), keyby = name, .SDcols = col_groups]
  d1[, lapply(.SD, sd, na.rm = TRUE), keyby = name, .SDcols = -"name"]
}

dt_rowSums_sd(data, list(g1 = c("a", "b")))
dt_rowSums_sd(data, list(g1 = c("a", "b"), g2 = c("x", "y")))

I guess if user provides a list of one character vector instead of the vector directly, it is most likely on purpose.

[jangorecki]

This may sounds like a self promotion of new metaprogramming interface, but I would advocate to use it instead of extending .SD.
It would look like

dt[, rowSums(.l1) / rowSums(.l2), env = list(
  .l1=list("a", "b"),
  .l2=list("x", "y", "z")
)]

substitute2(rowSums(.l1) / rowSums(.l2), env = list(.l1=list("a", "b"), .l2=list("x", "y", "z")))
#rowSums(list(a, b))/rowSums(list(x, y, z))

Providing columns by patterns should not be an issue because we can do whatever we want to prepare env arg.

.SD is just a special case of env usage

DT[, .SD, env=list(.SD=as.list(names(DT)))]

So if more general solution is already available then why not to use it?

[grantmcdermott]

Very cool @jangorecki, I hadn't seen the new metaprogramming interface. I'll upgrade to the dev version of table.table to try it out shortly.

In the meantime, just so I have something concrete to compare, would you mind showing me how you would implement my example at the very top of the thread? Don't worry about pattern matching, I'm just interested to see how it would work.

[jangorecki]

## mockup function that will return character column names
patterns = function(x) paste(sub("^", "", x, fixed=TRUE), c("Width","Length"), sep=".")

substitute2(c(
  lapply(.SD, sum), lapply(.SD2, mean)
  ), env = list(
    .SD = as.list(patterns("^Sepal")),
    .SD2 = as.list(patterns("^Petal"))
))
#c(lapply(list(Sepal.Width, Sepal.Length), sum), lapply(list(Petal.Width, Petal.Length), mean))

It is probably not wise to use .SD name in env. If we would use .SDcols as well, it would be ignored because .SD from env is being substituted at the very beginning and it doesn't exist anymore when .SDcols would be processed.

d[, 
  c(lapply(.SD, sum), lapply(.SD2, mean)), 
  env = list(.SD = as.list(patterns("^Sepal")), .SD2 = as.list(patterns("^Petal"))),
  by = Species]

[grantmcdermott]

Oh, very nice.

It's very close close to what I was looking for originally... and looks like it just about provides a more generalisable, drop-in replacement for .SD. Which I assume was partly your goal?

My one remaining issue is the naming of the columns. Retaining the original column names is a side-feature of .SD that I really like. (Again, discussed elsewhere.) Is there a way to do that here instead of returning "V1", "V2", etc.

[jangorecki]

Possibly this will do

patterns = function(x) setNames(nm=paste(sub("^", "", x, fixed=TRUE), c("Width","Length"), sep="."))
#...
#c(lapply(list(Sepal.Width = Sepal.Width, Sepal.Length = Sepal.Length), 
#    sum), lapply(list(Petal.Width = Petal.Width, Petal.Length = Petal.Length), 
#    mean))

[jangorecki]

Replacing .SD was not the goal because it works pretty well. Intention was more to replace get/mget which AFAIK could carry performance overhead.
https://rdatatable.gitlab.io/data.table/library/data.table/html/substitute2.html this manual can be useful

[grantmcdermott]

Excellent, thanks Jan. (Sorry for sporadic replies; I'm sneaking time online in between weekend parenting...)

Stepping back, here's my quick summary of this thread so far. Feel free to push back on or add to what I'm about to say.

1. The advantage of sticking with / modifying .SD is that it provides a familiar interface that will be intuitive to all data.table users. I particularly like @renkun-ken's suggestion of allowing .SD to accept (named) lists that could then be referenced accordingly.
2. Having said that, I've just stumbled on this example from Jan in a related thread, which I missed earlier. I gets me (us!) close to what I was originally looking for. However, I do worry that it's a bit opaque for new users and, let's be honest, is not as syntax friendly as equivalent operations in other packages or languages. I also don't think it's quite so straightforward to combine other convenience feature like patterns, but I might be wrong here.

s_cols = grep("^Sepal", names(d), value = TRUE)
p_cols = grep("^Petal", names(d), value = TRUE)

d[, 
  {
   SD = unclass(.SD)
   c(lapply(SD[s_cols], sum), lapply(SD[p_cols], mean))
  },
  .SDcols = c(s_cols, p_cols),
  by = Species]

3. The new metaprogramming interface is very cool and I need to spend more time with it. As a fairly experienced data.table user and advocate, I'm a bit concerned that we're sending mixed signals to new users about when to use .SD and when to switch to env. I like this earlier example from Jan (slightly adapted):

patterns2 = function(x) setNames(nm=paste(sub("^", "", x, fixed=TRUE), c("Width","Length"), sep="."))
d[, 
  c(lapply(SD1, sum), lapply(SD2, mean)), 
  env = list(SD1 = as.list(patterns2("^Sepal")), SD2 = as.list(patterns2("^Petal"))),
  by = Species]

It's probably just a limitation of this knock-up patterns2 function, but ideally I'd like to be able to combine it with another variable. However, this leaves a blank column:

d[, 
  c(lapply(SD1, sum), lapply(SD2, mean)), 
  env = list(SD1 = as.list(c(patterns2("^Sepal"), "Petal.Width")), SD2 = as.list(patterns2("^Petal"))),
  by = Species]

[matthewgson]

I'm happy to find this post, and env looks easy to comprehend. Would there be a way to assign column names inside the bracket expression in following example?

cols1 = c('var1','var2')
cols2 = c('var3','var4')
DT[, 
      c( lapply(SD1, sum), lapply(SD2, mean) ), 
      env = list(SD1 = as.list(cols1), .SD2 = as.list(cols2)),
      by = Species]

# pseudo code that I'd like to perform
DT[, 
      c(cols1, cols2) = c( lapply(SD1, sum), lapply(SD2, mean) ), 
      env = list(SD1 = as.list(cols1), .SD2 = as.list(cols2)),
      by = Species]