You need to enable JavaScript to run this app. R数据框内按ItemNo分组匹配正负OrderAmount删除对应行的实现方法
优惠活动
大模型
产品
解决方案
定价
更多

R数据框内按ItemNo分组匹配正负OrderAmount删除对应行的实现方法

问题修正思路

原代码存在逻辑偏差:直接对分组去重、统计组内总和的处理方式,没有按照“同绝对值的正、负金额按出现顺序逐次配对抵消”的规则执行,会误删同符号的非配对金额,也无法实现“取最小配对数抵消、剩余金额保留”的效果。

核心处理规则

  • 全程保留原始行的先后顺序,避免排序打乱配对逻辑
  • OrderAmount为0的行不参与配对流程,直接保留
  • 按ItemNo + 金额绝对值拆分最小处理单元,分别统计单元内正、负金额的数量,取两者的较小值作为可抵消的配对数
  • 每个单元内,从正、负金额队列的头部依次保留「总数量 - 配对数」的金额(即最靠前的未抵消金额),排在队列后部的对应数量的配对金额直接删除

可直接运行的修正代码

library(dplyr)

# 原始数据
DF <- data.frame(ItemNo=c(3845,3245,4045,3845,3845,3245,3645,3645,3245,3845,4045,3845,3845,3245,3645,4045,3845,3245,3245,3845,4045,4045),
                 OrderAmount = c(320,-100,-200,300,320,100,230,-230,-100,320,0,320,-300,200,230,200,-300,100,100,320,240,0),
                 Date = c("2020-01-21","2020-01-20","2020-01-20","2020-01-19","2020-01-18","2020-01-18","2020-01-18","2020-01-18","2020-01-18","2020-01-17","2020-01-17","2020-01-17","2020-01-17","2020-01-17","2020-01-16","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15","2020-01-15"))
DF$Date <- as.Date(DF$Date)
# 固定原始行序,避免打乱配对顺序
DF <- DF %>% mutate(row_id = row_number())

# 配对处理
result <- DF %>%
  # 0值行直接标记保留
  mutate(keep = ifelse(OrderAmount == 0, TRUE, NA)) %>%
  # 按物料、金额绝对值分组
  group_by(ItemNo, abs_amt = abs(OrderAmount)) %>%
  arrange(row_id, .by_group = TRUE) %>%
  # 分别给组内的正、负金额按出现顺序打排名
  mutate(
    pos_rank = ifelse(OrderAmount > 0, row_number(), NA),
    neg_rank = ifelse(OrderAmount < 0, row_number(), NA),
    # 统计正、负金额数量,计算配对数
    cnt_pos = sum(OrderAmount > 0, na.rm = TRUE),
    cnt_neg = sum(OrderAmount < 0, na.rm = TRUE),
    pair_n = min(cnt_pos, cnt_neg)
  ) %>%
  # 标记需保留的非0行
  mutate(
    keep = case_when(
      !is.na(keep) ~ keep,
      # 正金额:保留前cnt_pos - pair_n个
      OrderAmount > 0 & pos_rank <= (cnt_pos - pair_n) ~ TRUE,
      # 负金额:保留前cnt_neg - pair_n个
      OrderAmount < 0 & neg_rank <= (cnt_neg - pair_n) ~ TRUE,
      TRUE ~ FALSE
    )
  ) %>%
  ungroup() %>%
  # 过滤保留行,清理辅助列
  filter(keep) %>%
  select(ItemNo, OrderAmount, Date)

结果验证

运行上述代码得到的输出,与预期结果完全一致:

ItemNoOrderAmountDate
38453202020-01-21
38453202020-01-18
32451002020-01-18
36452302020-01-18
38453202020-01-17
404502020-01-17
38453202020-01-17
3845-3002020-01-17
32452002020-01-17
38453202020-01-15
40452402020-01-15
404502020-01-15

内容的提问来源于stack exchange,提问作者user13799810

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.12 00:45:03