R语言多条件循环实现LIFO资金流追踪生成REF标识列

逻辑思路 #

• 用列表模拟后进先出的栈结构，栈中每个元素存储对应IN记录的编号和剩余未抵扣余额
• 逐行遍历数据框：
  • 遇到IN记录时，按出现顺序生成递增编号，将编号和金额存入栈顶，当前行REF直接设为该编号
  • 遇到OUT记录时，从栈顶开始依次抵扣：
    1. 若栈顶余额足够覆盖当前流出金额，扣除对应余额，记录用到的IN编号，余额为0时弹出栈顶
    2. 若栈顶余额不足，扣除全部栈顶余额后弹出栈顶，继续用下一层栈顶抵扣，直到流出金额全部抵扣完成
    3. 将用到的IN编号升序排序后用分号拼接，作为当前行的REF值
• 浮点计算加入1e-6的容差，避免金额精度问题导致的逻辑错误

完整代码 #

# 构造原始数据框
Seq<-c(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31)
Direction<-c("IN","IN", "OUT", "IN", "OUT", "IN", "OUT", "OUT", "OUT", "OUT", "IN", "OUT", "OUT", "OUT", "OUT", "OUT", 
             "OUT", "OUT", "OUT", "OUT", "OUT", "OUT", "OUT", "OUT", "IN", "OUT", "OUT", "OUT", "IN", "OUT", "OUT", "OUT")
Amount<-c(0, 3000, 566, 600, 550, 25500,5500, 515, 3500, 3000, 100500, 4000, 515, 25500, 50510, 1560, 5869.43, 4850, 
          15500, 2000, 1367, 3083.41, 3500, 2500, 3000, 3000, 950, 516, 5500, 1500, 3500, 1400)
df<-data.frame(Seq, Direction, Amount)

# 初始化变量
in_counter <- 0
stack <- list()
df$REF <- NA_character_

# 逐行处理
for (i in seq_len(nrow(df))) {
  current_row <- df[i, ]
  if (current_row$Direction == "IN") {
    in_counter <- in_counter + 1
    df$REF[i] <- as.character(in_counter)
    stack <- append(stack, list(list(ref = in_counter, balance = current_row$Amount)))
  } else {
    remain_amount <- current_row$Amount
    used_refs <- c()
    # 循环抵扣直到金额付清或无可用流入
    while (remain_amount > 1e-6 && length(stack) > 0) {
      top_index <- length(stack)
      top_in <- stack[[top_index]]
      if (top_in$balance >= remain_amount - 1e-6) {
        used_refs <- c(used_refs, top_in$ref)
        stack[[top_index]]$balance <- top_in$balance - remain_amount
        remain_amount <- 0
        # 余额耗尽则弹出栈
        if (stack[[top_index]]$balance < 1e-6) {
          stack <- stack[-top_index]
        }
      } else {
        used_refs <- c(used_refs, top_in$ref)
        remain_amount <- remain_amount - top_in$balance
        stack <- stack[-top_index]
      }
    }
    # 升序排序后拼接，匹配示例格式
    df$REF[i] <- paste(sort(used_refs), collapse = ";")
  }
}

# 查看结果
print(df, digits = 10)

输出验证 #

运行上述代码得到的结果和你提供的预期输出完全一致，REF列的拼接规则、编号顺序都符合要求。

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