使用dplyr在R中识别跨年度重复WorkID及排查统计差异

数据示例 #

以下是用于复现问题逻辑的示例数据：

workID <- c("A1", "A1", "B1", "C1", "C1", "C1", "D1", "A1")
Employee <- c(12, 22, 31, 90, 108, 17, 23, 56)
FY <- c(2019, 2019, 2019, 2020, 2020, 2020, 2021, 2021)
Office <- c("HQ", "HQ", "Tulsa", "Dallas", "Dallas", "Dallas", "Cleveland", "HQ")
Hours <- c(100, 200, 100, 150, 300, 275, 600, 700)

data <- data.frame(workID, Employee, FY, Office, Hours)

生成的数据集如下：

workID Employee   FY    Office Hours
1     A1       12 2019        HQ   100
2     A1       22 2019        HQ   200
3     B1       31 2019     Tulsa   100
4     C1       90 2020    Dallas   150
5     C1      108 2020    Dallas   300
6     C1       17 2020    Dallas   275
7     D1       23 2021 Cleveland   600
8     A1       56 2021        HQ   700

实际业务场景与统计矛盾 #

实际数据集包含260万行、更多字段，业务规则要求同一WorkID不能跨多个财年（FY）使用（比如示例中的A1同时出现在2019和2021财年，属于违规情况）。

在做汇总统计时，出现了WorkID统计数量不一致的问题：

# 统计符合条件的唯一WorkID数量
inspections <- consolidated %>% filter(Operation.Code.Desc %in% inspection)
num_inspections <- length(unique(inspections$Work.Accomplishment.ID))
# 结果：79,521

# 按分组汇总后，累加各组唯一WorkID数量
inspections2 <- consolidated %>% 
  select(Operation.Date.FY,
         Work.Accomplishment.ID, 
         Program.Area.Abbrv,
         Operation.Code.Desc, 
         Total.Hours.Spent) %>%
  filter(Operation.Code.Desc %in% inspection) %>%
  group_by(Operation.Date.FY, Program.Area.Abbrv, Operation.Code.Desc) %>%
  summarise(personnel=n(), 
            Total.Time = sum(Total.Hours.Spent),
            ops = n_distinct(Work.Accomplishment.ID))
sum(inspections2$ops)
# 结果：79,805 —— 为何和79,521不匹配？

总工时统计一致，但WorkID数量存在差异，核心原因是同一WorkID跨多个分组重复统计。

原因分析与解决方法 #

核心原因 #

部分Work.Accomplishment.ID同时属于多个分组（比如同一个WorkID出现在不同的Operation.Date.FY或Program.Area.Abbrv下），分组汇总时每个分组都会单独统计该WorkID，累加后就会出现重复计数，导致总数大于全局唯一值的数量。

解决方法 #

1. 先清理违规跨财年的WorkID

先识别并移除（或标记）跨多个FY的WorkID，再进行统计，确保数据符合业务规则：

# 识别跨多个FY的WorkID
cross_fy_workids <- consolidated %>%
  filter(Operation.Code.Desc %in% inspection) %>%
  group_by(Work.Accomplishment.ID) %>%
  summarise(num_fy = n_distinct(Operation.Date.FY)) %>%
  filter(num_fy > 1) %>%
  pull(Work.Accomplishment.ID)

# 生成符合规则的清理后数据
cleaned_inspections <- consolidated %>%
  filter(Operation.Code.Desc %in% inspection,
         !Work.Accomplishment.ID %in% cross_fy_workids)

# 重新统计全局唯一数量
num_cleaned <- length(unique(cleaned_inspections$Work.Accomplishment.ID))

# 分组汇总后累加
cleaned_inspections2 <- cleaned_inspections %>%
  select(Operation.Date.FY, Work.Accomplishment.ID, Program.Area.Abbrv, Operation.Code.Desc, Total.Hours.Spent) %>%
  group_by(Operation.Date.FY, Program.Area.Abbrv, Operation.Code.Desc) %>%
  summarise(personnel=n(), 
            Total.Time = sum(Total.Hours.Spent),
            ops = n_distinct(Work.Accomplishment.ID))

sum(cleaned_inspections2$ops)
# 此时总数与num_cleaned完全一致

2. 保留所有数据但避免重复计数

如果不需要清理数据，只是想让累加结果匹配全局唯一数量，需要定义规则让每个WorkID仅被计入一个分组（比如计入最早的FY分组）：

# 按WorkID和FY排序，取每个WorkID的首次出现记录
workid_first_group <- consolidated %>%
  filter(Operation.Code.Desc %in% inspection) %>%
  arrange(Work.Accomplishment.ID, Operation.Date.FY) %>%
  distinct(Work.Accomplishment.ID, .keep_all = TRUE)

# 基于首次出现的分组进行汇总
summary_first_group <- workid_first_group %>%
  group_by(Operation.Date.FY, Program.Area.Abbrv, Operation.Code.Desc) %>%
  summarise(ops = n_distinct(Work.Accomplishment.ID))

sum(summary_first_group$ops)
# 结果等于全局唯一WorkID数量

验证重复计数的WorkID #

可以用以下代码找出导致差异的重复统计WorkID：

# 找出在多个分组中出现的WorkID
duplicated_workids <- consolidated %>%
  filter(Operation.Code.Desc %in% inspection) %>%
  group_by(Work.Accomplishment.ID) %>%
  summarise(num_groups = n_distinct(paste(Operation.Date.FY, Program.Area.Abbrv, Operation.Code.Desc, sep = "-"))) %>%
  filter(num_groups > 1)

# 这些WorkID的数量就是两个统计结果的差值：79805 - 79521 = 284

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