calculate indices with base year and relative percentage change
Another way would be to use cumprod() after converting the values to percentages:
library(dplyr)
start_tbl %>%
group_by(id, grp) %>%
mutate(idx_value = cumprod(c(100, (100 + value[-1]) / 100)))
# A tibble: 14 x 5
# Groups: id, grp [4]
id grp year value idx_value
<int> <int> <int> <dbl> <dbl>
1 1 1 7 2 100
2 1 1 8 -7 93
3 1 1 9 -2.3 90.9
4 1 1 10 1.1 91.9
5 1 2 7 -1 100
6 1 2 8 -12 88
7 1 2 9 -4 84.5
8 1 2 10 2 86.2
9 2 1 7 1 100
10 2 1 8 -3 97
11 2 1 9 2 98.9
12 2 2 7 -1 100
13 2 2 8 -4 96
14 2 2 9 -2 94.1
Based on the new dataset
library(purrr)
library(dplyr)
start_tbl2 %>%
group_by(id, grp) %>%
mutate(idx_vlue = accumulate(value[-1], ~ .x * (1 + .y/100), .init = 100 ))
# A tibble: 8 x 5
# Groups: id, grp [2]
# id grp year value idx_vlue
# <int> <int> <int> <dbl> <dbl>
#1 1 1 7 2 100
#2 1 1 8 -12 88
#3 1 1 9 -18.3 71.9
#4 1 1 10 100 144.
#5 1 2 7 15 100
#6 1 2 8 30 130
#7 1 2 9 40 182
#8 1 2 10 -50 91
and using 'start_tbl
start_tbl %>%
group_by(id, grp) %>%
mutate(idx_vlue = accumulate(value[-1], ~ .x * (1 + .y/100), .init = 100 ))
# A tibble: 14 x 5
# Groups: id, grp [4]
# id grp year value idx_vlue
# <int> <int> <int> <dbl> <dbl>
# 1 1 1 7 2 100
# 2 1 1 8 -7 93
# 3 1 1 9 -2.3 90.9
# 4 1 1 10 1.1 91.9
# 5 1 2 7 -1 100
# 6 1 2 8 -12 88
# 7 1 2 9 -4 84.5
# 8 1 2 10 2 86.2
# 9 2 1 7 1 100
#10 2 1 8 -3 97
#11 2 1 9 2 98.9
#12 2 2 7 -1 100
#13 2 2 8 -4 96
#14 2 2 9 -2 94.1