存在隐式缺失值时，如何用R的pivot_wider处理单列数据？

从网站获取的单列数据集，数据本该分布在多列，且存在隐式缺失值（部分条目缺少Abstract字段，无NA占位）。原始数据结构如下：

structure(list(original_data = c("Title1", "Authors1", "Reference1 Publication Month Date, Year", 
"Abstract1", "Title2", "Authors2", "Reference2 Publication Month Date, Year", 
"Abstract2", "Title3", "Authors3", "Reference3 Publication Month Date, Year", 
"Title4", "Authors4", "Reference4 Publication Month Date, Year", 
"Abstract1")), class = "data.frame", row.names = c(NA, -15L))

期望转换为以下多列格式：

structure(list(Titles_Data = c("Title1", "Title2", "Title3", 
"Title4"), Authors_Data = c("Authors1", "Authors2", "Authors3", 
"Authors4"), Details_Data = c("Reference1 Publication Month Date, Year", 
"Reference2 Publication Month Date, Year", "Reference3 Publication Month Date, Year", 
"Reference4 Publication Month Date, Year"), Abstracts_Data = c("Abstract1", 
"Abstract2", NA, "Abstract4")), class = "data.frame", row.names = c(NA, 
-4L))

实际数据集约1700行，如何实现？

核心思路是：先以每个条目的起始行（Title开头）为标记分组，再将每组数据转换为宽格式，自动填充缺失的Abstract为NA。以下是R语言的实现代码：

library(dplyr)
library(tidyr)

# 假设原始数据框名为df（替换为你的实际数据对象名）
df <- structure(list(original_data = c("Title1", "Authors1", "Reference1 Publication Month Date, Year", 
"Abstract1", "Title2", "Authors2", "Reference2 Publication Month Date, Year", 
"Abstract2", "Title3", "Authors3", "Reference3 Publication Month Date, Year", 
"Title4", "Authors4", "Reference4 Publication Month Date, Year", 
"Abstract1")), class = "data.frame", row.names = c(NA, -15L))

# 1. 生成组ID：每个Title开头的行标记为新组的起始
df <- df %>%
  mutate(group_id = cumsum(grepl("^Title", original_data)))

# 2. 标记组内每行的位置（1=Title, 2=Authors, 3=Details, 4=Abstract）
df <- df %>%
  group_by(group_id) %>%
  mutate(position = row_number()) %>%
  ungroup()

# 3. 转换为宽格式并设置列名
result <- df %>%
  pivot_wider(
    id_cols = group_id,
    names_from = position,
    values_from = original_data,
    names_prefix = "col_"
  ) %>%
  rename(
    Titles_Data = col_1,
    Authors_Data = col_2,
    Details_Data = col_3,
    Abstracts_Data = col_4
  ) %>%
  select(-group_id)

# 输出结果
print(result)

关键说明 #

• 分组逻辑：通过grepl("^Title", original_data)识别所有条目起始行，用cumsum生成递增组ID，确保每个条目对应一个组。
• 缺失值处理：pivot_wider会自动为缺少第4行（Abstract）的组填充NA，完美匹配需求。
• 适配大数据集：代码可直接处理1700行数据，若条目起始标记不是"Title"，只需修改grepl的正则表达式（比如^文章标题）即可。

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