R中大数据框左外连接内存占用过高的优化方案咨询

我有三个数据框：result（652265行10列）、rat（107190行2列）、mouse（219016行2列），存储了Ensembl基因ID等同源基因信息。用dplyr做左外连接时程序卡住，换成sqldf执行后内存直接占满系统上限，求优化方法。

数据结构 #

> dim(result)
[1] 652265     10
> dim(rat)
[1] 107190      2
> dim(mouse)
[1] 219016      2
> names(result)
 [1] "ensembl_gene_id"                             "ensembl_transcript_id"                      
 [3] "external_gene_name"                          "mmusculus_homolog_ensembl_gene"             
 [5] "mmusculus_homolog_associated_gene_name"      "rnorvegicus_homolog_ensembl_gene"            
 [7] "rnorvegicus_homolog_associated_gene_name"    "celegans_homolog_ensembl_gene"               
 [9] "celegans_homolog_associated_gene_name"       "celegans_homolog_ensembl_gene_transcript_id"
> names(rat)
[1] "rnorvegicus_homolog_ensembl_gene"          "rnorvegicus_homolog_ensembl_transcript_id"
> names(mouse)
[1] "mmusculus_homolog_ensembl_gene"          "mmusculus_homolog_ensembl_transcript_id"

数据示例 #

head(result)
  ensembl_gene_id ensembl_transcript_id external_gene_name mmusculus_homolog_ensembl_gene
1 ENSG00000198888       ENST00000361390             MT-ND1             ENSMUSG00000064341
2            <NA>                  <NA>               <NA>                           <NA>
3 ENSG00000198804       ENST00000361624             MT-CO1             ENSMUSG00000064351
4 ENSG00000198712       ENST00000361739             MT-CO2             ENSMUSG00000064354
5            <NA>                  <NA>               <NA>                           <NA>
6            <NA>                  <NA>               <NA>                           <NA>
  mmusculus_homolog_associated_gene_name rnorvegicus_homolog_ensembl_gene
1                                 mt-Nd1               ENSRNOG00000030644
2                                   <NA>                             <NA>
3                                 mt-Co1               ENSRNOG00000034234
4                                 mt-Co2               ENSRNOG00000030371
5                                   <NA>                             <NA>
6                                   <NA>                             <NA>
  rnorvegicus_homolog_associated_gene_name celegans_homolog_ensembl_gene
1                                   Mt-nd1                WBGene00010959
2                                     <NA>                          <NA>
3                                   Mt-co1                WBGene00010964
4                                   Mt-co2                WBGene00010965
5                                     <NA>                          <NA>
6                                     <NA>                          <NA>
  celegans_homolog_associated_gene_name celegans_homolog_ensembl_gene_transcript_id
1                                                                         MTCE.11.1
2                                  <NA>                                        <NA>
3                                 ctc-1                                   MTCE.26.1
4                                 ctc-2                                   MTCE.31.1
5                                  <NA>                                        <NA>
6                                  <NA>                                        <NA>
> head(rat)
  rnorvegicus_homolog_ensembl_gene rnorvegicus_homolog_ensembl_transcript_id
1               ENSRNOG00000033395                        ENSRNOT00000044979
2               ENSRNOG00000070901                        ENSRNOT00000119508
3               ENSRNOG00000031391                        ENSRNOT00000045306
4               ENSRNOG00000067904                        ENSRNOT00000099225
5               ENSRNOG00000067904                        ENSRNOT00000099225
6               ENSRNOG00000067904                        ENSRNOT00000099225
> head(mouse)
  mmusculus_homolog_ensembl_gene mmusculus_homolog_ensembl_transcript_id
1             ENSMUSG00000064341                      ENSMUST00000082392
2             ENSMUSG00000064345                      ENSMUST00000082396
3             ENSMUSG00000064351                      ENSMUST00000082402
4             ENSMUSG00000064354                      ENSMUST00000082405
5             ENSMUSG00000064356                      ENSMUST00000082407
6             ENSMUSG00000064357                      ENSMUST00000082408

当前尝试的sqldf代码 #

joined_data <- sqldf("
    SELECT *
    FROM result
    LEFT OUTER JOIN rat ON result.rnorvegicus_homolog_ensembl_gene = rat.rnorvegicus_homolog_ensembl_gene
    LEFT OUTER JOIN mouse ON result.mmusculus_homolog_ensembl_gene = mouse.mmusculus_homolog_ensembl_gene
")

1. 去重小数据框，避免行数膨胀 #

观察rat和mouse数据，同一基因ID对应多个转录本会导致连接时行数剧增。先对这两个数据框按基因ID去重：

library(dplyr)

# 对rat去重，保留每个基因ID的第一条记录
rat_unique <- rat %>% 
  distinct(rnorvegicus_homolog_ensembl_gene, .keep_all = TRUE)

# 对mouse去重
mouse_unique <- mouse %>% 
  distinct(mmusculus_homolog_ensembl_gene, .keep_all = TRUE)

# 执行连接
joined_data <- result %>%
  left_join(rat_unique, by = "rnorvegicus_homolog_ensembl_gene") %>%
  left_join(mouse_unique, by = "mmusculus_homolog_ensembl_gene")

2. 使用data.table高效连接 #

data.table的连接效率和内存控制远优于dplyr/sqldf，适合大数据集：

library(data.table)

# 转换为data.table格式
setDT(result)
setDT(rat)
setDT(mouse)

# 可选：去重小数据框
rat_unique <- unique(rat, by = "rnorvegicus_homolog_ensembl_gene")
mouse_unique <- unique(mouse, by = "mmusculus_homolog_ensembl_gene")

# 链式左连接
joined_data <- result[rat_unique, on = "rnorvegicus_homolog_ensembl_gene"][
  mouse_unique, on = "mmusculus_homolog_ensembl_gene"]

3. 分块处理数据 #

如果内存仍不足，将result拆分为小块逐块连接后合并：

library(dplyr)

# 定义分块大小
chunk_size <- 100000
chunks <- split(result, ceiling(seq_len(nrow(result))/chunk_size))

# 逐块执行连接
joined_chunks <- lapply(chunks, function(chunk) {
  chunk %>%
    left_join(rat_unique, by = "rnorvegicus_homolog_ensembl_gene") %>%
    left_join(mouse_unique, by = "mmusculus_homolog_ensembl_gene")
})

# 合并所有分块结果
joined_data <- bind_rows(joined_chunks)

4. 优化数据类型，降低内存占用 #

将字符型基因ID转换为因子类型，可大幅减少内存消耗：

library(dplyr)

# 转换关键列为因子
result <- result %>%
  mutate(across(c(ensembl_gene_id, mmusculus_homolog_ensembl_gene, rnorvegicus_homolog_ensembl_gene), as.factor))

rat <- rat %>%
  mutate(rnorvegicus_homolog_ensembl_gene = as.factor(rnorvegicus_homolog_ensembl_gene))

mouse <- mouse %>%
  mutate(mmusculus_homolog_ensembl_gene = as.factor(mmusculus_homolog_ensembl_gene))

# 执行连接
joined_data <- result %>%
  left_join(rat, by = "rnorvegicus_homolog_ensembl_gene") %>%
  left_join(mouse, by = "mmusculus_homolog_ensembl_gene")

5. 筛选必要列，减少数据量 #

如果不需要result的全部列，提前筛选出核心列再连接：

library(dplyr)

# 仅保留连接和分析需要的列
result_subset <- result %>%
  select(ensembl_gene_id, mmusculus_homolog_ensembl_gene, rnorvegicus_homolog_ensembl_gene)

# 执行连接
joined_data <- result_subset %>%
  left_join(rat, by = "rnorvegicus_homolog_ensembl_gene") %>%
  left_join(mouse, by = "mmusculus_homolog_ensembl_gene")

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