Repository navigation
Implement DT[, across(.SD, fun1, fun2, fun3), by=group] #4970
Description
Activity
My 2￠:
While havingacrossimplemented similarly todplyr::acrosswill have utility if implemented only with.SD, I think its utility can be increased further if we could get across to work with a character,patternsor.SDto allow it to work the waydplyr::acrossallows. Example from here:df %>% group_by(g1, g2) %>% summarise( across(where(is.numeric), mean), across(where(is.factor), nlevels), n = n(), )
Where is this useful?
Creating different summary statistics (mean, median, unique counts, count of rows, % of anyxovery) instead of doing a join after creating those columns, or chaining. Something that gives good flexibility (like alapplyand.SDcurrently do):as.data.table(Lahman::Batting)[, .( across(patterns("$R^|(X.B)|HR"), .(sum, mean)), across(c("stint", "teamID"), .(last, uniqueN)), across(.SD, .(uniqueN, \(x) sum(x)/uniqueN(yearID))), playerID, .SDcols = c("R", "IBB", "SO")]
My understanding of baseball is a little rusty. What I was aiming for was to create a single table by payer that gives me
- the sum and mean of runs, doubles, triples and home-runs, followed by
- the last and unique count of stints and teams player for, followed by
- the number of runs, international walks and strikeouts per year
all in one shot. I often have to revert to summarizing my data in Excel or, if the data is too big, calculate all these independently in R and do a join at the end. In this specific case, we could even employ the proposed
mergelist#4370 to create these efficiently, perhaps in parallel? It'll be amazing to have the ability to create this in R.P.S. - I realize that .SD provides a list, while the others provide character vectors. I was aiming for more flexibility as opposed to using only
.SD. It might also work if there was a way to split.SDinto.SD_1,.SD_2etc, but that would probably be a bit much.Reacted by Václav Tlapák, Matt Dowle, rfmanz, Ryan D and Michael YoungHi @mattdowle, I think what you're proposing here is closely related to what's being discussed in #1063, specifically the discussion around "colwise". #1063 groups together row operations and column operations into one issue, but they seem pretty separate to me (and I think rowwise operations would be better solved by implementing more functions like pmin/pmax as you suggested for psum in #3467).
I also like your suggestion of across (rather than colwise), and the proposed syntax since it will be familiar to
dplyrusers. It seems like you're suggesting the second argument be "..." to take an arbitrary number of functions, but I think it would be better if the second argument took a single function or a list like dplyr across (https://dplyr.tidyverse.org/reference/across.html).Before we can implement across, I think we should solve #2311 by merging my PR #4883, since this addresses how columns are named in this situation.
Once #4883 is merged, we could just implement across so that it expands into several lapply(.SD,) calls concatenated together with c(). This will ensure GForce optimization is used without any additional work. E.g:
x <- data.table(a=1:3,b=1:3) x[, across(.SD, list(min=min, max=max)]would just internally be expanded to
x[, c(min=lapply(.SD, min), max=lapply(.SD, max))]and the resulting column names would be
c("min.a", "min.b", "max.a", "max.b")which is consistent with base R and how naming is implemented in #4883.An outstanding question is how we might name columns when functions are not explicitly named:
x[, across(.SD, list(min, max)]Interactively it would be convenient for them to be
c("min.a", "min.b", "max.a", "max.b")without explicitly tagging each function, but this might break down when the list of functions is specified programmatically so perhapsc("F1.a", "F1.b", "F2.a", "F2.b")is more predictable.Reacted by DanielAlso see this discussion with Hadley (tidyverse/dtplyr#173) on translating dplyr::across to data.table syntax for the dtplyr package.
Note that the dplyr across allows arbitrary specification of how the function name and input column names are combined to determine how the output columns are named (specifying both order and the separator) but I'm not sure that's a road we want to go down (see the names argument here: https://dplyr.tidyverse.org/reference/across.html) . Sticking with base R's naming behavior (e.g.
c(A=list(a=3,2), B=list(a=1,b=2))) will be much easier to maintain since naming in across will just rely directly on how naming works forx[, c(A=lapply(.SD), B=lapply(.SD))](once #4883 is merged) without any additional magic code unique to across.Any thought to reinvigorating this? #4883 was merged a couple of months ago. The code there is a little more detailed than I want to dive in on to try to implement this myself (at least, not at this moment).
BTW, I further suggest the first argument should default to the current
.SD, such asacross = function(x = .SD, funs) { }
As for the notion of
funs = list(min, max), I suggest one of two paths:- require non-empty names, fail if
is.null(names(funs)) || any(!nzchar(names(funs))); or - for empty names, use
V1,V2, and/or other counting names.
Part of me wants to go with the first option to remove any/all ambiguity, but I'm conscious of interactive usability.
- require non-empty names, fail if
As of now, I'm not sure we should implement this. {dtplyr} does a decent job of acting as translation layer:
library(dplyr) library(dtplyr) iris |> lazy_dt() |> summarize(across(matches("^Sepal"), sum)) |> show_query() # `_DT6`[, .(Sepal.Length = sum(Sepal.Length), Sepal.Width = sum(Sepal.Width))] iris |> lazy_dt() |> summarize(across(matches("^Sepal"), sum), .by="Species") |> show_query() # `_DT8`[, .(Sepal.Length = sum(Sepal.Length), Sepal.Width = sum(Sepal.Width)), # keyby = .(Species)] # showing that GForce still gets invoked & is not lost in translation options(datatable.verbose=TRUE) iris |> lazy_dt() |> summarize(across(matches("^Sepal"), sum), .by="Species") # ... # ... GForce optimized j to 'list(gsum(Sepal.Length), gsum(Sepal.Width))' (see ?GForce) # ...
I haven't tested it extensively, but I'm loath to take on new maintenance overhead for something another package already does well. If there are specific feature requests/poor translations, those can be addressed.
Leaving this open for now for further input.
Reacted by Tyson Barrett and Jan GoreckiWhen closing this, maybe we could write, here in a comment, example across implementation that redirects to unlist lapply, so whoever needs can just copy to their package. It is possibly a one liner.
@jangorecki, do you mean a rough comparison like this?
library(dtplyr) lazy_dt(iris) |> summarize(across(matches("^Sepal"), list(sum=sum, med=median)), .by = Species) |> collect() # # A tibble: 3 × 5 # Species Sepal.Length_sum Sepal.Length_med Sepal.Width_sum Sepal.Width_med # <fct> <dbl> <dbl> <dbl> <dbl> # 1 setosa 250. 5 171. 3.4 # 2 versicolor 297. 5.9 139. 2.8 # 3 virginica 329. 6.5 149. 3
versus
as.data.table(iris) |> _[, Map(\(fun, nm) setNames(lapply(.SD, fun), paste0(names(.SD), "_", nm)), list(sum, median), c("sum", "med")) |> unlist(recursive = FALSE), .SDcols = patterns("Sepal"), by = "Species"] # Species Sepal.Length_sum Sepal.Width_sum Sepal.Length_med Sepal.Width_med # <fctr> <num> <num> <num> <num> # 1: setosa 250.3 171.4 5.0 3.4 # 2: versicolor 296.8 138.5 5.9 2.8 # 3: virginica 329.4 148.7 6.5 3.0
(There is likely an easier to do this?)
There's also a hybrid approach, though I don't think this addresses the OP, though it does not return a
data.table:library(dplyr) as.data.table(iris) |> _[, summarize(.SD, across(matches("Sepal"), list(sum=sum, med=median))), by = "Species" ] # Species Sepal.Length_sum Sepal.Length_med Sepal.Width_sum Sepal.Width_med # <fctr> <num> <num> <num> <num> # 1: setosa 250.3 5.0 171.4 3.4 # 2: versicolor 296.8 5.9 138.5 2.8 # 3: virginica 329.4 6.5 148.7 3.0 ### Note that this is a data.frame, not a data.table or tibble as.data.table(iris) |> _[, summarize(.SD, .by = Species, across(matches("Sepal"), list(sum=sum, med=median)))] # Species Sepal.Length_sum Sepal.Length_med Sepal.Width_sum Sepal.Width_med # 1 setosa 250.3 5.0 171.4 3.4 # 2 versicolor 296.8 5.9 138.5 2.8 # 3 virginica 329.4 6.5 148.7 3.0
(I wish R had two native functions:
lapplywhere the inner func knows the names similar topurrr::imap, and a form ofouterthat doesn't use simple vectors. Wishlist. Not fordata.table.)The challenge will be that:
.cols=argument builds on the rich {tidyselect} column selection language. It will be nontrivial to replicate that with {base} -- essentially equivalent to re-writing a whole package.names=argument also packagesglue()-formatted column name generation
https://dplyr.tidyverse.org/reference/across.html
So I think producing a fully-compatible
across()equivalent will be a major undertaking. Something like Bill's comment that hints at how to do it will suffice to get people going and then adapt to their specific needs.While I'm not arguing that onerous maintenance should not be avoided, I don't think those two challenges are an issue unless I'm missing something in NSE nuances. For 1 (
.cols=) we can use existing.SDcols=mechanisms; and for 2 (.names=) I think the glue-mechanism is a nice convenience but not strictly required, I think.sep=may suffice?facross <- function(x, .funs, .sep = "_") { if (is.null(names(.funs))) names(.funs) <- "" noname <- !nzchar(names(.funs)) names(.funs)[noname] <- paste0("V", cumsum(noname[noname])) Map(\(fun, nm) setNames(lapply(x, unname(fun)), paste(names(x), nm, sep = .sep)), .funs, names(.funs)) |> unname() |> unlist(recursive = FALSE) } as.data.table(iris) |> _[, facross(.SD, list(sum=sum, median)), by = "Species", .SDcols = patterns("Sepal")] # Species Sepal.Length_sum Sepal.Width_sum Sepal.Length_V1 Sepal.Width_V1 # <fctr> <num> <num> <num> <num> # 1: setosa 250.3 171.4 5.0 3.4 # 2: versicolor 296.8 138.5 5.9 2.8 # 3: virginica 329.4 148.7 6.5 3.0 ### same thing, pipeless as.data.table(iris)[, facross(.SD, list(sum=sum, median)), by = "Species", .SDcols = patterns("Sepal")] ### same thing, pipeless formatted as.data.table(iris)[, facross(.SD, list(sum=sum, median)), by = "Species", .SDcols = patterns("Sepal")]
(This could be extended to support
...instead of.funs=, though if we're looking for some basic similarity withdplyr::across, its allowance of named/unnamed arguments in...is deprecated. I suspect the rationale for the deprecation is covering a reasonable (though likely rare) corner case that somebody would want to name a column one of"x",".funs", or".sep"here.)Edit: to better align with some
data.tablefunction-naming conventions, I renamed this fromdt_across()tofacross(). :-)I think average user does not need missing names handling version, then it can be simpler.
Pipes make code less clear actually - why not dt_across(as.data.table(iris), ...)I find utility in both piped and pipeless: using pipes can be visually simplifying, one step per line, so the line with
dt_across(.)doesn't have much space preserved for precursor requirements (not thatas.data.table(.)is onerous). Using the pipe there reduces it so that the "only thing" on the line is just what is required for the operation. But there are times when I prefer pipe-less as well, no judgement really, as always "it depends".However, doing
dt_across(as.data.table(iris), ..)disables my suggested use of.SDcols=to address MichaelChirico's first issue of convenient column selection. I think suggestingdt_across()should have its own internal column-selection mechanism is as MichaelChirico suggested more complicated than is strictly necessary. Demonstrating it within[.data.tableallows us to capitalize on the existingdata.table-methodology.I updated my comment above for a pipeless alternative, is that more along the lines of what you were thinking, @jangorecki ?
Edit: see my renaming of it, my OCD was clicking a little hard just now :-)
Sorry I won't be able to really look at this anytime soon. Others can join and move it forward.
@r2evans Worth thinking about the possibilities of
envhere too. I actually visited this issue because I've been looking more atenvthanks to that SO question last week about programming a multi-argument (variadic) condition ini, and your question to me about how we could generalise the solution I gave. (I ought to thank you for that because it sent me to the programming vignette for a close read!)What I came up with was pairing
do.callwith the fact that, when the character names are in alist,substitute2not only converts them tonames but also constructs alistcall:as.data.table(iris)[do.call(pmin, cols) < 3, .N, by=Species, env=list(cols=as.list(c("Sepal.Length","Sepal.Width")))] # Argument 'i' after substitute: do.call(pmin, list(Sepal.Length, Sepal.Width)) < 3But playing around with
envand a couple of helper functions (make_callanducalls, at the bottom), the possibilities are very rich. For a start we can just write the expression directly:as.data.table(iris)[T < 3, .N, by=Species, verbose=TRUE, env=list(T=make_call("pmin", c("Sepal.Length","Sepal.Width")))] # Argument 'i' after substitute: pmin(Sepal.Length, Sepal.Width) < 3And we can also use
envto achieve theacrosstype of query you are talking about, allowing for extra common arguments and not obstructing GForce. Switching to the now-inbuiltpenguinssince it hasNAs:as.data.table(penguins)[T < 20, j, by="species", env=list(T=make_call("pmin", c("bill_len","bill_dep")), j=c(".N", ucalls(c("mean","median"), c("flipper_len","body_mass"), na.rm=TRUE, over="cols")))]Argument 'j' after substitute: list(.N, flipper_len_mean = mean(flipper_len, na.rm = TRUE), flipper_len_median = median(flipper_len, na.rm = TRUE), body_mass_mean = mean(body_mass, na.rm = TRUE), body_mass_median = median(body_mass, na.rm = TRUE)) Argument 'i' after substitute: pmin(bill_len, bill_dep) < 20 (...) GForce optimized j to 'list(.N, gmean(flipper_len, na.rm = TRUE), gmedian(flipper_len, na.rm = TRUE), gmean(body_mass, na.rm = TRUE), gmedian(body_mass, na.rm = TRUE))' (see ?GForce) (...) species N flipper_len_mean flipper_len_median body_mass_mean body_mass_median <fctr> <int> <num> <num> <num> <num> 1: Adelie 133 189.4211 190 3644.173 3600 2: Gentoo 123 217.1870 216 5076.016 5000 3: Chinstrap 63 195.3810 195 3700.397 3700These are the helpers - simple stuff. The "u" is for unary:
make_call <- function(fun, cols, ...) { dots <- as.list(substitute(list(...)))[-1] as.call(c(list(as.name(fun)), lapply(cols, as.name), dots)) } ucalls <- function(funs, cols, over=c("funs","cols"), named=TRUE, ...) { if (match.arg(over)=="funs") { ans <- lapply(funs, \(f) lapply(cols, \(c) make_call(f,c,...))) if (isTRUE(named)) nms <- paste(rep(cols,times=length(funs)),rep(funs,each=length(cols)),sep="_") } else { ans <- lapply(cols, \(c) lapply(funs, \(f) make_call(f,c,...))) if (isTRUE(named)) nms <- paste(rep(cols,each=length(funs)),rep(funs,times=length(cols)),sep="_") } ans <- do.call(c, ans) if (isTRUE(named)) names(ans) <- nms ans }Not the same as an inbuilt solution (and uses standard evaluation) but might be food for thought anyway. It's certainly brought home to me just how amazingly useful and well conceived
envis.Reacted by r2evans and Jan GoreckiReacted by Jan Gorecki
Inspired by
dplyr::acrossand triggered by JuliaData/DataFrames.jl#2725 (comment)Instead of :
it could be
I didn't find any related issues or PRs in a quick search. If there are any, and S.O. questions, please link them here.