calculating tibble rows , depending on a values in previous row with hard min and max values.

Hi R community,

I would like to calculate the state of charge of a battery at the end of each day, depending on charge and discharge loads. I can mutate a column reflecting current state of charge using cumsum().

The problem is, using cumsum() leads to values above or below minimum and maximum capacites of the battery.

I achieved this using a for loop, but regarding that my table has about 60K rows and I need to repeat the calculation about 200 times. The for loop takes hours.

Is there a quicker way to do that?

this is my code:

``` r
library(tidyverse)
#> Warning: Paket 'tidyr' wurde unter R Version 4.1.3 erstellt
#> Warning: Paket 'readr' wurde unter R Version 4.1.3 erstellt

set.seed(2)
battery_max <- 300
discharge <- rnorm(5, 500, 50)
charge <- rnorm(5, 0, 1000)

df <- tibble(day = c(1:5),
       discharge = discharge * -1,
       charge = charge,
       balance = discharge + charge,
       )

df
#> # A tibble: 5 x 4
#>     day discharge charge balance
#>   <int>     <dbl>  <dbl>   <dbl>
#> 1     1     -455.   132.   -323.
#> 2     2     -509.   708.    199.
#> 3     3     -579.  -240.   -819.
#> 4     4     -443.  1984.   1541.
#> 5     5     -496.  -139.   -635.

# current state of charge using cumsum()
df |> 
  mutate(
    cumul_balance = cumsum(balance),
    current_level = cumul_balance +battery_max
  ) 
#> # A tibble: 5 x 6
#>     day discharge charge balance cumul_balance current_level
#>   <int>     <dbl>  <dbl>   <dbl>         <dbl>         <dbl>
#> 1     1     -455.   132.   -323.        -323.          -22.7
#> 2     2     -509.   708.    199.        -124.          176. 
#> 3     3     -579.  -240.   -819.        -943.         -643. 
#> 4     4     -443.  1984.   1541.         598.          898. 
#> 5     5     -496.  -139.   -635.         -36.9         263.

# Using for loop

df <- df |> 
  mutate(
    current_level = as.numeric(NA)
  )

for(i in 1:dim(df)[1]) {
    
  if(i == 1){
    new_current_level <- df[i, "balance"] + battery_max
  }
  else{
    new_current_level <- df[i, "balance"] + df[i-1, "current_level"]
  }
    
  if(new_current_level >= battery_max){
    df[i, "current_level"] <- battery_max
  }
  else if(new_current_level <= 0){
    df[i, "current_level"] <- 0
  }
  else {
    df[i, "current_level"] <- new_current_level
  }
}

df
#> # A tibble: 5 x 5
#>     day discharge charge balance current_level
#>   <int>     <dbl>  <dbl>   <dbl>         <dbl>
#> 1     1     -455.   132.   -323.            0 
#> 2     2     -509.   708.    199.          199.
#> 3     3     -579.  -240.   -819.            0 
#> 4     4     -443.  1984.   1541.          300 
#> 5     5     -496.  -139.   -635.            0
```

<sup>Created on 2022-07-06 by the [reprex package](https://reprex.tidyverse.org) (v2.0.1)</sup>

Thanks for your help

Posted Jul 6, 2022, 10:54 AM UTC

29
Vietdanglse•

Loops that depend on previous iterations are the bane of R programmers. While certain functions like `cumsum` exist, they don't always solve the problem at hand. In that case, your best bet is to write your function in C++ via Rcpp.

Do not fret about the C++ code, it's quite basic and you should be able to follow along without issues:

library(Rcpp)
cppFunction(' NumericVector getCurrent(NumericVector balance, double bmax){

      int n = balance.size() ;
      NumericVector current(n);
      float newCurrent; 
      
      current[0] = balance[0] + bmax; 
      
      for(int i = 0; i < n; ++i){
      
        if(i == 0){
          newCurrent = balance[0] + bmax; 
        }else{
          newCurrent = balance[i] + current[i-1];
        }
      
        if(newCurrent > bmax){
          newCurrent = bmax;  
        }else if(newCurrent < 0){
          newCurrent = 0; 
        }
        
        current[i] = newCurrent;
      }
      
      return current;
    }
                
    ')

This makes the function getCurrent available for use. You can see that my result is consistent with yours:

set.seed(2)
battery_max <- 300
discharge <- rnorm(5, 500, 50)
charge <- rnorm(5, 0, 1000)

df <- tibble(day = c(1:5),
             discharge = discharge * -1,
             charge = charge,
             balance = discharge + charge,
)

df$current <- getCurrent(df$balance, battery_max)
df
# A tibble: 5 × 5
    day discharge charge balance current
  <int>     <dbl>  <dbl>   <dbl>   <dbl>
1     1     -455.   132.   -323.      0 
2     2     -509.   708.    199.    199.
3     3     -579.  -240.   -819.      0 
4     4     -443.  1984.   1541.    300 
5     5     -496.  -139.   -635.      0 

Even for 60k rows, it's blazing fast. I omit the codes for generating the dummy data. But here's my benchmark for 60k rows:

> microbenchmark::microbenchmark(
+   getCurrent(df$balance, battery_max)
+ )
Unit: microseconds
                                expr   min     lq    mean median    uq    max neval
 getCurrent(df$balance, battery_max) 366.4 445.75 466.201    450 465.7 1603.6   100

That's less than half of a millisecond. Running it 200 times should thus take you (well, on my computer anyway) 1/10 of a second.

4
SamplayingOP•

This is an act of magic!!

I should realy learn c++

1
Vietdanglse•

C++ is a big language, but it’s not hard to learn to translate these occasional loops to C++. The tutorial I linked about should get you started.

Another option, I guess, would be to switch to Julia :)

1
SamplayingOP•

Thanks for the Julia tip, problem is, the whole data science / programming aspect is 10-20% of my work, and a useful hobby. I am not sure, that the time investment would be worth it. Thanks though

1
Maschel•

See what you happens if you try:

df |> 
  mutate(
    cumul_balance = cumsum(balance),
    current_level = pmax(0, pmin(cumul_balance + battery_max, battery_max)))
1
Vietdanglse•

The numbers here are clearly inconsistent with the output that OP aimed for.

1
Maschel•

Hmm, so it is. What it should have been is pmax(0, pmin(balance, battery_max)).

2
idoitoutdoors•

Look into using the lag() function. https://stackoverflow.com/questions/45308743/how-to-use-values-from-a-previous-row-and-column

FYI, to use the next value you use lead().

0
SamplayingOP•

Lag does not work. Try it!!

1