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?