如何用dplyr实现Pubmed长表按作者分组拼接后转宽表

核心思路 #

核心解决AD行数量不固定的分组问题，利用PubMed导出数据的固定块结构规律处理：

• 每个作者的记录块统一为「2行FAU→1行AU→N行AD」的结构，每个作者块的首个FAU行的前一行一定不是FAU
• 用cumsum()生成作者专属组号，将同个作者的所有记录绑定到同一分组，不受AD行数影响
• 分组后分别提取、拼接对应字段即可

完整实现代码 #

library(tidyverse)

# 示例数据赋值，实际使用替换为你自己的数据集即可
df <- structure(list(pubmed_id = c(29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 29542687L, 
29542687L, 29542687L, 29542687L, 29542687L, 29542687L), variable_id = c("PMID", 
"FAU", "FAU", "AU", "AD", "AD", "AD", "AD", "AD", "AD", "AD", 
"AD", "AD", "AD", "FAU", "FAU", "AU", "AD", "AD", "AD", "AD", 
"AD", "AD", "AD", "AD", "AD", "FAU", "FAU", "AU", "AD", "AD", 
"AD", "AD", "AD", "AD", "AD", "AD", "FAU", "FAU", "AU", "AD", 
"AD", "AD", "AD"), entry = c("29542687", "Kato", "Gregory J", 
"Kato GJ", "Heart", "Lung and Blood Vascular Medicine Institute and the Division of", 
"Hematology-Oncology", "Department of Medicine", "University of Pittsburgh", 
"200 Lothrop", "Street", "Pittsburgh", "PA 15261", "USA.", "Piel", 
"Fredrich", "Piel FB", "PHE Centre for Environment and Health", 
"Department of Epidemiology and", "Biostatistics", "School of Public Health", 
"Faculty of Medicine", "Imperial College", "London", "London", 
"UK.", "Reid", "Clarice D", "Reid CD", "Sickle Cell Disease Branch", 
"National Heart", "Lung and Blood Institute", "NIH", "Bethesda", 
"", "MD", "USA.", "Gaston", "Marilyn H", "Gaston MH", "The Gaston and Porter Health Improvement Center", 
"Potomac", "MD", "USA.")), class = "data.frame", row.names = c(NA, 
-44L))

result <- df %>%
  # 过滤无用的PMID行
  filter(variable_id != "PMID") %>%
  # 按文献ID分组，生成作者组号
  group_by(pubmed_id) %>%
  mutate(author_grp = cumsum(variable_id == "FAU" & lag(variable_id, default = "") != "FAU")) %>%
  ungroup() %>%
  # 按文献和作者组汇总字段
  group_by(pubmed_id, author_grp) %>%
  summarise(
    full_author = paste(entry[variable_id == "FAU"], collapse = ", "),
    author = entry[variable_id == "AU"][1],
    address = paste(entry[variable_id == "AD" & str_squish(entry) != ""], collapse = " "),
    .groups = "drop"
  ) %>%
  select(-author_grp)

输出结果说明 #

运行后得到的result就是要求的宽表格式，每行对应一个作者，自动拼接全名、保留缩写名、合并完整地址，支持批量处理多篇PubMed文献数据，不需要提前知道每个作者的地址行数。

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