如何在tidyverse中更简洁地使用查找表过滤数据集？

Great question! Your paste workaround gets the job done, but it’s not the most robust or scalable approach—especially if you end up needing to match more columns down the line. The tidyverse has a purpose-built tool for exactly this scenario that’s far cleaner and more intuitive: semi_join().

The Best Solution: semi_join() #

semi_join() is designed to filter rows from your main dataset that have a matching entry in a lookup table, based on shared columns. It’s perfect for multi-column matches and avoids the pitfalls of string concatenation (like accidental matches from special characters or whitespace).

Here’s how to apply it to your data:

library(tidyverse)

# Example data (set seed for reproducible response values)
set.seed(123)
data <- data.frame(
  patients = 1:5,
  treatment = letters[1:5],
  hospital = c("yyy", "yyy", "zzz", "www", "uuu"),
  response = rnorm(5)
)

lookup <- tibble(
  hospital = c("yyy", "uuu"),
  patients = c(1,5)
)

# Filter using semi_join
filtered_data <- data %>%
  as_tibble() %>%
  semi_join(lookup, by = c("hospital", "patients"))

filtered_data

This will return exactly the rows you want:

# A tibble: 2 × 4
  patients treatment hospital response
     <int> <chr>     <chr>       <dbl>
1        1 a         yyy        -0.560
2        5 e         uuu         1.56 

Why This Is Better Than Your Original Approach #

• Robustness: No risk of false matches from string concatenation (e.g., if a hospital name had a space like "yy y", your paste method would break or mismatch).
• Scalability: Adding more matching columns is as simple as extending the by vector (e.g., by = c("hospital", "patients", "visit_date")).
• Readability: Anyone familiar with the tidyverse will immediately understand what semi_join() does—your intent is clear without extra mental work.

Alternative: Filter with Row-Wise Matching #

If you prefer to stick with filter() for some reason, you can use rowwise() and cur_data() to check each row against the lookup table, though this is less efficient for large datasets:

data %>%
  as_tibble() %>%
  rowwise() %>%
  filter(all(c(hospital, patients) %in% lookup %>% select(hospital, patients) %>% flatten_chr())) %>%
  ungroup()

But semi_join() is almost always the better choice here—it’s optimized for this exact use case.

内容的提问来源于stack exchange，提问作者Marco