如何在R中为重复观测数据集生成嵌套分组指标列I？

我有一个包含大量重复观测的数据集：

Id    Date        Group  Diagnosis    
  1     8/16/2004   Red    A
  1     8/16/2004   Red    B
  1     8/16/2004   Red    C

  2     4/23/2010   Blue    A
  2     4/23/2010   Blue    C

  3     5/13/2006   Blue    A
  3     5/13/2006   Blue    B
  3     5/13/2006   Blue    C
  3     6/05/2011   Blue    A
  3     6/05/2011   Blue    B
  3     6/05/2011   Blue    C

  4     10/06/2009   Blue    A
  4     10/06/2009   Blue    B
  4     10/06/2009   Blue    C
  4     7/22/2010    Blue    A
  4     7/22/2010    Blue    B

需要创建新指标列I，规则如下：

• 仅含一组诊断记录的Id（如Id1、Id2）标记为1-1
• 含多组诊断记录的Id（如Id3、Id4），按日期排序后，第一组标记为2-1，第二组标记为2-2

期望输出：

Expected output

      Id    Date        Group  Diagnosis   I   
      1     8/16/2004   Red    A           1-1
      1     8/16/2004   Red    B           1-1
      1     8/16/2004   Red    C           1-1

      2     4/23/2010   Blue    A          1-1
      2     4/23/2010   Blue    C          1-1

      3     5/13/2006   Blue    A          2-1
      3     5/13/2006   Blue    B          2-1      
      3     5/13/2006   Blue    C          2-1 
      3     6/05/2011   Blue    A          2-2 
      3     6/05/2011   Blue    B          2-2
      3     6/05/2011   Blue    C          2-2

      4     10/06/2009   Blue    A         2-1
      4     10/06/2009   Blue    B         2-1
      4     10/06/2009   Blue    C         2-1
      4     7/22/2010    Blue    A         2-2
      4     7/22/2010    Blue    B         2-2

之前尝试用group_by(Id, Group, Diagnosis)结合n()和n_distinct函数未成功，求解决方案。

可以用dplyr包按以下步骤实现：

1. 将Date列转换为日期格式，确保排序逻辑正确
2. 按Id分组，统计每个Id对应的唯一日期数量（即诊断组数）
3. 给每个Id下的日期组按顺序分配序号
4. 拼接生成指标列I

完整代码：

library(dplyr)
library(lubridate)

# 构造示例数据
df <- tibble(
  Id = c(1,1,1,2,2,3,3,3,3,3,3,4,4,4,4,4),
  Date = mdy(c("8/16/2004","8/16/2004","8/16/2004",
               "4/23/2010","4/23/2010",
               "5/13/2006","5/13/2006","5/13/2006","6/05/2011","6/05/2011","6/05/2011",
               "10/06/2009","10/06/2009","10/06/2009","7/22/2010","7/22/2010")),
  Group = c("Red","Red","Red","Blue","Blue",
            "Blue","Blue","Blue","Blue","Blue","Blue",
            "Blue","Blue","Blue","Blue","Blue"),
  Diagnosis = c("A","B","C","A","C",
                "A","B","C","A","B","C",
                "A","B","C","A","B")
)

# 生成指标列I
df_result <- df %>%
  # 转换日期格式，保证排序正确
  mutate(Date = mdy(Date)) %>%
  # 按Id分组，计算每个Id的日期组数
  group_by(Id) %>%
  mutate(group_count = n_distinct(Date)) %>%
  # 给每个Id内的日期组分配排序后的序号
  mutate(group_num = dense_rank(Date)) %>%
  # 拼接指标列
  mutate(I = case_when(
    group_count == 1 ~ "1-1",
    TRUE ~ paste0("2-", group_num)
  )) %>%
  # 取消分组
  ungroup() %>%
  # 恢复原日期格式显示（可选）
  mutate(Date = format(Date, "%m/%d/%Y"))

print(df_result)

代码说明 #

• mdy()：将字符型日期转换为标准日期格式，避免字符串排序的错误
• n_distinct(Date)：统计每个Id对应的唯一日期数，判断是单组还是多组记录
• dense_rank(Date)：对每个Id内的日期按时间顺序排序，生成组的序号
• case_when()：根据组数拼接对应的指标字符串，满足需求的标记规则

内容的提问来源于stack exchange，提问作者Ahir Bhairav Orai