A possible solution with base R by using a combination of colSums, which, toString and apply:
strs$colids <- apply(strs, 1, function(x) toString(which(colSums(lut == x, na.rm=TRUE) > 0)))
which gives:
> strs
strings colids
1 O75663 1, 3
2 O95400 1, 3
3 O95433 1, 3
4 O95456 2, 3, 4
5 O95670 2, 3, 4
6 O95801 4
7 P00352 4
8 P00492
To see what each part does, start by looking at the output of lut == 'O75663' which will give you a TRUE/FALSE table. By wrapping this in colSums you sum the TRUE/FALSE. A 0 means that there is no match in that column for that string, a number above zero means that there is one or more matches. With which you get the column indexes and by wrapping that in toString you get a character values with indexes of the matching columns.
This approach could also be implemented with either data.table or dplyr:
library(data.table)
setDT(strs)[, colids := toString(which(colSums(lut == strings, na.rm=TRUE) > 0)), by = 1:nrow(strs)][]
library(dplyr)
strs %>% rowwise() %>% mutate(colids = toString(which(colSums(lut == strings, na.rm=TRUE) > 0)))
In response to your comment: An example for multiple columns in strs with data.table:
# create an extra strings column
set.seed(1)
strs$strings2 <- sample(strs$strings)
# create two 'colids' columns
library(data.table)
setDT(strs)[, c('colids1','colids2') := lapply(.SD, function(x) toString(which(colSums(lut == x, na.rm=TRUE) > 0))), by = 1:nrow(strs)][]
which gives:
strings strings2 colids1 colids2
1: O75663 O95433 1, 3 1, 3
2: O95400 P00492 1, 3
3: O95433 O95456 1, 3 2, 3, 4
4: O95456 O95670 2, 3, 4 2, 3, 4
5: O95670 O75663 2, 3, 4 1, 3
6: O95801 P00352 4 4
7: P00352 O95400 4 1, 3
8: P00492 O95801 4
Used data:
lut <- structure(list(V1 = c("O75663", "O95400", "O95433", NA, NA),
V2 = c("O95456", "O95670", NA, NA, NA),
V3 = c("O75663", "O95400", "O95433", "O95456", "O95670"),
V4 = c("O95456", "O95670", "O95801", "P00352", NA)),
.Names = c("V1", "V2", "V3", "V4"), class = "data.frame", row.names = c(NA, -5L))
strs <- structure(list(strings = c("O75663", "O95400", "O95433", "O95456", "O95670", "O95801", "P00352", "P00492")),
.Names = "strings", class = "data.frame", row.names = c(NA, -8L))
With regard to the extended example you added to your question: The reason you are getting that error is because you are trying to compare factor-variables with character-variables. See the difference in output between sapply(strs,class) and sapply(lut,class):
> sapply(strs,class)
strings1 strings2 strings3 strings4 strings5
"factor" "factor" "factor" "factor" "factor"
> sapply(lut,class)
V1 V2 V3 V4 V5 V6 V7 V8
"character" "character" "character" "character" "character" "character" "character" "character"
It is therefore necessary to convert the factor‘s to character‘s first and then do the comparison. The following code:
library(data.table)
setDT(strs)[, lapply(.SD, as.character)
][, paste0('colids.',seq_along(strs)) := lapply(.SD, function(x) toString(which(colSums(lut == x, na.rm=TRUE) > 0))),
by = 1:nrow(strs)][]
now gives the correct output:
strings1 strings2 strings3 strings4 strings5 colids.1 colids.2 colids.3 colids.4 colids.5
1: O75663 O95456 O95456 O95400 P00492 1, 3, 5, 7 2, 3, 4, 6, 7, 8 2, 3, 4, 6, 7, 8 1, 3, 5, 7
2: O95400 O75663 O95801 P00492 O95400 1, 3, 5, 7 1, 3, 5, 7 4, 8 1, 3, 5, 7
3: O95433 P00492 P00352 O95456 P00352 1, 3, 5, 7 4, 8 2, 3, 4, 6, 7, 8 4, 8
4: O95456 P00352 P00492 O95801 O75663 2, 3, 4, 6, 7, 8 4, 8 4, 8 1, 3, 5, 7
5: O95670 O95433 O75663 O95433 2, 3, 4, 6, 7, 8 1, 3, 5, 7 1, 3, 5, 7 1, 3, 5, 7
6: O95801 O95400 O95801 4, 8 1, 3, 5, 7 4, 8
7: O95670 O95670 2, 3, 4, 6, 7, 8 2, 3, 4, 6, 7, 8
8: O95456 2, 3, 4, 6, 7, 8
Used data extended example:
strs <- structure(list(strings1 = structure(c(2L, 3L, 4L, 5L, 6L, 7L, 1L, 1L), .Label = c("", "O75663", "O95400", "O95433", "O95456", "O95670", "O95801"), class = "factor"),
strings2 = structure(c(4L, 2L, 6L, 5L, 3L, 1L, 1L, 1L), .Label = c("", "O75663", "O95433", "O95456", "P00352", "P00492"), class = "factor"),
strings3 = structure(c(4L, 6L, 7L, 8L, 2L, 3L, 5L, 1L), .Label = c("", "O75663", "O95400", "O95456", "O95670", "O95801", "P00352", "P00492"), class = "factor"),
strings4 = structure(c(2L, 5L, 3L, 4L, 1L, 1L, 1L, 1L), .Label = c("", "O95400", "O95456", "O95801", "P00492"), class = "factor"),
strings5 = structure(c(8L, 2L, 7L, 1L, 3L, 6L, 5L, 4L), .Label = c("O75663", "O95400", "O95433", "O95456", "O95670", "O95801", "P00352", "P00492"), class = "factor")),
.Names = c("strings1", "strings2", "strings3", "strings4", "strings5"), class = "data.frame", row.names = c(NA, -8L))
lut <- structure(list(V1 = c("O75663", "O95400", "O95433", NA, NA),
V2 = c("O95456", "O95670", NA, NA, NA),
V3 = c("O75663", "O95400", "O95433", "O95456", "O95670"),
V4 = c("O95456", "O95670", "O95801", "P00352", NA),
V5 = c("O75663", "O95400", "O95433", NA, NA),
V6 = c("O95456", "O95670", NA, NA, NA),
V7 = c("O75663", "O95400", "O95433", "O95456", "O95670"),
V8 = c("O95456", "O95670", "O95801", "P00352", NA)),
.Names = c("V1", "V2", "V3", "V4", "V5", "V6", "V7", "V8"), row.names = c(NA, -5L), class = "data.frame")
14
solved Finding the index based on two data frames of strings