如何基于已有dataframe创建新的pathways dataframe？

问题 #

我需要创建一个包含两列的dataframe pathways：

1. Genes：包含enriched数据框中所有的基因；
2. Pathways：对应每个基因的enriched数据框行名，单个基因可对应多行。

我不知道该怎么入手，以下是enriched数据框的生成代码：

rownames(enrich.top5) <- enrich.top5[,2]
enrich.top5[,2] <- NULL
enriched <- data.frame(do.call('rbind', strsplit(as.character(enrich.top5$Genes),';',fixed=TRUE)))
rownames(enriched) <- rownames(enrich.top5)

enriched数据框示例：

> dput(enriched[1:5,1:20])
structure(list(X1 = c("CALML6", "ATF2", "MYLK2", "ATF2", "PRDM4"
), X2 = c("CALML3", "ARAF", "ITGA2B", "PPP2R2A", "CALML6"), X3 = c("CALML4", 
"ELK1", "TNC", "TCL1B", "IRS1"), X4 = c("ACTB", "CRKL", "ELK1", 
"TCL1A", "CALML3"), X5 = c("CRKL", "ELK4", "ACTB", "PPP2R1B", 
"CALML4"), X6 = c("AKT2", "RPS6KA4", "MYLK3", "PPP2R1A", "CRKL"
), X7 = c("RASSF5", "RPS6KA3", "CRKL", "CREB3L4", "RPS6KA3"), 
    X8 = c("AKT3", "RPS6KA6", "MYLK", "CREB3L1", "RPS6KA6"), 
    X9 = c("KDR", "RPS6KA5", "ACTG1", "MYC", "RPS6KA5"), X10 = c("AKT1", 
    "MYC", "IGF1R", "AKT2", "AKT2"), X11 = c("PLCE1", "AKT2", 
    "MYLK4", "MYB", "ARHGDIA"), X12 = c("PRKCG", "RPS6KA2", "PPP1CB", 
    "CREB3L2", "RPS6KA2"), X13 = c("PRKCI", "AKT3", "COMP", "AKT3", 
    "AKT3"), X14 = c("PRKCB", "STMN1", "PPP1CC", "KDR", "RPS6KA1"
    ), X15 = c("PRKCA", "RPS6KA1", "CCND3", "AKT1", "ARHGDIB"
    ), X16 = c("TIAM1", "KDR", "CCND2", "FLT3LG", "AKT1"), X17 = c("ADCY9", 
    "AKT1", "CCND1", "PRKCA", "MAP3K5"), X18 = c("PRKD3", "PRKACA", 
    "IBSP", "EREG", "MAP2K1"), X19 = c("PARD3", "PRKACB", "TNN", 
    "CDC37", "MAP2K2"), X20 = c("PFN4", "PRKCG", "AKT2", "DDIT4", 
    "PRKCD")), row.names = c("Rap1 signaling pathway", "MAPK signaling pathway", 
"Focal adhesion", "PI3K-Akt signaling pathway", "Neurotrophin signaling pathway"
), class = "data.frame")

期望输出示例：

pathways = data.frame(
  Genes = c(
    "TP53",
    "WT1",
    "PHF6",
    "DNMT3A",
    "DNMT3B",
    "TET1",
    "TET2",
    "IDH1",
    "IDH2",
    "FLT3",
    "KIT",
    "KRAS",
    "NRAS",
    "RUNX1",
    "CEBPA",
    "ASXL1",
    "EZH2",
    "KDM6A"
  ),
  Pathway = rep(c(
    "TSG", "DNAm", "Signalling", "TFs", "ChromMod"
  ), c(3, 6, 4, 2, 3)),
  stringsAsFactors = FALSE
)

head(pathways)
#>    Genes Pathway
#> 1   TP53     TSG
#> 2    WT1     TSG
#> 3   PHF6     TSG
#> 4 DNMT3A    DNAm
#> 5 DNMT3B    DNAm
#> 6   TET1    DNAm

解决方案 #

方法1：Base R实现 #

利用基础R函数将宽格式数据转为长格式，匹配基因与通路：

# 重复通路名称，次数等于每一行的基因数量（列数）
pathway_names <- rep(rownames(enriched), ncol(enriched))
# 将宽格式的基因矩阵转为一维向量
genes <- as.vector(t(enriched))
# 构建目标dataframe
pathways <- data.frame(
  Genes = genes,
  Pathways = pathway_names,
  stringsAsFactors = FALSE
)
# 可选：去除空值（若存在无基因的列）
pathways <- pathways[pathways$Genes != "", ]

方法2：Tidyverse实现 #

借助tidyverse工具链的pivot_longer函数，更直观地转换格式：

library(tidyverse)

pathways <- enriched %>%
  # 添加通路列，值为原数据的行名
  mutate(Pathways = rownames(.)) %>%
  # 将所有以X开头的列转为长格式，提取基因
  pivot_longer(cols = starts_with("X"), 
               names_to = NULL, 
               values_to = "Genes") %>%
  # 调整列顺序并过滤空值
  select(Genes, Pathways) %>%
  filter(Genes != "")

两种方法均可生成符合需求的两列dataframe，同一基因若属于多个通路会自动生成多行记录。

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