Calculate storage value considering inputs and outputs in R

Question

I'm having difficulty performing a reservoir balance process, I explain.

I want to calculate the value stored at the end of the month considering input and output (consumption).

EX:

At the beginning (beginning of January) the reservoir is empty, so there is an input of X value and an output of Y value. What remains will be the stored value. So in the month of February there is a new input (X may be different from X in January) and the exit remains constant. The stored value will now be February Input + January Stored Value - output. and so on until the month of December.

With the proviso that if the result is negative it will consider 0 and if it is greater than 16000 it will consider 16000

My data is:

month	input	Exit
jan	4700	2250
fev	6990	2250
mar	8900	2250
abr	9000	2250
may	5250	2250
jun	2790	2250
jul	1770	2250
ago	492	2250
set	89	2250
out	572	2250
nov	830	2250
dez	744	2250

expected result:

month	input	Exit	Stored
jan	4700	2250	2516
fev	6990	2250	7256
mar	8900	2250	13980
abr	9000	2250	16000
may	5250	2250	16000
jun	2790	2250	15688
jul	1770	2250	14677
ago	492	2250	12772
set	89	2250	10584
out	572	2250	8735
nov	830	2250	7066
dez	744	2250	5915

I managed to do it in excel, but I need to automate some things and that's why I'm using R

I have rounded the values... so it may diverge but, the logic would be. StoregeJAN = input - output StorageFEV = Input + storageJAN - output StorageMar = input + storageFEV - output — Herminio Sabino, Jul 28 '21 at 20:57
Please DONT ROUND VALUES, it goes against the very core of what we consider a REPRODUCIBLE EXAMPLE — GuedesBF, Jul 28 '21 at 20:58
Please read this: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — GuedesBF, Jul 28 '21 at 20:59
Data Here https://drive.google.com/file/d/1IhsxhwMGKO1MNAK1_0SUz5mjwcDDyTPi/view?usp=sharing — Herminio Sabino, Jul 28 '21 at 21:56
Please let SO-members know that the solution works by accepting it. If it does not do the job, please let me know. Maybe I can fix it. — rjen, Aug 01 '21 at 20:52

rjen · Accepted Answer · 2021-07-30T22:31:27.247

A tidyverse option using purrr::accumulate could look as follows.

library(dplyr)
library(purrr)

bound_sum <- function(x, y) {
  
  if (x+y > 16000) 
    16000 
  else if (x+y < 0)
    0
  else
    x+y
}

df %>%
  mutate(diff = case_when(row_number() == 1 & input-exit < 0 ~ 0,
                          row_number() == 1 & input-exit > 16000 ~ 16000,
                          T ~ input-exit),
         stored = accumulate(diff, bound_sum)) %>%
  select(-diff)

# # A tibble: 12 x 4
#    month input  exit stored
#    <chr> <dbl> <dbl>  <dbl>
#  1 jan    4700  2250   2450
#  2 fev    6990  2250   7190
#  3 mar    8900  2250  13840
#  4 abr    9000  2250  16000
#  5 may    5250  2250  16000
#  6 jun    2790  2250  16000
#  7 jul    1770  2250  15520
#  8 ago     492  2250  13762
#  9 set      89  2250  11601
# 10 out     572  2250   9923
# 11 nov     830  2250   8503
# 12 dez     744  2250   6997

Data

df <- structure(list(month = c("jan", "fev", "mar", "abr", "may", "jun", 
"jul", "ago", "set", "out", "nov", "dez"), input = c(4700, 6990, 
8900, 9000, 5250, 2790, 1770, 492, 89, 572, 830, 744), exit = c(2250, 
2250, 2250, 2250, 2250, 2250, 2250, 2250, 2250, 2250, 2250, 2250
)), row.names = c(NA, -12L), class = c("tbl_df", "tbl", "data.frame"
))

Thank you very much, it worked very well. Now I will go ahead in the application!! — Herminio Sabino, Jul 30 '21 at 18:41
doing some tests I realized that when the output is greater than the input in the first month it is not getting 0,. what can I add to get around this problem — Herminio Sabino, Jul 30 '21 at 19:57
@HerminioSabino: I have edited the solution to solve the problem. It now also handles the opposite case where the difference in the first month is greater than 16000. — rjen, Jul 30 '21 at 22:34

Calculate storage value considering inputs and outputs in R

1 Answers1