如何在R中实现带条件的分组求和：Central区域排除Program B

需求描述 #

需要对数据表中的value列按location和date分组求和，但仅当location为"Central"时，需排除program为"B"的行。

常规分组求和代码：

data <- file %>%
  group_by(location, date) %>%
  summarize(value = sum(value))

尝试的错误代码：

data <- file %>%
  group_by(location, date) %>%
  summarize(value = 
              case_when(location == "Central" ~ filter(program != "B")),
            TRUE ~ sum(value)
  )

可复现数据：

structure(list(pid = c(123, 123, 123, 123, 123, 123, 123, 
123, 123, 123), program = c("A", "A", 
"A", "A", "A", 
"A", "A", "A", 
"A", "A"), location = c("Central", 
"Central", "Central", "Central", "Central", "Central", "Central", 
"Central", "Central", "Central"), locationid = c("123-Central", 
"123-Central", "123-Central", "123-Central", "123-Central", 
"123-Central", "123-Central", "123-Central", "123-Central", 
"123-Central"), date = structure(c(1302480000, 1305072000, 1307750400, 
1310342400, 1313020800, 1315699200, 1318291200, 1323561600, 1326240000, 
1328918400), tzone = "UTC", class = c("POSIXct", "POSIXt")), 
    value = c(37207.43, -56936.95, -52871, 6980.05, 10703.16, 
    4006.1, 6505.3, 9661.29, 6897.26, 7212.87)), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))

错误分析 #

你写的代码里，case_when无法直接嵌套filter函数——filter是用来筛选整行数据的，而case_when需要返回单个数值（这里是求和结果），两者逻辑不匹配，因此会报错。

修正方案 #

有两种简洁的实现方式：

方法1：先条件过滤再分组求和 #

先剔除location为"Central"且program为"B"的行，再执行常规分组求和，逻辑直观清晰：

data <- file %>%
  # 排除符合"Central地区且program为B"条件的行
  filter(!(location == "Central" & program == "B")) %>%
  group_by(location, date) %>%
  summarize(value = sum(value), .groups = "drop")

方法2：在summarize中用条件求和 #

如果不想提前过滤行，可以在sum函数内加入条件判断，仅对符合要求的value进行求和：

data <- file %>%
  group_by(location, date) %>%
  summarize(
    value = sum(
      # 当location是Central时，跳过program为B的value；否则累加所有value
      if_else(location == "Central" & program == "B", 0, value)
    ),
    .groups = "drop"
  )

也可以用case_when结合sum实现：

data <- file %>%
  group_by(location, date) %>%
  summarize(
    value = sum(case_when(
      location == "Central" ~ if_else(program != "B", value, 0),
      TRUE ~ value
    )),
    .groups = "drop"
  )

验证说明 #

你提供的测试数据中所有program均为"A"，因此两种方法运行后都会得到每个date对应的value直接求和结果（无需要排除的行）。后续若加入program为"B"的Central地区数据，代码会自动排除这些行的value参与求和。

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