Subset data frame where values are greater than another data frame

user_8685805 0
Viewed 1093

Say I have a data frame with 3 columns of data (a,b,c) and 1 column of categories with multiple instances of each category (class).

set.seed(273)
a <- floor(runif(20,0,100))
b <- floor(runif(20,0,100))
c <- floor(runif(20,0,100))
class <- floor(runif(20,0,6))

df1 <- data.frame(a,b,c,class)

print(df1)
    a  b  c class
1  31 73 28     3
2  44 33 57     3
3  19 35 53     0
4  68 70 39     4
5  92  7 57     2
6  13 67 23     3
7  73 50 14     2
8  59 14 91     5
9  37  3 72     5
10 27  3 13     4
11 63 28  0     5
12 51  7 35     4
13 11 36 76     3
14 72 25  8     5
15 23 24  6     3
16 15  1 16     5
17 55 24  5     5
18  2 54 39     1
19 54 95 20     3
20 60 39 65     1

And I have another data frame with the same 3 columns of data and category column, however this only has one instance per category (class).

a <- floor(runif(6,0,20))
b <- floor(runif(6,0,20))
c <- floor(runif(6,0,20))
class <- seq(0,5)

df2 <- data.frame(a,b,c,class)

print(df2)
   a  b  c class
1  8 15 13     0
2  0  3  6     1
3 14  4  0     2
4  7 10  6     3
5 18 18 16     4
6 17 17 11     5

How to I subset the first data frame so that only rows where a, b, and c are all greater than the value in the second data frame for each class? For example, I only want rows where class == 0 if a > 8 & b > 15 & c > 13.

Note that I don't want to join the data frames, as the second data frame is the lowest acceptable value for the the first data frame.

r
1 Answers

As commented by Frank this can be done with non-equi joins. 

    # coerce to data.table
tmp <- setDT(df1)[
  # non-equi join to find which rows of df1 fulfill conditions in df2
  setDT(df2), on = .(class, a > a, b > b, c > c), rn, nomatch = 0L, which = TRUE]

# return subset in original order of df1
df1[sort(tmp)]

     a  b  c class
 1: 31 73 28     3
 2: 44 33 57     3
 3: 19 35 53     0
 4: 68 70 39     4
 5: 92  7 57     2
 6: 13 67 23     3
 7: 73 50 14     2
 8: 11 36 76     3
 9:  2 54 39     1
10: 54 95 20     3
11: 60 39 65     1

The parameter which = TRUE returns a vector of the matching row numbers instead of the joined data set. This saves us from creating a row id column before the join. (Credit to @Frank for reminding me of the which parameter!)

Note that there is no row in df1 which fulfills the condition for class == 5 in df2. Therefore, the parameter nomatch = 0L is used to exclude non-matching rows from the result.

This can be put together in a "one-liner":

setDT(df1)[sort(df1[setDT(df2), on = .(class, a > a, b > b, c > c), nomatch = 0L, which = TRUE])]
user_38170040
Related