使用dplyr条件过滤：基于AB数据集筛选ABC的正确实现

问题需求 #

现有25×2的AB数据集和30×3的ABC数据集，需基于AB的a、b变量对ABC进行筛选，得到ABC_filtered。筛选规则为：ABC中的实例必须满足：

• a值属于AB$a的取值范围
• 该a对应的b值，属于AB中相同a值对应的b子集（例如a=A时b需为1-5，a=B时为6-10等）

最终ABC_filtered需按s和a分组，统计每组符合条件的实例总数。

错误尝试代码 #

用户尝试的代码未达到预期效果：

library(magrittr)
ABC %>% 
  group_by(s, a, b) %>% 
  dplyr::filter(all(
    a %in% AB$a, b %in% AB$b[AB$a])) %>% 
  count() # DID NOT WORK

示例数据集与预期输出 #

# 示例规则（非代码）
a=A AND b=c(1,2,3,4,5) 
a=B AND b=c(6,7,8,9,10) 
a=C AND b=c(11,12,13,14,15) 
a=D AND b=c(16,17,18,19,20) 
a=E AND b=c(21,22,23,24,25)

AB <- data.frame(a=rep(c("A","B","C","D","E"), each=5), b=seq(1:25))
ABC <- structure(list(s = c("s1", "s1", "s1", "s1", "s1", "s1", 
                            "s1", "s1", "s1", "s1", "s2", "s2", 
                            "s2", "s2", "s2", "s2", "s2", "s2", 
                            "s2", "s2", "s3", "s3", "s3", "s3", 
                            "s3", "s3", "s3", "s3", "s3", "s3"), 
                      a = c("D", "H", "H", "F", "F", "H", "C", 
                            "C", "F", "E", "G", "G", "C", "G", 
                            "A", "C", "F", "H", "G", "B", "C", 
                            "G", "C", "F", "A", "G", "E", "G", 
                            "B", "D"), 
                      b = c(3L, 24L, 8L, 23L, 9L, 17L, 14L, 2L, 
                            1L, 2L, 1L, 23L, 19L, 25L, 15L, 19L, 
                            5L, 21L, 13L, 6L, 18L, 23L, 7L, 23L, 
                            17L, 23L, 14L, 15L, 6L, 18L)), 
                 class = "data.frame", row.names = c(NA, -30L))
ABC_filtered <- data.frame(s=c("s1","s2","s3","s3"), 
                           a=c("C","B","B","D"), n=c(1,1,1,1))

正确解决方案 #

最直接的方式是利用内连接（inner_join），因为AB数据集本身就存储了所有合法的(a,b)组合，通过内连接可以直接筛选出ABC中符合条件的行，再分组计数即可：

library(dplyr)

ABC_filtered <- ABC %>%
  # 内连接AB，仅保留AB中存在的(a,b)组合
  inner_join(AB, by = c("a", "b")) %>%
  # 按s和a分组，统计每组的实例数
  group_by(s, a) %>%
  count(name = "n") %>%
  ungroup()

# 验证结果
print(ABC_filtered)

错误代码分析 #

之前的代码存在两个核心问题：

1. 使用all()会要求组内所有行都满足条件，而不是对每行单独判断是否符合规则；
2. AB$b[AB$a]的索引方式错误，无法针对当前行的a值匹配对应的合法b子集，而是取了AB中所有a对应的b值集合，逻辑不符合需求。

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