<div>I am wondering if performing X[Y] as a "merge" in correspondence with R's base "merge", if the basic idea of "i" becomes confusing. That is, when "i" is not a data.table in X[i] it indexes by rows. When `i` is a data.table, instead of the current definition which is in par with the subletting operation that use `i` (here data.table) as an index to subset X and then JOIN both X and Y, we say, here X and Y are data.tables and we perform a merge. I think this becomes confusing regarding the purpose of `i`.</div><div><br></div><div>Remember that the main purpose of having the X[Y] is to have the flexibility of using `j` to to filter/subset only the desired columns. So, for example if you want to get 1 column of Y out of 100 columns when joining, you do: X[Y, list(cols_of_x, one_col_of_y)] and here, it doesn't go with the traditional definition of merge.</div><div><br></div><div>As much as I like the idea of having consistent syntax, I also love the feature of X[Y, j]. So I'm confused as to how to deal with this.</div><div><br></div><div><div>Arun</div><div><br></div></div>
<p style="color: #A0A0A8;">On Friday, May 3, 2013 at 6:54 PM, Gabor Grothendieck wrote:</p>
<blockquote type="cite" style="border-left-style:solid;border-width:1px;margin-left:0px;padding-left:10px;">
<span><div><div><div>I think that from the viewpoint of compatibility and convenience it</div><div>would be best to implement all.x and all.y and not rely on swapping X</div><div>and Y. SQLite did something like this (they implemented left join but</div><div>not right join based on the idea that all you have to do is swap join</div><div>arguments) but the problem with it is that it adds a layer of mental</div><div>specification effort if the actual problem is better stated in the</div><div>unsupported orientation.</div><div><br></div><div>On Fri, May 3, 2013 at 12:49 PM, Eduard Antonyan</div><div><<a href="mailto:eduard.antonyan@gmail.com">eduard.antonyan@gmail.com</a>> wrote:</div><blockquote type="cite"><div><div>Arun, it only needs the addition of smth like X[Y, keep.all = TRUE], all of</div><div>the other merge options already exist as either X[Y] or Y[X] with or without</div><div>nomatch = 0/NA.</div><div><br></div><div><br></div><div>On Fri, May 3, 2013 at 11:45 AM, Arunkumar Srinivasan</div><div><<a href="mailto:aragorn168b@gmail.com">aragorn168b@gmail.com</a>> wrote:</div><blockquote type="cite"><div><div><br></div><div>Gabor,</div><div><br></div><div>Very true. I suppose your request is that the x[i] where `i` is a</div><div>data.table should have the same set of options like R's base `merge`</div><div>function, like, by.y=TRUE, by.x=TRUE or all=TRUE. I like the idea by itself.</div><div>However, I am not able to think of a way to do this. I mean, I find the</div><div>syntax X[Y, by.x=TRUE] weird / not making sense. That is, to me even though</div><div><br></div><div>X[Y] is equal to Y[X, by.y=TRUE] (or) X[Y, by.x=TRUE] (ignoring the</div><div>reordered columns) the latter 2 don't seem to make sense/is redundant (maybe</div><div>it's because I am used to this syntax).</div><div><br></div><div>Arun</div><div><br></div><div>On Friday, May 3, 2013 at 5:57 PM, Gabor Grothendieck wrote:</div><div><br></div><div>In my last post it should have read:</div><div><br></div><div>That X[Y] is not the same as Y[X] is analogous to the fact that</div><div>merge(X, Y, all.y=TRUE) is not the same as merge(Y, X, all.y=TRUE)</div><div><br></div><div>On Fri, May 3, 2013 at 11:55 AM, Gabor Grothendieck</div><div><<a href="mailto:ggrothendieck@gmail.com">ggrothendieck@gmail.com</a>> wrote:</div><div><br></div><div>Assuming same-named keys, then these are all the same except possibly</div><div>for row and column order:</div><div><br></div><div>X[Y,,nomatch=0]</div><div>Y[X,,nomatch=0]</div><div>merge(X, Y)</div><div>merge(Y, X)</div><div><br></div><div>That X[Y] is not the same as Y[X] is analogous to the fact that</div><div>merge(X, Y, all.x=TRUE) is not the same as merge(Y, X, all.x=TRUE)</div><div><br></div><div>On Fri, May 3, 2013 at 11:46 AM, Arunkumar Srinivasan</div><div><<a href="mailto:aragorn168b@gmail.com">aragorn168b@gmail.com</a>> wrote:</div><div><br></div><div>Gabor,</div><div><br></div><div>X[Y] and Y[X] are not necessarily the same operations (meaning, they don't</div><div>*have* to give the same output). However, merge(X,Y) and merge(Y,X) *have*</div><div>to provide the same output (except for the column order and names). In</div><div>that</div><div>sense, a join is a bit different from a merge, no?</div><div><br></div><div>Arun</div><div><br></div><div>On Friday, May 3, 2013 at 5:36 PM, Gabor Grothendieck wrote:</div><div><br></div><div>Yes, except that is not really what happens since match() only matches</div><div>one row whereas with mult="all", the default, all rows are matched</div><div>which is not really matching in the sense of match(). The current</div><div>naming confuses matching with joining and its really the latter that</div><div>is being done.</div><div><br></div><div>Regarding the existence of merge the advantage of [ is that it will</div><div>automatically only take the columns needed so merge is not really</div><div>equivalent to [ in all respects. Furthermore having to use different</div><div>constructs for different types of merge seems awkward.</div><div><br></div><div><br></div><div>On Fri, May 3, 2013 at 11:27 AM, Eduard Antonyan</div><div><<a href="mailto:eduard.antonyan@gmail.com">eduard.antonyan@gmail.com</a>> wrote:</div><div><br></div><div>Btw the way I think about the "nomatch" name is as follows - normally X[Y]</div><div>tries to match rows of Y with rows of X, and then "nomatch" tells it what</div><div>to</div><div>do when there is *no match*.</div><div><br></div><div><br></div><div>On Fri, May 3, 2013 at 10:23 AM, Eduard Antonyan</div><div><<a href="mailto:eduard.antonyan@gmail.com">eduard.antonyan@gmail.com</a>></div><div>wrote:</div><div><br></div><div><br></div><div>To clarify - that behavior is already implemented in merge (more</div><div>specifically merge.data.table). I don't really have a view on having it in</div><div>X[Y] as well - I don't like all.x and all.y as the names, since there are</div><div>no</div><div>params named 'x' and 'y' in [.data.table (as opposed to merge), but some</div><div>param that would do a full outer join could certainly be added.</div><div><br></div><div><br></div><div>On Fri, May 3, 2013 at 10:09 AM, Gabor Grothendieck</div><div><<a href="mailto:ggrothendieck@gmail.com">ggrothendieck@gmail.com</a>> wrote:</div><div><br></div><div><br></div><div>Yes, sorry. Its nomatch= which presumably derives from the parameter</div><div>of the same name in the match() function. If the idea of the nomatch=</div><div>name was to leverage off existing argument names in R then I would</div><div>prefer all.y= to be consistent with merge() in place of nomatch= since</div><div>we are really merging/joining rather than just matching. That would</div><div>also allow extension to all types of join by adding all.an x= argument</div><div>too.</div><div><br></div><div>On Fri, May 3, 2013 at 10:59 AM, Eduard Antonyan</div><div><<a href="mailto:eduard.antonyan@gmail.com">eduard.antonyan@gmail.com</a>> wrote:</div><div><br></div><div>I would prefer nomatch=0 as a default though, simply because that's</div><div>what I</div><div>do most of the time :)</div><div><br></div><div><br></div><div>On Fri, May 3, 2013 at 9:57 AM, Eduard Antonyan</div><div><<a href="mailto:eduard.antonyan@gmail.com">eduard.antonyan@gmail.com</a>></div><div>wrote:</div><div><br></div><div><br></div><div>A correction - the param is called "nomatch", not "match".</div><div><br></div><div>This use case seems like smth a user shouldn't really do - in an ideal</div><div>world you should have them both keyed by the same-name column.</div><div><br></div><div>As is, my view on it is that data.table is correcting the user mistake</div><div>of</div><div>naming the column in Y - y, instead of x, and so the output makes</div><div>sense and</div><div>I don't see the need of complicating the behavior by adding more cases</div><div>one</div><div>has to go through to figure out what the output columns would be.</div><div>Similar to</div><div>asking for X[J(c("b", "c", "d"))] - you wouldn't want an anonymous</div><div>column</div><div>there, would you?</div><div><br></div><div><br></div><div><br></div><div>On Fri, May 3, 2013 at 6:18 AM, Gabor Grothendieck</div><div><<a href="mailto:ggrothendieck@gmail.com">ggrothendieck@gmail.com</a>> wrote:</div><div><br></div><div><br></div><div>I am moving this discussion which started with mdowle to the list.</div><div><br></div><div>Consider this example slightly modified from the data.table FAQ:</div><div><br></div><div>X = data.table(x=c("a","a","b","b","b","c","c"), foo=1:7, key="x")</div><div>Y = data.table(y=c("b","c","d"), bar=c(4,2,3))</div><div>out <- X[Y]; out</div><div><br></div><div>x foo bar</div><div>1: b 3 4</div><div>2: b 4 4</div><div>3: b 5 4</div><div>4: c 6 2</div><div>5: c 7 2</div><div>6: d NA 3</div><div><br></div><div>Note that the first column of the output is labelled x even though</div><div>the</div><div>data to produce it comes from y, e.g. "d" in out$x is not in X$x but</div><div>does appear in Y$y so clearly the data is coming from y as opposed to</div><div>x . In terms of SQL the above would be written:</div><div><br></div><div>select Y.y as x, ...</div><div><br></div><div>and the need to renamne the first column of out suggests that there</div><div>may be a deeper problem here.</div><div><br></div><div>Here are some ideas to address this (they would require changes to</div><div>data.table):</div><div><br></div><div>- the default of X[Y,, match=NA] would be changed to a default of</div><div>X[Y,,match=0] so that it corresponds to the defaults in R's merge and</div><div>in SQL joins.</div><div><br></div><div>- the column name of the first column in the example above would be</div><div>changed to y if match=0 but be left at x if match=NA. In the case</div><div>that match=0 (the proposed new default) x and y are equal so the</div><div>first</div><div>column can be validly labelled as x but in the case that match=NA</div><div>they</div><div>are not so y would be used as the column name.</div><div><br></div><div>- the name match= does seem a bit misleading since R's match only</div><div>matches one item in the target whereas in data.table match matches</div><div>many if mult="all" and that is the default. Perhaps some thought</div><div>should be given to a name change here?</div><div><br></div><div>The above would seem to correspond more closely to R's merge and SQL</div><div>join defaults. Any use cases or other comments?</div><div><br></div><div>--</div><div>Statistics & Software Consulting</div><div>GKX Group, GKX Associates Inc.</div><div>tel: 1-877-GKX-GROUP</div><div>email: ggrothendieck at <a href="http://gmail.com">gmail.com</a></div><div>_______________________________________________</div><div>datatable-help mailing list</div><div><a href="mailto:datatable-help@lists.r-forge.r-project.org">datatable-help@lists.r-forge.r-project.org</a></div><div><br></div><div><br></div><div><br></div><div><a href="https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help">https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help</a></div><div><br></div><div><br></div><div><br></div><div><br></div><div>--</div><div>Statistics & Software Consulting</div><div>GKX Group, GKX Associates Inc.</div><div>tel: 1-877-GKX-GROUP</div><div>email: ggrothendieck at <a href="http://gmail.com">gmail.com</a></div><div><br></div><div><br></div><div><br></div><div><br></div><div>--</div><div>Statistics & Software Consulting</div><div>GKX Group, GKX Associates Inc.</div><div>tel: 1-877-GKX-GROUP</div><div>email: ggrothendieck at <a href="http://gmail.com">gmail.com</a></div><div>_______________________________________________</div><div>datatable-help mailing list</div><div><a href="mailto:datatable-help@lists.r-forge.r-project.org">datatable-help@lists.r-forge.r-project.org</a></div><div><br></div><div><a href="https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help">https://lists.r-forge.r-project.org/cgi-bin/mailman/listinfo/datatable-help</a></div><div><br></div><div><br></div><div><br></div><div><br></div><div>--</div><div>Statistics & Software Consulting</div><div>GKX Group, GKX Associates Inc.</div><div>tel: 1-877-GKX-GROUP</div><div>email: ggrothendieck at <a href="http://gmail.com">gmail.com</a></div><div><br></div><div><br></div><div><br></div><div><br></div><div>--</div><div>Statistics & Software Consulting</div><div>GKX Group, GKX Associates Inc.</div><div>tel: 1-877-GKX-GROUP</div><div>email: ggrothendieck at <a href="http://gmail.com">gmail.com</a></div></div></blockquote></div></blockquote><div><br></div><div><br></div><div><br></div><div>-- </div><div>Statistics & Software Consulting</div><div>GKX Group, GKX Associates Inc.</div><div>tel: 1-877-GKX-GROUP</div><div>email: ggrothendieck at <a href="http://gmail.com">gmail.com</a></div></div></div></span>
</blockquote>
<div>
<br>
</div>