How to join (merge) data frames (inner, outer, left, right)

Source: sql - What is the difference between JOIN and UNION? - Stack Overflow from stackoverflow.com

sql dataset linking specific

fixed 2 information frames:

df1 = information.framework(CustomerId = c(1:6), merchandise = c(rep("Toaster", three), rep("energy", three)))
df2 = information.framework(CustomerId = c(2, four, 6), government = c(rep("Alabama", 2), rep("Ohio", 1)))

df1
#  CustomerId merchandise
#           1 Toaster
#           2 Toaster
#           three Toaster
#           four   energy
#           5   energy
#           6   energy

df2
#  CustomerId   government
#           2 Alabama
#           four Alabama
#           6    Ohio

however tin one bash database kind, one.e., sql kind, joins? That is, however bash one acquire:

• An interior articulation of df1 and df2:
  instrument lone the rows successful which the near array person matching keys successful the correct array.
• An outer articulation of df1 and df2:
  Returns each rows from some tables, articulation data from the near which person matching keys successful the correct array.
• A near outer articulation (oregon merely near articulation) of df1 and df2
  instrument each rows from the near array, and immoderate rows with matching keys from the correct array.
• A correct outer articulation of df1 and df2
  instrument each rows from the correct array, and immoderate rows with matching keys from the near array.

---

other recognition:

however tin one bash a SQL kind choice message?

By utilizing the merge relation and its optionally available parameters:

interior articulation: merge(df1, df2) volition activity for these examples due to the fact that R mechanically joins the frames by communal adaptable names, however you would about apt privation to specify merge(df1, df2, by = "CustomerId") to brand certain that you had been matching connected lone the fields you desired. You tin besides usage the by.x and by.y parameters if the matching variables person antithetic names successful the antithetic information frames.

Outer articulation: merge(x = df1, y = df2, by = "CustomerId", each = actual)

near outer: merge(x = df1, y = df2, by = "CustomerId", each.x = actual)

correct outer: merge(x = df1, y = df2, by = "CustomerId", each.y = actual)

transverse articulation: merge(x = df1, y = df2, by = NULL)

conscionable arsenic with the interior articulation, you would most likely privation to explicitly walk "CustomerId" to R arsenic the matching adaptable. one deliberation it's about ever champion to explicitly government the identifiers connected which you privation to merge; it's safer if the enter information.frames alteration unexpectedly and simpler to publication future connected.

You tin merge connected aggregate columns by giving by a vector, e.g., by = c("CustomerId", "OrderId").

If the file names to merge connected are not the aforesaid, you tin specify, e.g., by.x = "CustomerId_in_df1", by.y = "CustomerId_in_df2" wherever CustomerId_in_df1 is the sanction of the file successful the archetypal information framework and CustomerId_in_df2 is the sanction of the file successful the 2nd information framework. (These tin besides beryllium vectors if you demand to merge connected aggregate columns.)

one would urge checking retired Gabor Grothendieck's sqldf bundle, which permits you to explicit these operations successful SQL.

room(sqldf)

## interior articulation
df3 - sqldf("choice CustomerId, merchandise, government 
              FROM df1
              articulation df2 utilizing(CustomerID)")

## near articulation (substitute 'correct' for correct articulation)
df4 - sqldf("choice CustomerId, merchandise, government 
              FROM df1
              near articulation df2 utilizing(CustomerID)")

one discovery the SQL syntax to beryllium easier and much earthy than its R equal (however this whitethorn conscionable indicate my RDBMS bias).

seat Gabor's sqldf GitHub for much accusation connected joins.

You tin bash joins arsenic fine utilizing Hadley Wickham's superior dplyr bundle.

room(dplyr)

#brand certain that CustomerId cols are some the aforesaid kind
#they aren’t successful the supplied information (1 is integer and 1 is treble)
df1$CustomerId - arsenic.treble(df1$CustomerId)

Mutating joins: adhd columns to df1 utilizing matches successful df2

#interior
inner_join(df1, df2)

#near outer
left_join(df1, df2)

#correct outer
right_join(df1, df2)

#alternate correct outer
left_join(df2, df1)

#afloat articulation
full_join(df1, df2)

Filtering joins: filter retired rows successful df1, don't modify columns

#support lone observations successful df1 that lucifer successful df2.
semi_join(df1, df2)

#driblet each observations successful df1 that lucifer successful df2.
anti_join(df1, df2)

location is the information.array attack for an interior articulation, which is precise clip and representation businesslike (and essential for any bigger information.frames):

room(information.array)
  
dt1 - information.array(df1, cardinal = "CustomerId") 
dt2 - information.array(df2, cardinal = "CustomerId")

joined.dt1.dt.2 - dt1[dt2]

merge besides plant connected information.tables (arsenic it is generic and calls merge.information.array)

merge(dt1, dt2)

information.array documented connected stackoverflow:
however to bash a information.array merge cognition
Translating SQL joins connected abroad keys to R information.array syntax
businesslike options to merge for bigger information.frames R
however to bash a basal near outer articulation with information.array successful R?

but different action is the articulation relation recovered successful the plyr bundle. [line from 2022: plyr is present retired and has been outmoded by dplyr. articulation operations successful dplyr are described successful this reply.]

room(plyr)

articulation(df1, df2,
     kind = "interior")

#   CustomerId merchandise   government
# 1          2 Toaster Alabama
# 2          four   energy Alabama
# three          6   energy    Ohio

choices for kind: interior, near, correct, afloat.

From ?articulation: dissimilar merge, [articulation] preserves the command of x nary substance what articulation kind is utilized.

location are any bully examples of doing this complete astatine the R Wiki. one'll bargain a mates present:

Merge technique

Since your keys are named the aforesaid the abbreviated manner to bash an interior articulation is merge():

merge(df1, df2)

a afloat interior articulation (each data from some tables) tin beryllium created with the "each" key phrase:

merge(df1, df2, each=actual)

a near outer articulation of df1 and df2:

merge(df1, df2, each.x=actual)

a correct outer articulation of df1 and df2:

merge(df1, df2, each.y=actual)

you tin flip 'em, slap 'em and hitch 'em behind to acquire the another 2 outer joins you requested astir :)

Subscript technique

A near outer articulation with df1 connected the near utilizing a subscript methodology would beryllium:

df1[,"government"]-df2[df1[ ,"merchandise"], "government"]

The another operation of outer joins tin beryllium created by mungling the near outer articulation subscript illustration. (yea, one cognize that's the equal of saying "one'll permission it arsenic an workout for the scholar...")

replace connected information.array strategies for becoming a member of datasets. seat beneath examples for all kind of articulation. location are 2 strategies, 1 from [.information.array once passing 2nd information.array arsenic the archetypal statement to subset, different manner is to usage merge relation which dispatches to accelerated information.array technique.

df1 = information.framework(CustomerId = c(1:6), merchandise = c(rep("Toaster", three), rep("energy", three)))
df2 = information.framework(CustomerId = c(2L, 4L, 7L), government = c(rep("Alabama", 2), rep("Ohio", 1))) # 1 worth modified to entertainment afloat outer articulation

room(information.array)

dt1 = arsenic.information.array(df1)
dt2 = arsenic.information.array(df2)
setkey(dt1, CustomerId)
setkey(dt2, CustomerId)
# correct outer articulation keyed information.tables
dt1[dt2]

setkey(dt1, NULL)
setkey(dt2, NULL)
# correct outer articulation unkeyed information.tables - usage `connected` statement
dt1[dt2, connected = "CustomerId"]

# near outer articulation - swap dt1 with dt2
dt2[dt1, connected = "CustomerId"]

# interior articulation - usage `nomatch` statement
dt1[dt2, nomatch=NULL, connected = "CustomerId"]

# anti articulation - usage `!` function
dt1[!dt2, connected = "CustomerId"]

# interior articulation - utilizing merge technique
merge(dt1, dt2, by = "CustomerId")

# afloat outer articulation
merge(dt1, dt2, by = "CustomerId", each = actual)

# seat ?merge.information.array arguments for another circumstances

beneath benchmark checks basal R, sqldf, dplyr and information.array.
Benchmark exams unkeyed/unindexed datasets. Benchmark is carried out connected 50M-1 rows datasets, location are 50M-2 communal values connected articulation file truthful all script (interior, near, correct, afloat) tin beryllium examined and articulation is inactive not trivial to execute. It is kind of articulation which fine emphasis articulation algorithms. Timings are arsenic of sqldf:zero.four.eleven, dplyr:zero.7.eight, information.array:1.12.zero.

# interior
part: seconds
   expr       min        lq      average    median        uq       max neval
   basal 111.66266 111.66266 111.66266 111.66266 111.66266 111.66266     1
  sqldf 624.88388 624.88388 624.88388 624.88388 624.88388 624.88388     1
  dplyr  fifty one.91233  fifty one.91233  fifty one.91233  fifty one.91233  fifty one.91233  fifty one.91233     1
     DT  10.40552  10.40552  10.40552  10.40552  10.40552  10.40552     1
# near
part: seconds
   expr        min         lq       average     median         uq        max 
   basal 142.782030 142.782030 142.782030 142.782030 142.782030 142.782030     
  sqldf 613.917109 613.917109 613.917109 613.917109 613.917109 613.917109     
  dplyr  forty nine.711912  forty nine.711912  forty nine.711912  forty nine.711912  forty nine.711912  forty nine.711912     
     DT   9.674348   9.674348   9.674348   9.674348   9.674348   9.674348       
# correct
part: seconds
   expr        min         lq       average     median         uq        max
   basal 122.366301 122.366301 122.366301 122.366301 122.366301 122.366301     
  sqldf 611.119157 611.119157 611.119157 611.119157 611.119157 611.119157     
  dplyr  50.384841  50.384841  50.384841  50.384841  50.384841  50.384841     
     DT   9.899145   9.899145   9.899145   9.899145   9.899145   9.899145     
# afloat
part: seconds
  expr       min        lq      average    median        uq       max neval
  basal 141.79464 141.79464 141.79464 141.79464 141.79464 141.79464     1
 dplyr  ninety four.66436  ninety four.66436  ninety four.66436  ninety four.66436  ninety four.66436  ninety four.66436     1
    DT  21.62573  21.62573  21.62573  21.62573  21.62573  21.62573     1

beryllium alert location are another varieties of joins you tin execute utilizing information.array:
- replace connected articulation - if you privation to lookup values from different array to your chief array
- mixture connected articulation - if you privation to combination connected cardinal you are becoming a member of you bash not person to materialize each articulation outcomes
- overlapping articulation - if you privation to merge by ranges
- rolling articulation - if you privation merge to beryllium capable to lucifer to values from preceeding/pursuing rows by rolling them guardant oregon backward
- non-equi articulation - if your articulation information is non-close

codification to reproduce:

room(microbenchmark)
room(sqldf)
room(dplyr)
room(information.array)
sapply(c("sqldf","dplyr","information.array"), packageVersion, simplify=mendacious)

n = 5e7
fit.fruit(108)
df1 = information.framework(x=example(n,n-1L), y1=rnorm(n-1L))
df2 = information.framework(x=example(n,n-1L), y2=rnorm(n-1L))
dt1 = arsenic.information.array(df1)
dt2 = arsenic.information.array(df2)

mb = database()
# interior articulation
microbenchmark(instances = 1L,
               basal = merge(df1, df2, by = "x"),
               sqldf = sqldf("choice * FROM df1 interior articulation df2 connected df1.x = df2.x"),
               dplyr = inner_join(df1, df2, by = "x"),
               DT = dt1[dt2, nomatch=NULL, connected = "x"]) -> mb$interior

# near outer articulation
microbenchmark(occasions = 1L,
               basal = merge(df1, df2, by = "x", each.x = actual),
               sqldf = sqldf("choice * FROM df1 near OUTER articulation df2 connected df1.x = df2.x"),
               dplyr = left_join(df1, df2, by = c("x"="x")),
               DT = dt2[dt1, connected = "x"]) -> mb$near

# correct outer articulation
microbenchmark(occasions = 1L,
               basal = merge(df1, df2, by = "x", each.y = actual),
               sqldf = sqldf("choice * FROM df2 near OUTER articulation df1 connected df2.x = df1.x"),
               dplyr = right_join(df1, df2, by = "x"),
               DT = dt1[dt2, connected = "x"]) -> mb$correct

# afloat outer articulation
microbenchmark(occasions = 1L,
               basal = merge(df1, df2, by = "x", each = actual),
               dplyr = full_join(df1, df2, by = "x"),
               DT = merge(dt1, dt2, by = "x", each = actual)) -> mb$afloat

lapply(mb, mark) -> nul

fresh successful 2014:

particularly if you're besides curious successful information manipulation successful broad (together with sorting, filtering, subsetting, summarizing and so on.), you ought to decidedly return a expression astatine dplyr, which comes with a assortment of capabilities each designed to facilitate your activity particularly with information frames and definite another database varieties. It equal affords rather an elaborate SQL interface, and equal a relation to person (about) SQL codification straight into R.

The 4 becoming a member of-associated capabilities successful the dplyr bundle are (to punctuation):

• inner_join(x, y, by = NULL, transcript = mendacious, ...): instrument each rows from x wherever location are matching values successful y, and each columns from x and y
• left_join(x, y, by = NULL, transcript = mendacious, ...): instrument each rows from x, and each columns from x and y
• semi_join(x, y, by = NULL, transcript = mendacious, ...): instrument each rows from x wherever location are matching values successful y, maintaining conscionable columns from x.
• anti_join(x, y, by = NULL, transcript = mendacious, ...): instrument each rows from x wherever location are not matching values successful y, maintaining conscionable columns from x

It's each present successful large item.

choosing columns tin beryllium performed by choice(df,"file"). If that's not SQL-ish adequate for you, past location's the sql() relation, into which you tin participate SQL codification arsenic-is, and it volition bash the cognition you specified conscionable similar you have been penning successful R each on (for much accusation, delight mention to the dplyr/databases vignette). For illustration, if utilized appropriately, sql("choice * FROM hflights") volition choice each the columns from the "hflights" dplyr array (a "tbl").

dplyr since zero.four carried out each these joins together with outer_join, however it was worthy noting that for the archetypal fewer releases anterior to zero.four it utilized not to message outer_join, and arsenic a consequence location was a batch of truly atrocious hacky workaround person codification floating about for rather a piece afterwards (you tin inactive discovery specified codification successful truthful, Kaggle solutions, github from that play. therefore this reply inactive serves a utile intent.)

articulation-associated merchandise highlights:

v0.5 (6/2016)

• dealing with for POSIXct kind, timezones, duplicates, antithetic cause ranges. amended errors and warnings.
• fresh suffix statement to power what suffix duplicated adaptable names have (#1296)

v0.four.zero (1/2015)

• instrumentality correct articulation and outer articulation (#ninety six)
• Mutating joins, which adhd fresh variables to 1 array from matching rows successful different. Filtering joins, which filter observations from 1 array primarily based connected whether or not oregon not they lucifer an reflection successful the another array.

v0.three (10/2014)

• tin present left_join by antithetic variables successful all array: df1 %>% left_join(df2, c("var1" = "var2"))

v0.2 (5/2014)

• *_join() nary longer reorders file names (#324)

v0.1.three (four/2014)

• has inner_join, left_join, semi_join, anti_join
• outer_join not carried out but, fallback is usage basal::merge() (oregon plyr::articulation())
• didn't but instrumentality right_join and outer_join
• Hadley mentioning another advantages present
• 1 insignificant characteristic merge presently has that dplyr doesn't is the quality to person abstracted by.x,by.y columns arsenic e.g. Python pandas does.

Workarounds per hadley's feedback successful that content:

• right_join(x,y) is the aforesaid arsenic left_join(y,x) successful status of the rows, conscionable the columns volition beryllium antithetic orders. easy labored about with choice(new_column_order)
• outer_join is fundamentally federal(left_join(x, y), right_join(x, y)) - one.e. sphere each rows successful some information frames.

For the lawsuit of a near articulation with a zero..*:zero..1 cardinality oregon a correct articulation with a zero..1:zero..* cardinality it is imaginable to delegate successful-spot the unilateral columns from the joiner (the zero..1 array) straight onto the joinee (the zero..* array), and thereby debar the instauration of an wholly fresh array of information. This requires matching the cardinal columns from the joinee into the joiner and indexing+ordering the joiner's rows accordingly for the duty.

If the cardinal is a azygous file, past we tin usage a azygous telephone to lucifer() to bash the matching. This is the lawsuit one'll screen successful this reply.

present's an illustration based mostly connected the OP, but one've added an other line to df2 with an id of 7 to trial the lawsuit of a non-matching cardinal successful the joiner. This is efficaciously df1 near articulation df2:

df1 - information.framework(CustomerId=1:6,merchandise=c(rep('Toaster',3L),rep('energy',3L)));
df2 - information.framework(CustomerId=c(2L,4L,6L,7L),government=c(rep('Alabama',2L),'Ohio','Texas'));
df1[names(df2)[-1L]] - df2[lucifer(df1[,1L],df2[,1L]),-1L];
df1;
##   CustomerId merchandise   government
## 1          1 Toaster    
## 2          2 Toaster Alabama
## three          three Toaster    
## four          four   energy Alabama
## 5          5   energy    
## 6          6   energy    Ohio

successful the supra one difficult-coded an presumption that the cardinal file is the archetypal file of some enter tables. one would reason that, successful broad, this is not an unreasonable presumption, since, if you person a information.framework with a cardinal file, it would beryllium unusual if it had not been fit ahead arsenic the archetypal file of the information.framework from the outset. And you tin ever reorder the columns to brand it truthful. An advantageous effect of this presumption is that the sanction of the cardinal file does not person to beryllium difficult-coded, though one say it's conscionable changing 1 presumption with different. Concision is different vantage of integer indexing, arsenic fine arsenic velocity. successful the benchmarks beneath one'll alteration the implementation to usage drawstring sanction indexing to lucifer the competing implementations.

one deliberation this is a peculiarly due resolution if you person respective tables that you privation to near articulation in opposition to a azygous ample array. Repeatedly rebuilding the full array for all merge would beryllium pointless and inefficient.

connected the another manus, if you demand the joinee to stay unaltered done this cognition for any ground, past this resolution can not beryllium utilized, since it modifies the joinee straight. though successful that lawsuit you might merely brand a transcript and execute the successful-spot duty(s) connected the transcript.

---

arsenic a broadside line, one concisely seemed into imaginable matching options for multicolumn keys. unluckily, the lone matching options one recovered had been:

• inefficient concatenations. e.g. lucifer(action(df1$a,df1$b),action(df2$a,df2$b)), oregon the aforesaid thought with paste().
• inefficient cartesian conjunctions, e.g. outer(df1$a,df2$a,`==`) & outer(df1$b,df2$b,`==`).
• basal R merge() and equal bundle-primarily based merge features, which ever allocate a fresh array to instrument the merged consequence, and frankincense are not appropriate for an successful-spot duty-based mostly resolution.

For illustration, seat Matching aggregate columns connected antithetic information frames and getting another file arsenic consequence, lucifer 2 columns with 2 another columns, Matching connected aggregate columns, and the dupe of this motion wherever one primitively got here ahead with the successful-spot resolution, harvester 2 information frames with antithetic figure of rows successful R.

---

Benchmarking

one determined to bash my ain benchmarking to seat however the successful-spot duty attack compares to the another options that person been provided successful this motion.

investigating codification:

room(microbenchmark);
room(information.array);
room(sqldf);
room(plyr);
room(dplyr);

solSpecs - database(
    merge=database(testFuncs=database(
        interior=relation(df1,df2,cardinal) merge(df1,df2,cardinal),
        near =relation(df1,df2,cardinal) merge(df1,df2,cardinal,each.x=T),
        correct=relation(df1,df2,cardinal) merge(df1,df2,cardinal,each.y=T),
        afloat =relation(df1,df2,cardinal) merge(df1,df2,cardinal,each=T)
    )),
    information.array.unkeyed=database(argSpec='information.array.unkeyed',testFuncs=database(
        interior=relation(dt1,dt2,cardinal) dt1[dt2,connected=cardinal,nomatch=0L,let.cartesian=T],
        near =relation(dt1,dt2,cardinal) dt2[dt1,connected=cardinal,let.cartesian=T],
        correct=relation(dt1,dt2,cardinal) dt1[dt2,connected=cardinal,let.cartesian=T],
        afloat =relation(dt1,dt2,cardinal) merge(dt1,dt2,cardinal,each=T,let.cartesian=T) ## calls merge.information.array()
    )),
    information.array.keyed=database(argSpec='information.array.keyed',testFuncs=database(
        interior=relation(dt1,dt2) dt1[dt2,nomatch=0L,let.cartesian=T],
        near =relation(dt1,dt2) dt2[dt1,let.cartesian=T],
        correct=relation(dt1,dt2) dt1[dt2,let.cartesian=T],
        afloat =relation(dt1,dt2) merge(dt1,dt2,each=T,let.cartesian=T) ## calls merge.information.array()
    )),
    sqldf.unindexed=database(testFuncs=database( ## line: essential walk transportation=NULL to debar moving in opposition to the unrecorded DB transportation, which would consequence successful collisions with the residual tables from the past question add
        interior=relation(df1,df2,cardinal) sqldf(paste0('choice * from df1 interior articulation df2 utilizing(',paste(illness=',',cardinal),')'),transportation=NULL),
        near =relation(df1,df2,cardinal) sqldf(paste0('choice * from df1 near articulation df2 utilizing(',paste(illness=',',cardinal),')'),transportation=NULL),
        correct=relation(df1,df2,cardinal) sqldf(paste0('choice * from df2 near articulation df1 utilizing(',paste(illness=',',cardinal),')'),transportation=NULL) ## tin't bash correct articulation appropriate, not but supported; inverted near articulation is equal
        ##afloat =relation(df1,df2,cardinal) sqldf(paste0('choice * from df1 afloat articulation df2 utilizing(',paste(illness=',',cardinal),')'),transportation=NULL) ## tin't bash afloat articulation appropriate, not but supported; imaginable to hack it with a federal of near joins, however excessively unreasonable to see successful investigating
    )),
    sqldf.listed=database(testFuncs=database( ## crucial: requires an progressive DB transportation with preindexed chief.df1 and chief.df2 fit to spell; arguments are really ignored
        interior=relation(df1,df2,cardinal) sqldf(paste0('choice * from chief.df1 interior articulation chief.df2 utilizing(',paste(illness=',',cardinal),')')),
        near =relation(df1,df2,cardinal) sqldf(paste0('choice * from chief.df1 near articulation chief.df2 utilizing(',paste(illness=',',cardinal),')')),
        correct=relation(df1,df2,cardinal) sqldf(paste0('choice * from chief.df2 near articulation chief.df1 utilizing(',paste(illness=',',cardinal),')')) ## tin't bash correct articulation appropriate, not but supported; inverted near articulation is equal
        ##afloat =relation(df1,df2,cardinal) sqldf(paste0('choice * from chief.df1 afloat articulation chief.df2 utilizing(',paste(illness=',',cardinal),')')) ## tin't bash afloat articulation appropriate, not but supported; imaginable to hack it with a federal of near joins, however excessively unreasonable to see successful investigating
    )),
    plyr=database(testFuncs=database(
        interior=relation(df1,df2,cardinal) articulation(df1,df2,cardinal,'interior'),
        near =relation(df1,df2,cardinal) articulation(df1,df2,cardinal,'near'),
        correct=relation(df1,df2,cardinal) articulation(df1,df2,cardinal,'correct'),
        afloat =relation(df1,df2,cardinal) articulation(df1,df2,cardinal,'afloat')
    )),
    dplyr=database(testFuncs=database(
        interior=relation(df1,df2,cardinal) inner_join(df1,df2,cardinal),
        near =relation(df1,df2,cardinal) left_join(df1,df2,cardinal),
        correct=relation(df1,df2,cardinal) right_join(df1,df2,cardinal),
        afloat =relation(df1,df2,cardinal) full_join(df1,df2,cardinal)
    )),
    successful.spot=database(testFuncs=database(
        near =relation(df1,df2,cardinal)  cns - setdiff(names(df2),cardinal); df1[cns] - df2[lucifer(df1[,cardinal],df2[,cardinal]),cns]; df1; ,
        correct=relation(df1,df2,cardinal)  cns - setdiff(names(df1),cardinal); df2[cns] - df1[lucifer(df2[,cardinal],df1[,cardinal]),cns]; df2; 
    ))
);

getSolTypes - relation() names(solSpecs);
getJoinTypes - relation() alone(unlist(lapply(solSpecs,relation(x) names(x$testFuncs))));
getArgSpec - relation(argSpecs,cardinal=NULL) if (is.null(cardinal)) argSpecs$default other argSpecs[[cardinal]];

initSqldf - relation() 
    sqldf(); ## creates sqlite transportation connected archetypal tally, cleans ahead and closes present transportation other
    if (exists('sqldfInitFlag',envir=globalenv(),inherits=F) && sqldfInitFlag)  ## mendacious lone connected archetypal tally
        sqldf(); ## creates a fresh transportation
     other 
        delegate('sqldfInitFlag',T,envir=globalenv()); ## fit to actual for the 1 and lone clip
    ; ## extremity if
    invisible();
; ## extremity initSqldf()

setUpBenchmarkCall - relation(argSpecs,joinType,solTypes=getSolTypes(),env=genitor.framework()) 
    ## builds and returns a database of expressions appropriate for passing to the database statement of microbenchmark(), and assigns variables to resoluteness signal references successful these expressions
    callExpressions - database();
    nms - quality();
    for (solType successful solTypes) 
        testFunc - solSpecs[[solType]]$testFuncs[[joinType]];
        if (is.null(testFunc)) adjacent; ## this articulation kind is not outlined for this resolution kind
        testFuncName - paste0('tf.',solType);
        delegate(testFuncName,testFunc,envir=env);
        argSpecKey - solSpecs[[solType]]$argSpec;
        argSpec - getArgSpec(argSpecs,argSpecKey);
        argList - setNames(nm=names(argSpec$args),vector('database',dimension(argSpec$args)));
        for (one successful seq_along(argSpec$args)) 
            argName - paste0('tfa.',argSpecKey,one);
            delegate(argName,argSpec$args[[one]],envir=env);
            argList[[one]] - if (one%successful%argSpec$copySpec) telephone('transcript',arsenic.signal(argName)) other arsenic.signal(argName);
        ; ## extremity for
        callExpressions[[dimension(callExpressions)+1L]] - bash.telephone(telephone,c(database(testFuncName),argList),punctuation=T);
        nms[dimension(nms)+1L] - solType;
    ; ## extremity for
    names(callExpressions) - nms;
    callExpressions;
; ## extremity setUpBenchmarkCall()

harmonize - relation(res) 
    res - arsenic.information.framework(res); ## coerce to information.framework
    for (ci successful which(sapply(res,is.cause))) res[[ci]] - arsenic.quality(res[[ci]]); ## coerce cause columns to quality
    for (ci successful which(sapply(res,is.logical))) res[[ci]] - arsenic.integer(res[[ci]]); ## coerce logical columns to integer (plant about sqldf quirk of munging logicals to integers)
    ##for (ci successful which(sapply(res,inherits,'POSIXct'))) res[[ci]] - arsenic.treble(res[[ci]]); ## coerce POSIXct columns to treble (plant about sqldf quirk of dropping POSIXct people) ----- POSIXct doesn't activity astatine each successful sqldf.listed
    res - res[command(names(res))]; ## command columns
    res - res[bash.telephone(command,res),]; ## command rows
    res;
; ## extremity harmonize()

checkIdentical - relation(argSpecs,solTypes=getSolTypes()) 
    for (joinType successful getJoinTypes()) 
        callExpressions - setUpBenchmarkCall(argSpecs,joinType,solTypes);
        if (dimension(callExpressions)2L) adjacent;
        ex - harmonize(eval(callExpressions[[1L]]));
        for (one successful seq(2L,len=dimension(callExpressions)-1L)) 
            y - harmonize(eval(callExpressions[[one]]));
            if (!isTRUE(each.close(ex,y,cheque.attributes=F))) 
                ex - ex;
                y - y;
                solType - names(callExpressions)[one];
                halt(paste0('non-equivalent: ',solType,' ',joinType,'.'));
            ; ## extremity if
        ; ## extremity for
    ; ## extremity for
    invisible();
; ## extremity checkIdentical()

testJoinType - relation(argSpecs,joinType,solTypes=getSolTypes(),metric=NULL,occasions=100L) 
    callExpressions - setUpBenchmarkCall(argSpecs,joinType,solTypes);
    bm - microbenchmark(database=callExpressions,occasions=occasions);
    if (is.null(metric)) instrument(bm);
    bm - abstract(bm);
    res - setNames(nm=names(callExpressions),bm[[metric]]);
    attr(res,'part') - attr(bm,'part');
    res;
; ## extremity testJoinType()

testAllJoinTypes - relation(argSpecs,solTypes=getSolTypes(),metric=NULL,instances=100L) 
    joinTypes - getJoinTypes();
    resList - setNames(nm=joinTypes,lapply(joinTypes,relation(joinType) testJoinType(argSpecs,joinType,solTypes,metric,instances)));
    if (is.null(metric)) instrument(resList);
    items - unname(unlist(lapply(resList,attr,'part')));
    res - bash.telephone(information.framework,c(database(articulation=joinTypes),setNames(nm=solTypes,rep(database(rep(NA_real_,dimension(joinTypes))),dimension(solTypes))),database(part=models,stringsAsFactors=F)));
    for (one successful seq_along(resList)) res[one,lucifer(names(resList[[one]]),names(res))] - resList[[one]];
    res;
; ## extremity testAllJoinTypes()

testGrid - relation(makeArgSpecsFunc,sizes,overlaps,solTypes=getSolTypes(),joinTypes=getJoinTypes(),metric='median',instances=100L) 

    res - grow.grid(dimension=sizes,overlap=overlaps,joinType=joinTypes,stringsAsFactors=F);
    res[solTypes] - NA_real_;
    res$part - NA_character_;
    for (ri successful seq_len(nrow(res))) 

        dimension - res$dimension[ri];
        overlap - res$overlap[ri];
        joinType - res$joinType[ri];

        argSpecs - makeArgSpecsFunc(dimension,overlap);

        checkIdentical(argSpecs,solTypes);

        cur - testJoinType(argSpecs,joinType,solTypes,metric,instances);
        res[ri,lucifer(names(cur),names(res))] - cur;
        res$part[ri] - attr(cur,'part');

    ; ## extremity for

    res;

; ## extremity testGrid()

---

present's a benchmark of the illustration primarily based connected the OP that one demonstrated earlier:

## OP's illustration, supplemented with a non-matching line successful df2
argSpecs - database(
    default=database(copySpec=1:2,args=database(
        df1 - information.framework(CustomerId=1:6,merchandise=c(rep('Toaster',3L),rep('energy',3L))),
        df2 - information.framework(CustomerId=c(2L,4L,6L,7L),government=c(rep('Alabama',2L),'Ohio','Texas')),
        'CustomerId'
    )),
    information.array.unkeyed=database(copySpec=1:2,args=database(
        arsenic.information.array(df1),
        arsenic.information.array(df2),
        'CustomerId'
    )),
    information.array.keyed=database(copySpec=1:2,args=database(
        setkey(arsenic.information.array(df1),CustomerId),
        setkey(arsenic.information.array(df2),CustomerId)
    ))
);
## fix sqldf
initSqldf();
sqldf('make scale df1_key connected df1(CustomerId);'); ## add and make an sqlite scale connected df1
sqldf('make scale df2_key connected df2(CustomerId);'); ## add and make an sqlite scale connected df2

checkIdentical(argSpecs);

testAllJoinTypes(argSpecs,metric='median');
##    articulation    merge information.array.unkeyed information.array.keyed sqldf.unindexed sqldf.listed      plyr    dplyr successful.spot         part
## 1 interior  644.259           861.9345          923.516        9157.752      1580.390  959.2250 270.9190       NA microseconds
## 2  near  713.539           888.0205          910.045        8820.334      1529.714  968.4195 270.9185 224.3045 microseconds
## three correct 1221.804           909.1900          923.944        8930.668      1533.one hundred thirty five 1063.7860 269.8495 218.1035 microseconds
## four  afloat 1302.203          3107.5380         3184.729              NA            NA 1593.6475 270.7055       NA microseconds

---

present one benchmark connected random enter information, attempting antithetic scales and antithetic patterns of cardinal overlap betwixt the 2 enter tables. This benchmark is inactive restricted to the lawsuit of a azygous-file integer cardinal. arsenic fine, to guarantee that the successful-spot resolution would activity for some near and correct joins of the aforesaid tables, each random trial information makes use of zero..1:zero..1 cardinality. This is applied by sampling with out substitute the cardinal file of the archetypal information.framework once producing the cardinal file of the 2nd information.framework.

makeArgSpecs.singleIntegerKey.optionalOneToOne - relation(dimension,overlap) 

    com - arsenic.integer(measurement*overlap);

    argSpecs - database(
        default=database(copySpec=1:2,args=database(
            df1 - information.framework(id=example(measurement),y1=rnorm(measurement),y2=rnorm(dimension)),
            df2 - information.framework(id=example(c(if (com>0L) example(df1$id,com) other integer(),seq(dimension+1L,len=dimension-com))),y3=rnorm(dimension),y4=rnorm(measurement)),
            'id'
        )),
        information.array.unkeyed=database(copySpec=1:2,args=database(
            arsenic.information.array(df1),
            arsenic.information.array(df2),
            'id'
        )),
        information.array.keyed=database(copySpec=1:2,args=database(
            setkey(arsenic.information.array(df1),id),
            setkey(arsenic.information.array(df2),id)
        ))
    );
    ## fix sqldf
    initSqldf();
    sqldf('make scale df1_key connected df1(id);'); ## add and make an sqlite scale connected df1
    sqldf('make scale df2_key connected df2(id);'); ## add and make an sqlite scale connected df2

    argSpecs;

; ## extremity makeArgSpecs.singleIntegerKey.optionalOneToOne()

## transverse of assorted enter sizes and cardinal overlaps
sizes - c(1e1L,1e3L,1e6L);
overlaps - c(zero.ninety nine,zero.5,zero.01);
scheme.clip( res - testGrid(makeArgSpecs.singleIntegerKey.optionalOneToOne,sizes,overlaps); );
##     person   scheme  elapsed
## 22024.sixty five 12308.sixty three 34493.19

one wrote any codification to make log-log plots of the supra outcomes. one generated a abstracted game for all overlap percent. It's a small spot cluttered, however one similar having each the resolution varieties and articulation sorts represented successful the aforesaid game.

one utilized spline interpolation to entertainment a creaseless curve for all resolution/articulation kind operation, drawn with idiosyncratic pch symbols. The articulation kind is captured by the pch signal, utilizing a dot for interior, near and correct space brackets for near and correct, and a diamond for afloat. The resolution kind is captured by the colour arsenic proven successful the fable.

plotRes - relation(res,titleFunc,useFloor=F) 
    solTypes - setdiff(names(res),c('dimension','overlap','joinType','part')); ## deduce from res
    normMult - c(microseconds=1e-three,milliseconds=1); ## normalize to milliseconds
    joinTypes - getJoinTypes();
    cols - c(merge='purple',information.array.unkeyed='bluish',information.array.keyed='#00DDDD',sqldf.unindexed='brownish',sqldf.listed='orangish',plyr='reddish',dplyr='#00BB00',successful.spot='magenta');
    pchs - database(interior=20L,near='',correct='>',afloat=23L);
    cexs - c(interior=zero.7,near=1,correct=1,afloat=zero.7);
    NP - 60L;
    ord - command(reducing=T,colMeans(res[res$measurement==max(res$dimension),solTypes],na.rm=T));
    ymajors - information.framework(y=c(1,1e3),description=c('1ms','1s'),stringsAsFactors=F);
    for (overlap successful alone(res$overlap)) 
        x1 - res[res$overlap==overlap,];
        x1[solTypes] - x1[solTypes]*normMult[x1$part]; x1$part - NULL;
        xlim - c(1e1,max(x1$dimension));
        xticks - 10^seq(log10(xlim[1L]),log10(xlim[2L]));
        ylim - c(1e-1,10^((if (useFloor) level other ceiling)(log10(max(x1[solTypes],na.rm=T))))); ## usage level() to zoom successful a small much, lone sqldf.unindexed volition interruption supra, however xpd=NA volition support it available
        yticks - 10^seq(log10(ylim[1L]),log10(ylim[2L]));
        yticks.insignificant - rep(yticks[-dimension(yticks)],all=9L)*1:9;
        game(NA,xlim=xlim,ylim=ylim,xaxs='one',yaxs='one',axes=F,xlab='measurement (rows)',ylab='clip (sclerosis)',log='xy');
        abline(v=xticks,col='lightgrey');
        abline(h=yticks.insignificant,col='lightgrey',lty=3L);
        abline(h=yticks,col='lightgrey');
        axis(1L,xticks,parse(matter=sprintf('10^%d',arsenic.integer(log10(xticks)))));
        axis(2L,yticks,parse(matter=sprintf('10^%d',arsenic.integer(log10(yticks)))),las=1L);
        axis(4L,ymajors$y,ymajors$description,las=1L,tick=F,cex.axis=zero.7,hadj=zero.5);
        for (joinType successful rev(joinTypes))  ## reverse to gully afloat archetypal, since it's bigger and would beryllium much obtrusive if drawn past
            x2 - x1[x1$joinType==joinType,];
            for (solType successful solTypes) 
                if (immoderate(!is.na(x2[[solType]]))) 
                    xy - spline(x2$measurement,x2[[solType]],xout=10^(seq(log10(x2$dimension[1L]),log10(x2$dimension[nrow(x2)]),len=NP)));
                    factors(xy$x,xy$y,pch=pchs[[joinType]],col=cols[solType],cex=cexs[joinType],xpd=NA);
                ; ## extremity if
            ; ## extremity for
        ; ## extremity for
        ## customized fable
        ## owed to logarithmic skew, essential bash each region calcs successful inches, and person to person coords afterward
        ## the bottommost-near area of the fable volition beryllium outlined successful normalized fig coords, though we tin person to inches instantly
        limb.cex - zero.7;
        limb.x.successful - grconvertX(zero.275,'nfc','successful');
        limb.y.successful - grconvertY(zero.6,'nfc','successful');
        limb.x.person - grconvertX(limb.x.successful,'successful');
        limb.y.person - grconvertY(limb.y.successful,'successful');
        limb.outpad.w.successful - zero.1;
        limb.outpad.h.successful - zero.1;
        limb.midpad.w.successful - zero.1;
        limb.midpad.h.successful - zero.1;
        limb.sol.w.successful - max(strwidth(solTypes,'successful',limb.cex));
        limb.sol.h.successful - max(strheight(solTypes,'successful',limb.cex))*1.5; ## multiplication cause for larger formation tallness
        limb.articulation.w.successful - max(strheight(joinTypes,'successful',limb.cex))*1.5; ## ditto
        limb.articulation.h.successful - max(strwidth(joinTypes,'successful',limb.cex));
        limb.chief.w.successful - limb.articulation.w.successful*dimension(joinTypes);
        limb.chief.h.successful - limb.sol.h.successful*dimension(solTypes);
        limb.x2.person - grconvertX(limb.x.successful+limb.outpad.w.successful*2+limb.chief.w.successful+limb.midpad.w.successful+limb.sol.w.successful,'successful');
        limb.y2.person - grconvertY(limb.y.successful+limb.outpad.h.successful*2+limb.chief.h.successful+limb.midpad.h.successful+limb.articulation.h.successful,'successful');
        limb.cols.x.person - grconvertX(limb.x.successful+limb.outpad.w.successful+limb.articulation.w.successful*(zero.5+seq(0L,dimension(joinTypes)-1L)),'successful');
        limb.strains.y.person - grconvertY(limb.y.successful+limb.outpad.h.successful+limb.chief.h.successful-limb.sol.h.successful*(zero.5+seq(0L,dimension(solTypes)-1L)),'successful');
        limb.sol.x.person - grconvertX(limb.x.successful+limb.outpad.w.successful+limb.chief.w.successful+limb.midpad.w.successful,'successful');
        limb.articulation.y.person - grconvertY(limb.y.successful+limb.outpad.h.successful+limb.chief.h.successful+limb.midpad.h.successful,'successful');
        rect(limb.x.person,limb.y.person,limb.x2.person,limb.y2.person,col='achromatic');
        matter(limb.sol.x.person,limb.traces.y.person,solTypes[ord],cex=limb.cex,pos=4L,offset=zero);
        matter(limb.cols.x.person,limb.articulation.y.person,joinTypes,cex=limb.cex,pos=4L,offset=zero,srt=ninety); ## srt rotation applies *last* pos/offset positioning
        for (one successful seq_along(joinTypes)) 
            joinType - joinTypes[one];
            factors(rep(limb.cols.x.person[one],dimension(solTypes)),ifelse(colSums(!is.na(x1[x1$joinType==joinType,solTypes[ord]]))==0L,NA,limb.traces.y.person),pch=pchs[[joinType]],col=cols[solTypes[ord]]);
        ; ## extremity for
        rubric(titleFunc(overlap));
        readline(sprintf('overlap %.02f',overlap));
    ; ## extremity for
; ## extremity plotRes()

titleFunc - relation(overlap) sprintf('R merge options: azygous-file integer cardinal, zero..1:zero..1 cardinality, %d%% overlap',arsenic.integer(overlap*a hundred));
plotRes(res,titleFunc,T);

---

present's a 2nd ample-standard benchmark that's much dense-work, with regard to the figure and varieties of cardinal columns, arsenic fine arsenic cardinality. For this benchmark one usage 3 cardinal columns: 1 quality, 1 integer, and 1 logical, with nary restrictions connected cardinality (that is, zero..*:zero..*). (successful broad it's not advisable to specify cardinal columns with treble oregon analyzable values owed to floating-component examination issues, and fundamentally nary 1 always makes use of the natural kind, overmuch little for cardinal columns, truthful one haven't included these varieties successful the cardinal columns. besides, for accusation's interest, one initially tried to usage 4 cardinal columns by together with a POSIXct cardinal file, however the POSIXct kind didn't drama fine with the sqldf.listed resolution for any ground, perchance owed to floating-component examination anomalies, truthful one eliminated it.)

makeArgSpecs.assortedKey.optionalManyToMany - relation(dimension,overlap,uniquePct=seventy five) 

    ## figure of alone keys successful df1
    u1Size - arsenic.integer(dimension*uniquePct/a hundred);

    ## (approximately) disagreement u1Size into bases, truthful we tin usage grow.grid() to food the required figure of alone cardinal values with repetitions inside idiosyncratic cardinal columns
    ## usage ceiling() to guarantee we screen u1Size; volition truncate afterward
    u1SizePerKeyColumn - arsenic.integer(ceiling(u1Size^(1/three)));

    ## make the alone cardinal values for df1
    keys1 - grow.grid(stringsAsFactors=F,
        idCharacter=replicate(u1SizePerKeyColumn,paste(illness='',example(letters,example(four:12,1L),T))),
        idInteger=example(u1SizePerKeyColumn),
        idLogical=example(c(F,T),u1SizePerKeyColumn,T)
        ##idPOSIXct=arsenic.POSIXct('2016-01-01 00:00:00','UTC')+example(u1SizePerKeyColumn)
    )[seq_len(u1Size),];

    ## rbind any repetitions of the alone keys; this volition fix 1 broadside of the galore-to-galore relation
    ## besides scramble the command afterward
    keys1 - rbind(keys1,keys1[example(nrow(keys1),measurement-u1Size,T),])[example(dimension),];

    ## communal and unilateral cardinal counts
    com - arsenic.integer(measurement*overlap);
    uni - measurement-com;

    ## make any unilateral keys for df2 by synthesizing extracurricular of the idInteger scope of df1
    keys2 - information.framework(stringsAsFactors=F,
        idCharacter=replicate(uni,paste(illness='',example(letters,example(four:12,1L),T))),
        idInteger=u1SizePerKeyColumn+example(uni),
        idLogical=example(c(F,T),uni,T)
        ##idPOSIXct=arsenic.POSIXct('2016-01-01 00:00:00','UTC')+u1SizePerKeyColumn+example(uni)
    );

    ## rbind random keys from df1; this volition absolute the galore-to-galore relation
    ## besides scramble the command afterward
    keys2 - rbind(keys2,keys1[example(nrow(keys1),com,T),])[example(measurement),];

    ##keyNames - c('idCharacter','idInteger','idLogical','idPOSIXct');
    keyNames - c('idCharacter','idInteger','idLogical');
    ## line: was going to usage natural and analyzable kind for 2 of the non-cardinal columns, however information.array doesn't look to full activity them
    argSpecs - database(
        default=database(copySpec=1:2,args=database(
            df1 - cbind(stringsAsFactors=F,keys1,y1=example(c(F,T),dimension,T),y2=example(dimension),y3=rnorm(measurement),y4=replicate(dimension,paste(illness='',example(letters,example(four:12,1L),T)))),
            df2 - cbind(stringsAsFactors=F,keys2,y5=example(c(F,T),dimension,T),y6=example(dimension),y7=rnorm(measurement),y8=replicate(measurement,paste(illness='',example(letters,example(four:12,1L),T)))),
            keyNames
        )),
        information.array.unkeyed=database(copySpec=1:2,args=database(
            arsenic.information.array(df1),
            arsenic.information.array(df2),
            keyNames
        )),
        information.array.keyed=database(copySpec=1:2,args=database(
            setkeyv(arsenic.information.array(df1),keyNames),
            setkeyv(arsenic.information.array(df2),keyNames)
        ))
    );
    ## fix sqldf
    initSqldf();
    sqldf(paste0('make scale df1_key connected df1(',paste(illness=',',keyNames),');')); ## add and make an sqlite scale connected df1
    sqldf(paste0('make scale df2_key connected df2(',paste(illness=',',keyNames),');')); ## add and make an sqlite scale connected df2

    argSpecs;

; ## extremity makeArgSpecs.assortedKey.optionalManyToMany()

sizes - c(1e1L,1e3L,1e5L); ## 1e5L alternatively of 1e6L to regard much dense-work inputs
overlaps - c(zero.ninety nine,zero.5,zero.01);
solTypes - setdiff(getSolTypes(),'successful.spot');
scheme.clip( res - testGrid(makeArgSpecs.assortedKey.optionalManyToMany,sizes,overlaps,solTypes); );
##     person   scheme  elapsed
## 38895.50   784.19 39745.fifty three

The ensuing plots, utilizing the aforesaid plotting codification fixed supra:

titleFunc - relation(overlap) sprintf('R merge options: quality/integer/logical cardinal, zero..*:zero..* cardinality, %d%% overlap',arsenic.integer(overlap*one hundred));
plotRes(res,titleFunc,F);

successful becoming a member of 2 information frames with ~1 cardinal rows all, 1 with 2 columns and the another with ~20, one've amazingly recovered merge(..., each.x = actual, each.y = actual) to beryllium quicker past dplyr::full_join(). This is with dplyr v0.four

Merge takes ~17 seconds, full_join takes ~sixty five seconds.

any nutrient for although, since one mostly default to dplyr for manipulation duties.

1. utilizing merge relation we tin choice the adaptable of near array oregon correct array, aforesaid manner similar we each acquainted with choice message successful SQL (EX : choice a.* ...oregon choice b.* from .....)

2. We person to adhd other codification which volition subset from the recently joined array .

   • SQL :- choice a.* from df1 a interior articulation df2 b connected a.CustomerId=b.CustomerId

   • R :- merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df1)]

aforesaid manner

• SQL :- choice b.* from df1 a interior articulation df2 b connected a.CustomerId=b.CustomerId

• R :- merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df2)]

For an interior articulation connected each columns, you may besides usage fintersect from the information.array-bundle oregon intersect from the dplyr-bundle arsenic an alternate to merge with out specifying the by-columns. This volition springiness the rows that are close betwixt 2 dataframes:

merge(df1, df2)
#   V1 V2
# 1  B  2
# 2  C  three

dplyr::intersect(df1, df2)
#   V1 V2
# 1  B  2
# 2  C  three

information.array::fintersect(setDT(df1), setDT(df2))
#    V1 V2
# 1:  B  2
# 2:  C  three

---

illustration information:

df1 - information.framework(V1 = LETTERS[1:four], V2 = 1:four)
df2 - information.framework(V1 = LETTERS[2:three], V2 = 2:three)

replace articulation. 1 another crucial SQL-kind articulation is an "replace articulation" wherever columns successful 1 array are up to date (oregon created) utilizing different array.

Modifying the OP's illustration tables...

income = information.framework(
  CustomerId = c(1, 1, 1, three, four, 6), 
  twelvemonth = 2000:2005,
  merchandise = c(rep("Toaster", three), rep("energy", three))
)
cust = information.framework(
  CustomerId = c(1, 1, four, 6), 
  twelvemonth = c(2001L, 2002L, 2002L, 2002L),
  government = government.sanction[1:four]
)

income
# CustomerId twelvemonth merchandise
#          1 2000 Toaster
#          1 2001 Toaster
#          1 2002 Toaster
#          three 2003   energy
#          four 2004   energy
#          6 2005   energy

cust
# CustomerId twelvemonth    government
#          1 2001  Alabama
#          1 2002   Alaska
#          four 2002  Arizona
#          6 2002 Arkansas

say we privation to adhd the buyer's government from cust to the purchases array, income, ignoring the twelvemonth file. With basal R, we tin place matching rows and past transcript values complete:

income$government - cust$government[ lucifer(income$CustomerId, cust$CustomerId) ]

# CustomerId twelvemonth merchandise    government
#          1 2000 Toaster  Alabama
#          1 2001 Toaster  Alabama
#          1 2002 Toaster  Alabama
#          three 2003   energy     
#          four 2004   energy  Arizona
#          6 2005   energy Arkansas

# cleanup for the adjacent illustration
income$government - NULL

arsenic tin beryllium seen present, lucifer selects the archetypal matching line from the buyer array.

---

replace articulation with aggregate columns. The attack supra plant fine once we are becoming a member of connected lone a azygous file and are glad with the archetypal lucifer. say we privation the twelvemonth of measure successful the buyer array to lucifer the twelvemonth of merchantability.

arsenic @bgoldst's reply mentions, lucifer with action mightiness beryllium an action for this lawsuit. much straightforwardly, 1 might usage information.array:

room(information.array)
setDT(income); setDT(cust)

income[, government := cust[income, connected=.(CustomerId, twelvemonth), x.government]]

#    CustomerId twelvemonth merchandise   government
# 1:          1 2000 Toaster    
# 2:          1 2001 Toaster Alabama
# three:          1 2002 Toaster  Alaska
# four:          three 2003   energy    
# 5:          four 2004   energy    
# 6:          6 2005   energy    

# cleanup for adjacent illustration
income[, government := NULL]

---

Rolling replace articulation. Alternately, we whitethorn privation to return the past government the buyer was recovered successful:

income[, government := cust[income, connected=.(CustomerId, twelvemonth), rotation=actual, x.government]]

#    CustomerId twelvemonth merchandise    government
# 1:          1 2000 Toaster     
# 2:          1 2001 Toaster  Alabama
# three:          1 2002 Toaster   Alaska
# four:          three 2003   energy     
# 5:          four 2004   energy  Arizona
# 6:          6 2005   energy Arkansas

---

The 3 examples supra each direction connected creating/including a fresh file. seat the associated R FAQ for an illustration of updating/modifying an current file.

illness supplies different articulation model with articulation (disposable successful the dev. interpretation of the bundle). It is noticeably quicker than immoderate another action.

remotes::install_github("SebKrantz/illness")
room(illness)

articulation(
  df1, 
  df2, 
  however = c("near", "correct", "interior", "afloat", "semi", "anti")
)