R语言如何为收支序列数据框新增追踪金额和追踪引用列

前置处理（适配原始矩阵格式数据） #

如果你是用cbind生成的字符矩阵，先转换为标准数据框：

# 原始矩阵转数据框，修正字段类型
df <- as.data.frame(df, stringsAsFactors = FALSE)
df$Seq <- as.integer(df$Seq)
df$Amount <- as.numeric(df$Amount)

如果是从头构造数据，可直接生成标准数据框：

Seq <- c(1,2,3,4,5,6,7,8,9,10,11)
Direction <- c("INCOMING","OUTGOING", "INCOMING", "OUTGOING", "OUTGOING","INCOMING", "INCOMING", "OUTGOING","INCOMING", "OUTGOING", "OUTGOING")
Amount <- c(500,200,300,400,200,300,200,100,200,100,50)
df <- data.frame(Seq, Direction, Amount, stringsAsFactors = FALSE)

核心计算代码 #

# 1. 确保按Seq升序排列
df <- df[order(df$Seq), ]

# 2. 计算Trace_Reference追踪引用列
# 逻辑：cumsum对逻辑值按1/0累加，每遇到INCOMING编号+1，后续OUTGOING沿用当前编号
df$Trace_Reference <- as.character(cumsum(df$Direction == "INCOMING"))

# 3. 计算Traced_Amount追踪金额列
# 逻辑：INCOMING行留空，OUTGOING行取当前行Amount值
df$Traced_Amount <- ifelse(df$Direction == "INCOMING", "", as.character(df$Amount))

# 查看结果
print(df)

输出结果验证 #

运行后得到的结果完全匹配需求：

Seq Direction Amount Trace_Reference Traced_Amount
1    1  INCOMING    500               1              
2    2  OUTGOING    200               1           200
3    3  INCOMING    300               2              
4    4  OUTGOING    400               2           400
5    5  OUTGOING    200               2           200
6    6  INCOMING    300               3              
7    7  INCOMING    200               4              
8    8  OUTGOING    100               4           100
9    9  INCOMING    200               5              
10  10  OUTGOING    100               5           100
11  11  OUTGOING     50               5            50

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