如何在R中基于列模式处理超500万行表格并修正异常？

需求说明 #

现有超500万行的表格，包含字段chr、start、t1-t6（0=No，1=Yes）、mR。需要实现：

• 按t1-t6的0/1模式**结合染色体chr**分组
• 每组内计算mR的平均值得到CmR，拼接组内所有chr-start为Location列
• 仅在每组最后一行填充CmR和Location，其余行设为NA；当组内所有mR为0时，CmR需强制设为0

现有方法的问题 #

处理小数据集正常，但大数据集出现两个核心问题：

1. 当组内mR全为0时，CmR未被正确设为0
2. 染色体切换时，Location错误包含前一染色体的同模式位置

原始数据示例 #

chr   start    t1    t2    t3    t4    t5    t6    mR
   <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1  chr1      0     0     0     0     0     0     0     0
2  chr1    100     1     1     0     0     0     0     2.04
3  chr1    200     0     0     1     1     0     0     3.20
4  chr1    300     0     0     1     0     1     0     2.76
5  chr1    400     0     1     0     1     0     0     3.44
6  chr1    500     0     0     1     0     1     0     2.12
7  chr1    600     1     1     0     0     0     0     1.40
8  chr1    700     1     1     0     0     0     0     1.93
9  chr1    800     0     0     0     0     0     0     0
10 chr1    900     1     0     0     1     1     0     5.40
11 chr1    1000    0     1     1     0     0     0     2.65

期望输出示例 #

chr   start    t1    t2    t3    t4    t5    t6    mR    CmR    Location
   <chr>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>    <dbl>    <chr>    
1  chr1      0     0     0     0     0     0     0     0       0
2  chr1    100     1     1     0     0     0     0     2.04    NA
3  chr1    200     0     0     1     1     0     0     3.20    3.20
4  chr1    300     0     0     1     0     1     0     2.76    NA
5  chr1    400     0     1     0     1     0     0     3.44    3.44
6  chr1    500     0     0     1     0     1     0     2.12    2.44    chr1-300,chr1-500
7  chr1    600     1     1     0     0     0     0     1.40    NA
8  chr1    700     1     1     0     0     0     0     1.93    1.79    chr1-100,chr1-600,chr1-700
9  chr1    800     0     0     0     0     0     0     0       0
10 chr1    900     1     0     0     1     1     0     5.40    5.40
11 chr1    1000    0     1     1     0     0     0     2.65    2.65

异常情况示例 #

大数据集mR为0时CmR错误

SAM_bc01f[95:103,]
# A tibble: 9 × 11
  chr   start    t1    t2    t3    t4    t5    t6    mR   CmR Location
  <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <chr>   
1 chr1   9400     0     0     0     0     0     0  0     3.64 NA      
2 chr1   9500     0     0     0     0     0     0  0     3.64 NA      
3 chr1   9600     0     0     0     0     0     0  0     3.64 NA      
4 chr1   9700     0     0     0     0     0     0  0     3.64 NA      
5 chr1   9800     0     0     0     0     0     0  0     3.64 NA      
6 chr1   9900     0     0     0     0     0     0  0.16 NA    NA      
7 chr1  24600     0     0     0     1     0     0  5.92 NA    NA      
8 chr1  24700     0     0     0     1     0     0  4.44 NA    NA      
9 chr1  26900     0     1     0     0     0     0  1.64 NA    NA     

跨染色体Location拼接错误

[1] NA
[2] "chr1-214747600,chr1-220003800,chr2-214747600,chr2-220003800"
[3] "chr1-224595000,chr2-224595000" 
[4] "chr1-191887000,chr2-191887000,chr3-191887000"
[5] "chr1-180010900,chr1-207226200,chr2-180010900,chr2-207226200,chr3-180010900,chr4-180010900,chr5-180010900"
[6] "chr1-163682300,chr2-163682300,chr3-163682300,chr4-163682300,chr5-163682300,chr6-163682300" 

解决方案 #

针对大数据集的效率和准确性需求，推荐使用data.table（处理超大规模数据更高效）或dplyr实现，核心修复点为：按chr+t1-t6组合分组、强制处理全0组、仅保留组最后一行的计算结果。

data.table版本（推荐用于500万行数据集） #

library(data.table)

# 转换为data.table格式
setDT(df)

# 生成临时位置字符串，按chr+t1-t6分组计算
df[, loc := paste(chr, start, sep = "-")]
df[, `:=`(
  CmR = ifelse(all(mR == 0), 0, mean(mR)),
  Location = paste(loc, collapse = ","),
  is_last = .I == .N  # 标记组内最后一行
), by = .(chr, t1, t2, t3, t4, t5, t6)]

# 非最后一行设为NA，清理临时列
df[!is_last, `:=`(CmR = NA_real_, Location = NA_character_)]
df[, c("loc", "is_last") := NULL]

dplyr版本 #

library(dplyr)

df_processed <- df %>%
  mutate(loc = paste(chr, start, sep = "-")) %>%
  group_by(chr, t1, t2, t3, t4, t5, t6) %>%
  mutate(
    CmR = ifelse(all(mR == 0), 0, mean(mR)),
    Location = paste(loc, collapse = ","),
    CmR = ifelse(row_number() == n(), CmR, NA_real_),
    Location = ifelse(row_number() == n(), Location, NA_character_)
  ) %>%
  ungroup() %>%
  select(-loc)

关键修复说明 #

1. 分组逻辑：必须同时按chr和t1-t6分组，彻底避免跨染色体的同模式数据被错误合并
2. 全0处理：通过ifelse(all(mR == 0), 0, mean(mR))强制全0组的CmR为0，解决平均值计算的异常
3. 效率优化：data.table的分组操作内存占用更低、速度更快，更适合处理500万行的大规模数据

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