Working for single input row but not for multiple

Question

Dataframe 1:

 Item   LC1    LC2   LC3
8T4121  MW92    OY01    RM11

Dataframe 2:

 Item   LC   custfcst
8T4121  MW92    10
8T4121  OY01    12
8T4121  RM11    10
AB7654  MW92    20
AB7654  WK14    10
AB7654  RM11    8

Dataframe 3:

 Item    LC     ToLC    Rolledfcst
8T4121  MW92    OY01    22
8T4121  OY01    RM11    10
AB7654  MW92    WK14    30
AB7654  WK14    RM11    12

Dataframe 4:

Item    LC      Safetystock(SS)       X
8T4121  MW92    15                   .25
8T4121  OY01    7                    .25
8T4121  RM11    5                    .25
AB7654  MW92    30                   .25
AB7654  WK14    8                    .25
AB7654  RM11    20                   .25

Output: This is the output when I take single row as input in Dataframe 1

     Item    LC    xprcnt remainingss prcntvalue share       SSNew `Leftover`
1   8T4121  MW92    3.75    11.25   0.3125000   3.515625    7.265625    7.734375
2   8T4121  OY01    1.75    5.25    0.5454545   2.863636    4.613636    2.386364
3   8T4121  RM11    NA  NA  NA  NA  NA  NA

But when I take more than 1 row as input in dataframe 1 it is not giving the desired output. Can someone help me with this.

Dataframe 1:

 Item   LC1    LC2   LC3
8T4121  MW92    OY01    RM11
AB7654  MW92    WK14    RM11

Code:

library(plyr)
library(dplyr)
library(igraph)
library(data.table)
library(magrittr)
library(tidyr)

lctolc <- read.csv("LCtoLC.csv") #DF to get DF1
custfcst <- read.csv("custfcst.csv") #DF2
rolledfcst <- read.csv("rolledfcst.csv") #DF3
safetystock <- read.csv("safetystock.csv") #DF4

bodlane <- lapply(
  lapply(split(lctolc, lctolc$Item), function(x) graph.data.frame(x[, 2:3])), 
  function(x) lapply(
    all_simple_paths(x, from = V(x)[degree(x, mode = "in") == 0], 
                     to = V(x)[degree(x, mode = "out") == 0]),
    function(y) as.data.table(t(names(y))) %>% setnames(paste0("LC", seq_along(.)))
  ) %>% rbindlist(fill = TRUE)
) %>% rbindlist(fill = TRUE, idcol = "Item")



distributn <- bodlane %>%
  as_tibble() %>%
  gather(key = LC_ref, value = LC, - Item) %>%
  left_join(select(custfcst, -Item), by = "LC") %>%
  left_join(select(rolledfcst, -Item), by = "LC","ToLC") %>%
  left_join(select(safetystock, -Item), by = "LC") %>%
  mutate(xprcnt= (x * SS))  %>%
  mutate(remainingss= (SS - xprcnt))  %>%
  mutate(prcntvalue = (custfcst  / (custfcst +Rolledfcst)))   %>%
  mutate(share = (prcntvalue * remainingss))   %>%
  mutate(SSNew = (xprcnt + share))   %>%
  mutate(Leftover = (SS - SSNew))   %>%
  select(Item, LC, xprcnt, remainingss,prcntvalue,share,SSNew,Leftover)

Without seeing your code, how would you explain what you want to do? — NelsonGon, Jul 04 '19 at 17:18
I didn't get you. I have pasted the code which I am working upon — RBeginner55, Jul 04 '19 at 17:23
See [this question for details](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — NelsonGon, Jul 04 '19 at 17:24
I read it..so can you let me know what is missing here....I have pasted code as well as dataframe and the problem which I am facing — RBeginner55, Jul 04 '19 at 17:26
You give the output but no explanation of what is being done. I would add some explanation of what I am trying to achieve. The code is also too long with several library calls. Several mutate calls might imply it's time to think of a new approach. — NelsonGon, Jul 04 '19 at 17:33
Basically, here for each Item LC in DF1, calculating xprcnt=x*SS, remainingss= SS-xprcnt Similarly, other values are being calculated — RBeginner55, Jul 04 '19 at 17:54
The best way to get an answer is to post a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) (see also the link cited by @NelsonGon). We can't help you unless we know (1) what you're trying to accomplish, and (2) exactly why the things you've tried so far don't work. (Code comments go a long way.) Also, other users are more likely to take the time to help you if you make it easy for them by posting a sample of your data, using `dput`. (We can't easily copy tabular output, and we obviously don't have your csv files.) — A. S. K., Jul 04 '19 at 21:04

Paul · Accepted Answer · 2019-07-05T07:31:35.727

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).

score 0 · Answer 2 · answered Jul 05 '19 at 05:25

library(plyr)
library(dplyr)
library(tidyr)
library(igraph)
library(data.table)
library(magrittr)


lctolc <- read.csv("LCtoLC.csv")
custfcst <- read.csv("custfcst.csv")
rolledfcst <- read.csv("rolledfcst.csv")
safetystock <- read.csv("safetystock.csv")

bodlane <- lapply(
  lapply(split(lctolc, lctolc$Item), function(x) graph.data.frame(x[, 2:3])), 
  function(x) lapply(
    all_simple_paths(x, from = V(x)[degree(x, mode = "in") == 0], 
                     to = V(x)[degree(x, mode = "out") == 0]),
    function(y) as.data.table(t(names(y))) %>% setnames(paste0("LC", seq_along(.)))
  ) %>% rbindlist(fill = TRUE)
) %>% rbindlist(fill = TRUE, idcol = "Item")


df1<- merge(custfcst,lctolc,by=c("LC","Item"),all.x=TRUE)
df2<- merge(rolledfcst, df1,by.x=c("LC","Item","ToLC"),by.y=c("LC","Item","ToLC"),all=TRUE)
Final<- merge(safetystock, df2, by = c("LC","Item"))
Final$xprcnt <- (Final$x * Final$SS)
Final$remainingss= (Final$SS - Final$xprcnt)  
Final$prcntvalue = (Final$custfcst  / (Final$custfcst +Final$Rolledfcst))  
Final$share = (Final$prcntvalue * Final$remainingss) 
Final$SSNew = (Final$xprcnt + Final$share)  
Final$Leftover = (Final$SS - Final$SSNew)
Final<-Final[,c(2,1,5,7,6,3,4,8,9,10,11,12,13)]

This code worked perfectly

score 0 · Answer 3 · answered Jul 05 '19 at 06:03

Item LC   ToLC custfcst Rolledfcst SS    x    xprcnt remainingss prcntvalue   share   SSNew    Leftover
1   8T4121  MW92    OY01    10  22  15  0.25    3.75    11.25   0.3125000   3.515625    7.265625    7.734375
2   8T4121  OY01    RM11    12  10  7   0.25    1.75    5.25    0.5454545   2.863636    4.613636    2.386364
3   8T4121  RM11    NA  10  NA  5   NA  NA  NA  NA  NA  NA  NA


I have pasted the code above.
Now I am stuck with like Item 8T4121 and LC MW92 after the computations whatever is the leftover it should be added to SS at 8T4121 and OY01 for computing the other calculations at that level.
Similarly, leftover at OY should added to SS at RM11.
THis should work for all the Items

Working for single input row but not for multiple

3 Answers3

This code worked perfectly