tidyverse:按组的逐行计算 [英] tidyverse: row wise calculations by group

问题描述

我正在尝试在R中进行库存计算，这需要对每种Mat-Plant组合进行逐行计算.这是一个测试数据集-

I am trying to do an inventory calculation in R which requires a row wise calculation for each Mat-Plant combination. Here's a test data set -

df <- structure(list(Mat = c("A", "A", "A", "A", "A", "A", "B", "B"
), Plant = c("P1", "P1", "P1", "P2", "P2", "P2", "P1", "P1"), 
    Day = c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L), UU = c(0L, 10L, 
    0L, 0L, 0L, 120L, 10L, 0L), CumDailyFcst = c(11L, 22L, 33L, 
    0L, 5L, 10L, 20L, 50L)), .Names = c("Mat", "Plant", "Day", 
"UU", "CumDailyFcst"), class = "data.frame", row.names = c(NA, 
-8L))

  Mat Plant Day  UU CumDailyFcst
1   A    P1   1   0           11
2   A    P1   2  10           22
3   A    P1   3   0           33
4   A    P2   1   0            0
5   A    P2   2   0            5
6   A    P2   3 120           10
7   B    P1   1  10           20
8   B    P1   2   0           50

我需要一个新字段"EffectiveFcst"，以使当Day = 1时，然后EffectiveFcst = CumDailyFcst ，随后的几天-

I need a new field "EffectiveFcst" such that when Day = 1 then EffectiveFcst = CumDailyFcst and for following days -

这是所需的输出-

  Mat Plant Day  UU CumDailyFcst EffectiveFcst
1   A    P1   1   0           11            11
2   A    P1   2  10           22            22
3   A    P1   3   0           33            23
4   A    P2   1   0            0             0
5   A    P2   2   0            5             5
6   A    P2   3 120           10            10
7   B    P1   1  10           20            20
8   B    P1   2   0           50            40

我当前正在使用for循环，但实际表超过30万行，因此希望使用 tidyverse 进行此操作，以实现更优雅，更快速的方法.尝试了以下方法，但没有解决-

I am currently using a for loop but the actual table is >300K rows so hoping to do this with tidyverse for more elegant and faster approach. Tried the following but didn't work out -

group_by(df, Mat, Plant) %>%
  mutate(EffectiveFcst = ifelse(row_number()==1, CumDailyFcst, 0)) %>%
  mutate(EffectiveFcst = ifelse(row_number() > 1, CumDailyFcst - lag(CumDailyFcst, default = 0) + max(lag(EffectiveFcst, default = 0) - lag(UU, default = 0), 0), EffectiveFcst)) %>%
  print(n = nrow(.))

推荐答案

我们可以使用 purrr

library(tidyverse)
df %>% 
   group_by(Mat, Plant) %>% 
   mutate(EffectiveFcst =  accumulate(CumDailyFcst - lag(UU, default = 0),  ~ 
          .y , .init = first(CumDailyFcst))[-1] ) 
# A tibble: 8 x 6
# Groups:   Mat, Plant [3]
#  Mat   Plant   Day    UU CumDailyFcst EffectiveFcst
#  <chr> <chr> <int> <int>        <int>         <dbl>
#1 A     P1        1     0           11            11
#2 A     P1        2    10           22            22
#3 A     P1        3     0           33            23
#4 A     P2        1     0            0             0
#5 A     P2        2     0            5             5
#6 A     P2        3   120           10            10
#7 B     P1        1    10           20            20
#8 B     P1        2     0           50            40

这篇关于tidyverse:按组的逐行计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！