如何将按ID分组的日期与结果列拆分为多观测新列？

我希望按时间顺序将日期列拆分为新列（Date_1、Date_2、Date_3等），同时添加对应结果的列。我觉得或许可以用Accession编号作为键，但不确定具体操作方法。

原始数据 #

> CV
       Name Accession   MRN  Collected   Result
1 Doe, John       123 55555 2022-01-05 Detected
2 Doe, John       234 55555 2022-01-06 Negative
3 Doe, John       345 55555 2022-01-07 Detected
4 Doe, Jane       456 66666 2022-01-08 Negative
5 Doe, Jane       567 66666 2022-01-09 Negative
6 Doe, Jane       678 66666 2022-01-20 Negative

期望输出 #

Name           MRN        Date_1      Result_1      Date_2      Result_2     Date_3    Result_3        

Doe, John     55555     2022-01-05    Detected     2022-01-06   Negative   2022-01-07  Detected
Doe, Jane     66666     2022-01-08    Negative     2022-01-09   Negative   2022-01-20  Negative     

数据构造代码 #

Name <- c("Doe, John", "Doe, John", "Doe, John", "Doe, Jane", 
          "Doe, Jane", "Doe, Jane")
Accession <- c(123, 234, 345, 456, 567, 678)
MRN <- c(55555, 55555, 55555, 66666, 66666, 66666)
Collected <- c("2022-01-05", "2022-01-06", "2022-01-07", "2022-01-08", 
              "2022-01-09", "2022-01-20")
Result <- c("Detected", "Negative", "Detected", "Negative", 
            "Negative", "Negative")
CV <- data.frame(Name, Accession, MRN, Collected, Result)

使用tidyverse工具包可以高效实现需求，核心是将长格式数据转换为宽格式，同时保证日期的时间顺序：

步骤说明 #

1. 按Name和MRN分组，确保同一用户的记录放在一起
2. 按Collected日期对每组内的记录排序，生成组内序号
3. 将Collected和Result列按序号展开为Date_n和Result_n格式的宽列

完整代码 #

# 安装并加载tidyverse（首次运行需安装）
# install.packages("tidyverse")
library(tidyverse)

# 转换数据格式
CV_wide <- CV %>%
  group_by(Name, MRN) %>%
  arrange(Collected, .by_group = TRUE) %>%  # 按采集日期排序
  mutate(record_id = row_number()) %>%     # 生成组内记录序号
  pivot_wider(
    names_from = record_id,
    values_from = c(Collected, Result),
    names_glue = "{.value}_{record_id}"    # 定义新列名格式
  ) %>%
  ungroup() %>%
  rename_with(~str_replace(., "Collected_", "Date_"), starts_with("Collected"))  # 重命名日期列

# 调整列顺序以匹配期望输出
CV_wide <- CV_wide %>%
  select(Name, MRN, Date_1, Result_1, Date_2, Result_2, Date_3, Result_3)

# 查看结果
print(CV_wide, row.names = FALSE)

运行结果 #

Name   MRN     Date_1 Result_1     Date_2 Result_2     Date_3 Result_3
Doe, John 55555 2022-01-05 Detected 2022-01-06 Negative 2022-01-07 Detected
Doe, Jane 66666 2022-01-08 Negative 2022-01-09 Negative 2022-01-20 Negative

内容的提问来源于stack exchange，提问作者T.McMillen