Mimic incremental calculation from excel in R

user_13188513 0
Viewed 79

I am attempting mimic a process that is currently done manually in Excel using R in order to help streamline the process so it doesn't need to be done manually. Here is an example of some sample data that I am working with (keep in mind this is a snippet of a much larger set of data).

qty_available<- c(13500, 8500, 4600)
supply_qty<- c(0, 1000, 0)
forecast<- c(1200, 400, 3000)
demand_q<- c( 100, 800, 6000)

df<- data.frame(qty_available, supply_qty, forecast, demand_q)

I am attempting to do the following manipulation (pseduo-code):

qty_available = previous(qty_available) + supply_qty - forecast - demand_q

I can ignore the first observation because it is irrelevant in the context of my task.

So in the second observation, we would have 13,500 + 1000 - 400 - 800 giving us 13,300. The third observation would then be the 13,300 + 0 - 3000 - 6000 giving us 4300.

If I were to open this dataset in Excel (read it in a CSV), the process is simple. I would use this formula in cell A3 = A2+B3-C3-D3. Then just drag it down through the rest of the column.

How would I mimic this in R?

I have attempted doing so with the following command

df<- mutate(df, qty_available = lag(qty_available) + supply_qty - forecast - demand_q)

This command does not work since it is using the original 8500 rather than the newly calculated 13,300 in the lagged quantity available, and I am struggling to figure out another way to do it. Any suggestions would be greatly appreciated!

Desired output is as follows:

qty_available<- c(13500, 13300, 4300)

desired_output<- data.frame(qty_available, supply_qty, forecast, demand_q)
data-manipulation dataframe r excel
2 Answers

Here ..1 is the previous value in the "accumulation" and ..3 is the next value of the second argument. The initial value for ..1 is the first element of the first argument.

library(tidyverse) # or dplyr and purrr

df %>% 
  mutate(newcol = accumulate2(qty_available, 
                              tail(supply_qty - forecast - demand_q, -1), 
                              ~ ..1 + ..3))
         

#   qty_available supply_qty forecast demand_q newcol
# 1         13500          0     1200      100  13500
# 2          8500       1000      400      800  13300
# 3          4600          0     3000     6000   4300

Another option (gives same output)

df %>% 
  mutate(newcol = accumulate(tail(supply_qty - forecast - demand_q, -1), `+`, 
                             .init = qty_available[1]))

And another without any accumulate

df %>% 
  mutate(newcol =  qty_available[1] + 
                    cumsum(replace(supply_qty - forecast - demand_q, 1, 0)))
user_67627550

I am thinking of using a simple for-loop:

df1 <- data.frame(qty_available = c(13500, 8500, 4600),
                  supply_qty = c(0, 1000, 0),
                  forecast = c(1200, 400, 3000),
                  demand_q = c( 100, 800, 6000))

qty_available_new <- df1$qty_available[1]

for (i in 2:nrow(df1)) {
  qty_available_new[i] <- qty_available_new[i-1] + 
                                df1$supply_qty[i] - df1$forecast[i] - df1$demand_q[i]
}

df1$qty_available <- qty_available_new

df1
#>   qty_available supply_qty forecast demand_q
#> 1         13500          0     1200      100
#> 2         13300       1000      400      800
#> 3          4300          0     3000     6000

It is not as sophisticated as the other answer, but I wanted to show that loops would still be useful in R, even though there's this myth that we should steer clear of writing loops.

user_64614620
Related