Filter using multiple rows in two dataframes in R

Question

Data

df1

  col1
1    a
2    a
3    b
4    e

df2

   col1  col2
1   1     a
2   1     c
3   1     c
4   1     e
5   2     a
6   2     b
7   2     b
8   2     e
9   3     a
10  3     a
11  3     b
12  3     e

I want to filter df2 using df1. So far I have this code.

filter(df2, any(col2==df1$col1[1]))

This allows me to filter row by row. But I want to filter by multiple rows. Not the whole df1 at once. I want to filter df2 using df1$col1[1:2]. So "a" followed by "a". I tried the following code but got this message.

filter(df2, col2==df1$col1[1] & col2==df1$col1[2])

[1] col1 col2 <0 rows> (or 0-length row.names)

Ideally output:

df2

   col1  col2
1   3     a
2   3     a
3   3     b
4   3     e

Answer 1

You could use package Biostrings.

df1 <- data.frame(col1=c("a", "a", "b", "e"))
df2 <- data.frame(col1=c(rep(1, 4), rep(2, 4), rep(3, 4)),
                  col2=letters[c(1, 3, 3, 5, 1, 2, 2, 5, 1, 1, 2, 5)])

aabe <- paste0(df1$col1, collapse = "")
cand <- paste0(df2$col2, collapse = "")

# # Install the package
# source("https://bioconductor.org/biocLite.R")
# biocLite("Biostrings")

library(Biostrings)

match <- matchPattern(aabe, cand)
str(matchPattern(aabe, cand))

x1 <- match@ranges@start
x2 <- x1 + match@ranges@width - 1

> df2[x1:x2, ]
   col1 col2
9     3    a
10    3    a
11    3    b
12    3    e

Answer 2

Using the same approach as in @jaySf's answer, you can also use gregexpr.

matchpattern <- unlist(gregexpr(pattern = paste(df1$col1, collapse = ""), 
                                          paste(df2$col2, collapse = "")))
df2[matchpattern:(matchpattern + nrow(df1) - 1),]

#   col1 col2
#9     3    a
#10    3    a
#11    3    b
#12    3    e

Or stri_locate from stringi.

library(stringi)
index <- unlist(stri_locate(paste(df2$col2, collapse = ""), 
                            fixed = paste(df1$col1, collapse = "")))
df2[index[1]:index[2],]

#   col1 col2
#9     3    a
#10    3    a
#11    3    b
#12    3    e