在R中基于ID将时间点单列值拆分为多列的实现方法

你需要的效果可以通过两种常用方案实现，均不使用spread函数，且支持自动补全最多14个Timepoint空列：

方案1：tidyverse套件实现（推荐，语法简洁） #

第一步：构造示例数据 #

library(tidyverse)
df <- tibble(
  ID = c("A","A","A","A","B","B","C","C","D"),
  Timepoint = c(1,2,3,4,7,11,4,5,7),
  Value = rep("yes", 9)
)

第二步：转换为宽表并补全14个Timepoint列 #

df_wide <- df %>%
  # 按ID分组，给每个ID下的Timepoint按出现顺序生成序号
  group_by(ID) %>%
  mutate(tp_seq = paste0("Timepoint", row_number())) %>%
  ungroup() %>%
  # 转宽表，保留ID和Value列，Timepoint值按序号填充到对应列
  pivot_wider(
    id_cols = c(ID, Value),
    names_from = tp_seq,
    values_from = Timepoint
  ) %>%
  # 补全14个Timepoint列，不存在的列填充NA
  add_column(!!!set_names(rep(NA_real_, 14), paste0("Timepoint", 1:14))[!paste0("Timepoint",1:14) %in% names(.)]) %>%
  # 调整列顺序匹配需求
  select(ID, paste0("Timepoint",1:14), Value)

方案2：基础R实现（无需加载第三方包） #

# 按ID拆分Timepoint为列表
tp_list <- split(df$Timepoint, df$ID)
# 每个列表元素补全长度为14，不足的填NA
tp_list <- lapply(tp_list, function(x) {length(x) <- 14; x})
# 转换为Timepoint数据框
tp_df <- do.call(rbind, tp_list)
colnames(tp_df) <- paste0("Timepoint", 1:14)
# 提取每个ID对应的唯一Value
value_df <- aggregate(Value ~ ID, df, head, 1)
# 合并得到最终宽表
df_wide <- merge(value_df, tp_df, by.x = "ID", by.y = "row.names")
# 调整列顺序匹配需求
df_wide <- df_wide[, c("ID", paste0("Timepoint",1:14), "Value")]

两种方案运行后，前4个Timepoint列完全符合你给出的预期输出格式，Timepoint5到Timepoint14会自动填充为NA，你可以根据需要将NA替换为空白值。

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