如何用tidyverse在R中转换并重塑杂乱表格？

我手上有一张约2000条记录的表格，包含姓名（names）、职位（positions）、专业领域（field of expertise）以及教授地址信息，但表格结构非常混乱。我需要一种程序化的方法，将其转换为**整洁（tidy）**格式，最终得到至少包含names、positions、field of expertise和email address列的规整表格。

数据的规律是：不同人的记录之间用NA分隔。

数据示例 #

数据复现代码 #

tibble::tribble(
                                                  ~data, ~field,
                                         "Person 1, MD",    "A",
                             "Associate Professor of A",    "A",
                                 "Program Associate, A",    "A",
              "Senior Medical Director, FGP Operations",    "A",
                              "UMMG Ambulatory Surgery",    "A",
                                    "Residency Program",    "A",
                                "Core Educational Lead",    "A",
                                     "Email1@email.edu",    "A",
                                                     NA,    "A",
                                         "Person 2, MD",    "B",
                                             "Person 2",    "B",
                    "Clinical Assistant Professor of B",    "B",
  "Medical Student Clerkship and Core Educational Lead",    "B",
                                     "Email2@email.edu",    "B",
                                                     NA,    "B",
                                              "labcoat",    "B",
                                         "Person 3, MD",    "B",
                    "Clinical Assistant Professor of B",    "B",
                                     "Email3@email.edu",    "B",
                                                     NA,    "B",
                                              "labcoat",    "B",
                                         "Person 4, MD",    "B",
                                       "Professor of B",    "B",
                                       "Professor of B",    "B",
                                     "Email4@email.edu",    "B",
                                                     NA,    "B",
                                         "Person 5, MD",    "C",
                                             "Person 5",    "C",
                                       "Professor of C",    "C",
                                       "Professor of C",    "C",
                             "Associate Chair, Quality",    "C",
              "Department of Urology and Service Chief",    "C",
                                     "Email5@email.edu",    "C",
                                         "132-547-1321",    "C",
                                                     NA,    "C"
  )

解决方案（R语言） #

利用dplyr和tidyr包可以高效完成数据规整，核心步骤是按NA分割记录组，再在组内提取目标字段：

library(dplyr)
library(tidyr)
library(stringr)

# 加载数据
df <- tibble::tribble(
  # 粘贴上述复现代码内容
)

# 1. 标记记录组并过滤无效行
df_clean <- df %>%
  mutate(group_id = cumsum(is.na(data))) %>%
  filter(!is.na(data) & data != "labcoat")

# 2. 按组提取并整理字段
tidy_df <- df_clean %>%
  group_by(group_id, field) %>%
  summarise(
    # 提取姓名：匹配带", MD"的行，兼容重复的纯姓名行
    names = {
      name_with_title <- first(data[grepl(", MD", data)])
      first(data[data == str_remove(name_with_title, ", MD") | data == name_with_title])
    },
    # 提取邮箱：匹配含@email的行
    email_address = first(data[grepl("@email", data)]),
    # 整合职位：排除姓名和邮箱后，用分号连接多个职位
    positions = paste(data[!grepl(", MD|@email", data)], collapse = "; "),
    field_of_expertise = field
  ) %>%
  ungroup() %>%
  select(names, positions, field_of_expertise, email_address)

# 查看最终结果
print(tidy_df)

结果说明 #

• 姓名提取：自动识别带职称的姓名行，同时兼容重复出现的纯姓名条目
• 职位整合：将同一人的多个职位合并为单个字符串，避免数据冗余
• 邮箱识别：通过关键词精准匹配邮箱地址
• 专业领域：直接复用原表格的field列数据

内容的提问来源于stack exchange，提问作者a.sa.5969