CODEWITHSUNDEEP
rdplyr
learnmorer
learnmorer

Reputation: 565

How to calculate an iterative row-by-row function in dplyr (e.g. growth rates)>

I am embarrassed that I cannot seem to figure this out. Given a column in levels and a column in growth rates, I'd like to calculate the new level. For row 3, this value is lag(level)*(1+pchange). Every single function I have tried will only generate a value for row 3.

I'd be grateful if anyone has thoughts on how to do this.

df <- structure(list(obs = 1:9, level = structure(c(242082.4, 241954.139694373, 
NA, NA, NA, NA, NA, NA, NA), .Dim = c(9L, 1L)), pchange = c(NA, 
NA, -0.210448040080465, -0.000210098220356602, -0.0181173124932753, 
0.026797769617468, 0.0550078908432114, 0.0477175804376899, 0.0166852340657672
), 
 expected_output = c(242082.4, 241954.1, 191035.3339, 190995.1977, 
 187534.878, 192560.3945, 203152.7356, 212846.6926, 216398.0895)), 
   class = "data.frame", row.names = c(NA, -9L))

Upvotes: 1

Views: 186

Answers (3)

chinsoon12
chinsoon12

Reputation: 25225

Another option using cumprod with data.table::nafill:

df$outp <- cumprod(1+df$pchange) * data.table::nafill(df$level, "locf")

Upvotes: 1

Muzammil Aziz
Muzammil Aziz

Reputation: 186

if you want to do it in base R

for(i in 1:nrow(df)){
  df$level[i] <- ifelse(is.na(df$pchange[i]), df$level[i], df$level[i-1]*(1+df$pchange[i]))
}

Upvotes: 0

akrun
akrun

Reputation: 887128

We could use accumulate

library(purrr)
library(dplyr)
df %>%
     mutate(new = coalesce(level, replace(pchange, 3:n(), 
      accumulate(pchange[-c(1:2)], ~ (1 + .y) * .x, .init = level[2])[-1])))
#. obs    level       pchange expected_output      new
#1   1 242082.4            NA        242082.4 242082.4
#2   2 241954.1            NA        241954.1 241954.1
#3   3       NA -0.2104480401        191035.3 191035.4
#4   4       NA -0.0002100982        190995.2 190995.2
#5   5       NA -0.0181173125        187534.9 187534.9
#6   6       NA  0.0267977696        192560.4 192560.4
#7   7       NA  0.0550078908        203152.7 203152.8
#8   8       NA  0.0477175804        212846.7 212846.7
#9   9       NA  0.0166852341        216398.1 216398.1

Or using Reduce from base R

c(df$level[1], Reduce(function(x, y) (1 + y) * x, df$pchange[-c(1:2)], init = df$level[2], accumulate = TRUE))
#[1] 242082.4 241954.1 191035.4 190995.2 187534.9 192560.4 203152.8 212846.7 216398.1

Upvotes: 1

Related Questions