Sum based on the cell in previsous row and in the same column-CodePudding

I have a very simple table

A	R	B
0	0	29.90709
7	0.1	25.1978
13	0.4	17.07692
4	0.2	15.69231
8	0.3	10
10	0.1	0

where number 29.90709 is calculated as 7 25.1978/(1 0.1).

My question is how to implement calculation of column B. I know how to do it in Excel, but not in R, since I need the value from the previous cell of column B. Is the any way or a function that can use a reference to a previous calculated cell?

Example data:

d <- read.table(text = "A   R   B
0   0   29.90709
7   0.1 25.1978
13  0.4 17.07692
4   0.2 15.69231
8   0.3 10
10  0.1 0", header = TRUE)

CodePudding user response：

You can use purrr::accumulate2() which can operate on two vectors:

library(purrr)
library(dplyr)

df %>%
  mutate(B = rev(head(unlist(purrr::accumulate2(rev(A), rev(R), ~ ..2   ..1 / (1   ..3), .init = 0)), -1)))

# A tibble: 6 x 3
      A     R     B
  <dbl> <dbl> <dbl>
1     0   0    29.9
2     7   0.1  25.2
3    13   0.4  17.1
4     4   0.2  15.7
5     8   0.3  10  
6    10   0.1   0

Or with base Reduce() by indexing the vectors:

df %>%
  mutate(B = rev(head(Reduce(function(x, y) A[y]   x / (1   R[y]), rev(seq_along(A)), init = 0, accumulate = TRUE), -1)))

Because you want the calculation from the bottom up it might make more sense to reverse the rows of your data frame than each element and the result individually:

df %>%
  arrange(desc(row_number())) %>%
  mutate(B = head(Reduce(function(x, y) A[y]   x / (1   R[y]), seq_along(A), init = 0, accumulate = TRUE), -1)) %>%
  arrange(desc(row_number()))

CodePudding user response：

Using forloop:

x <- vector("numeric", nrow(d))

for(i in (nrow(d) - 1):1){
    x[ i ] <- d$A[i   1]   x[i   1] / (1   d$R[i   1])   
  }

d$result <- x  
#    A   R        B   result
# 1  0 0.0 29.90709 29.90709
# 2  7 0.1 25.19780 25.19780
# 3 13 0.4 17.07692 17.07692
# 4  4 0.2 15.69231 15.69231
# 5  8 0.3 10.00000 10.00000
# 6 10 0.1  0.00000  0.00000