R subset df based on multiple columns from another data frame

Question

I am trying to find a more succinct way to filter a data frame using rows from another data frame (I am currently using a loop).

For example, suppose you have the following data frame df1 consisting of quantities of apples, pears, lemons and oranges. There is also a 5th column which we will call happiness.

require(gtools)
df1 <- data.frame(permutations(n = 4, r = 4, v = 1:4)) %>% cbind(sample(1:24))
colnames(df1) <- c("Apples", "Pears", "Lemons", "Oranges", "Happiness")

However you wish to filter this dataframe to leave only certain combinations of fruit which exist in a second data frame (not with the same column order):

df2 = data.frame(Apples = c(1, 3, 2, 4), Pears = c(4, 1, 1, 3), Lemons = c(2, 2, 3, 1), Oranges = c(3, 4, 4, 2))

Currently I am using a loop to apply each row of df2 as a filter condition one-by-one and then binding the result e.g:

df.ss = list()
for (i in 1:nrow(df2)){

df.ss[[i]] = filter(df1, 
                    df1$Apples == df2$Apples & 
                    df1$Pears == df2$Pears &
                    df1$Lemons == df2$Lemons & 
                    df1$Oranges == df2$Oranges)
}

df.ss %>% bind_rows()

Is there a more elegant way of going about this ?

your expected output is the result of df.ss %>% bind_rows() ? I'm not sure because there are many duplicated rows — Julien Navarre
– Julien Navarre, Commented Mar 14, 2018 at 8:58
There’s a curly bracket missing at the end of your code somewhere. Perhaps it is also useful to paste the desired output explicitly here — KenHBS
– KenHBS, Commented Mar 14, 2018 at 14:58

Julien Navarre · Accepted Answer · 2018-03-14 09:01:26Z

2

I think you are looking for an inner join

dplyr::inner_join(df1, df2)

edited Mar 14, 2018 at 9:01

answered Mar 14, 2018 at 8:56

Julien Navarre

7,8603 gold badges46 silver badges73 bronze badges

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

2 Comments

Mike Tauber Over a year ago

What if you had multiple dataframes (similar to df1) and an additional column giving the date - i.e. if you have df1 for each day in a week and you binded the dataframes together. What approach could you use then?

drmariod Over a year ago

I was trying the same with merge but this gives different results then his bind_rows() ...

Collectives™ on Stack Overflow

R subset df based on multiple columns from another data frame

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related