Repository navigation
add a 'having' parameter to [.data.table #788
Description
Activity
Great FR. I've been pondering about this use case for quite a while as well. We can do this without the additional argument like so:
dt[, .SD[mean(var)>1], by=id]
(But for speed, this'll need optimisation of
.SD[.]internally - #735.)It's most likely this case that we resort to
.Iinstead:dt[dt[, .I[mean(var) > 1], by=id]$V1]
And it'd be great to get this directly (even better if we can achieve it without
having) - maybe ifjexpression evaluates to a 1-column logical vector? Just thinking out loud.Reacted by iagogv3Hi Arun. Thanks for the answer. Once optimisation of
.SDwill be available then this would be just a matter of 'taste' in terms of what is clearer to read between:dt[, .SD[mean(var)>1], by=id]and
dt[, .SD, by = id, having = mean(var) > 1]Even though the second may also have a better appeal for people coming from other languages (SQL in particular). But again, this might be just my opinion. Maybe I just using SQL too much in the last period (lol).
Reacted by iagogv3As far as the taste part goes - I really dislike adding an extra param, when it can be accomplished with simple and standard syntax (i.e. the first option above).
Reacted by Jan Gorecki and iagogv3Curious. I was sure that you were that one most likely to appreciate this :-) (considering how much you wanted to eliminate by-without-by, mainly to improve readability, especially for people coming from other languages, if I remember correctly). Anyway, I know the two are quite different scenarios. I just wanted to share my point of view,:
- I'm pretty sure it will be more readable for anyone not very familiar with R (or just data.table)
- 15 vs 14 (current) parameters of
[.data.tabledoesn't really harm - You won't be forced to use it and it won't break any code. It will be an expression that, if provided, will possibly skip the execution of
jfor a particular group
The reasons I didn't like the silent by-without-by and "having" are actually the same - I don't like to remember extra stuff, whether it be extra params or extra strange behavior.
I would argue that the first expression you wrote is much easier to read, because you don't have to keep reading the line, then discover that some new param is specified, and have to go back to the beginning of the sentence and reevaluate your mental model of what's going on.
What do you think of not adding
havingarg to[, but turning it intohaving()function and making it works iniargument, the same asorder()works, e.g.:dt[ having(var > 1), .(var = mean(var)), by = id ] # would perform below without additional copy: dt[, .(var = mean(var)), by = id ][ var > 1 ]
havingwould be a function to evaluate its argument in frame ofdtand provide filtering toi.Reacted by Pasha Stetsenko, Benjamin Schwendinger and Christopher EelesI think this FR is closely tied with #1269 "Returning only groups." I often want to get groups with some attribute and store them in a vector, like
my_teamsin this SO post. Here's the relevant line:my_teams <- FantasyTeams[, max(table(Team)) <= 3, by=team_no][(V1)]$team_no # or my_teams <- FantasyTeams[, if ( max(table(Team)) <= 3 ) 1, by=team_no]$team_noWith the
havingand "Returning only groups" FRs, this could be something likemy_teams <- FantasyTeams[, .(), by = team_no, having = { max(table(Team)) <= 3 }]$team_noThe code is just as long, but I prefer it, so I don't have to read
jcarefully to understand the objective.Another example from SO. The goal is to overwrite the
Valuecolumn with3Lif some by-group level condition holds:DT = setDT(structure(list(Ind = c(1L, 1L, 2L, 2L, 3L, 3L, 4L, 4L), ID = c("A", "A", "A", "A", "B", "B", "B", "B"), RegionStart = c(1L, 101L, 1L, 101L, 1L, 101L, 1L, 101L), RegionEnd = c(100L, 200L, 100L, 200L, 100L, 200L, 100L, 200L), Value = c(3L, 2L, 3L, 2L, 3L, 2L, 5L, 5L), TN = c("N", "N", "T", "T", "N", "N", "T", "T")), .Names = c("Ind", "ID", "RegionStart", "RegionEnd", "Value", "TN"), row.names = c(NA, -8L), class = "data.frame")) # current syntax DT[, Value := { fixit = ( Value[TN=="N"] != 3L ) & ( uniqueN(Value) == 1L ) if (fixit) 3L else Value }, by=.(ID, RegionStart)] # with "having" DT[, Value := 3L , by=.(ID, RegionStart) , having={ ( Value[TN=="N"] != 3L ) & ( n_distinct(Value) == 1L ) }]Besides arguably nicer syntax, I guess the
having=way might also be more efficient, since only a subset of by-groups need to be modified. The most efficient way withouthaving=probably looks like...myeyes = DT[, .I[ ( Value[TN=="N"] != 3L ) & ( uniqueN(Value) == 1L )], by=.(ID, RegionStart)]$V1 DT[ myeyes, Value := 3L] # or mygs = DT[, ( Value[TN=="N"] != 3L ) & ( uniqueN(Value) == 1L ), by=.(ID, RegionStart)][(V1)][, V1 := NULL] DT[ mygs, Value := 3L, on=names(mygs)]which are rather convoluted.
Edit: And another example to update if/when this feature is available: http://stackoverflow.com/q/36292702
(2016/4/26:) http://stackoverflow.com/q/36869784
(2016/06/16:) http://stackoverflow.com/q/37855013/Another example from SO. It could be used to select strictly unique rows (related to #1163 ):
DT = setDT(structure(list(id = c(1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4), dsp = c(5, 6, 7, 8, 6, 6, 7, 8, 5, 6, 9, 8, 5, 6, 7, NA), status = c(FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, TRUE)), .Names = c("id", "dsp", "status"), row.names = c(NA, -16L), class = "data.frame")) # my current way to select "strictly unique" rows Bigdt[, .N, by=names(Bigdt)][N == 1][, N := NULL][] # could be... Bigdt[, .SD, by=names(Bigdt), having ={.N == 1L}]Note that
Bigdt[, if (.N == 1L) .SD, by=names(Bigdt)]does not work here, since.SDis empty. Maybe that could be helped by #1269 , though.
And another from SO: http://stackoverflow.com/q/38272608/ They want to select groups based on stuff in the last row, so
having =heath condition[.N] == "not healthy"should do it.
And another simple case (filtering by size): http://stackoverflow.com/q/39085450/
And another, with an anti join:
ID <- c("A","A","A","B","B","C","D") Value <- c(0,1,2,0,2,0,0) df <- data.frame(ID,Value) library(data.table) setDT(df) # use j = max() to get GForce speedup df[ !df[, max(Value), by=ID][V1 > 0, .(ID, Value = 0)], on=.(ID, Value)] # do the more standard thing, if j = if (...) x df[ !df[, if (max(Value) > 0) .(Value = 0), by=ID], on=.(Value, ID) ] # desired syntax df[ !df[, .(Value = 0), by=ID, having = max(Value) > 0], on=.(Value, ID) ]Not such a great example, though.
And another with an answer like
dt[, if(uniqueN(time)==1L) .SD, by=name, .SDcols="time"]
And another: http://stackoverflow.com/q/43354165/
And another: http://stackoverflow.com/q/43613087/
Another (though it might get deleted): http://stackoverflow.com/q/43635968/
Another http://stackoverflow.com/a/43765352/
Another http://chat.stackoverflow.com/transcript/message/37148860#37148860
Un autre https://stackoverflow.com/q/45557011/
Haiyou https://stackoverflow.com/questions/45598397/filter-data-frame-matching-all-values-of-a-vector
Um mais https://stackoverflow.com/a/45721286/
lingwai yige https://stackoverflow.com/a/45820567/
and https://stackoverflow.com/q/46251221/
uno mas https://stackoverflow.com/questions/46307315/show-sequences-that-include-a-variable-in-r
tambem https://stackoverflow.com/q/46638058/
And another. I want to subset my data.table (myDT) to entries that aren't found in a reference table (idDT):
library(data.table) idDT = data.table(id = 1:3, v = c("A","B","C")) myDT = data.table(id = 3:4, z = c("gah","egad")) # my attempt idDT[myDT, on=.(id), .SD[.N == 0L], by=.EACHI] # Empty data.table (0 rows) of 2 cols: id,v # workaround myDT[, .SD[idDT[.SD, on=.(id), .N == 0, by=.EACHI]$V1]] # desired notation (with having=) myDT[, .SD, by = id, having = idDT[.BY, on=.(id), .N]==0L]This would be inefficient, though, since my desired notation entails each by= value making a separate join to idDT. In that sense, maybe it's not the best example.
mais um https://stackoverflow.com/questions/47765283/r-data-table-group-by-where/47765308?noredirect=1#comment82524998_47765308 could do
DT[, if (any(status == "A") && !any(status == "B")) .SD, by=id]or with a having parameterDT[, .SD, by=id, having = any(status == "A") && !any(status == "B")]and then https://stackoverflow.com/a/48669032/
m[, if(isTRUE(any(passed))) .SD, by=id]should bem[by = id, having = isTRUE(any(passed))]mais um exemplo https://stackoverflow.com/q/49072250/
ein anderer https://stackoverflow.com/a/49211292/
stock_profile[, sum(Value), by=Pcode, having=any(Location=="A" & NoSales == "Y")][, sum(V1)]mais um https://stackoverflow.com/a/49366998/
autre https://stackoverflow.com/a/49919015/
y https://stackoverflow.com/questions/50257643/deleting-rows-in-r-with-value-less-than-x
moar https://stackoverflow.com/q/54582048
e https://stackoverflow.com/q/56283005
keep groups if .N==k (also many at the dupe target) https://stackoverflow.com/questions/56794306/only-get-data-table-groups-with-a-given-number-of-rows
keep groups if any(diff(sorted_col)) <= threshold https://stackoverflow.com/q/57512417
keep if max(x) < threshold https://stackoverflow.com/a/57698641
Reacted by Tokhir Dadaev, Hong Zhang, Pasha Stetsenko, Michael Chirico, Artem Klevtsov, Cole Miller and iagogv3@eantonya IMHO, adding the
havingparameter will actually make it easier to remember. Excessive conciseness can be hard to memorize. Moreover, makingdata.tablemore like SQL isn't a bad idea.In
data.tableFAQ:2.16 I’ve heard that data.table syntax is analogous to SQL.
Yes : ...Reacted by Datocrat One@ywhuofu data.table already accepts
orderfunction toiargument, which is what base R user would expect. The same way we translate sql ORDER toi = order(...)we can do with HAVING. It fits well, asiin data.frame is used for subsetting (having is just delayed subsetting after aggregation) or reordering.Could this be the API?
dt <- data.table(id = rep(1:2, each = 2), var = c(0.2, 0.5, 1.5, 1.3)) dt[having.i(mean(var) > 1, by = id)] id var 1 2 1.5 2 2 1.3I have implemented this version although it sets a restriction of only using
gforceoptimized functions as well as some functions that do not depend on groupings (e.g.,+,|,&, etc.). In SQL, summary functions are what are used although I would understand ifCdogroupswould want to be supported.One additional note. It seems like it would be difficult to fit in
dt[having(var > 3), .(var = mean(x)), by = .(grp)]within the current'[.data.table'code. There would need to be some checks to make sure the syntax were correct.n = 1e6 grps = 1e5 head_n = 2L dt = data.table::data.table(x = sample(grps, n, TRUE), y = runif(n)) # A tibble: 2 x 13 expression min median <bch:expr> <bch:tm> <bch:tm> 1 lw[having.i(.N < 2L | sum(y) > 11 | median(y) < 0.7, by = x)] 114.13ms 124.98ms 2 dt[dt[, .I[.N < 2L | sum(y) > 11 | median(y) < 0.7], by = x]$V1] 4000ms 4000ms # A tibble: 2 x 13 expression min median `itr/sec` mem_alloc `gc/sec` n_itr <bch:expr> <bch:t> <bch:t> <dbl> <bch:byt> <dbl> <int> 1 lw[having.i(.N < 2L, by = x)] 30.2ms 35.3ms 27.9 8.02MB 3.99 14 2 dt[dt[, .I[.N < 2L], by = x]$V1] 106.1ms 110.4ms 8.81 6.13MB 10.6 5I would prefer this as an added parameter, named either as
having=orgroup_filter=(or something else that doesn't rely on SQL awareness to know what it does on sight).e.g. I think it would be confusing to combine row filters in
iwith group-level filters also iniWould
having =work on a subset of the data or would you only be able to use theiarg or thehavingarg? I also assume thathavingwould occur prior to thejbeing evaluated. How would.BYand.GRPand soon.NGRPwork with ```having = ````?2 remaining items
@franknarf1 It seems that while we are trying to detect
ifinj, we could also checkifhaselse ifandelse. We could optimizeif-only case and leaveif-elseun-optimized. Maybe later we could handleif-elsecase too. Personally I still prefer optimizing code to overriding or leveraging too much on existing operators.@franknarf1 this is cool C syntax, although not sure if it wouldn't complicate to much here.
var > 1 ? d : ecould work as well, isn't it?Reacted by iagogv3var > 1 ? d : elooks concise but only works for inline simple case sincedandecould be anything like{...}and the operator precedence may be confusing. Are we only trying to allow.SDto perform pure group filtering, or any expression injhere?Adding syntax has a problem that user needs to be aware that the syntax is specially handled and should not work inside
j. For example, user may expectdt[, mean(var) > 1 ? 0 : (sd(var) < 1 ? 1 : 0), by = id]
to work, and even
dt[, mean(var) > 1 ? 0 : 1] dt[, mean(var) > 1 ? 0 : (sd(var) < 1 ? 1 : 0)]
to work in general.
Reacted by Jan GoreckiI'm a bit confused here.
Does
dt[, .SD, by = id, having = mean(var) > 1]
have any advantage over
dt[, if(mean(var) > 1) .SD, by = id]since
mean(var) > 1will always be evaluated for each group. Does it only serve as a syntactic sugar or we are trying to optimize over this somehow to have higher performance?Reacted by Jan Gorecki and Xianying Tan@franknarf1 this is cool C syntax, although not sure if it wouldn't complicate to much here.
var > 1 ? d : ecould work as well, isn't it?Yeah, that would be cool. Operator precedence might get in the way without
{}s as @renkun-ken pointed out (ex = quote(x & y ? a+b : v+w); str(rapply(as.list(ex), as.list, how="replace")))I'm a bit confused here.
Does
dt[, .SD, by = id, having = mean(var) > 1]
have any advantage over
dt[, if(mean(var) > 1) .SD, by = id]since
mean(var) > 1will always be evaluated for each group. Does it only serve as a syntactic sugar or we are trying to optimize over this somehow to have higher performance?I guess until now I had preferred
having=because I find it a little clearer to read and imagine it's easier to maintain as compared to adding further syntactical magic toj. On the other hand, I think I might instead prefer thejsyntax magic, since- I am used to
if () ...already; and like the?way too if it's feasible. - If it is integrated in
j, then no additional questions need to be answered about its behavior (eg,DT[, x := if (cond) y, by=id]creates NAs if the condition is met in some groups but not others and this behavior shouldn't need to be re-explained forhaving=).
Regarding optimization, it seems like there are a lot of examples where the having condition itself could benefit from some version of GForce, since it usually is an expression like
max(x) > 0,max(x) == 0.For my own use, besides the optimization, I guess it would be mostly useful for the return-only-groups case mentioned above #1269
> dt[, if (mean(var) > 1) .(), by=id] > # instead of ... > dt[, mean(var) > 1, by=id][V1 == TRUE, !"V1"] id 1: 2Reacted by Michael Chirico and Kun Ren- I am used to
- Nice points Frank. in addition to the huge compendium of use cases you've built (thanks again btw!). it may in fact be easier to do GForce in the having= version since we can just apply the gforce logic to having similar to j rather than trying to do NSE to accomplish the same. though that may interact w Jan's WIP to move a lot of j code to C -- any thoughts there Jan?…On Sat, Feb 15, 2020, 1:40 PM Frank ***@***.***> wrote: @jangorecki <https://github.com/jangorecki> @franknarf1 <https://github.com/franknarf1> this is cool C syntax, although not sure if it wouldn't complicate to much here. var > 1 ? d : e could work as well, isn't it? Yeah, that would be cool. Operator precedence might get in the way without {}s as @renkun-ken <https://github.com/renkun-ken> pointed out (ex = quote(x & y ? a+b : v+w); str(rapply(as.list(ex), as.list, how="replace")) ) I'm a bit confused here. Does dt[, .SD, by = id, having = mean(var) > 1] have any advantage over dt[, if(mean(var) > 1) .SD, by = id] since mean(var) > 1 will always be evaluated for each group. Does it only serve as a syntactic sugar or we are trying to optimize over this somehow to have higher performance? I guess until now I had preferred having= because I find it a little clearer to read and imagine it's easier to maintain as compared to adding further syntactical magic to j. On the other hand, I think I might instead prefer the j syntax magic, since - I am used to if () ... already; and like the ? way too if it's feasible. - If it is integrated in j, then no additional questions need to be answered about its behavior (eg, DT[, x := if (cond) y, by=id] creates NAs if the condition is met in some groups but not others and this behavior shouldn't need to be re-explained for having=). Regarding optimization, it seems like there are a lot of examples where the having condition itself could benefit from some version of GForce, since it usually is an expression like max(x) > 0, max(x) == 0. For my own use, besides the optimization, I guess it would be mostly useful for the return-only-groups case mentioned above #1269 <#1269> > dt[, if (mean(var) > 1) .(), by=id] > # instead of ... > dt[, mean(var) > 1, by=id][V1 == TRUE, !"V1"] id 1: 2 — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#788?email_source=notifications&email_token=AB2BA5OCN4IW3N6QQJU6RJ3RC555BA5CNFSM4ATSQPMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEL3CK7A#issuecomment-586556796>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB2BA5MD7ZXWSRRHVEJM6C3RC555BANCNFSM4ATSQPMA> .Reacted by iagogv3
j code to be moved to C is the code that is responsible for column selection only, so guessing
withargument. Won't interfere here.Since the FR is for add a 'having' parameter..., the word
havingshould be somewhere in the solution. Optimization for ternary operators seems like a separate issue.My preference for
having.i()is because of the mantra of data.table: subset / order in i, select in j, group in by.havingis just a special case of subsetting.Regardless, if there is a new argument
having, would the API support aniarg? Most use cases do not seem to need that requirement.What should be the behavior of ordering? That is, most of the current approaches automatically re-order:
library(data.table) dt = data.table(grp = c(1L, 2L, 1L, 2L), x = letters[sample(4L)]) dt #> grp x #> <int> <char> #> 1: 1 a #> 2: 2 b #> 3: 1 c #> 4: 2 d dt[dt[, .I[.N > 0L], by = grp]$V1] #> grp x #> <int> <char> #> 1: 1 a #> 2: 1 c #> 3: 2 b #> 4: 2 d
Should the
havingarg return a result that is re-ordered according toby?What should be the behavior of ordering?
Should thehavingarg return a result that is re-ordered according toby?@ColeMiller1 Fwiw, I would expect
having=to only appear whenby=also appears, so the results would be grouped as in your example with...$V1.Reacted by Cole MillerYes I would expect the ordering to be consistent:
DT[i, j, by, having] # < == > DT[i, if (having) j, by]Reacted by Cole MillerI think there was no agreement on API, particularly on having new
havingargument in[. @mattdowle wdyt?
Current approach of usingDT[, if (.N > 1L) .SD, col1]is good one, not really complicated, easy to extend, but little bit more difficult to optimise.
My idea was to usehavingas function call ini:DT[having(N > 1L), .N, col1], but then it is not possible to provide normal subsetting toi.
Alternatively new arg could be a sub-argument ofby, haven't much thought about it but something likeDT[, .N, by=.(col1, .having = N > 1L)], so the extra grouping-related argument is encapsulated intobyargument. This is the proper way to scale up number of arguments.
Currently, to have the equivalent (or something similar) of the SQL
havingclause you need to write a[.data.tablefirst usingbyand then feed the result into theiparameter of a second[.data.table, like in:Another option is to use conditional statement inside
j, very powerful, I do all the time, and so far there is nothing that the current syntax did not allow me to do. However having ahavingparameter I believe will allow writing much more clear and readable codes. For example the above can be written as:dt[, if(mean(var) > 1) .SD, by = id]What I propose is something like:
dt[, .SD, by = id, having = mean(var) > 1]The idea is to have an expression that always evaluates to a logical of length 1 which would tell whether or not
jhas to be evaluated for the current group.Thanks,
Michele