dataframe - R merge or lookup cell value based on two columns -

i have 2 tables, , want transfer information 1 of them other.

there names , subnames. each name + subname combination has value associated it, calculated independently in first step.

in next step, each name assigned 2 special subnames (sub1 , sub2), , need retrieve value associated each name + subname pair.

in other words, each name need retrieve value name+sub1 , value name+sub2.

in first table, each row has name, , value every possible sub-name. here subnames called x, y, w, , z. values numbers.

name    x    y    w    z abc     4    8    3    1 def     2    5    1    7 

in second table, each row has name in first column , 2 relevant sub-names in next 2 columns. both tables have full set of names in first column, not every subname in second table exists in first table. these rows, fine return 'na.'

name    sub1        sub2 abc     x           y def     w           z 

i want combine these tables this:

name    sub1        sub2    sub1.value    sub2.value abc     x           y       4             8 def     w           z       1             7 

these copy/pasteable sample tables (the first table has ~1k rows , ~30k columns).

first.table=data.frame(name=c('abc','def'),x=c(4,2),y=c(8,5),w=c(3,1),z=c(1,7)) second.table=data.frame(name=c('abc','def'),sub1=c('x','w'),sub2=c('y','z')) 

et voilà:

library(reshape2) # melt long format ldf <- melt(first.table,id.vars='name')  # merge on name , sub's second.table <- merge(second.table,ldf,by.x=c('name','sub1'),by.y=c('name','variable')) second.table <- merge(second.table,ldf,by.x=c('name','sub2'),by.y=c('name','variable'))  # rename columns correct values colnames(second.table)[4:5] <- c('sub1.value','sub2.value')