filter one data.frame by another data.frame by specific columns

Question

full = data.frame(group = c('a', 'a', 'a', 'a', 'a', 'b', 'c'), values = c(1, 2, 2, 3, 5, 3, 4))
filter = data.frame(group = c('a', 'b', 'c'), values = c(4, 3, 3))
## find rows of full where values are larger than filter for the given group
full[full$group == filter$group & full$values > filter$values, ]

prints an empty data.frame with the warning:

Warning messages: 1: In full$group == filter$group : longer object length is not a multiple of shorter object length 2: In full$values > filter$values : longer object length is not a multiple of shorter object length

I'm looking for all the rows in full that match that criteria, to end up with: full

> group
group  values
    a      5
    c      4

Answer 1

Using merge

full=merge(full,filter,by='group')
full=full[full$values.x>full$values.y,]
full$values.y=NULL
names(full)=c('group','values')
> full
  group values
5     a      5
7     c      4

Or match

full$Filter=filter$values[match(full$group,filter$group)]
full=full[full$values>full$Filter,]
full$Filter=NULL
> full
  group values
5     a      5
7     c      4

Answer 2

full[unlist(sapply(1:NROW(filter), function(i)
    which(full$group == filter$group[i] & full$values > filter$values[i]))),]
#  group values
#5     a      5
#7     c      4

Answer 3

Using base R functions Map, split, unlist, and logical indexing you can do

full[unlist(Map(">", split(full$values, full$group), split(filter$values, filter$group))),]
  group values
5     a      5
7     c      4

here, you split the value vectors by group into lists and feed these to Map, which applies >. As Map returns a list, unlist returns a logical vector which is fed to [ for subsetting. Note that this requires that both data.frames are sorted by group and that each has the same levels in the group variable.

Answer 4

One option is to use dplyr.

library(dplyr)

dt <- full %>%
  left_join(filter, by = "group") %>%
  dplyr::filter(values.x > values.y) %>%
  select(group, values = values.x)
dt
  group values
1     a      5
2     c      4

Or purrr.

library(purrr)

dt <- full %>%
  split(.$group) %>%
  map2_df(filter %>% split(.$group), ~.x[.x$values > .y$values, ])
dt
  group values
1     a      5
2     c      4

Answer 5

join_by was added in dplyr 1.1.0 for more advanced join specifications:

library(dplyr)

inner_join(full, filter, by = join_by(group == group, values > values)) |>
  select(group, values = values.x)

Note the equality condition and inequality conditions both have the same variable names. They are distinguished by the order of the data frames. The LHS refers to the first data frame (full in this case) and the RHS to the second data frame (filter in this case).