merge - How to intersect values from two data frames with R -

i create new column data frame values intersection of row , column.

i have data.frame called "time":

q   1    2   3   4    5   1    13  43  5    3 b   2    21  12  3353 34 c   3    21  312 123  343 d   4    123 213 123  35 e   4556 11  123 12   3 

and table, called "event":

q   dt   1 b   3 c   4 d   2 e   1 

i want put column called inter on second table fill values in intersection between q , columns dt first data.frame. result this:

q   dt  inter   1   1 b   3   12 c   4   123 d   2   123 e   1   4556 

i have tried use merge(event, time, by.x = "q", by.y = "dt"), generate error aren't same id. have tried transpose time data.frame cross section values didn't have success.

library(reshape2) merge(event, melt(time, id.vars = "q"),        by.x=c('q','dt'), by.y=c('q','variable'), all.x = true) 

output:

  q dt value 1  1     1 2 b  3    12 3 c  4   123 4 d  2   123 5 e  1  4556 

notes

use function melt package reshape2 convert data frame time wide long format. , merge (left outer join) data frames event , melted time 2 columns (q , dt in event, q , variable in melted time) .

data:

time <- structure(list(q = structure(1:5, .label = c("a", "b", "c", "d",  "e"), class = "factor"), `1` = c(1l, 2l, 3l, 4l, 4556l), `2` = c(13l,  21l, 21l, 123l, 11l), `3` = c(43l, 12l, 312l, 213l, 123l), `4` = c(5l,  3353l, 123l, 123l, 12l), `5` = c(3l, 34l, 343l, 35l, 3l)), .names = c("q",  "1", "2", "3", "4", "5"), class = "data.frame", row.names = c(na,  -5l))  event <- structure(list(q = structure(1:5, .label = c("a", "b", "c", "d",  "e"), class = "factor"), dt = c(1l, 3l, 4l, 2l, 1l)), .names = c("q",  "dt"), class = "data.frame", row.names = c(na, -5l))