How to query a dataframe using a column of other dataframe in R

Question

I have 2 dataframes in R and I want to do a query using the dataframe "y" like parameter to dataframe "x".

I have this code:

x <- c('The book is on the table','I hear birds outside','The electricity 
came back')
x <- data.frame(x)
colnames(x) <- c('text')
x

y <- c('book','birds','electricity')
y <- data.frame(y)
colnames(y) <- c('search')
y

r <- sqldf("select * from x where text IN (select search from y)")
r

I think to use "like" here, but I don´t know. Can you helpme ?

score 2 · Accepted Answer · answered Aug 11 '17 at 16:21

2

If you want a sqldf solution, I think that this would work:

sqldf("select x.text, y.search FROM x JOIN y on x.text LIKE '%' || y.search || '%'")

##                          text      search
## 1    The book is on the table        book
## 2        I hear birds outside       birds
## 3 The electricity \ncame back electricity

answered Aug 11 '17 at 16:21

Oriol Mirosa

2,756
1
13
15

Thanks. And if I need to query using each words in "y" to filter "x" ? – Robson Brandão Aug 11 '17 at 18:38
Isn't that what the code does? If you modify `y` to only contain one or two terms (for instance: `y <- c('book','birds'`), you will see that only the first two lines of `x` are shown. Isn't that what you wanted? – Oriol Mirosa Aug 11 '17 at 18:42
Thanks. it´s perfect. – Robson Brandão Aug 11 '17 at 18:45

score 0 · Answer 2 · answered Aug 11 '17 at 15:39

You could use the fuzzyjoin package:

library(dplyr)
library(fuzzyjoin)

regex_join(
  mutate_if(x, is.factor, as.character), 
  mutate_if(y, is.factor, as.character), 
  by = c("text" = "search")
)

#                          text      search
# 1    The book is on the table        book
# 2        I hear birds outside       birds
# 3 The electricity \ncame back electricity

score 0 · Answer 3 · answered Aug 11 '17 at 15:55

It's hard to know if this is what you want without a more varied fixture. To add a little bit of variation, I added an extra word to y$search - y = c('book','birds','electricity', 'cat'). More variation would further clarify

Just know which words are in which statements? sapply and grepl

> m = sapply(y$search, grepl, x$text)
> rownames(m) = x$text
> colnames(m) = y$search
> m
                             book birds electricity   cat
The book is on the table     TRUE FALSE       FALSE FALSE
I hear birds outside        FALSE  TRUE       FALSE FALSE
The electricity \ncame back FALSE FALSE        TRUE FALSE

Pulling out just the matching rows?

> library(magrittr)  # To use the pipe, "%>%"
> x %>% data.table::setDT()  # To return the result as a table easily
>
> x[(sapply(y$search, grepl, x$text) %>% rowSums() %>% as.logical()) * (1:nrow(x)), ]
                          text
1:    The book is on the table
2:        I hear birds outside
3: The electricity \ncame back

@Aurèle's solution will give the best result for matching text and the text it match to. Note that if back was also in y$search, the text The electricity \ncame back would get reported twice in the result for the different search terms matched, so this is better in the case that uniqueness is not important.

So it largely depends on your desired output.

How to query a dataframe using a column of other dataframe in R

3 Answers3