如何在R中当City或Floor变化时为policy_ID追加计数器？

原始数据 #

df <- data.frame(policy_ID = c("P1","P1","P1","P2","P2","P3","P4","P4","P4","P4"), 
                 City = c("Copenhagen", "Copenhagen", "London", "LA", "LA",
                          "Tokyo", "Madrid", "Madrid", "Rome", "Milan"),
                 Floor = c("1","1","1","3","4","2","1","1","4","4"))

需求 #

当同一policy_ID分组内，City或Floor任一值发生变化时，对policy_ID进行修改：初始组保留原ID，后续每变化一次，在原ID后追加_2、_3这样的编号。期望输出如下：

df <- data.frame(policy_ID = c("P1","P1","P1_2","P2","P2_2","P3","P4","P4","P4_2","P4_3"), 
                 City = c("Copenhagen", "Copenhagen", "London", "LA", "LA",
                          "Tokyo", "Madrid", "Madrid", "Rome", "Milan"),
                 Floor = c("1","1","1","3","4","2","1","1","4","4"))

当前已实现的代码 #

已能检测到City或Floor的变化，生成标记列：

df %>%
  group_by(policy_ID) %>%
  mutate(City_or_Floor_changed = ifelse(City != lag(City, default = first(City)) | 
                                        Floor != lag(Floor, default = first(Floor)), 
                                      1, 0)) %>%
  ungroup()

解决方案 #

基于已有检测逻辑，通过累加变化标记生成分组编号，再拼接policy_ID即可实现需求：

library(dplyr)

df_result <- df %>%
  group_by(policy_ID) %>%
  # 生成连续相同City+Floor组合的分组编号，初始组为1，每变化一次编号+1
  mutate(grp = cumsum(City != lag(City, default = first(City)) | 
                        Floor != lag(Floor, default = first(Floor))) + 1) %>%
  # 根据分组编号修改policy_ID
  mutate(policy_ID = case_when(
    grp == 1 ~ policy_ID,
    TRUE ~ paste0(policy_ID, "_", grp)
  )) %>%
  select(-grp) %>% # 移除临时分组列
  ungroup()

# 查看结果
print(df_result)

代码说明 #

1. 生成分组编号：用cumsum累加变化标记，得到每个连续相同City+Floor组合的组号，初始组为1，每次变化后组号递增。
2. 修改policy_ID：组号为1时保留原ID，组号大于1时在原ID后拼接_组号。
3. 清理临时列：移除用于计算的grp列，得到最终结果。

运行后输出与期望完全一致。

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