如何在R中为同一年份多唯一ID的AREA值添加后缀编号

需求说明 #

当同一YEAR（年份）中的AREA（区域）对应多个唯一ID时，需在AREA值末尾添加数字编号。

原始数据框 #

data <- data.frame(
  AREA = c("Riverview", "Riverview", "Riverview", "Lake Beach", "Lake Beach", "Lake Beach", "Lake Beach", "Lake Beach", "Lake Beach", "Oasis", "Oasis"),
  YEAR = c(2012, 2013, 2014, 2012, 2012, 2012, 2013, 2013, 2013, 2012, 2013),
  ID = c("A0", "A0", "A1", "A2", "A2", "A3", "A3", "A3", "A2", "A4", "A4")
)

原始数据展示 #

AREA          YEAR   ID
-----------------------
Riverview     2012   A0
Riverview     2013   A0
Riverview     2014   A1
Lake Beach    2012   A2
Lake Beach    2012   A2
Lake Beach    2012   A3
Lake Beach    2013   A3   
Lake Beach    2013   A3
Lake Beach    2013   A2
Oasis         2012   A4
Oasis         2013   A4

期望转换结果 #

AREA            YEAR   ID
-------------------------
Riverview       2012   A0
Riverview       2013   A0
Riverview       2014   A1
Lake Beach 1    2012   A2
Lake Beach 1    2012   A2
Lake Beach 2    2012   A3
Lake Beach 2    2013   A3   
Lake Beach 2    2013   A3
Lake Beach 1    2013   A2
Oasis           2012   A4
Oasis           2013   A4

实现代码 #

我们可以使用dplyr包高效处理这个需求，代码如下：

library(dplyr)

# 加载原始数据（若已定义可跳过）
data <- data.frame(
  AREA = c("Riverview", "Riverview", "Riverview", "Lake Beach", "Lake Beach", "Lake Beach", "Lake Beach", "Lake Beach", "Lake Beach", "Oasis", "Oasis"),
  YEAR = c(2012, 2013, 2014, 2012, 2012, 2012, 2013, 2013, 2013, 2012, 2013),
  ID = c("A0", "A0", "A1", "A2", "A2", "A3", "A3", "A3", "A2", "A4", "A4")
)

# 核心处理逻辑
result <- data %>%
  # 按区域和年份分组，确保仅在同一年份的同一区域内处理ID
  group_by(AREA, YEAR) %>%
  mutate(
    # 为组内每个唯一ID分配对应的编号
    id_num = match(ID, unique(ID)),
    # 仅当组内存在多个唯一ID时，拼接区域名称与编号；否则保留原名称
    AREA = ifelse(n_distinct(ID) > 1, paste(AREA, id_num), AREA)
  ) %>%
  # 取消分组，恢复普通数据框结构
  ungroup() %>%
  # 移除临时生成的id_num列（可选操作）
  select(-id_num)

# 输出结果
print(result)

代码说明 #

• group_by(AREA, YEAR)：限定处理范围为同一年份的同一区域，避免跨年份或跨区域的ID干扰
• match(ID, unique(ID))：根据组内唯一ID的出现顺序，为每个ID分配对应的数字编号
• ifelse(n_distinct(ID) > 1, ...)：判断组内是否存在多个唯一ID，仅在满足条件时才修改区域名称
• ungroup()：取消分组状态，确保后续操作不受分组限制

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