如何有条件地选择一列，并从 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),
                       reimbursed_id = c(NA, "R", "R", NA, NA, 2, 3))
# # A tibble: 7 × 7
#      id   day   sent_to         received_from reference      decrease  increase reimbursed_id
#    <int>  <chr> <chr>           <chr>         <chr>          <dbl>     <dbl>    <chr>
# 1     1   day1  NA              ATM           add_cash       NA        50       NA
# 2     2   day2  Garden Cinema   NA            cinema_tickets 10.8      NA       R
# 3     3   day3  Pasta House     NA            meal           12.5      NA       R
# 4     4   day4  NA              Sarah         gift           NA        30       NA
# 5     5   day5  Blue Superstore NA            shopping       15.2      NA       NA
# 6     6   day6  Jane            NA            reimbursed     NA        5.4      2
# 7     7   day7  Joe             NA            reimbursed     NA        7.25     3

解释reimbursed_id column:

• R表示减少列中的值并不代表用户的实际支出，因为它包括代表某人支付的金额
• 2（或任何数字）代表id用户得到偿还（归还借入金额）

期望的结果：

我想向该数据集添加一个“actual_decrease”列，该列本质上是查看reimbursed_id数值栏，收集报销金额increase列并从中的值中减去它decrease对于各自的id's.

我不确定如何更好地解释这一点，所以这是期望的结果：

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

除了一个非常复杂的函数之外，我想不出任何可以帮助解决这个问题的函数ifelse()语句，也许还有一个 for 循环（但我更愿意避免循环）。

请注意，您的答案最好不涉及行号，而是涉及id列，因为对于我的实际数据集，这些值不对应。

任何意见都表示赞赏:)

解决此问题的一种选择是通过联接。基本上，我首先通过过滤“报销”交易来创建一个辅助 df，然后将其连接到原始 df 以最终计算actual_decrease:

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

transactions_help <- transactions |> 
  filter(reference == "reimbursed") |> 
  select(reimbursed_decrease = increase, reimbursed_id) |> 
  mutate(reimbursed_id = as.numeric(reimbursed_id))

transactions <- transactions |> 
  left_join(transactions_help, by = c("id" = "reimbursed_id")) |> 
  replace_na(list(reimbursed_decrease = 0)) |> 
  mutate(actual_decrease = decrease - reimbursed_decrease)

transactions
#> # A tibble: 7 × 10
#>      id day   sent_to    recei…¹ refer…² decre…³ incre…⁴ reimb…⁵ reimb…⁶ actua…⁷
#>   <dbl> <chr> <chr>      <chr>   <chr>     <dbl>   <dbl> <chr>     <dbl>   <dbl>
#> 1     1 day1  <NA>       ATM     add_ca…    NA     50    <NA>       0      NA   
#> 2     2 day2  Garden Ci… <NA>    cinema…    10.8   NA    R          5.4     5.4 
#> 3     3 day3  Pasta Hou… <NA>    meal       12.5   NA    R          7.25    5.25
#> 4     4 day4  <NA>       Sarah   gift       NA     30    <NA>       0      NA   
#> 5     5 day5  Blue Supe… <NA>    shoppi…    15.2   NA    <NA>       0      15.2 
#> 6     6 day6  Jane       <NA>    reimbu…    NA      5.4  2          0      NA   
#> 7     7 day7  Joe        <NA>    reimbu…    NA      7.25 3          0      NA   
#> # … with abbreviated variable names ¹​received_from, ²​reference, ³​decrease,
#> #   ⁴​increase, ⁵​reimbursed_id, ⁶​reimbursed_decrease, ⁷​actual_decrease