How to check if my table matches criteria in another table

Question

I am quite new in R. This question seems to be quite common but I wasn't able to find a relevant answer from previous questions.

I have data as follow:

And have my maximum limitation as the following tables (each row is a separate criterion).

And I would like to do a comparison of all the rows in my data matching those criteria and would like to have the yellow column as a return as a result.

Hope this is clear.

Possible duplicate of https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right — zx8754, Dec 04 '19 at 07:19
Just checked that out. That post explains the way to merge two tables, instead of comparing them and create a new one. — MATTHEW, Dec 04 '19 at 07:23
How do you compare `Data 6` If it is not present at the `Max_Limit` df? — Sotos, Dec 04 '19 at 07:29
I see, it is not a duplicate... Please post your data as text, see https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — zx8754, Dec 04 '19 at 07:32
@Sotos The max limit 1-5 are separate criterion for ALL data. For example, data row 6 is compared to max limit criterion 1 (not exceeded), max limit criterion 2-4 (not exceeded) and max limit criterion 5 (exceeded) separately, as summarised in the last table. The last column in the last table is simply checking whether one or more those max limit has/have been exceeded. — MATTHEW, Dec 04 '19 at 07:42

score 4 · Accepted Answer · answered Dec 04 '19 at 07:53

We can use outer to get all combinations of values compared with the function greater than, (>). We do this for both columns and add them together. We are looking for both columns to exceed limit so basically looking for sum of 2. Once we have that, we can use rowSums to get the rows with at least 1 non-zero, i.e.

m1 <- (outer(df$column1, df1$Forcolumn1, `>`) + outer(df$column2, df1$Forcolumn2, `>`) == 2) * 1

#     [,1] [,2] [,3] [,4] [,5]
#[1,]    0    0    0    0    0
#[2,]    0    1    0    0    0
#[3,]    0    0    0    0    0
#[4,]    0    0    1    0    0
#[5,]    0    0    1    0    0
#[6,]    0    0    0    0    1

Using rowSums we get your expected output,

rowSums(m1 > 0)
#[1] 0 1 0 1 1 1

DATA

dput(df)
structure(list(Data = 1:6, column1 = 11:16, column2 = c(3, 3, 
2, 2, 1, 0)), class = "data.frame", row.names = c(NA, -6L))

dput(df1)
structure(list(max_limit = 1:5, Forcolumn1 = c(11, 11, 13, 14, 
15), Forcolumn2 = c(3, 2, 0, 1, -1)), class = "data.frame", row.names = c(NA, 
-5L))

How to check if my table matches criteria in another table

1 Answers1