如何在R中按配对格式融合（melt）目标数据框？

你需要把前5列作为静态标识，然后将后续每连续两列（connected_gene和对应的score）拆分为新行，这个需求可以用tidyverse工具轻松实现，下面给你两种可行的方案：

方法一：拆分动态列并逐组合并 #

这种方法思路直观，先把静态列和动态列分开，再把动态列按每2列一组拆分，最后将每组和静态列合并并去除空值：

library(tidyverse)

# 加载你的数据（如果还没加载的话）
sample <- structure(list(chrom = c("chr10", "chr20"), start = c(121780088L, 
54214412L), end = c(121780259L, 54215291L), score = c(0.27, 0.78
), genehancer_id = c("GH10G121780", "GH20G054214"), connected_gene = c("TACC2", 
"PFDN4"), score.1 = c("3.94", "0.43"), connected_gene.1 = c("ATE1", 
"DOK5"), score.2 = c("1.38", "0.06"), connected_gene.2 = c("GC10M121752", 
NA), score.3 = c("0.31", NA), connected_gene.3 = c("GC10M121821", 
NA), score.4 = c("0.22", NA)), .Names = c("chrom", "start", "end", 
"score", "genehancer_id", "connected_gene", "score.1", "connected_gene.1", 
"score.2", "connected_gene.2", "score.3", "connected_gene.3", 
"score.4"), class = "data.frame", row.names = c(NA, -2L))

# 提取静态列（前5列）
static_data <- sample[, 1:5]

# 提取动态列（第6列及以后）
dynamic_data <- sample[, 6:ncol(sample)]

# 将动态列按每2列一组拆分
dynamic_groups <- split.default(dynamic_data, ceiling(seq_along(dynamic_data)/2))

# 合并每组数据与静态列，并重命名列，最后去除空值
result <- map_dfr(dynamic_groups, function(group) {
  colnames(group) <- c("connected_gene", "score")
  cbind(static_data, group)
}) %>%
  drop_na(connected_gene, score) %>%
  mutate(score = as.numeric(score)) %>%  # 转换score为数值型（原数据是字符）
  arrange(chrom, start)  # 调整行顺序与你期望的输出一致

# 查看结果
print(result)

运行后得到的结果就是你想要的格式：

chrom     start       end score genehancer_id connected_gene score
1  chr10 121780088 121780259  0.27   GH10G121780          TACC2  3.94
2  chr10 121780088 121780259  0.27   GH10G121780          ATE1  1.38
3  chr10 121780088 121780259  0.27   GH10G121780    GC10M121752  0.31
4  chr10 121780088 121780259  0.27   GH10G121780    GC10M121821  0.22
5  chr20  54214412  54215291  0.78   GH20G054214          PFDN4  0.43
6  chr20  54214412  54215291  0.78   GH20G054214          DOK5  0.06

方法二：用tidyr的pivot系列函数重塑 #

这种方法通过两次重塑（先变长再变宽）来实现列的配对，适合更复杂的列名规则：

library(tidyverse)

result <- sample %>%
  # 第一步：将所有动态列转为长格式，处理无后缀的connected_gene列
  pivot_longer(
    cols = -c(chrom, start, end, score, genehancer_id),
    names_to = c("type", "pair"),
    names_pattern = "(connected_gene|score)\\.(.*)",
    names_repair = ~ if_else(.x == "connected_gene", "connected_gene.0", .x)
  ) %>%
  # 第二步：将类型（connected_gene/score）转为列，实现配对
  pivot_wider(
    names_from = type,
    values_from = value
  ) %>%
  # 去除空值行
  filter(!is.na(connected_gene), !is.na(score)) %>%
  # 转换score为数值型
  mutate(score = as.numeric(score)) %>%
  # 调整列顺序和行顺序
  select(chrom, start, end, score_static = score, genehancer_id, connected_gene, score) %>%
  rename(score = score_static) %>%
  arrange(chrom, start)

print(result)

这个方法同样能得到你需要的输出，优点是不需要手动拆分列，适合列名有规律但后缀不一致的场景。

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