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
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:
This makes the function
getCurrentavailable for use. You can see that my result is consistent with yours: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:
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.
This is an act of magic!!
I should realy learn c++
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 :)
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