如何为按资产类型和年份分组的预测退役资产分配序列号

问题 #

现有两个数据框：

• retirements：按资产类型和年份分组，记录各分组的预测退役资产数量
• sn：记录对应资产类型的序列号列表

需求：为retirements的每一行分配Num.Retired个序列号（按从小到大顺序），如果对应资产类型的序列号不足，则补充虚拟序列号“XXXX”。

数据定义如下：

retirements <- data.frame(Asset.Type = c(rep("abc", 5), rep("def", 5)), 
                          Year = c(seq(2023, 2027), seq(2023, 2027)), 
                          Num.Retired = c(0, 2, 4, 3, 1, 0, 7, 0, 1,0))

sn <- data.frame(Asset.Type = c(rep("abc", 12), rep("def", 5)),
                 SerialNo = c(seq(from = 58043, by = 1, length.out = 12), seq(4342, by = 1, length.out = 5)))

期望输出示例：

Asset.Type Year SerialNo.Retired
1         abc 2023               NA
2         abc 2024            58043
3         abc 2024            58044
4         abc 2025            58045
5         abc 2025            58046
6         abc 2025            58047
7         abc 2025            58048
8         abc 2026            58049
9         abc 2026            58050
10        abc 2026            58051
11        abc 2027            58052
12        def 2023               NA
13        def 2024             4342
14        def 2024             XXXX
15        def 2024             XXXX
16        def 2024             XXXX
17        def 2024             XXXX
18        def 2024             XXXX
19        def 2024             XXXX
20        def 2025               NA
21        def 2026             4343
22        def 2027               NA

解决方案 #

使用dplyr包可以高效实现需求，代码如下：

library(dplyr)

# 为每个资产类型的序列号添加顺序索引，确保按从小到大分配
sn_indexed <- sn %>%
  group_by(Asset.Type) %>%
  mutate(sn_order = row_number()) %>%
  ungroup()

# 处理退役数据：展开行、匹配序列号、补充虚拟序列号
result <- retirements %>%
  group_by(Asset.Type) %>%
  # 按退役数量展开行，退役数为0时保留1行
  slice(rep(1:n(), times = ifelse(Num.Retired == 0, 1, Num.Retired))) %>%
  # 添加组内分配顺序索引，用于匹配序列号
  mutate(alloc_order = row_number()) %>%
  # 左连接序列号数据
  left_join(sn_indexed, by = c("Asset.Type", "alloc_order" = "sn_order")) %>%
  # 处理序列号显示规则
  mutate(SerialNo.Retired = case_when(
    Num.Retired == 0 ~ NA_character_,
    is.na(SerialNo) ~ "XXXX",
    TRUE ~ as.character(SerialNo)
  )) %>%
  # 保留目标列
  select(Asset.Type, Year, SerialNo.Retired) %>%
  ungroup()

# 查看完整结果
print(result, n = Inf)

代码逻辑说明 #

1. 序列号索引化：给每个资产类型的序列号按顺序添加索引，保证后续分配时遵循从小到大的顺序
2. 行展开：根据Num.Retired的值展开retirements的行，退役数为0时保留1行用于显示NA
3. 匹配序列号：通过组内分配索引与序列号索引的对应关系，匹配对应序列号
4. 规则处理：用case_when处理三种场景：退役数为0显示NA、序列号不足显示"XXXX"、有对应序列号则显示序列号本身

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