如何计算Level 4子类别对应Level 3父类别的价值占比

需求说明 #

需计算Level 4类别对应其Level 3父类别的价值占比，Level 1-3类别的价值占比统一设为100。类别编码规则：01 xxx为Level 1，01.01.01.00 xxx为Level 4。

示例数据框 #

df <- data.frame(Store = c("A", "A", "A", "A", "A", "A", "A", "A", "A", "A", "B", "B", "B", "B", 
                           "B", "B", "B", "B", "B", "B"), 
                 Category = c("01 Fruits & Vegetables", "01.01 Vegetables", "01.01.03 Carrots", "01.01.03.00 Baby Carrots", 
                              "01.01.03.01 Purple Carrots", "01.01.03.01 Sliced Carrots", "01.01.05 Spinach", 
                              "01.01.05.00 Cubes Packed", "01.01.05.01 Choped", "01.01.05.02 Leafs", 
                              "01 Fruits & Vegetables", "01.01 Vegetables", "01.01.03 Carrots", 
                              "01.01.03.00 Baby Carrots", "01.01.03.01 Purple Carrots", "01.01.03.01 Sliced Carrots", 
                              "01.01.05 Spinach", "01.01.05.00 Cubes Packed", "01.01.05.01 Choped", 
                              "01.01.05.02 Leafs"),
                 Value = c(1092030, 519696, 123991, 
                           2116, 8087, 33946, 43059, 7410, 
                           41, 24411, 1289392, 654442, 140990, 
                           11351, 2235, 48679, 64681, 9553, 
                           2921, 36109),
                 Level = c(1,2,3,4,4,4,3,4,4,4))

数据预览：

Store                   Category   Value Level
1      A     01 Fruits & Vegetables 1092030     1
2      A           01.01 Vegetables  519696     2
3      A           01.01.03 Carrots  123991     3
4      A   01.01.03.00 Baby Carrots    2116     4
5      A 01.01.03.01 Purple Carrots    8087     4
6      A 01.01.03.01 Sliced Carrots   33946     4
7      A           01.01.05 Spinach   43059     3
8      A   01.01.05.00 Cubes Packed    7410     4
9      A         01.01.05.01 Choped      41     4
10     A          01.01.05.02 Leafs   24411     4
11     B     01 Fruits & Vegetables 1289392     1
12     B           01.01 Vegetables  654442     2
13     B           01.01.03 Carrots  140990     3
14     B   01.01.03.00 Baby Carrots   11351     4
15     B 01.01.03.01 Purple Carrots    2235     4
16     B 01.01.03.01 Sliced Carrots   48679     4
17     B           01.01.05 Spinach   64681     3
18     B   01.01.05.00 Cubes Packed    9553     4
19     B         01.01.05.01 Choped    2921     4
20     B          01.01.05.02 Leafs   36109     4

错误尝试代码 #

之前的代码存在父类别匹配逻辑错误、分组方式不合理的问题，导致结果错误：

dt1<-df %>%
  mutate(Level3_Category = ifelse(Level == 3, Category, 
                                  gsub("(^\\d{2}\\.\\d{2})(\\..*)?", "\\1", Category, perl = TRUE))) %>%
  group_by(Store, Category, Level3_Category) %>%
  summarise(Value = sum(Value)) %>%
  group_by(Level3_Category) %>%
  mutate(share_percentage = Value / sum(Value) * 100)

期望结果示例 #

正确解法 #

代码实现 #

library(dplyr)

df_result <- df %>%
  # 提取类别开头的数字编码部分，用于匹配父类
  mutate(Category_Code = sub("^(\\d+(\\.\\d+)*).*", "\\1", Category)) %>%
  # 为Level4生成对应的Level3父类编码：去掉最后一段.xx
  mutate(Level3_Code = ifelse(Level == 4, sub("(\\.\\d+)$", "", Category_Code), NA)) %>%
  # 关联同门店下Level3类别的价值作为父类价值
  left_join(df %>% filter(Level == 3) %>% select(Store, Category_Code, Parent_Value = Value),
            by = c("Store", "Level3_Code" = "Category_Code")) %>%
  # 计算价值占比：Level1-3设为100，Level4计算占比并保留两位小数
  mutate(`Value Share %` = case_when(
    Level %in% 1:3 ~ 100,
    Level == 4 ~ round(Value / Parent_Value * 100, 2)
  )) %>%
  # 移除中间辅助列
  select(-Category_Code, -Level3_Code, -Parent_Value)

代码说明 #

1. 提取类别编码：用正则提取Category字段开头的数字编码，简化父类匹配逻辑。
2. 生成父类编码：对Level4类别，去掉编码最后一段，得到对应Level3父类的编码。
3. 关联父类价值：通过左连接获取同门店下Level3类别的价值数据。
4. 计算占比：按Level设置规则，Level1-3直接赋值100，Level4计算占比并保留两位小数。
5. 清理结果：删除中间生成的辅助列，得到最终结构的结果。

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