r - Look up from different dataframes depending on a column -

supposing have following dataframes:

d1 <- data.frame(index = c(1,2,3,4), location = c('barn', 'house', 'restaurant', 'tomb'), random = c(5,3,2,1), different_col1 = c(66,33,22,11)) d2 <- data.frame(index = c(1,2,3,4), location = c('server', 'computer', 'home', 'dictionary'), random = c(1,7,2,9), differen_col2 = c('hi', 'there', 'different', 'column')) 

what trying location based on index , dataframe is. have following:

data <- data.frame(src = c('one', 'one', 'two', 'one', 'two'), index = c(1,4,2,3,2))

where src indicates dataframe data should come , index, value in index index column.

src  |  index ------------- 1  |    1 one  |    4 2  |    2 1  |    3 2  |    2 

and become:

src  | index | location ----------------------- 1  |   1   | barn 1  |   4   | tomb 2  |   2   | computer 1  |   3   | restaurant 2  |   2   | computer 

due size of data avoid merge or comparable joins (sqldf, etc).

here's 1 way add new column by reference using data.table:

require(data.table) setdt(d1); setdt(d2); setdt(data) # convert data.frames data.tables  data[src == "one", location := d1[.sd, location, on="index"]] data[src == "two", location := d2[.sd, location, on="index"]] 

.sd stands subset of data, , contains columns in data matches condition provided in i-argument.

see vignettes more.

you can use match in expression right of := instead of extracting location using join. it'd not extensible if you'd want match on multiple columns.