efficiently filter data frame according to conditions given as rows of another data frame

user_8453123 0
Viewed 279

I have the following sample data frame:

df <- structure(list(PC1 = c(1.08553700088979, 3.0948497436612, 
-0.997456334603069, 
1.41407630966724, 0.287288941434462, -0.304145457046063, 0.0540331738096902, 
0.276994168448363, -0.178887591197422, 1.03793040779083, -0.964485366085487, 
0.781189811085296, -0.360466840689429, -2.25639643892807, 
-0.688600791894463, 
1.05031184739218, 3.30341296998208, 0.265388275042453, 0.187534314978584, 
2.58042550274586, 0.564788667016578), PC2 = c(-0.560967999647005, 
0.856204454728214, 0.720760276550347, 1.75595629874967, -0.707834522512927, 
0.891530126176209, 0.631768747109977, -0.845237959897621, 
-0.412613566320007, 
-0.159362864836617, -0.569253016944671, -0.0181844049717689, 
-0.0218393445421908, 1.86197538876216, -0.263011388351398, 
0.0582985416071711, 
1.7585346351499, 1.74997701136744, 0.723398654405442, -0.482322211724498, 
-0.240535930597667), PC3 = c(0.36287528575844, -2.01764685704277, 
-0.408829080806452, 0.97914722241214, -0.665892667247256, 
-0.242401102421392, 
0.497651711177106, 1.26726883331746, 1.27889899812577, 0.54485872382572, 
0.191895005811088, 0.381351220912963, -0.613213748902156, 
0.0685178101199476, 
0.532000414181072, 1.19230092657081, 1.48731243525717, 1.16110479193897, 
0.486880645956999, -2.69479147849705, 0.169949194117217)), row.names = c(NA, 
-21L), class = c("tbl_df", "tbl", "data.frame"))

I would like to filter rows of df according to the following set of conditions relating to PC1, given as rows of another data frame f1:

f1 <- structure(list(xmin = c(-3.59811981997059, -3.10182743100913, 
-2.8536812365284, 2.8536812365284, 3.59811981997058), xmax = 
c(-3.34997362548985, 
-2.8536812365284, -2.60553504204766, 3.10182743100912, 3.84626601445132
)), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"
))

The filtration of PC2 should be done according to f2,

f2 <- structure(list(xmin = c(-2.56910324629848, -2.37879930212822, 
2.56910324629848, 2.949711134639), xmax = c(-2.37879930212822, 
-2.18849535795797, 2.75940719046874, 3.14001507880926)), row.names = c(NA, 
-4L), class = c("tbl_df", "tbl", "data.frame"))

In other words, the values in the column PC1 of the data frame df have to be either between -3.6 and -3.35 OR between -3.1 and -2.85, and so on, and the values of PC2 have to be between -2.57 and -2.38 and so on. For each column of df I have such a data frame that tells me how to filter the corresponding column.

I could of course write out the condition:

df %>% filter(PC1 > -3.6 & PC1 < -3.35 | PC1 > -3.1 & PC1 < -2.85 & PC2 > -2.57 & PC2 < -2.38 ....),

and repeat this for every column. But eventually I am going to have many conditions and this is not practical.

Is there a shorter and more efficient way of doing this?

Thanks!

dplyr filter r
1 Answers

One way of getting this to work is using glue, eval and parse functions.

I created a function (my_conditions) so it can be used more easily. There is still some manual work involved for changing columns names / condition tables, but not as much and this can probably be automated as well. The function calls on the glue package. 

my_conditions <- function(column_name, condition_table){
  # create conditions
  conditions <- glue::glue("{column_name} > {condition_table$xmin} & {column_name} < {condition_table$xmax}")
  # collapse into 1 statement using " | " for or statement
  conditions <- paste0(conditions, collapse = " | ")
  return(conditions)
}

The result of calling my_conditions("PC1", f1) is a long string which has all the conditions of table f1.

[1] "PC1 > -3.59811981997059 & PC1 < -3.34997362548985 | PC1 > -3.10182743100913 & PC1 < -2.8536812365284 | PC1 > -2.8536812365284 & PC1 < -2.60553504204766 | PC1 > 2.8536812365284 & PC1 < 3.10182743100912 | PC1 > 3.59811981997058 & PC1 < 3.84626601445132"

Using eval and parse to parse and evaluate the conditions in the code.

using dplyr:

df %>% 
  filter(eval(parse(text = my_conditions("PC1", f1))))
# A tibble: 1 x 3
    PC1   PC2   PC3
  <dbl> <dbl> <dbl>
1  3.09 0.856 -2.02

filtering in base R: just add the table name in front of the column

df[eval(parse(text = my_conditions("df$PC1", f1))), ]

# A tibble: 1 x 3
    PC1   PC2   PC3
  <dbl> <dbl> <dbl>
1  3.09 0.856 -2.02
user_49851760
Related