Retain rows in a dataframe based on 2 columns satisfying different criteria ranges; there are 27 rows of ranges in the dataframe

Question

Edited for better clarity:

I have a dataframe dat which I need to extract the entire row based on 2 columns ut and ctz which must satisfy their respective rows of ranges in data.frame range_criteria simultaneously; the ranges are different for ut and ctz and they must satisfy their respective ranges. If either the ut or ctz is out of the range, the entire row will be discarded.

In another words when checking to each row of criteria, dat$ut must be equal OR within range_criteria$ut_min to range_criteria$ut_max AND dat$ctz must be equal OR within range_criteria$ctz_min to range_criteria$ctz_max

I have been cracking my brain over this for for 12 hours, I must make sure each row of ut and dat is checked by every row of the respective range_criteria. I know I have to loop, but I am not sure how... please help!

dat <- data.frame(name = c("Asics", 'Tom', "Harry", "David", "Daniel", "Harri", "Davidi", "Daniely", "May", "Kelly"),
             ut = c(33, 2.4, 3.2, 3.5,9.5,5.2,6.0,45, 46, 51),
             ctz = c(7.3, 1, 6.0, 3.5, 5.1, 51.5, 6.6, 7, 9.1, 10.1))

range_criteria <- data.frame(ut_min = c(0.0, 0.5, 1.0, 2.0, 7.2, 9.0, 21.0),
    ut_max = c(5, 10, 15, 25, 30, 35, 50),
    ctz_min = c(0, 1, 2, 3.2, 4.3, 6.3, 6.9),
    ctz_max = c(5, 5.5, 6.1, 6.2, 6.4 ,6.5, 7.8))

The expected outcome should be:

interest <- data.frame(name = c('Asics', 'Tom', "David", "Daniely" , "May"),
                 ut = c(33, 2.4, 3.5,45, 46),
                 ctz = c(7.3, 1, 3.5, 7, 9.1))

Thank you so much !!

Answer 1

Based on your description it sounds like you want the ith row of dat to satisfy both ranges specified in the ith row of range_criteria, is that correct?

If so, there's no need to loop (explicitly). R's vectorized approach makes this work pretty easily:

dat <- data.frame(name = c('Tom', "Harry", "David", "Daniel", "Harri", "Davidi", "Daniely"),
                  ut = c(2.4, 3.2, 3.5,9.5,5.2,6.0,45),
                  ctz = c(1, 6.0, 3.5, 5.1, 51.5, 6.6, 7))

rc <- data.frame(ut_min = c(0.0, 0.5, 1.0, 2.0, 7.2, 9.0, 21.0),
                             ut_max = c(5, 10, 15, 25, 30, 35, 50),
                             ctz_min = c(0, 1, 2, 3.2, 4.3, 6.3, 6.9),
                             ctz_max = c(5, 5.5, 6.1, 6.2, 6.4 ,6.5, 7.8))

dat[dat$ut >= rc$ut_min & dat$ut <= rc$ut_max & dat$ctz >= rc$ctz_min & dat$ctz <= rc$ctz_max,]

This also returns "Daniel" in addition to the other three names you mentioned, but looking at the data I think that's correct.

Alternately you could use a package designed for data manipulation like dplyr or data.table to do the same thing a bit more smoothly.

library(data.table)

both <- cbind(dat, rc)
setDT(both)
interest <- both[between(ut, ut_min, ut_max) & between(ctz, ctz_min, ctz_max)]

or

library(dplyr)

both <- bind_cols(dat, rc)

interest <- both %>%
  filter(ut >= ut_min & ut <= ut_max & ctz >= ctz_min & ctz <= ctz_max)