如何在R语言中为数据集添加余额列：基于增减列计算累计值

数据集示例 #

transactions <- tibble(id = seq(1:7),
                       day = paste(rep("day", each = 7), seq(1:7), sep = ""),
                       sent_to = c(NA, "Garden Cinema", "Pasta House", NA, "Blue Superstore", "Jane", "Joe"),
                       received_from = c("ATM", NA, NA, "Sarah", NA, NA, NA),
                       reference = c("add_cash", "cinema_tickets", "meal", "gift", "shopping", "reimbursed", "reimbursed"),
                       decrease = c(NA, 10.8, 12.5, NA, 15.25, NA, NA),
                       increase = c(50, NA, NA, 30, NA, 5.40, 7.25))

需求 #

• 第1行balance列初始值为50
• 第2行及以后：上一行余额 + 当前行increase值 - 当前行decrease值

解决方案 #

可以用dplyr的lag()函数结合条件判断实现，同时先处理increase和decrease列的NA值（替换为0，避免计算出错）：

library(dplyr)

transactions <- transactions %>%
  # 将NA替换为0，确保计算正常
  mutate(
    increase = coalesce(increase, 0),
    decrease = coalesce(decrease, 0)
  ) %>%
  # 计算余额列
  mutate(
    balance = case_when(
      id == 1 ~ 50,
      TRUE ~ lag(balance) + increase - decrease
    )
  )

结果说明 #

运行后得到的数据集如下：

# # A tibble: 7 × 8
#      id day   sent_to         received_from reference      decrease increase balance
#    <int> <chr> <chr>           <chr>         <chr>          <dbl>    <dbl>    <dbl>
# 1     1 day1  NA              ATM           add_cash           0       50      50.0
# 2     2 day2  Garden Cinema   NA            cinema_tickets    10.8      0      39.2
# 3     3 day3  Pasta House     NA            meal              12.5      0      26.7
# 4     4 day4  NA              Sarah         gift               0       30      56.7
# 5     5 day5  Blue Superstore NA            shopping          15.2      0      41.45
# 6     6 day6  Jane            NA            reimbursed         0        5.4    46.85
# 7     7 day7  Joe             NA            reimbursed         0        7.25   54.10

关键细节解释 #

• coalesce(increase, 0)：简洁地将increase列的所有NA值替换为0，decrease列同理，避免NA参与算术运算导致结果异常。
• lag(balance)：获取上一行的balance值，因为dplyr的mutate是按行顺序执行的，所以后续行可以直接引用已计算好的上一行余额。
• case_when：明确指定第一行的初始余额，后续行按规则计算。

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