在Tidyverse中为员工表添加直属下属数与全部后代数

现有员工表（主键为emp_id），已构建层级关系，需新增两列：

• employee_count：当前员工作为直属上级的员工数量（即boss_name等于当前emp_name的行数）
• empire_count：当前员工所有层级后代的数量（即层级以当前员工的完整层级路径开头的行数）

示例数据 #

library(tidyverse)

employees = tibble(
  emp_id = c(1,2,3,4,5,6,7),
  emp_name = c('BigBoss','MedBoss','MedBoss2','Emp1','Emp2','Emp3','Emp4'),
  boss_name = c('','BigBoss','BigBoss','MedBoss','MedBoss','MedBoss2','MedBoss2'),
  hierarchy = c('','BigBoss','BigBoss','BigBoss>MedBoss','BigBoss>MedBoss','BigBoss>MedBoss2','BigBoss>MedBoss2')
)

数据预览：

# A tibble: 7 × 4
  emp_id emp_name boss_name  hierarchy          
   <dbl> <chr>    <chr>      <chr>              
1      1 BigBoss  ""         ""                 
2      2 MedBoss  "BigBoss"  "BigBoss"          
3      3 MedBoss2 "BigBoss"  "BigBoss"          
4      4 Emp1     "MedBoss"  "BigBoss>MedBoss"  
5      5 Emp2     "MedBoss"  "BigBoss>MedBoss"  
6      6 Emp3     "MedBoss2" "BigBoss>MedBoss2"
7      7 Emp4     "MedBoss2" "BigBoss>MedBoss2"

预期结果：BigBoss和两位MedBoss的employee_count均为2，BigBoss的empire_count为6。

问题分析 #

你尝试的自定义函数单独调用有效，但在mutate中返回全7，原因是函数没有做向量化处理——当传入整个boss_name列时，filter(boss_name==bossname)会匹配所有行，最终返回总行数。

解决方案 #

1. 计算employee_count #

方案一：高效分组统计后连接（推荐大数据场景）

先统计每个上级的直属下属数量，再通过left_join合并回原表：

# 统计各上级的直属下属数
boss_counts <- employees %>%
  filter(boss_name != "") %>%
  count(boss_name, name = "employee_count")

# 合并到原表，空值替换为0
employees %>%
  left_join(boss_counts, by = c("emp_name" = "boss_name")) %>%
  mutate(employee_count = replace_na(employee_count, 0))

方案二：用purrr::map_int逐行处理

通过map_int遍历每个员工的名字，逐个统计下属数量：

employees %>%
  mutate(employee_count = map_int(emp_name, ~nrow(filter(employees, boss_name == .x))))

2. 计算empire_count #

利用hierarchy字段的字符串特征，判断后代的层级路径是否以当前员工的完整层级路径开头：

employees %>%
  # 构造当前员工的完整层级前缀
  mutate(full_hierarchy = ifelse(hierarchy == "", emp_name, str_c(hierarchy, ">", emp_name))) %>%
  rowwise() %>%
  # 统计匹配前缀的后代数量
  mutate(empire_count = sum(str_detect(employees$hierarchy, str_c("^", full_hierarchy, "(>|$)")), na.rm = TRUE)) %>%
  ungroup() %>%
  select(-full_hierarchy)

完整合并代码 #

library(tidyverse)

employees = tibble(
  emp_id = c(1,2,3,4,5,6,7),
  emp_name = c('BigBoss','MedBoss','MedBoss2','Emp1','Emp2','Emp3','Emp4'),
  boss_name = c('','BigBoss','BigBoss','MedBoss','MedBoss','MedBoss2','MedBoss2'),
  hierarchy = c('','BigBoss','BigBoss','BigBoss>MedBoss','BigBoss>MedBoss','BigBoss>MedBoss2','BigBoss>MedBoss2')
)

# 计算employee_count
boss_counts <- employees %>%
  filter(boss_name != "") %>%
  count(boss_name, name = "employee_count")

employees_with_counts <- employees %>%
  left_join(boss_counts, by = c("emp_name" = "boss_name")) %>%
  mutate(employee_count = replace_na(employee_count, 0)) %>%
  # 计算empire_count
  mutate(full_hierarchy = ifelse(hierarchy == "", emp_name, str_c(hierarchy, ">", emp_name))) %>%
  rowwise() %>%
  mutate(empire_count = sum(str_detect(employees$hierarchy, str_c("^", full_hierarchy, "(>|$)")), na.rm = TRUE)) %>%
  ungroup() %>%
  select(-full_hierarchy)

# 查看结果
employees_with_counts

运行后输出：

# A tibble: 7 × 6
  emp_id emp_name boss_name  hierarchy           employee_count empire_count
   <dbl> <chr>    <chr>      <chr>                        <dbl>        <int>
1      1 BigBoss  ""         ""                               2            6
2      2 MedBoss  "BigBoss"  "BigBoss"                        2            2
3      3 MedBoss2 "BigBoss"  "BigBoss"                        2            2
4      4 Emp1     "MedBoss"  "BigBoss>MedBoss"                0            0
5      5 Emp2     "MedBoss"  "BigBoss>MedBoss"                0            0
6      6 Emp3     "MedBoss2" "BigBoss>MedBoss2"               0            0
7      7 Emp4     "MedBoss2" "BigBoss>MedBoss2"               0            0

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