如何用dplyr高效实现跨区域多数据集柑橘类数值减法

步骤1：先搞两个示例数据集 #

先模拟你提到的df1和df2，方便你直接复制测试：

library(dplyr)

# df1：含合并柑橘类的水果统计
df1 <- tibble(
  region = c("华东", "华东", "华东", "华南", "华南", "华南"),
  fruit_type = c("柑橘类（合并）", "苹果", "香蕉", "柑橘类（合并）", "苹果", "葡萄"),
  count1 = c(150, 80, 60, 200, 90, 70),
  gtotal1 = c(3000, 1600, 1200, 4000, 1800, 1400)
)

# df2：细分柑橘类的统计
df2 <- tibble(
  region = c("华东", "华东", "华东", "华南", "华南"),
  citrus_variety = c("蜜橘", "橙子", "柚子", "橙子", "沃柑"),
  count2 = c(50, 40, 30, 60, 70),
  gtotal2 = c(1000, 800, 600, 1200, 1400)
)

步骤2：汇总df2各区域的柑橘类总和 #

先把df2按区域分组，算出每个区域柑橘类的count和gtotal总和：

citrus_summary <- df2 %>%
  group_by(region) %>%
  summarize(
    count_sub = sum(count2),
    gtotal_sub = sum(gtotal2),
    .groups = "drop"  # 取消分组，避免后续关联出问题
  )

步骤3：关联数据并调整数值生成df3 #

把df1和刚才的汇总表按区域关联，然后只对柑橘类的行做减法，其他行保持原样：

df3 <- df1 %>%
  left_join(citrus_summary, by = "region") %>%
  mutate(
    # 对柑橘类行做数值调整，非柑橘类保持原数值
    count_final = if_else(fruit_type == "柑橘类（合并）", count1 - count_sub, count1),
    gtotal_final = if_else(fruit_type == "柑橘类（合并）", gtotal1 - gtotal_sub, gtotal1)
  ) %>%
  # 保留需要的列，去掉临时关联的汇总列
  select(region, fruit_type, count_final, gtotal_final) %>%
  # 可以重命名列和原df1一致，方便后续使用
  rename(count1 = count_final, gtotal1 = gtotal_final)

关键注意点 #

• 确保df1里的柑橘类标识统一，比如如果有多个类似“柑橘类”“柑橘（合并）”的写法，要先统一（比如用mutate(fruit_type = str_replace(fruit_type, "柑橘.*", "柑橘类（合并）"))）
• 区域列的类型和内容要完全匹配，比如都是字符型，没有“华东”和“华东地区”这种差异，否则关联会失败
• 如果df1里有多个需要调整的类别，只要把if_else的判断条件改成%in% c("类别1", "类别2")就行，比如if_else(fruit_type %in% c("柑橘类（合并）", "柑橘类混装"), count1 - count_sub, count1)

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