How to use shift computation with a cumulative value in a data table. R
I have a data table with the following layout
TestData <- data.table(Threshold = 20,
Upto = 100,
Demand = c(0,0,5,0,50,10,10,10,10,50,20,60),
Stock = c(100,0,0,0,0,0,0,0,0,0,0,0))
The stock value should be calculated as a cumulative value using the formula:
If Inventory (previous row) minus Demand (current row) is less than or equal to the threshold, than update the current bin in the warehouse with the value in "Upto". Else updates the stock value (current row) with the stock (previous row) minus the query (current row).
Then the procedure should start again. The result should look like this:
TestData <- data.table(Threshold = 20,
Upto = 100,
Demand = c(0,0,5,0,50,10,10,10,10,50,20,60),
Stock = c(100,100,95,95,45,35,25,100,90,40,100,40))
Threshold Upto Demand Stock
1: 20 100 0 100
2: 20 100 0 100
3: 20 100 5 95
4: 20 100 0 95
5: 20 100 50 45
6: 20 100 10 35
7: 20 100 10 25
8: 20 100 10 100
9: 20 100 10 90
10: 20 100 50 40
11: 20 100 20 100
12: 20 100 60 40
I did the following:
TestData[, Stock:= ifelse(cumsum(shift(Stock, 0, type="lead") - Demand) <= Threshold,
Upto,
cumsum(shift(Stock, 0, type="lead") - Demand))]
But after the first update, the calculation stops and shows 100 every time the end ends. The first value in stock is the initial value that I manually set. Thank!
source to share
If you can live with a looped solution. I don't think this is doable with dplyr (or data.table), but I would like someone to prove that I am wrong.
for (i in 2:nrow(TestData)) {
# If stock - demand <= threshold, restock
if ((TestData[i-1, "Stock"] - TestData[i, "Demand"]) <= TestData[i, "Threshold"])
{
TestData[i, "Stock"] <- TestData[i-1, "Upto"]
}
# Else update stock with stock - demand
else
{
TestData[i, "Stock"] <- TestData[i-1, "Stock"] - TestData[i, "Demand"]
}
}
source to share
here is the solution data.table
. Innovation is in the group by
. Please post any edge cases where this solution doesn't work.
TestData <- data.table(Threshold = 20,
Upto = 100,
Demand = c(0,0,5,0,50,10,10,10,10,50,20,60),
Stock = c(100,0,0,0,0,0,0,0,0,0,0,0))
#to see by grouping
#TestData[,trunc(cumsum(Demand)/(Threshold - Upto))]
TestData[, Stock2 := c(Upto[1], Upto[1] - cumsum(Demand[-1])),
by=trunc(cumsum(Demand)/(Threshold - Upto))]
TestData
source to share
It was difficult. Did it with the do-while principle:
upVal = 100
threshVal = 20
cumVals <- TestData$Demand
cumMaster <- cumsum(cumVals)
repeat{
IND <- cumMaster>=upVal-threshVal
cumVals[min(which(IND == TRUE))] <- 0
cumMaster[IND] <- cumsum(cumVals[IND])
if(all(cumMaster<upVal-threshVal)){
break
}
}
TestData$Stock <- 100 - cumMaster
Result
TestData$Stock
source to share