基于多级Lookup表扩展表格并重复行的R语言实现需求

问题背景 #

现有如下数据集Data和查找表Lookup，需要将Data中的数据以Level1层级展示：Level1的行直接保留，非Level1的行需根据Lookup表中对应的Level1标识重复，并保留Value1至Value3的数值。

library(dplyr)

Data <- tibble(
           Code = c("A001", "A002", "A003","B001","B002", "C001","D044"),
           CodeLevel = c("Level1", "Level1", "Level1","Level2","Level2", "Level3","Level4"),
           Value1 = 1:7,
           Value2 = 101:107,
           Value3 = 201:207)
           
Lookup <- tibble(
            Level1 = paste0("A",sprintf("%03d", 1:15)),
            Level2 = c(rep("B010",3),rep("B009",2), "B002",rep("B001",2),"B002",rep("B007",4),rep("B008",2)),
            Level3 = c(rep("C006",3),rep("C001",3),rep("C006",2),rep("C001",2), rep("C003",3),rep("C004",2)),
            Level4 = c(rep("D023",5) , rep("D044",10))) 

期望输出 #

最终需要得到如下格式的结果：

Output <- tibble(
            ID = c(paste0("A",sprintf("%03d", 1:3)),rep("A007",2), "A006", "A009", paste0("A",sprintf("%03d", 4:6)),"A009","A010",paste0("A",sprintf("%03d", 6:15))),
            Code = c(paste0("A",sprintf("%03d", 1:3)),rep("B001",2),rep("B002",2), rep("C001",5),rep("D044",10)),
            CodeLevel = c(rep("Level1",3),rep("Level2",4), rep("Level3",5),rep("Level4",10)),
            Value1 = c(1:3,rep(4,2),rep(5,2),rep(6,5),rep(7,10)),   
            Value2 = c(101:103,rep(104,2),rep(105,2),rep(106,5),rep(107,10)),       
            Value3 = c(201:203,rep(204,2),rep(205,2),rep(206,5),rep(207,10)))           
            
Output %>% print(n=nrow(Output))

解决方案代码 #

library(dplyr)
library(tidyr)

# 将Lookup表转为长格式，建立各层级Code与Level1的映射
lookup_long <- Lookup %>%
  pivot_longer(cols = -Level1, names_to = "CodeLevel", values_to = "Code")

# 关联Data与映射表，完成数据展开
Output <- Data %>%
  left_join(lookup_long, by = c("CodeLevel", "Code")) %>%
  # Level1行用自身Code作为ID，其他行用匹配到的Level1值
  mutate(ID = ifelse(CodeLevel == "Level1", Code, Level1)) %>%
  select(ID, Code, CodeLevel, Value1, Value2, Value3) %>%
  # 按层级和Code排序，匹配期望输出顺序
  arrange(match(CodeLevel, c("Level1", "Level2", "Level3", "Level4")), Code)

# 查看完整结果
Output %>% print(n = nrow(Output))

代码说明 #

1. 转换Lookup格式：用pivot_longer把宽格式的Lookup表转成键值对形式，清晰展示每个Level2/Level3/Level4的Code对应的所有Level1值。
2. 关联并展开数据：通过left_join按CodeLevel和Code匹配Data与映射表，自动为非Level1行生成对应Level1的重复行。
3. 处理ID列：Level1行直接复用自身Code作为ID，其他行使用匹配到的Level1值。
4. 调整输出顺序：按层级和Code排序，确保结果结构与期望输出一致。

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