using lag for creating an x+1 column

Question

I'm trying to implement a lag function but it seems i need an existing x column for it to work

lets say i have this data frames

df <- data.frame(AgeGroup=c("0-4", "5-39", "40-59","60-69","70+"),
                 px=c(.99, .97, .95, .96,.94))

i want a column Ix that is lag(Ix)*lag(px) starting from 1000.

The data i want is

df2 <- data.frame(AgeGroup=c("0-4", "5-39", "40-59","60-69","70+"),
                 px=c(.99, .97, .95, .96, .94),
                 Ix=c(1000, 990, 960.3, 912.285, 875.7936))

I've tried

library(dplyr)
   df2<-mutate(df,Ix = lag(Ix, default = 1000)*lag(px))

ifelse statements don't work after the creation of a reference

df$Ix2=NA 
df[1,3]=1000
df$Ix<-ifelse(df[,3]==1000,1000,
               lag(df$Ix, default = 1000)*lag(px,default =1))

and have been playing around with creating separate Ix column with Ix=1000 then run the above but it doesn't seem to work. Does anyone have any ideas how i can create the x+1 column?

Answer 1

You could use cumprod() combined with dplyr::lag() for this:

> df$Ix <- 1000*dplyr::lag(cumprod(df$px), default = 1)
> df
  AgeGroup   px        Ix
1      0-4 0.99 1000.0000
2     5-39 0.97  990.0000
3    40-59 0.95  960.3000
4    60-69 0.96  912.2850
5      70+ 0.94  875.7936

Answer 2

You can also use accumulate from purrr. Using head(px, -1) includes all values in px except the last one, and the initial Ix is set to 1000.

library(tidyverse)

df %>%
  mutate(Ix = accumulate(head(px, -1), prod, .init = 1000))

Output

  AgeGroup   px        Ix
1      0-4 0.99 1000.0000
2     5-39 0.97  990.0000
3    40-59 0.95  960.3000
4    60-69 0.96  912.2850
5      70+ 0.94  875.7936