r - 如何将一列添加到数据集中，从 R 中的一列中添加值并从另一列中减去值？

Question

这是我拥有的数据类型的一个小示例:

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))

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

我想在此数据集中添加一个“余额”列，其中:

• 第 1 行:以 50 开头
• 第 2 行:之前的余额金额 + 增加 - 减少
• 第 3 行等:与第 2 行公式相同

我自己一直在努力做到这一点，因为我不知道是否有任何现有函数可以帮助进行此类数据操作。我想到的唯一函数是 dplyr::lag() ，但我不确定如何使用它。

感谢任何帮助:)

Answer 1

您可以首先创建change列，然后使用purrr::accumulate创建您的balance列:

library(dplyr, warn = FALSE)
library(purrr)

transactions |> 
  mutate(change = coalesce(increase, -decrease),
         balance = accumulate(change, ~ .x + .y))
#> # A tibble: 7 × 9
#>      id day   sent_to         received_…¹ refer…² decre…³ incre…⁴ change balance
#>   <int> <chr> <chr>           <chr>       <chr>     <dbl>   <dbl>  <dbl>   <dbl>
#> 1     1 day1  <NA>            ATM         add_ca…    NA     50     50       50  
#> 2     2 day2  Garden Cinema   <NA>        cinema…    10.8   NA    -10.8     39.2
#> 3     3 day3  Pasta House     <NA>        meal       12.5   NA    -12.5     26.7
#> 4     4 day4  <NA>            Sarah       gift       NA     30     30       56.7
#> 5     5 day5  Blue Superstore <NA>        shoppi…    15.2   NA    -15.2     41.4
#> 6     6 day6  Jane            <NA>        reimbu…    NA      5.4    5.4     46.8
#> 7     7 day7  Joe             <NA>        reimbu…    NA      7.25   7.25    54.1
#> # … with abbreviated variable names ¹​received_from, ²​reference, ³​decrease,
#> #   ⁴​increase