r - Data.table: join a "long format" multi-time point table with a single time point table -

suppose have following 2 data.tables:

mult.year <- data.table(id=c(1,1,1,2,2,2,3,3,3),                          time=rep(1:3, 3),                         a=rnorm(9),                         b=rnorm(9)) setkey(mult.year, id) single <- data.table(id=c(1,2,3),                       c.3=rnorm(3)) setkey(single, id) 

i want join 2 tables, variable c.3 appears mult.year[time == 3]

i can psuedo assigning new column:

mult.year[time == 3, c := single[,c.3]] 

but lose join functionality: requires ids in both datasets. there way while maintaining join functionality?

using above tables, i'm trying this:

   id time                   b        c.3 1:  1    1 -1.0460085  0.0896452         na 2:  1    2  0.2054772  1.5631978         na 3:  1    3 -1.7574449  0.5661457  0.6495645 4:  2    1  0.4171095 -0.2182779         na 5:  2    2 -0.9238671  0.8263605         na 6:  2    3 -0.5452715 -0.5842541 -1.5233764 7:  3    1  0.1793009  1.4399366         na 8:  3    2  0.3438980  1.7419869         na 9:  3    3  0.1067989  0.7630496  1.9658157 

if you're willing include time in data.table's key, this:

## add time ... setkeyv(mult.year, c("id", "time"))                     ## ... mult.year's key single <- data.table(id=c(1,2,3), time=3, c.3=rnorm(3)) ## ... , indexing dt  ## set simple call [.data.table mult.year[single, c.3:=c.3] mult.year #    id time                    b         c.3 # 1:  1    1 -0.6264538 -0.30538839          na # 2:  1    2  0.1836433  1.51178117          na # 3:  1    3 -0.8356286  0.38984324  0.61982575 # 4:  2    1  1.5952808 -0.62124058          na # 5:  2    2  0.3295078 -2.21469989          na # 6:  2    3 -0.8204684  1.12493092 -0.05612874 # 7:  3    1  0.4874291 -0.04493361          na # 8:  3    2  0.7383247 -0.01619026          na # 9:  3    3  0.5757814  0.94383621 -0.15579551 

alternatively, leave both single , current key intact, use approach suggested in mnel's comment above:

mult.year[single, c.3 := ifelse(time==3,c.3,na)] mult.year #    id time                    b       c.3 # 1:  1    1 -0.6264538 -0.30538839        na # 2:  1    2  0.1836433  1.51178117        na # 3:  1    3 -0.8356286  0.38984324 0.8212212 # 4:  2    1  1.5952808 -0.62124058        na # 5:  2    2  0.3295078 -2.21469989        na # 6:  2    3 -0.8204684  1.12493092 0.5939013 # 7:  3    1  0.4874291 -0.04493361        na # 8:  3    2  0.7383247 -0.01619026        na # 9:  3    3  0.5757814  0.94383621 0.9189774