Check if item exists in dataframe in another dataframe
Suppose I have the following two tables:
Table1:
id word
1 apple
1 banana
2 cherry
2 donuts
3 eggplant
3 fish
Table2 (keywords):
key_words
apple
orange
cherry
peach
I want to check if every item exists in the "word" column of table1 in table2 and get the following results, for example:
id apple orange cherry peach
1 1 0 0 0
2 0 0 1 0
3 0 0 0 0
For example, a
1 in the first row and an "apple" column means that identifier 1 has an apple.
A 0 in the second row and an "orange" column means that ID 2 is not orange.
To get this 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")
He works.
However, if my table size and number of keywords get much larger, for example if I have 10,000 IDs and 1000 keywords, the for loop will take a very long time.
Is there a faster method to shorten the execution time?
+3
source to share
2 answers
library(data.table)
dat <- fread("id word
1 apple
1 banana
2 cherry
2 donuts
3 eggplant
3 fish")
dat_key <- fread("key_words
apple
orange
cherry
peach")
dat_wide <- data.table(ID = unique(dat$id))
l <- lapply(dat_key$key_words, function(x) dat_wide[, (x) := ifelse(ID %in% dat[word == x]$id, 1, 0)][])
dat_wide
ID apple orange cherry peach
1: 1 1 0 0 0
2: 2 0 0 1 0
3: 3 0 0 0 0
+2
source to share
data.frame(t(sapply(split(table1, table1$id), function(a)
colSums(sapply(table2$key_words, function(x) a$word %in% x)) > 0)))
# apple orange cherry peach
#1 TRUE FALSE FALSE FALSE
#2 FALSE FALSE TRUE FALSE
#3 FALSE FALSE FALSE FALSE
The boolean value can be changed to a numerical value in another step if needed
temp = data.frame(t(sapply(split(table1, table1$id), function(a)
colSums(sapply(table2$key_words, function(x) a$word %in% x)) > 0)))
data.frame(sapply(temp, as.numeric), row.names = row.names(temp))
# apple orange cherry peach
#1 1 0 0 0
#2 0 0 1 0
#3 0 0 0 0
DATA
table1 = structure(list(id = c(1L, 1L, 2L, 2L, 3L, 3L), word = c("apple",
"banana", "cherry", "donuts", "eggplant", "fish")), .Names = c("id",
"word"), class = "data.frame", row.names = c(NA, -6L))
table2 = structure(list(key_words = c("apple", "orange", "cherry", "peach"
)), .Names = "key_words", class = "data.frame", row.names = c(NA,
-4L))
+1
source to share