优化分组数据中rowwise()操作：基于Type匹配等位基因相关值

问题背景 #

现有一个匿名基因数据集子集（已编辑以展示多类型场景），数据结构如下：

structure(list(`Sample File` = c("A", "A", "A", "A", "A", "A", 
"A", "A", "A", "B", "B", "B", "B", "B", "C", "C", "C", "C"), 
    Marker = c("X", "X", "X", "X", "Y", "Y", "Y", "Y", "Y", "Z", 
    "Z", "Z", "Z", "Z", "q", "q", "q", "q"), Allele = c(19, 20, 
    22, 23, 18, 18.2, 19, 19.2, 20, 12, 13, 14, 15, 16, 10, 10.2, 
    11, 12), Size = c(249.15, 253.13, 260.64, 264.68, 366, 367.81, 
    369.97, 372.02, 373.95, 91.65, 95.86, 100, 104.24, 108.38, 
    177.51, 179.4, 181.42, 185.49), Height = c(173L, 1976L, 145L, 
    1078L, 137L, 62L, 1381L, 45L, 1005L, 38L, 482L, 5766L, 4893L, 
    19L, 287L, 36L, 5001L, 50L), Type = c("minusone", "allele", 
    "minusone", "allele", "ambiguous", "minushalf", "allele", 
    "minushalf", "allele", "minustwo", "ambiguous", "allele", 
    "allele", "plusone", "minusone", "minushalf", "allele", "plusone"
    ), LUS = c(11.75, 11.286, 13.375, 13.5, 18, 9, 19, 10, 20, 
    12, 11, 14, 15, 16, 9.5, NA, 11, 11.5)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -18L), groups = structure(list(
    `Sample File` = c("A", "A", "B", "C"), Marker = c("X", "Y", 
    "Z", "q"), .rows = structure(list(1:4, 5:9, 10:14, 15:18), ptype = integer(0), class = c("vctrs_list_of", 
    "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -4L), .drop = TRUE))

需求说明 #

需要基于Type列的规则，在同一Sample File和Marker分组内，匹配对应Allele的Height、Allele、LUS值，生成ParentHeight、ParentAllele、ParentLUS三列：

• "minustwo"：匹配当前行Allele + 2的对应行数据
• "minusone"：匹配当前行Allele + 1的对应行数据
• "minushalf"：调用辅助函数plusTwoBP()计算目标Allele后匹配对应行数据（根据示例推断plusTwoBP(18.2)返回19，plusTwoBP(19.2)返回20）
• "plusone"：匹配当前行Allele - 1的对应行数据
• "allele"或"ambiguous"：对应列填NA

理想输出如下：

# A tibble: 18 × 10
# Rowwise:  Sample File, Marker
   `Sample File` Marker Allele  Size Height Type        LUS ParentHeight ParentAllele ParentLUS
   <chr>         <chr>   <dbl> <dbl>  <int> <chr>     <dbl>        <int>        <dbl>     <dbl>
 1 A             X        19   249.     173 minusone   11.8         1976           20      11.3
 2 A             X        20   253.    1976 allele     11.3           NA           NA      NA  
 3 A             X        22   261.     145 minusone   13.4         1078           23      13.5
 4 A             X        23   265.    1078 allele     13.5           NA           NA      NA  
 5 A             Y        18   366      137 ambiguous  18             NA           NA      NA  
 6 A             Y        18.2 368.      62 minushalf   9           1381           19      19  
 7 A             Y        19   370.    1381 allele     19             NA           NA      NA  
 8 A             Y        19.2 372.      45 minushalf  10           1005           20      20  
 9 A             Y        20   374.    1005 allele     20             NA           NA      NA  
10 B             Z        12    91.6     38 minustwo   12           5766           14      14  
11 B             Z        13    95.9    482 ambiguous  11             NA           NA      NA  
12 B             Z        14   100     5766 allele     14             NA           NA      NA  
13 B             Z        15   104.    4893 allele     15             NA           NA      NA  
14 B             Z        16   108.      19 plusone    16           4893           15      15  
15 C             q        10   178.     287 minusone    9.5         5001           11      11  
16 C             q        10.2 179.      36 minushalf  NA           5001           11      11  
17 C             q        11   181.    5001 allele     11             NA           NA      NA  
18 C             q        12   185.      50 plusone    11.5         5001           11      11 

当前实现问题 #

目前采用**逐类筛选+rowwise()**的方式实现，示例代码如下：

# eg for minustwo
sampleData %>%
  filter(Type == "minustwo") %>%
  rowwise() %>%
  mutate(ParentHeight = sampleData$Height[sampleData$`Sample File` == `Sample File` & sampleData$Marker == Marker & sampleData$Allele == (Allele + 2)],
         ParentAllele = sampleData$Allele[sampleData$`Sample File` == `Sample File` & sampleData$Marker == Marker & sampleData$Allele == (Allele + 2)],
         ParentLUS = sampleData$LUS[sampleData$`Sample File` == `Sample File` & sampleData$Marker == Marker & sampleData$Allele == (Allele + 2)]) %>%
  right_join(sampleData)

该方法需重复编写多类规则代码，针对数千行的真实数据集效率极低。尝试用group_map()实现，但未正确遵循分组规则，且无法一次性生成三列。

解决方案 #

可以利用dplyr的分组操作结合case_when统一处理所有规则，避免逐类重复代码，同时用match函数高效查找匹配项，替代低效的rowwise()操作。

步骤1：定义辅助函数plusTwoBP #

根据示例推断函数逻辑（如果实际逻辑不同，替换即可）：

plusTwoBP <- function(allele) {
  # 针对带.2的等位基因，加0.8得到整数
  ifelse(allele %% 1 == 0.2, allele + 0.8, allele)
}

步骤2：高效实现分组匹配 #

library(dplyr)

# 先取消原有分组，重新按Sample File和Marker分组
result <- sampleData %>%
  ungroup() %>%
  group_by(`Sample File`, Marker) %>%
  mutate(
    # 先计算目标Allele
    target_allele = case_when(
      Type == "minustwo" ~ Allele + 2,
      Type == "minusone" ~ Allele + 1,
      Type == "minushalf" ~ plusTwoBP(Allele),
      Type == "plusone" ~ Allele - 1,
      TRUE ~ NA_real_
    ),
    # 用match查找目标allele在当前分组中的位置
    match_pos = match(target_allele, Allele),
    # 提取对应列的值，无匹配则为NA
    ParentHeight = Height[match_pos],
    ParentAllele = Allele[match_pos],
    ParentLUS = LUS[match_pos]
  ) %>%
  # 移除中间变量
  select(-target_allele, -match_pos) %>%
  ungroup()

# 查看结果
print(result, n = 18)

方案优势 #

1. 代码简洁：用case_when统一处理所有Type规则，无需重复编写筛选和匹配逻辑
2. 效率更高：利用分组内的向量操作和match函数替代rowwise()的逐行循环，处理数千行数据时性能提升明显
3. 易维护：若后续新增Type规则，只需在case_when中添加对应分支即可

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