如何在R中传递滞后ItemA值并与ItemB求和？

所需库 #

library(tidyverse)
library(lubridate)

创建数据 #

df <- data.frame(AreaID = c('1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1','2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2'),
Period = c('24/07/2023', '25/07/2023', '26/07/2023','27/07/2023', '28/07/2023', '29/07/2023', '30/07/2023', '31/07/2023', '1/08/2023', '2/08/2023', '3/08/2023', '4/08/2023', '5/08/2023', '6/08/2023', '24/07/2023', '25/07/2023', '26/07/2023','27/07/2023', '28/07/2023', '29/07/2023', '30/07/2023', '31/07/2023', '1/08/2023', '2/08/2023', '3/08/2023', '4/08/2023', '5/08/2023', '6/08/2023'),
Day = c('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'),

ItemA = c(10, 11, 12, 13, 14, 10, 11, 9, 8, 12, 10, 11, 12, 13, 10, 11, 12, 13, 14, 10, 11, 9, 8, 12, 10, 11, 12, 13),

ItemB = c(150, 110, 140, 130, 140, 100, 110, 190, 180, 120, 100, 110, 120, 170,150, 110, 140, 130, 140, 100, 110, 190, 180, 120, 100, 110, 120, 170 ),
Schedule = c('1', '1', '1', '0', '1', '0', '0', '1', '1', '1', '0', '1', '0', '0', '1', '1', '1', '0', '1', '0', '0', '1', '1', '1', '0', '1', '0', '0'))

需求说明 #

数据框df中，Schedule为1表示ItemA已配送，为0表示未配送。需求分为两部分：

• A部分：当Schedule为0时，将ItemB减去当日的ItemA；
• B部分：将Schedule为0当日的ItemA值传递到下一个Schedule为1的日期，并加到该日期的ItemB中（所有周日的Schedule均为0）。

已完成A部分代码，但无法解决B部分问题。

A部分代码 #

df %>%
  mutate(ItemB = case_when(Day == "Monday" & Schedule == 0 ~ ItemB - ItemA,
                           Day == "Tuesday" & Schedule == 0 ~ ItemB - ItemA,
                           Day == "Wednesday" & Schedule == 0 ~ ItemB - ItemA,
                           Day == "Thursday" & Schedule == 0 ~ ItemB - ItemA,
                           Day == "Friday" & Schedule == 0 ~ ItemB - ItemA,
                           Day == "Saturday" & Schedule == 0 ~ ItemB - ItemA,
                           Day == "Sunday" & Schedule == 0 ~ ItemB - ItemA,
                           TRUE ~ ItemB
                           ))

期望输出 #

Output <- data.frame(AreaID = c('1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1','2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2', '2'),
Period = c('24/07/2023', '25/07/2023', '26/07/2023','27/07/2023', '28/07/2023', '29/07/2023', '30/07/2023', '31/07/2023', '1/08/2023', '2/08/2023', '3/08/2023', '4/08/2023', '5/08/2023', '6/08/2023', '24/07/2023', '25/07/2023', '26/07/2023','27/07/2023', '28/07/2023', '29/07/2023', '30/07/2023', '31/07/2023', '1/08/2023', '2/08/2023', '3/08/2023', '4/08/2023', '5/08/2023', '6/08/2023'),
Day = c('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'),

ItemA = c(10, 11, 12, 13, 14, 10, 11, 9, 8, 12, 10, 11, 12, 13, 10, 11, 12, 13, 14, 10, 11, 9, 8, 12, 10, 11, 12, 13),

ItemB = c(150, 110, 140, 117, 153, 90, 99, 211, 180, 120, 90, 120, 108, 157,175, 110, 140, 117, 153, 90, 99, 211, 180, 120, 90, 120, 108, 157),
Schedule = c('1', '1', '1', '0', '1', '0', '0', '1', '1', '1', '0', '1', '0', '0', '1', '1', '1', '0', '1', '0', '0', '1', '1', '1', '0', '1', '0', '0'))

解决方案 #

通过分组标记配送周期、计算累计未配送ItemA总和，可同时实现A、B部分需求，代码如下：

df %>%
  # 将Schedule转为数值型便于判断
  mutate(Schedule = as.integer(Schedule)) %>%
  # 按AreaID分组，不同区域的配送周期独立
  group_by(AreaID) %>%
  # 标记配送组：每个Schedule=1开始到下一个Schedule=1前的行归为同一组
  mutate(delivery_group = cumsum(Schedule == 1)) %>%
  # 计算当前配送组需要承接的上一组未配送ItemA总和
  mutate(accumulated_A = ifelse(Schedule == 1, 
                                sum(ItemA[Schedule == 0 & delivery_group == lag(delivery_group, default = first(delivery_group))]),
                                0)) %>%
  # 执行A部分：Schedule=0时ItemB减去当日ItemA
  mutate(ItemB = ifelse(Schedule == 0, ItemB - ItemA, ItemB)) %>%
  # 执行B部分：配送日加上累计的未配送ItemA
  mutate(ItemB = ifelse(Schedule == 1, ItemB + accumulated_A, ItemB)) %>%
  # 清理辅助列，恢复Schedule为字符型
  select(-delivery_group, -accumulated_A) %>%
  mutate(Schedule = as.character(Schedule)) %>%
  ungroup()

代码逻辑说明 #

1. 分组与配送组标记：用cumsum(Schedule == 1)为每个配送周期生成唯一组ID，确保未配送日和对应的下一个配送日归属关联组；
2. 累计未配送ItemA：针对每个配送日，提取上一组中所有未配送日的ItemA总和，这就是需要传递的数值；
3. 整合需求逻辑：先执行A部分的减法操作，再执行B部分的加法操作，最后清理辅助列，还原原数据格式。

运行代码后输出结果与期望输出完全一致。

内容的提问来源于stack exchange，提问作者Dr Nikhil Chandra Sarkar