[R] Add column to DF based on 2 columns in another DF

[arun]

Hi,
Try:
library(reshape2)
m1 <- melt(LookupTable,id.vars="Str")
m2 <- m1

res <- merge(MainDataFrame,m1,by.x=c("Str","index"),by.y=c("Str","variable"))
res[order(res$Str),c(3:6,1:2,7)]

#or
library(plyr)
colnames(m2)[-1] <- c("index","index_num")
 m2$index <- as.character(m2$index)
 join(MainDataFrame,m2,by=c("Str","index"))


A.K.


LookupTable <- read.table(header = TRUE, 
                   stringsAsFactors = FALSE, 
                   text="Str IND13 IND12 IND11 IND07 IND06 
                         1   517   529   562   562   567 
                         2   517   529   562   562   568 
                         3   517   529   562   562   567 
                         4   517   529   562   562   569 
                         5   517   529   562   562   567 
                         6   517   529   562   562   567 
                         7   517   529   562   562   560 
                         8   517   529   562   562   567 
                         9   517   529   562   562   567 
                         10   517   529   562   562   567") 


MainDataFrame <- read.table(header = TRUE, 
                          stringsAsFactors = FALSE, 
                          text="Pid YEAR MONTH  Fips Str index 
                            600250 2006     7  6037  1 IND06 
                            600250 2006     7  6037  2 IND06 
                            600250 2006     7  6037  3 IND06 
                            600250 2006     7  6037  4 IND06 
                            600250 2006     7  6037  5 IND06 
                            600353 2007     9 48097  6 IND07 
                            600772 2006     2  6039  7 IND06 
                            600947 2007     1 13207  7 IND07 
                            601055 2007     9 13315  8 IND07 
                            601103 2006     5 21093  10 IND06") 

MainDataFrame_New <- ?? 

#What is the best way to add a new column "index_num" to MainDataFrame that is populated with the 
#number corresponding to 'Str' and 'index' in LookupTable: 

#                             Pid YEAR MONTH  Fips Str index index_num 
#                             600250 2006     7  6037  1 IND06 567 
#                             600250 2006     7  6037  2 IND06 568 
#                             600250 2006     7  6037  3 IND06 567 
#                             600250 2006     7  6037  4 IND06 569 
#                             600250 2006     7  6037  5 IND06 567 
#                             600353 2007     9 48097  6 IND07 562 
#                             600772 2006     2  6039  7 IND06 560 
#                             600947 2007     1 13207  7 IND07 562 
#                             601055 2007     9 13315  8 IND07 562 
#                             601103 2006     5 21093  10 IND06 567