如何在R语言中按客户和物料移除退货对应的正负销售记录对

需求说明 #

需要从销售出库数据中移除满足以下条件的交易行：

• 同一客户（cust）、同一物料（mat）下，存在一正一负的销售记录（sales）
• 负值退货记录的日期晚于正值出库记录的日期
  最终保留未发生退货的出库记录，以及未被抵消的其他记录。

原始数据 #

cust <- c(1, 1, 1, 1, 1,1,1,1,2,2,3,3,4,4,4)
mat <- c(123,123,123,123,123,124,123,123,124,123,125,125,130,124,124)
sales <- c(100.58,100.58,-100.58,-100.58,100.58,10,100,-100,10,100,50,-50,-1,20,20)
date <- c("2021-01-11", "2018-12-04", "2018-12-20", "2018-12-20", "2018-12-13", "2018-12-13", "2019-01-01","2019-01-02","2020-12-24","2021-01-20","2021-12-01", "2021-12-05","2013-01-01","2014-02-02","2015-03-02")
data<-data.frame(cust,mat,sales,date)

解决方案（R语言） #

使用dplyr包进行分组处理，核心思路是按客户、物料、销售金额绝对值分组，匹配可抵消的正负记录并移除：

library(dplyr)

# 转换日期格式并处理数据
result <- data %>%
  # 将字符型日期转为日期格式
  mutate(date = as.Date(date)) %>%
  # 按客户、物料分组，确保同客户同物料的记录在一起
  group_by(cust, mat) %>%
  # 按日期升序排列，保证时间顺序正确
  arrange(date) %>%
  # 新增辅助列：销售金额的符号、绝对值
  mutate(
    sign_sales = sign(sales),
    abs_sales = abs(sales)
  ) %>%
  # 进一步按销售金额绝对值分组，处理同一金额的正负抵消
  group_by(cust, mat, abs_sales) %>%
  mutate(
    # 统计当前分组内的正、负记录数量
    total_pos = sum(sign_sales == 1),
    total_neg = sum(sign_sales == -1),
    # 可抵消的记录对数为正负数量的最小值
    pair_count = min(total_pos, total_neg),
    # 标记需要被抵消的记录：正记录前pair_count个、负记录后pair_count个
    is_offset = ifelse(
      sign_sales == 1, 
      row_number() <= pair_count, 
      row_number() > (total_neg - pair_count)
    )
  ) %>%
  # 取消分组
  ungroup() %>%
  # 过滤掉被抵消的记录
  filter(!is_offset) %>%
  # 移除辅助列，保留原始字段
  select(cust, mat, sales, date) %>%
  # 按客户、物料排序，与示例结果对齐
  arrange(cust, mat)

# 查看结果
print(result)

最终结果 #

# A tibble: 7 × 4
   cust   mat sales date      
  <dbl> <dbl> <dbl> <date>    
1     1   123 100.6 2021-01-11
2     1   124  10   2018-12-13
3     2   123 100   2021-01-20
4     2   124  10   2020-12-24
5     4   124  20   2014-02-02
6     4   124  20   2015-03-02
7     4   130  -1   2013-01-01

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