R语言中如何处理重复报销场景下的条件列值相减？

示例数据集 #

首先构造符合场景的示例数据：

transactions <- data.frame(
  id = c("E1", "E2", "R1", "R2", "R3"),
  type = c("expense", "expense", "reimbursement", "reimbursement", "reimbursement"),
  reimbursed_id = c("R", "R", "E1", "E1", "E2"),
  decrease = c(100, 200, 0, 0, 0),
  increase = c(0, 0, 30, 40, 50)
)

实现逻辑 #

1. 筛选所有报销记录，按关联的支出ID分组，计算每组的报销金额总和
2. 将原数据集与报销总额数据关联，针对支出行用decrease值减去对应报销总额；报销行的actual_decrease设为0

dplyr实现代码 #

library(dplyr)

# 计算各支出项对应的报销总额
reimburse_totals <- transactions %>%
  filter(type == "reimbursement") %>%
  group_by(reimbursed_id) %>%
  summarise(total_reimbursed = sum(increase))

# 生成actual_decrease列
transactions <- transactions %>%
  left_join(reimburse_totals, by = c("id" = "reimbursed_id")) %>%
  mutate(
    actual_decrease = case_when(
      type == "expense" ~ decrease - coalesce(total_reimbursed, 0),
      TRUE ~ 0
    )
  ) %>%
  select(-total_reimbursed)

base R实现代码 #

如果不依赖dplyr包，可用base R完成：

# 计算各支出项的报销总额
reimburse_totals <- aggregate(
  increase ~ reimbursed_id,
  data = transactions[transactions$type == "reimbursement", ],
  sum
)
colnames(reimburse_totals) <- c("id", "total_reimbursed")

# 合并数据并处理缺失值
transactions <- merge(transactions, reimburse_totals, by = "id", all.x = TRUE)
transactions$total_reimbursed[is.na(transactions$total_reimbursed)] <- 0

# 生成actual_decrease列
transactions$actual_decrease <- ifelse(
  transactions$type == "expense",
  transactions$decrease - transactions$total_reimbursed,
  0
)

# 调整列顺序（可选）
transactions <- transactions[, c("id", "type", "reimbursed_id", "decrease", "increase", "actual_decrease")]

最终结果 #

执行代码后得到的数据集：

transactions
#    id        type reimbursed_id decrease increase actual_decrease
# 1 E1      expense             R      100        0              30
# 2 E2      expense             R      200        0             150
# 3 R1 reimbursement            E1        0       30               0
# 4 R2 reimbursement            E1        0       40               0
# 5 R3 reimbursement            E2        0       50               0

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