CODEWITHSUNDEEP
r
rrsa
rrsa

Reputation: 11

how do i filter a dataframe based on the values of a column in another dataframe in R?

For example I have a dataframe --

ad

key   value
ab      1
bc      2
cd      3

ad1

key  QS IS LISR Group version
ab    7 23  77    2    1.2
bc    7 30  70    1    1.5
cd    8 40  60    2    1.4
de    6 12  88    3    1.7
ef    3 23  77    4    1.2
fg    2 34  66    2    1.4

Result should be--

result

key  QS IS LISR Group version
ab    7 23  77    2    1.2
bc    7 30  70    1    1.5
cd    8 40  60    2    1.4

I have found some examples,but those are for small dataframes, both of my dataframes are large!

Trying to write a query but didn't work out? How could you solve this?

thanks!

Upvotes: 0

Views: 1374

Answers (2)

ulfelder
ulfelder

Reputation: 5335

This also works:

ad1[which(ad1$key %in% ad$key),]

Upvotes: 1

akrun
akrun

Reputation: 886938

We can use %in% and subset to subset the rows in 'ad1' based on the 'key' columns in both the datasets.

subset(ad1, key %in% ad$key)
#   key QS IS LISR Group version
#1  ab  7 23   77     2     1.2
#2  bc  7 30   70     1     1.5
#3  cd  8 40   60     2     1.4

Or another option is join from data.table. In the devel version, we can use on. We convert the 'data.frame' to 'data.table' (setDT(ad1)) and join the 'ad' dataset.

library(data.table)#v1.9.5+
setDT(ad1)[ad['key'], on='key']
#  key QS IS LISR Group version
#1:  ab  7 23   77     2     1.2
#2:  bc  7 30   70     1     1.5
#3:  cd  8 40   60     2     1.4

Upvotes: 3

Related Questions