在R中如何基于id条件选择列并计算实际支出值？

示例数据 #

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列：
  • 值为R时，说明decrease列的金额包含代付部分，不是实际支出
  • 值为数字（如2）时，代表该id对应的金额已被报销

需求 #

添加actual_decrease列，规则如下：

• 对于reimbursed_id为R的行，用decrease减去对应报销记录（reference为reimbursed且reimbursed_id等于当前行id）的increase金额，得到实际支出
• 对于reimbursed_id为NA且有decrease值的行，actual_decrease等于decrease
• 其他情况（如报销记录行），actual_decrease为NA

期望结果：

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

解决方案（基于dplyr，无循环） #

通过构建报销金额映射表，再匹配到主数据中计算实际支出，完全基于id列实现，不依赖行号：

library(dplyr)

# 提取报销记录，构建id到报销金额的映射
reimbursement_map <- transactions %>%
  filter(reference == "reimbursed") %>%
  mutate(reimbursed_id = as.integer(reimbursed_id)) %>%
  select(reimbursed_id, reimbursement_amount = increase)

# 计算actual_decrease列
transactions_final <- transactions %>%
  left_join(reimbursement_map, by = c("id" = "reimbursed_id")) %>%
  mutate(
    actual_decrease = case_when(
      reimbursed_id == "R" ~ decrease - reimbursement_amount,
      !is.na(decrease) & is.na(reimbursed_id) ~ decrease,
      TRUE ~ NA_real_
    )
  ) %>%
  select(-reimbursement_amount) # 移除临时列

# 查看结果
print(transactions_final)

代码解释 #

1. 构建报销映射表：从原数据筛选出报销记录，将reimbursed_id转为整数，提取id与对应报销金额的映射关系。
2. 匹配报销金额：用left_join将映射表中的报销金额匹配到主数据对应id的行。
3. 计算实际支出：通过case_when分情况计算：
   • 标记为R的行，用decrease减去匹配到的报销金额
   • 有支出且无报销标记的行，直接取decrease值
   • 其余情况设为NA
4. 清理临时列：移除用于计算的临时reimbursement_amount列

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