如何实现tibble多列pivot转换得到指定结构的宽表？

需要将tibble1转换为tibble2的结构：

原始数据（tibble1） #

tibble::tribble(
  ~SUBJID, ~ARM, ~TIMEPOINT,     ~TP.DATE,     ~TPR_ar,
  2610003,  "B",      "BL0", "2019-03-26", "Undefined",
  2610003,  "B",      "FU1", "2019-05-30",        "SD",
  2610003,  "B",      "FU2", "2019-08-05",        "SD",
  2610003,  "B",      "FU3", "2019-10-04",        "SD",
  2610003,  "B",      "FU4", "2019-12-04",        "NE",
  2610003,  "B",      "FU5", "2020-02-07",        "SD",
  2610003,  "B",      "FU6", "2020-04-14",        "SD",
  2610003,  "B",      "FU7", "2020-07-07",        "SD",
  2610004,  "C",      "BL0", "2019-03-26", "Undefined",
  2610004,  "C",      "FU1", "2019-06-11",        "SD",
  2610004,  "C",      "FU2", "2019-07-29",        "NE",
  2610004,  "C",      "FU3", "2019-08-20",        "PD"
)

目标结构（tibble2） #

tibble::tribble(
   ~SUBJID, ~ARM,         ~BL0,         ~FU1,         ~FU2,         ~FU3,         ~FU4,         ~FU5,         ~FU6,         ~FU7,
  2610003L,  "B", "03/26/2019", "05/30/2019", "08/05/2019", "10/04/2019", "12/04/2019", "02/07/2020", "04/14/2020", "07/07/2020",
  2610003L,  "B",  "Undefined",         "SD",         "SD",         "SD",         "NE",         "SD",         "SD",         "SD",
  2610004L,  "C", "03/26/2019", "06/11/2019", "07/29/2019", "08/20/2019",           NA,           NA,           NA,           NA,
  2610004L,  "C",  "Undefined",         "SD",         "NE",         "PD",           NA,           NA,           NA,           NA
)

尝试过的代码（未得到预期结果） #

df %>%
  pivot_wider(names_from = c('TIMEPOINT'), values_from = c('TP.DATE','TPR_ar'))

思路方向正确，但需要拆分处理日期和状态列，再将结果按行合并，同时调整日期格式和数据类型：

library(tidyverse)

df <- tibble::tribble(
  ~SUBJID, ~ARM, ~TIMEPOINT,     ~TP.DATE,     ~TPR_ar,
  2610003,  "B",      "BL0", "2019-03-26", "Undefined",
  2610003,  "B",      "FU1", "2019-05-30",        "SD",
  2610003,  "B",      "FU2", "2019-08-05",        "SD",
  2610003,  "B",      "FU3", "2019-10-04",        "SD",
  2610003,  "B",      "FU4", "2019-12-04",        "NE",
  2610003,  "B",      "FU5", "2020-02-07",        "SD",
  2610003,  "B",      "FU6", "2020-04-14",        "SD",
  2610003,  "B",      "FU7", "2020-07-07",        "SD",
  2610004,  "C",      "BL0", "2019-03-26", "Undefined",
  2610004,  "C",      "FU1", "2019-06-11",        "SD",
  2610004,  "C",      "FU2", "2019-07-29",        "NE",
  2610004,  "C",      "FU3", "2019-08-20",        "PD"
)

# 步骤1：处理日期格式并生成日期宽表
date_wide <- df %>%
  mutate(TP.DATE = format(as.Date(TP.DATE), "%m/%d/%Y")) %>%
  pivot_wider(names_from = TIMEPOINT, values_from = TP.DATE) %>%
  mutate(SUBJID = as.integer(SUBJID))

# 步骤2：生成状态宽表
status_wide <- df %>%
  pivot_wider(names_from = TIMEPOINT, values_from = TPR_ar) %>%
  mutate(SUBJID = as.integer(SUBJID))

# 步骤3：按行合并两个宽表
result <- bind_rows(date_wide, status_wide)

# 查看结果
result

代码说明 #

• 先将TP.DATE转换为日期类型，再格式化为mm/dd/yyyy格式
• 分别对日期列和状态列做pivot_wider，得到两个独立的宽表
• 将SUBJID转换为整数类型，匹配目标结构的2610003L格式
• 用bind_rows将两个宽表按行拼接，得到目标的两行一组结构

内容的提问来源于stack exchange，提问作者D. Shin