在R语言中依据reimbursed_id标签计算actual_decrease列

示例数据集 #

library(dplyr)

DF <- structure(list(id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
13, 14, 15, 16, 17, 18, 19), day = c("day1", "day2", "day3", 
"day4", "day5", "day6", "day6", "day7", "day8", "day9", "day10", 
"day10", "day11", "day12", "day13", "day14", "day14", "day14", 
"day14"), sent_to = c(NA, NA, "Blue Superstore", "Garden Cinema", 
"Pasta House", NA, NA, "Pizzaria", NA, "Ice Palace", NA, NA, 
"Shoes Centre", "Dreams Dessert", NA, "Chicken World", "Art Gallery", 
"Smoothie Hut", NA), received_from = c("ATM", "Sarah", NA, NA, 
NA, "Jane", "Joe", NA, "Sarah", NA, "Anna", "Jane", NA, NA, "Anna", 
NA, NA, NA, "Joe"), reference = c("add_cash", "gift", "shopping", 
"cinema_tickets", "meal", "reimbursed", "reimbursed", "meal", 
"reimbursed", "ice_rink_tickets", "reimbursed", "reimbursed", 
"shoes", "ice_cream", "reimbursed", "meal", "gallery_ticket", 
"drink", "reimbursed"), decrease = c(0, 0, 15.2, 10.8, 12.5, 
0, 0, 10, 0, 18, 0, 0, 15, 6.5, 0, 8, 3.5, 2, 0), increase = c(50, 
30, 0, 0, 0, 5.4, 7.25, 0, 10, 0, 6, 6, 0, 0, 21.5, 0, 0, 0, 
13.5), reimbursed_id = c(NA, NA, NA, "R", "R", "4", "5", "R", 
"8", "R", "10", "10", "R", "R", "13, 14", "R", "R", "R", "16, 17, 18"
), change = c(50, 30, -15.2, -10.8, -12.5, 5.4, 7.25, -10, 10, 
-18, 6, 6, -15, -6.5, 21.5, -8, -3.5, -2, 13.5), balance = c(50, 
80, 64.8, 54, 41.5, 46.9, 54.15, 44.15, 54.15, 36.15, 42.15, 
48.15, 33.15, 26.65, 48.15, 40.15, 36.65, 34.65, 48.15)), row.names = c(NA, 
-19L), class = c("tbl_df", "tbl", "data.frame"))

数据集预览 #

> DF
# A tibble: 19 × 10
      id day   sent_to         received_from reference        decrease increase reimbursed_id change balance
   <dbl> <chr> <chr>           <chr>         <chr>               <dbl>    <dbl> <chr>          <dbl>   <dbl>
 1     1 day1  NA              ATM           add_cash              0      50    NA             50       50  
 2     2 day2  NA              Sarah         gift                  0      30    NA             30       80  
 3     3 day3  Blue Superstore NA            shopping             15.2     0    NA            -15.2     64.8
 4     4 day4  Garden Cinema   NA            cinema_tickets       10.8     0    R             -10.8     54  
 5     5 day5  Pasta House     NA            meal                 12.5     0    R             -12.5     41.5
 6     6 day6  NA              Jane          reimbursed            0       5.4  4               5.4     46.9
 7     7 day6  NA              Joe           reimbursed            0       7.25 5               7.25    54.2
 8     8 day7  Pizzaria        NA            meal                 10       0    R             -10       44.2
 9     9 day8  NA              Sarah         reimbursed            0      10    8              10       54.2
10    10 day9  Ice Palace      NA            ice_rink_tickets     18       0    R             -18       36.2
11    11 day10 NA              Anna          reimbursed            0       6    10              6       42.2
12    12 day10 NA              Jane          reimbursed            0       6    10              6       48.2
13    13 day11 Shoes Centre    NA            shoes                15       0    R             -15       33.2
14    14 day12 Dreams Dessert  NA            ice_cream             6.5     0    R              -6.5     26.6
15    15 day13 NA              Anna          reimbursed            0      21.5  13, 14         21.5     48.2
16    16 day14 Chicken World   NA            meal                  8       0    R              -8       40.2
17    17 day14 Art Gallery     NA            gallery_ticket        3.5     0    R              -3.5     36.6
18    18 day14 Smoothie Hut    NA            drink                 2       0    R              -2       34.6
19    19 day14 NA              Joe           reimbursed            0      13.5  16, 17, 18     13.5     48.2

字段说明 #

reimbursed_id列含义 #

• R：该条记录的decrease金额包含代付部分，并非用户实际支出
• 单个数字：用户获得报销对应的交易ID
• 逗号分隔数字：用户通过多笔交易获得报销，对应多个交易ID

需求 #

添加actual_decrease列，规则如下：

• 非R标签行：actual_decrease等于decrease的值
• R标签行：需扣除对应所有报销行的increase总和，支持全额/部分报销、单笔/多笔报销场景

现有问题 #

当前基于dplyr的代码无法处理多ID报销场景（如第13、14、16-18行），且数据集较大，需要避免使用循环，寻求高效解决方案。

现有代码 #

DF %>%
  left_join(DF %>%
              filter(reference == "reimbursed") %>%
              group_by(id = as.numeric(reimbursed_id)) %>%
              summarise(actual_decrease = sum(increase)),
            by = "id") %>%
  mutate(actual_decrease = ifelse(!is.na(actual_decrease),
                                  decrease - actual_decrease,
                                  decrease))

期望输出 #

# A tibble: 19 × 9
      id day   sent_to         received_from reference        decrease increase reimbursed_id actual_decrease
   <dbl> <chr> <chr>           <chr>         <chr>               <dbl>    <dbl> <chr>                   <dbl>
 1     1 day1  NA              ATM           add_cash              0      50    NA                       0   
 2     2 day2  NA              Sarah         gift                  0      30    NA                       0   
 3     3 day3  Blue Superstore NA            shopping             15.2     0    NA                      15.2 
 4     4 day4  Garden Cinema   NA            cinema_tickets       10.8     0    R                        5.4 
 5     5 day5  Pasta House     NA            meal                 12.5     0    R                        5.25
 6     6 day6  NA              Jane          reimbursed            0       5.4  4                        0   
 7     7 day6  NA              Joe           reimbursed            0       7.25 5                        0   
 8     8 day7  Pizzaria        NA            meal                 10       0    R                        0   
 9     9 day8  NA              Sarah         reimbursed            0      10    8                        0   
10    10 day9  Ice Palace      NA            ice_rink_tickets     18       0    R                        6   
11    11 day10 NA              Anna          reimbursed            0       6    10                       0   
12    12 day10 NA              Jane          reimbursed            0       6    10                       0   
13    13 day11 Shoes Centre    NA            shoes                15       0    R                        0   
14    14 day12 Dreams Dessert  NA            ice_cream             6.5     0    R                        0   
15    15 day13 NA              Anna          reimbursed            0      21.5  13, 14                   0   
16    16 day14 Chicken World   NA            meal                  8       0    R                        0   
17    17 day14 Art Gallery     NA            gallery_ticket        3.5     0    R                        0   
18    18 day14 Smoothie Hut    NA            drink                 2       0    R                        0   
19    19 day14 NA              Joe           reimbursed            0      13.5  16, 17, 18               0 

解决方案 #

通过拆分报销ID、关联求和的方式实现，全程使用向量化操作，适合大数据集：

library(dplyr)
library(tidyr)

# 构建报销映射表：拆分多ID，计算每个交易对应的总报销金额
reimburse_map <- DF %>%
  filter(reference == "reimbursed") %>%
  separate_longer_delim(reimbursed_id, delim = ", ") %>%  # 拆分逗号分隔的ID
  mutate(reimbursed_id = as.numeric(reimbursed_id)) %>%
  group_by(reimbursed_id) %>%
  summarise(total_reimbursed = sum(increase))

# 关联映射表，计算actual_decrease
result <- DF %>%
  left_join(reimburse_map, by = c("id" = "reimbursed_id")) %>%
  mutate(
    actual_decrease = case_when(
      reimbursed_id == "R" ~ decrease - coalesce(total_reimbursed, 0),
      TRUE ~ decrease
    )
  ) %>%
  select(-total_reimbursed)  # 移除临时列

# 查看结果
result %>% select(id, day, reimbursed_id, decrease, actual_decrease)

代码说明 #

• separate_longer_delim：将逗号分隔的多个报销ID拆分成单独行，解决多ID关联问题
• coalesce(total_reimbursed, 0)：处理无报销记录的情况，避免NA值干扰计算
• case_when：清晰区分R标签行和普通行的计算逻辑
• 全程使用dplyr/tidyr的向量化操作，效率远高于循环

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