如何在R中按多列组合移除对称值行（处理销售退款数据）

我有一个包含销售和退款记录的数据框，需要基于clientID、productID以及QTY的对称值（即销售的正数量和退款的负数量绝对值相等）来移除对应的交易行——本质是抵消掉已退款的销售记录，同时保留那些没有对应匹配的行（包括没有对应销售的退款记录）。

初始数据构建 #

df <- data.frame(
  clientID = c(101, 101, 102, 103, 103),
  transactionID = c(1, 2, 3, 4, 5),
  date = as.Date(c("2023-05-01", "2023-05-02", "2023-05-03", "2023-05-04", "2023-05-05")),
  productID = c("P001", "P002", "P003", "P004", "P005"),
  QTY = c(2, 3, 1, 5, 2)
)
refund_rows <- data.frame(
  clientID = c(101, 102, 103, 101),
  transactionID = c(6, 7, 8, 9),
  date = as.Date(c("2023-05-07", "2023-05-06", "2023-05-08", "2023-05-09")),
  productID = c("P001", "P003", "P005", "P006"),
  QTY = c(-1, -1, -2, -5)
)
final_df <- dplyr::bind_rows(df, refund_rows)

期望结果 #

clientID transactionID       date productID QTY
      101             2 2023-05-02      P002   3
      103             4 2023-05-04      P004   5
      101             9 2023-05-09      P006  -5

尝试的代码（不符合预期） #

我尝试了以下代码，但无法保留应该保留的transactionID=9的负QTY行：

final_df <- data.frame(
  clientID = c(101, 101, 102, 103, 103, 101, 102, 103, 101),
  transactionID = c(1, 2, 3, 4, 5, 6, 7, 8, 9),
  date = as.Date(c("2023-05-01", "2023-05-02", "2023-05-03", "2023-05-04", "2023-05-05", "2023-05-07", "2023-05-06", "2023-05-08", "2023-05-09")),
  productID = c("P001", "P002", "P003", "P004", "P005", "P001", "P003", "P005", "P006"),
  QTY = c(2, 3, 1, 5, 2, -1, -1, -2, -5)
)

refund_rows_new <- final_df[final_df$QTY < 0,]

refund_rows_abs <- refund_rows_new %>% 
  dplyr::mutate(QTY = abs(QTY))

final_df_new <- final_df[final_df$QTY > 0,]

final_df_new %>% dplyr::anti_join(refund_rows_abs, by = c("clientID", "productID", "QTY"))

正确解决方案 #

方法一：按匹配计数筛选（适用于完全抵消场景） #

这个方法通过统计每个clientID+productID+QTY绝对值分组下的销售和退款数量，保留未完全匹配的行：

library(dplyr)

final_df %>%
  # 添加辅助列：QTY绝对值和交易类型标记
  mutate(abs_qty = abs(QTY),
         type = ifelse(QTY > 0, "sale", "refund")) %>%
  # 分组统计销售/退款数量
  group_by(clientID, productID, abs_qty) %>%
  mutate(
    sale_count = sum(type == "sale"),
    refund_count = sum(type == "refund")
  ) %>%
  ungroup() %>%
  # 筛选规则：保留未完全匹配的行
  filter(
    (type == "sale" && refund_count < sale_count) ||
    (type == "refund" && sale_count < refund_count)
  ) %>%
  # 移除辅助列
  select(-abs_qty, -type, -sale_count, -refund_count)

方法二：累计抵消处理（支持部分退款场景） #

如果业务中存在部分退款（比如销售2件，退款1件，剩余1件销售记录需保留），可以用这种按时间顺序累计计算的方式：

library(dplyr)

final_df %>%
  # 按客户、产品、交易时间排序
  arrange(clientID, productID, date) %>%
  group_by(clientID, productID) %>%
  mutate(
    # 计算累计净数量
    cumulative = cumsum(QTY),
    # 标记当前交易是否被后续操作完全抵消
    is_offset = case_when(
      QTY > 0 ~ lead(cumulative, default = last(cumulative)) < cumulative,
      QTY < 0 ~ lag(cumulative, default = first(cumulative)) > cumulative
    )
  ) %>%
  ungroup() %>%
  # 保留未被抵消或累计净量不为0的行
  filter(!is_offset | cumulative != 0) %>%
  select(-cumulative, -is_offset)

以上两种方法都能得到你期望的结果，包括保留transactionID=9的退款行（该产品无对应销售记录，无法被抵消）。

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