I am trying to populate a series like this.
My result ACTUAL Expected
FWK_SEQ_NBR a initial_d initial_c b c d b c d
914 9.161 131 62 0 62 69 0 62 69
915 9.087 131 0 0 53 78 0 53 78
916 8.772 131 0 0 44 140 0 44 87
917 8.698 131 0 0 0 140 0 35 96
918 7.985 131 0 69 52 139 69 96 35
919 6.985 131 0 78 63 138 78 168 0
920 7.077 131 0 140 126 138 87 247 0
921 6.651 131 0 140 126 138 96 336 0
922 6.707 131 0 139 125 138 35 364 0
a given
b lag of d by 4
c initial c for first week thereafter (c previous row + b current - a current)
d initial d - c current
Here is the code i used
DS1 = DS %>%
mutate(c = ifelse(FWK_SEQ_NBR == min(FWK_SEQ_NBR), intial_c, 0) ) %>%
mutate(c = lag(c) + b - a)) %>%
mutate(d = initial_d - c) %>%
mutate(d = ifelse(d<0,0,d)) %>%
mutate(b = shift(d, n=4, fill=0, type="lag"))
I am not getting the c right, do you know what i am missing. I have also attached the image of the actual and expected output. Thank you for your help!
Actual and Expected values Image
Image - Product and Store as the first two columns- please help
Below is the actual code, I have also copied the image of the expected and actual output. thank you!
Your example is not what I would call reproducible and the code snippet also did not provide much insight on what you were trying to do. However the screen image from excel was very helpful. Here is my solution
df <- as.data.frame(cbind(a = c(1:9), b = 0, c = 0, d = NA))
c_init = 62
d_init = 131
df$d <- d_init
df$c[1] <- c_init # initial data frame is ready at this stage
iter <- dim(df)[1] # for the loop to run item times
for(i in 1:iter){
if(i>4){
df[i, "b"] = df[i-4,"d"] # Calculate b with the lag
}
if(i>1){
df[i, "c"] = df[i-1, "c"] + df[i, "b"] - df[i, "a"] # calc c
}
df[i, "d"] <- d_init - df[i, "c"] # calc d
if(df[i, "d"] < 0) {
df[i, "d"] <- 0 # reset negative d values
}
}