Referencing previous value in dplyr

Question

In a table with a rb_date column and a value column, I want to calculate the price level (level). Assume the starting base level is 100, the price level within each period (meaning rows with same rb_date) is simply return * base level, and each period's base level is the last value of previous period's price level. I am able to realize it by using data.table. How could I do it in dplyr?

Below is a reproducible example of how I did it in data.table.

library(data.table)
library(dplyr)
table <- tibble(rb_date = rep(c("2017-01-01", "2017-02-01", "2017-03-01"), 3), 
                return = rnorm(9)) %>% arrange(rb_date)
rb_dates = sort(unique(table$rb_date))
table = as.data.table(table)
level_lag = 100


for (i in 1:length(rb_dates)) {
  table[rb_date == rb_dates[i], level := level_lag * (1 + return)]
  level_lag = table[rb_date == rb_date[i], last(level)]
}

I don't know a better way to do it. Since I'm referring back to previous period, lag might not be helpful. Please point out if Im wrong. — Ziyan Xu

jhvdz jhvdz · Accepted Answer · 2017-12-11T21:23:08

I doubt if the code in your question actually does what you describe. The last level value seems not to be calculated correctly. In this solution a new tibble is created with the last values of each period using dplyr::slice()

t1 <- table %>% 
  group_by(rb_date) %>%
  slice(n())

t1$level <- NA
level_lag <- 100
for (i in 1:nrow(t1)) {
    t1$level[i] <- level_lag*(1+t1$return[i])
    level_lag <- t1$level[i]
}

Referencing previous value in dplyr

1 Answers