如何移除数据中的抵消值记录并保留重复及其他有效记录？

我的数据中存在重复记录和反向抵消记录，需要删除所有反向抵消的记录，保留其余记录（包括重复记录）。例如以下数据：

Date       ID         Amount
1/1/2023   1234        $100.00
1/1/2023   1234        $100.00
1/1/2023   1234        -$100.00
1/3/2023   2345        $150.00
1/5/2023   3456        $200.00
1/6/2023   4444        $300.00
1/6/2023   4444        $300.00
1/6/2023   4444        $300.00
1/6/2023   4444        -$300.00

最终需要保留：ID 1234的1条记录、ID 2345的1条记录、ID 3456的1条记录，以及ID 4444的2条记录（含1条重复）。

我尝试过用dplyr的lag/lead方法，但无法解决问题，代码如下：

library(dplyr)

df%>%
    group_by(Date, ID) %>%
    mutate(diff = VALUE + lag(VALUE),
           diff2 = VALUE + lead(VALUE)) %>%
    mutate_at(vars(diff:diff2), funs(coalesce(., 1))) %>%
    filter((diff !=0 & diff2 !=0))  %>%
    select(-diff, -diff2)

可行解决方案 #

lag/lead方法的局限在于依赖记录的相邻顺序，且只能处理相邻的抵消对，无法覆盖非相邻的情况。可以通过以下逻辑处理：

1. 先清洗金额列，转换为数值类型以便计算
2. 按日期、ID、金额正负分组，给每组内的记录编序号，让成对的正负记录匹配相同序号
3. 过滤掉那些序号组内同时存在正负的记录（即抵消对），保留剩余记录

具体代码：

library(dplyr)

# 1. 清洗数据，将Amount转换为数值
df_clean <- df %>%
  mutate(Amount = as.numeric(gsub("\\$|,", "", Amount)))

# 2. 分组匹配抵消对并过滤
result <- df_clean %>%
  # 按日期、ID、金额正负分组，给每组记录编行号
  group_by(Date, ID, sign(Amount)) %>%
  mutate(row_num = row_number()) %>%
  ungroup() %>%
  # 按日期、ID、行号分组，过滤掉组内有2条记录的（一正一负的抵消对）
  group_by(Date, ID, row_num) %>%
  filter(n() != 2) %>%
  ungroup() %>%
  # 移除辅助列，将Amount转回原格式
  select(-row_num, -`sign(Amount)`) %>%
  mutate(Amount = ifelse(Amount >= 0, 
                         paste0("$", sprintf("%.2f", Amount)), 
                         paste0("-$", sprintf("%.2f", abs(Amount)))))

运行后得到的结果符合需求：

• ID 1234保留1条$100.00记录
• ID 4444保留2条$300.00记录
• 其他无抵消的ID保留全部记录

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