join vignette

Several places in the available vignettes refer to this mysterious vignette about join and rolling join. When will it be up? Thanks

See #944 . There are many ideas for vignettes there.

If you want some examples, you could see stackoverflow.com or maybe my notes.

Noting for reference: joins vignette would be a good place to have an example of replacing nested ifelse with a join.

I used the flights data to explain joins, in my slides for the data.table tutorial at the LatinR meeting last month, https://github.com/tdhock/2023-10-LatinR-data.table#english

mergelist PR is ready to merge so probably will land in master before the vignette, so should be included as well

foverlaps is missing

I think we should avoid the merge function other than as a side note. One of data.table's strengths is its merge syntax, and that is what should be front, right and centre.

In addition, the overlap join functions have a separate syntax, it might be worth placing all syntactically similar joins together to have them all in one place.

@avimallu

@avimallu

I think we should avoid the merge function other than as a side note. One of data.table's strengths is its merge syntax, and that is what should be front, right and centre.

I started the vignette with the merge function as is easier to understand for new users. In my case is normal to use many merge function in chain following the next syntax as is the only way to apply left join.

DtMerged <-
DT1[...
][, merge(.SD, DT2, by = "x", all.x = TRUE)
][, merge(.SD, DT3, by = "y", all.x = TRUE)]

What I could do is to move the mergelist from point 3 to point 2 to avoid the switch from function to syntax.

In addition, the overlap join functions have a separate syntax, it might be worth placing all syntactically similar joins together to have them all in one place.

I thought that overlap join is an application of non-equi join.

@avimallu

overlapping join (foverlaps api) is a special case of non-equi join ([.data.table api).

I second @avimallu suggestion about dropping merge. Mentioning it and linking manual sounds good, but it is sub-optimal in performance and in features so we should try not to onboard users into it. mergelist is good substitute.

Here is the new structure

data.table syntax joins

Right join
Left join (custom function)
Keyed joins
Natural join
Update x on join
Aggregate on join (by = .EACHI or by = x's columns)
Editing x based on i matching columns by x groups ()
i. and x. j's prefixes
Inner join
Not join
Many to many join
- allow.cartesian
- mult
Non-equi join (>=, >, <=, <)
Rolling join
Semi join
Cross join

Joining a list of tables (mergelist)
Full join (by using merge and link to documentation)

nb. mergelist supports full join as well, probably much more efficient than merge

nb. mergelist supports full join as well, probably much more efficient than merge

That's sounds really good.

So the will the vignette's structure:

data.table syntax joins

Right join
Left join (custom function)
Keyed joins
Natural join
Update x on join
Aggregate on join (by = .EACHI or by = x's columns)
Editing x based on i matching columns by x groups ()
i. and x. j's prefixes
Inner join
Not join
Many to many join
- allow.cartesian
- mult
Non-equi join (>=, >, <=, <)
Rolling join
Semi join
Cross join

Full join and joining a list of tables (mergelist)

Link to merge function documentation

Moreover, mergelist has more mult options as far as I recall.

Aggregate on join is not possible by x's column yet. Only by each I.

Tweaks on datatable-joins.Rmd

fixed by #6478

(textproc/R-data.table) Updated 1.16.4 to 1.18.4, Fix build against 4…

Navigation Menu

Repository navigation

join vignette #2181

42 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

join vignette #2181

Description

Activity

franknarf1 commented on May 27, 2017

arunsrinivasan commented on Jun 29, 2017

batcheneden commented on Jul 13, 2017

MichaelChirico commented on Jul 13, 2017

batcheneden commented on Jul 16, 2017

jangorecki commented on Aug 26, 2019

MichaelChirico commented on Aug 29, 2019

42 remaining items

tdhock commented on Nov 28, 2023

jangorecki commented on Nov 28, 2023

avimallu commented on Nov 28, 2023

AngelFelizR commented on Nov 28, 2023

jangorecki commented on Nov 28, 2023

AngelFelizR commented on Nov 28, 2023

jangorecki commented on Nov 28, 2023

AngelFelizR commented on Nov 28, 2023

jangorecki commented on Nov 28, 2023

tdhock commented on Nov 15, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions