如何将非KEGG_开头行的内容合并至前一行KEGG_开头行？

需求说明 #

处理数据框，将首列V1不以KEGG_开头的行，所有内容合并到前一行以KEGG_开头的行中，最终每行对应一个KEGG条目，后续列存放所有关联内容。

输入数据 #

df <- structure(list(V1 = c("KEGG_SNARE_INTERACTIONS_IN_VESICULAR_TRANSPORT", 
"KEGG_LYSOSOME", "CD164", "LIPA", "KEGG_CARDIAC_MUSCLE_CONTRACTION", 
"ATP1A3"), V3 = c("STX12", "PLA2G15", "HGSNAT", "AP1M1", "CACNA2D1", 
"ATP1A2"), V4 = c("STX2", "AP3B2", "ABCA2", "LAPTM4B", "CACNB1", 
"UQCRB"), V5 = c("VAMP5", "GGA1", "DNASE2B", "NAPSA", "COX8A", 
"COX6A2"), V6 = c("GOSR2", "SLC11A1", "AGA", "HEXB", "CACNB2", 
"SLC9A6")), row.names = 26:31, class = "data.frame")

已有初步代码 #

move.these.rows <- kegg[!(kegg$V1 %like% "KEGG_"),]
rows.with.kegg <- kegg[kegg$V1 %like% "KEGG_",]

完整解决方案代码 #

基于dplyr和tidyr实现分组合并，步骤清晰且易维护：

# 加载依赖包
library(dplyr)
library(tidyr)

# 1. 为每行标记所属KEGG分组：遇到KEGG_开头的行则启动新分组
df$group <- cumsum(grepl("^KEGG_", df$V1))

# 2. 按分组聚合，收集所有非空值并整理结构
result <- df %>%
  group_by(group) %>%
  # 收集当前组所有列的非NA值
  summarise(all_elements = list(unselect(., group) %>% unlist(use.names = FALSE))) %>%
  # 提取第一个元素作为KEGG标识，剩余为关联内容
  mutate(
    kegg_label = sapply(all_elements, `[`, 1),
    content = lapply(all_elements, `[`, -1)
  ) %>%
  select(kegg_label, content) %>%
  # 将内容列表展开为多列
  unnest_wider(content, names_sep = "") %>%
  # 重命名列以匹配期望格式
  rename_with(
    ~ ifelse(.x == "kegg_label", "", paste0("V", seq_along(.) - 1)),
    everything()
  ) %>%
  # 空值填充为空白字符
  mutate(across(everything(), ~ replace_na(.x, "")))

# 查看最终结果
print(result)

期望输出结果 #

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