sql - Inserting values of a column from one dataframe to another while respecting a given condition -

i have 2 data frames d1 , d2. d2 has column contains data prefer added d1.

each of data frames have equal number of rows , columns.

> d1      t1  t2 numvehicles avgbyrunrep 1   0.2 0.3          10    225.5000 2   0.2 0.4          10    219.6667 3   0.2 0.5          10    205.1667 4   0.2 0.6          10    220.6667 5   0.2 0.7          10    205.1667  > d2      t1  t2 numvehicles avglostperrep 1   0.2 0.3          10     14.333333 2   0.2 0.4          10      9.000000 3   0.2 0.5          10      8.000000 4   0.2 0.6          10      8.000000 5   0.2 0.7          10      6.833333 

so values in d2's avglostperrep column "transferred" d1 matching t1, t2, numvehicles.

so in end d1 like:

> d1      t1  t2 numvehicles avgbyrunrep  avglostperrep 1   0.2 0.3          10    225.5000  14.333333 2   0.2 0.4          10    219.6667  9.000000 3   0.2 0.5          10    205.1667  8.000000 4   0.2 0.6          10    220.6667  8.000000 5   0.2 0.7          10    205.1667  6.833333 

it possible save final resulting data frame in variable d3, if makes difference @ all.

i know how can solved sqldf pure r ok well.

i tried merge r, got big data-frame lot of na. tried update , insert into sqldf no avail.

1) performs left join along indicated columns:

library(sqldf) sqldf("select * d1 left join d2 using(t1, t2, numvehicles)") 

we alternately use left natural join causes join occur along commonly named columns:

sqldf("select * d1 left natural join d2") 

for data shown in question alternately use inner join omitting word left in either of above; however, if actual data not have value in d2 every row of d1 inner join omit rows of d1 whereas left join include them , add na joined d2 column.

2) corresponding native r code first sqldf statement

merge(d1, d2, all.x = true, = 1:3) 

and second:

merge(d1, d2, all.x = true) 

inner joins obtained omitting all.x = true in either case.