[Solved] Working for single input row but not for multiple

by


Here you go. I acknowledge you specified a loop in your question, but in R I avoid loops wherever possible. This is better.

This uses plyr::join_all to join all your data frames by Item and LC, then dplyr::mutate to do the calculations. Note you can put multiple mutations in one mutate() function:

library(plyr)
library(dplyr)
library(tidyr)

join_all(list(gather(df1, key = LC_ref, value = LC, - Item), df2, df3, df4),
         by = c("Item", "LC"),
         type = "left") %>%
  as_tibble() %>%
  rename("SS" = "Safetystock.SS.") %>%
  mutate(xprcnt= X * SS,
         remainingss= SS - xprcnt,
         prcntvalue = custfcst  / (custfcst + Rolledfcst),
         share = prcntvalue * remainingss,
         SSNew = xprcnt + share,
         Leftover = SS - SSNew) %>%
  arrange(Item, LC_ref) %>%
  group_by(Item) %>%
  mutate(lag = lag(Leftover, 1) + SS)

# A tibble: 6 x 15
# Groups:   Item [2]
  Item   LC_ref LC    custfcst ToLC  Rolledfcst    SS     X xprcnt remainingss prcntvalue share SSNew Leftover   lag
  <chr>  <chr>  <chr>    <int> <chr>      <int> <int> <dbl>  <dbl>       <dbl>      <dbl> <dbl> <dbl>    <dbl> <dbl>
1 8T4121 LC1    MW92        10 OY01          22    15  0.25   3.75       11.2       0.312  3.52  7.27     7.73 NA   
2 8T4121 LC2    OY01        12 RM11          10     7  0.25   1.75        5.25      0.545  2.86  4.61     2.39 14.7 
3 8T4121 LC3    RM11        10 NA            NA     5  0.25   1.25        3.75     NA     NA    NA       NA     7.39
4 AB7654 LC1    MW92        NA NA            NA    30  0.25   7.5        22.5      NA     NA    NA       NA    NA   
5 AB7654 LC2    WK14        NA NA            NA     8  0.25   2           6        NA     NA    NA       NA    NA   
6 AB7654 LC3    RM11        NA NA            NA    20  0.25   5          15        NA     NA    NA       NA    NA   

> select(.Last.value, -LC_ref, -(custfcst:X))
# A tibble: 6 x 9
# Groups:   Item [2]
  Item   LC    xprcnt remainingss prcntvalue share SSNew Leftover   lag
  <chr>  <chr>  <dbl>       <dbl>      <dbl> <dbl> <dbl>    <dbl> <dbl>
1 8T4121 MW92    3.75       11.2       0.312  3.52  7.27     7.73 NA   
2 8T4121 OY01    1.75        5.25      0.545  2.86  4.61     2.39 14.7 
3 8T4121 RM11    1.25        3.75     NA     NA    NA       NA     7.39
4 AB7654 MW92    7.5        22.5      NA     NA    NA       NA    NA   
5 AB7654 WK14    2           6        NA     NA    NA       NA    NA   
6 AB7654 RM11    5          15        NA     NA    NA       NA    NA

(Also note that dplyr and plyr have a few functions of matching names, I find it usually works best to load plyr first in your library statements).

solved Working for single input row but not for multiple