Use sqldf to subset rows from column in a different data.frame

Question

I'm translating some data.frame code into SQL by using sqldf. My goal here is to subset rows of a data.frame A using a column from B. Is this possible when A and B don't share any column names?

A = data.frame(a1 = c(1:4), a2 = c(101:104))
B = data.frame(b1 = c(1:2), b2 = c(55,56))

A[A$a1 %in% B$b1,]

##   a1  a2
## 1  1 101
## 2  2 102

I can subset A if I already know the values from B$b1, but that's not very scalable.

sqldf("select * from A where a1 in (1,2)")

Do I need an inner join and/or is it required to have identical column names?

G. Grothendieck · Accepted Answer · 2016-01-28 22:59:50Z

2

Try this:

fn$sqldf(" select * from A where a1 in ( `toString(B$b1)` ) ")

or

sqldf("select A.* from A join B on A.a1 = B.b1")

answered Jan 28, 2016 at 22:59

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mpalanco · Accepted Answer · 2016-01-28 22:14:10Z

1

We use paste twice. To concatenate the elements of the vector B$b1 separated by commas. And then to concatenate the final text string desired: [1] "select * from A where a1 in( 1,2 )"

sqldf(paste("select * from A where a1 in(", paste(B$b1, collapse = ","), ")"))

Output:

  a1  a2
1  1 101
2  2 102

answered Jan 28, 2016 at 22:14

mpalanco

13.7k3 gold badges66 silver badges71 bronze badges

Collectives™ on Stack Overflow

Use sqldf to subset rows from column in a different data.frame

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related