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)
结果验证
运行上述代码得到的输出,与预期结果完全一致:
| ItemNo | OrderAmount | Date |
|---|---|---|
| 3845 | 320 | 2020-01-21 |
| 3845 | 320 | 2020-01-18 |
| 3245 | 100 | 2020-01-18 |
| 3645 | 230 | 2020-01-18 |
| 3845 | 320 | 2020-01-17 |
| 4045 | 0 | 2020-01-17 |
| 3845 | 320 | 2020-01-17 |
| 3845 | -300 | 2020-01-17 |
| 3245 | 200 | 2020-01-17 |
| 3845 | 320 | 2020-01-15 |
| 4045 | 240 | 2020-01-15 |
| 4045 | 0 | 2020-01-15 |
内容的提问来源于stack exchange,提问作者user13799810
相关产品推荐
相关产品推荐

