Check whether an element in a data frame exists in another data frame

Viewed 1023

Suppose I have the two following tables:

Table1:

id    word
1     apple
1     banana
2     cherry
2     donuts
3     eggplant
3     fish

Table2 (key_words):

key_words
apple
orange
cherry
peach

I want to check whether each element in the 'word' column of table1 exists in table2 and get the following results like:

id    apple   orange   cherry   peach
1       1       0        0        0
2       0       0        1        0
3       0       0        0        0

For example,
1 in the first row and "apple" column means id 1 does have an apple.
0 in the second row and "orange" column means id 2 doesn't have an orange.

To get such result, I wrote a for loop:

data=list()
data[[1]]=table1$id
l=dim(table1)[1]
for(i in 2:(length(key_words)+1)){
  exist=c()
  for(j in 1:l){
    d1=table1[which(table1$id==data[[1]][j]),]
    if(key_words[i] %in% d1$word){
      exist[j]=1
    } else {
      exist[j]=0
    }
  }
  data[[i]]=exist
}
data=as.data.frame(data)
names(data)=c("id","apple","orange","cherry","peach")

It does work.

However, if my table size and keywords number become much larger, for example, if I have 10,000 ids and 1,000 keywords, the for loop will run for a very long time.

Is there some faster method to shrink the running time?

2 Answers
Related