I have a dataframe with annual growth values for different years and a value for a particular year and would like to decrease that value backward for years before the year with the value and increase that value forward for years after the year with the value. The example dataset is below
ex_df = structure(list(year = c(2015, 2016, 2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025),
growth = c(1152, -2051, 1074, 1200, 1036, 1593, 1007, 1701, 1642, -1800, 1607),
value = c(NA, NA, NA, NA, NA, NA, NA, 11278, NA, NA, NA),
new_value = c(5874, 7026, 4975, 6049, 7642, 8678, 10271, 11278, 12979, 14621, 12821)),
class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -11L))
The code I am trying is below
ex_df %>%
arrange(year) |>
mutate(cum_yr_increase = cumsum(growth),
new_value_2 = cum_yr_increase + value[year == 2024])
ex_df %>%
arrange(year) |>
# lag() is needed because the growth is added in next year
mutate(cum_yr_increase = cumsum(lag(growth,default = 0)),
# set the baseline for cum_yr_increase to year 2022
cum_yr_increase2 = cum_yr_increase-cum_yr_increase[year==2022],
new_value_2 = cum_yr_increase2 + value[year == 2022])
year growth value new_value cum_yr_increase cum_yr_increase2 new_value_2
<dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 2015 1152 NA 5874 0 -5011 6267
2 2016 -2051 NA 7026 1152 -3859 7419
3 2017 1074 NA 4975 -899 -5910 5368
4 2018 1200 NA 6049 175 -4836 6442
5 2019 1036 NA 7642 1375 -3636 7642
6 2020 1593 NA 8678 2411 -2600 8678
7 2021 1007 NA 10271 4004 -1007 10271
8 2022 1701 11278 11278 5011 0 11278
9 2023 1642 NA 12979 6712 1701 12979
10 2024 -1800 NA 14621 8354 3343 14621
11 2025 1607 NA 12821 6554 1543 12821