Repository navigation
Passing named lists to .SDcols / .SD #5020
Description
Activity
I have such use cases too.
If we don't want to introduce new parameters, it might make some sense to allow
.SDcolsto accept a list of character vectors so that.SDprovides a list of data.tables for such purpose. Then an example code looks likedt[, 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+$"))]
Reacted by Mark Fairbanks, avimallu, QIanGua, iagogv3, joihci and Jóhann Haraldsson@renkun-ken Oh, I like that too.
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?
Reacted by iagogv3unfortunately I don't think it can be so simple? since .SD[[I]] currently refers to the ith column of .SD, right?
Yes, when
.SDcolsis a character vector. If.SDcolsaccepts a list, then it seems to require that the user know that.SDwould also become a list of data.tables instead.Reacted by iagogv3This would work very well with what I proposed partially in #4970 (more like requested a feature). Building upon @renkun-ken's idea of
.SDcolsaccepting a list, we can potentially do the following without much workaround and breaking changes (to existing implementation):SDcolscan accept alistofcharacter, any function that returns logical or integer output, or an integer itself.- This is much like how the current
.SDcolsaccept any of the individual type of any of the above argument classes. - When
.SDcolsis named, we can refer to it as so:.SD["name"]or we can always use their indexes. - We would still need the use to refer to the first/last or nth item of
.SDwith.SD["name"][1L]or for a specific column by.SD["name"][["column"]]. - In the event that
length(.SDcols)has alengthof 1 or is a non-listclass, 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
.SDcolsabove, 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 tocolumnin.SDcan be forgiven, similar to howDT[x]is not filtering for values indicated byxif it is adata.tableobject (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.SDwithout breaking existing syntax.Reacted by Michael Chirico, Grant McDermott and Matthew SonThanks @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.
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 likedt[, 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
envarg..SDis just a special case ofenvusageDT[, .SD, env=list(.SD=as.list(names(DT)))]
So if more general solution is already available then why not to use it?
Reacted by Matthew Son, Kun Ren, Kyle Haynes and iagogv3Very 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.
## 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
.SDname inenv. If we would use.SDcolsas well, it would be ignored because.SDfromenvis being substituted at the very beginning and it doesn't exist anymore when.SDcolswould be processed.d[, c(lapply(.SD, sum), lapply(.SD2, mean)), env = list(.SD = as.list(patterns("^Sepal")), .SD2 = as.list(patterns("^Petal"))), by = Species]
Reacted by Grant McDermottOh, 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
.SDthat I really like. (Again, discussed elsewhere.) Is there a way to do that here instead of returning "V1", "V2", etc.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))
Replacing .SD was not the goal because it works pretty well. Intention was more to replace
get/mgetwhich AFAIK could carry performance overhead.
https://rdatatable.gitlab.io/data.table/library/data.table/html/substitute2.html this manual can be usefulExcellent, 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.
- The advantage of sticking with / modifying
.SDis that it provides a familiar interface that will be intuitive to all data.table users. I particularly like @renkun-ken's suggestion of allowing.SDto accept (named) lists that could then be referenced accordingly. - 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]
- 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
.SDand when to switch toenv.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
patterns2function, 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]
Reacted by Kamgang-BReacted by iagogv3- The advantage of sticking with / modifying
I'm happy to find this post, and
envlooks 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]Reacted by Mark Fairbanks12 remaining items
I think @Kamgang-B made a very good point of the simplicity and power of using
.SDcolsoverenv=for the use cases of quickly selecting columns.Reacted by Jan Gorecki, Matthew Son, Kyle Haynes and Michael ChiricoWell explained advantages of .SD over env. Agree this FR make sense.
Reacted by Kyle HaynesReally nice thread and comment by @Kamgang-B. It also resolves my concern about the earlier suggestions to do
.SDcols = list(a = ..., b = ...)combined with.SD$a, which creates a fundamental ambiguity about whether$arefers to a column or a data.table. @avimallu's approach to use[instead is an improvement (we'd maybe want to make.SDa new S3 class like("sd.data.table", "data.table", "data.frame")?), but still hits some fundamental conflicts sinceDT["key"]is already valid for other data.tables (even though there are no hits for this pattern as of today).The approach to require
.SDcolsbe a named list, whose names would then become corresponding symbols available for evaluation inj, looks good to me (it will be a bit harder to implement).Reacted by Jóhann Haraldsson
Is there any scope/appetite for supporting multiple
.SDand.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:
Here the summed Sepal columns get the
.SDconvenience 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:
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:
SessionInfo