# Create a new column on a data.frame that depends on its own lags

Suppose I estimate a model where the dependent variable is \Delta(Y_t) = Y_t - Y_{t-1}.

So, I have the forecast for \Delta(Y_t^*). In a data.frame, I have the actual values of Y_t, and the \Delta(Y_t^*) forecasted. How can I create a new column of Y_t^*, where it combines the actual values and the forecasted values?

For example: level_actual is Y_t, diff_forecast is \Delta(Y_t^*) and the answer I am looking for is in answer_needed. See that the known Y_t substitutes the forecasted values, when it's known.

``````x <- data.frame(level_actual = c(1, 2, 3, 4, NA, NA, NA, NA, NA),
diff_forecast = c(1, 2, 0, 1, 2, 1, 0, 2, 1),
answer_needed = c(1, 2, 3, 4, 6, 7, 7, 9, 10))
``````

I've try different ways, the most obvious one was:

``````x %>%
mutate(level_actual_forecast = level_actual,
level_actual_forecast = case_when(
!is.na(level_actual) ~ level_actual,
is.na(level_actual) ~ lag(level_actual_forecast) + diff_forecast
))
``````

But it only find the very next value. How can I fill up the whole column?

This is kind of ugly but does the job.

``````library(dplyr, warn.conflicts = FALSE)

x <- data.frame(level_actual = c(1, 2, 3, 4, NA, NA, NA, NA, NA),
diff_forecast = c(1, 2, 0, 1, 2, 1, 0, 2, 1),
answer_needed = c(1, 2, 3, 4, 6, 7, 7, 9, 10))

x %>%
mutate(helper_col = tidyr::replace_na(level_actual, 0),
helper_col = if_else(is.na(level_actual), lag(helper_col) + diff_forecast, 0),
level_actual_forecast = if_else(is.na(level_actual), cumsum(helper_col), level_actual))
#>   level_actual diff_forecast answer_needed helper_col level_actual_forecast
#> 1            1             1             1          0                     1
#> 2            2             2             2          0                     2
#> 3            3             0             3          0                     3
#> 4            4             1             4          0                     4
#> 5           NA             2             6          6                     6
#> 6           NA             1             7          1                     7
#> 7           NA             0             7          0                     7
#> 8           NA             2             9          2                     9
#> 9           NA             1            10          1                    10
``````

Created on 2020-06-14 by the reprex package (v0.3.0)

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.