Add a column recursively in R

user_14403266 0
Viewed 47

I have a dataframe, df that looks this way:

Year Colombia    Chile
------------------------
2006   9.5660    24.5576
2007  26.0079    10.9652
2008  16.0893    2.3721
2009  -5.1036   -5.0465

And I want to add two columns called index_Colombia and index_Chile so that in the first row f(1) the value is 100 and the value of the second row for Colombia is 100*(1+26.0079/100) and for Chile 100*(1+10.9652/100), and so on... so that we get the following df

Year Colombia    Chile    index_Colombia   index_Chile 
-------------------------------------------------------
2006   9.5660    24.5576      100            100       
2007  26.0079    10.9652   126.0079         110.9652
2008  16.0893    2.3721     146.2816         113.5974
2009  -5.1036   -5.0465     138.8159        107.864

it occurs to me that it is necessary to do with the mutate function but I do not know how to formulate it.... Can you help me?

dplyr dataframe r
2 Answers

A possible solution:

library(dplyr)

df <- data.frame(
  Year = c(2006L, 2007L, 2008L, 2009L),
  Colombia = c(9.566, 26.0079, 16.0893, -5.1036),
  Chile = c(24.5576, 10.9652, 2.3721, -5.0465)
)

bind_rows(df[1,],
 df %>% slice(-1) %>% 
 mutate(across(-Year, ~ 100 * cumprod(1 + .x/100), .names = "index_{.col}"))) %>% 
 replace(is.na(.), 100)

#>   Year Colombia   Chile index_Colombia index_Chile
#> 1 2006   9.5660 24.5576       100.0000    100.0000
#> 2 2007  26.0079 10.9652       126.0079    110.9652
#> 3 2008  16.0893  2.3721       146.2817    113.5974
#> 4 2009  -5.1036 -5.0465       138.8161    107.8647
user_115644870

If my calculations are correct, then this can be done with:

dat %>%
  mutate(
    across(c(Colombia, Chile),
           ~ lag(100 * cumprod(1 + ./100)),
           .names = "index_{col}"
    )
  )
#   Year Colombia   Chile index_Colombia index_Chile
# 1 2006   9.5660 24.5576             NA          NA
# 2 2007  26.0079 10.9652       109.5660    124.5576
# 3 2008  16.0893  2.3721       138.0618    138.2156
# 4 2009  -5.1036 -5.0465       160.2750    141.4942
user_33582720
Related