如何通过条件语句将R中长格式DataFrame转为宽格式？

完整可运行代码 #

# 若未安装tidyverse包，先执行安装
# install.packages("tidyverse")

# 加载所需工具包
library(dplyr)
library(tidyr)

# 原始长格式数据
df <- data.frame(id=as.integer(c(123,123,123,124,124,124,125,125,126,126,126)),
                 date=as.Date(c("2014-03-12", "2015-05-02", "2015-09-16", "2015-10-24", "2016-12-11", "2017-10-17", "2017-08-06", "2018-01-29", "2015-09-16", "2015-11-12", "2015-12-03")),
                 event=as.character(c("A", "C", "E", "A", "B", "D", "A", "E", "A", "B", "E")),
                 order=as.integer(c(1,2,3,1,2,3,1,2,1,2,3)),
                 diff=as.integer(c(0,416,553,0,414,724,0,176,0,57,78)))

# 1. 提取每个id的最终事件（D/E）及其天数差，用于后续填充
final_events <- df %>%
  filter(event %in% c("D", "E")) %>%
  select(id, final_diff = diff, final_event = event)

# 2. 长转宽，生成每个事件的_status和_time列
wide_df <- df %>%
  select(id, event, diff) %>%
  mutate(status = 1) %>%
  pivot_wider(
    id_cols = id,
    names_from = event,
    values_from = c(status, diff),
    names_glue = "{event}_{.value}",
    values_fill = list(status = 0, diff = NA)
  ) %>%
  left_join(final_events, by = "id")

# 3. 填充B/C缺席时的_time为最终事件的天数差
wide_df <- wide_df %>%
  mutate(
    B_time = ifelse(B_status == 0, final_diff, B_diff),
    C_time = ifelse(C_status == 0, final_diff, C_diff),
    A_time = A_diff
  )

# 4. 创建D.E_status和D.E_time列
wide_df <- wide_df %>%
  mutate(
    D.E_status = ifelse(final_event == "E", 1, 0),
    D.E_time = final_diff
  )

# 5. 整理列顺序，删除冗余列
result <- wide_df %>%
  select(
    id,
    A_status, A_time,
    B_status, B_time,
    C_status, C_time,
    D.E_status, D.E_time
  )

# 查看结果
print(result)

步骤说明 #

1. 提取最终事件：从原始数据中筛选每个id的最终事件（D或E），记录其与初始事件A的天数差，用于后续填充B/C缺席时的_time列。
2. 长转宽处理：使用pivot_wider自动生成事件_status和事件_time格式的列名，缺失事件用0填充_status，NA填充_time，再合并最终事件信息。
3. 填充缺失时间：对B/C状态为0（缺席）的行，将对应的_time替换为最终事件的天数差；同时统一A列的命名格式。
4. 生成D/E组合列：根据最终事件类型生成D.E_status（E为1，D为0），D.E_time直接取最终事件的天数差。
5. 整理输出格式：调整列顺序，删除中间过程产生的冗余列，得到与需求完全匹配的宽格式数据。

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