How to fill a column with loop for every group in R?

Question

I currently have a dataframe shown in the picture:

There is a initial sigma_t value on the first day of every PERMNO, and I want to fill the rest of the sigma_t column for every PERMNO with the following formula where lambda = 0.94:

I currently wrote a for loop that sort of implements this formula:

for(i in 2:nrow(data)){data$sigma_t[i] <- 0.94*data$sigma_t[i-1]^2+0.06*data$RET[i-1]^2}

However, how should I apply this for loop to every PERMNO group in the dataframe?

Answer 1

Assume that your data looks like this (i.e. the first sigma_t is a non-NA value for each PERMNO)

         Date PERMNO          RET   sigma_t
1  2000-01-03  10806  0.913192312 0.9979223
2  2000-01-04  10806 -0.092526597        NA
3  2000-01-05  10806  0.280083864        NA
4  2000-01-06  10806  0.130769875        NA
5  2000-01-07  10806  0.098093785        NA
6  2000-01-08  10806  0.558508840        NA
7  2000-01-09  10806  2.181083768        NA
8  2000-01-10  10806 -1.076594408        NA
9  2000-01-11  10806 -0.255776524        NA
10 2000-01-12  10806 -0.660575251        NA
11 2000-01-13  10806 -1.561518300        NA
12 2000-01-14  10806  0.402352610        NA
13 2000-01-15  10806  0.051492486        NA
14 2000-01-03  10807  0.135825002 0.5288962
15 2000-01-04  10807 -1.589023433        NA
16 2000-01-05  10807 -0.122931603        NA

Then you can do something like this

library(purrr)
library(dplyr)
data %>% group_by(PERMNO) %>% mutate(sigma_t = accumulate(head(RET, -1L), ~0.94 * .x * .x + 0.06 * .y * .y, .init = sigma_t[[1L]]))

Output

# A tibble: 65 x 4
# Groups:   PERMNO [5]
   Date       PERMNO     RET sigma_t
   <date>      <int>   <dbl>   <dbl>
 1 2000-01-03  10806  0.913   0.998 
 2 2000-01-04  10806 -0.0925  0.986 
 3 2000-01-05  10806  0.280   0.915 
 4 2000-01-06  10806  0.131   0.791 
 5 2000-01-07  10806  0.0981  0.589 
 6 2000-01-08  10806  0.559   0.327 
 7 2000-01-09  10806  2.18    0.119 
 8 2000-01-10  10806 -1.08    0.299 
 9 2000-01-11  10806 -0.256   0.153 
10 2000-01-12  10806 -0.661   0.0261
# ... with 55 more rows

Check if the results are the same as those generated by your for loop:

> res1 <- accumulate(head(data$RET, -1L), ~0.94 * .x * .x + 0.06 * .y * .y, .init = data$sigma_t[[1L]])
> res2 <- double(nrow(data))
> res2[[1L]] <- data$sigma_t[[1L]]
> for(i in 2:nrow(data)){
+   res2[[i]] <- 0.94*res2[[i-1]]*res2[[i-1]]+0.06*data$RET[[i-1]]*data$RET[[i-1]]
+ }
> all(res1 == res2)

[1] TRUE

Data I used

structure(list(Date = structure(c(10959, 10960, 10961, 10962, 
10963, 10964, 10965, 10966, 10967, 10968, 10969, 10970, 10971, 
10959, 10960, 10961, 10962, 10963, 10964, 10965, 10966, 10967, 
10968, 10969, 10970, 10971, 10959, 10960, 10961, 10962, 10963, 
10964, 10965, 10966, 10967, 10968, 10969, 10970, 10971, 10959, 
10960, 10961, 10962, 10963, 10964, 10965, 10966, 10967, 10968, 
10969, 10970, 10971, 10959, 10960, 10961, 10962, 10963, 10964, 
10965, 10966, 10967, 10968, 10969, 10970, 10971), class = "Date"), 
    PERMNO = c(10806L, 10806L, 10806L, 10806L, 10806L, 10806L, 
    10806L, 10806L, 10806L, 10806L, 10806L, 10806L, 10806L, 10807L, 
    10807L, 10807L, 10807L, 10807L, 10807L, 10807L, 10807L, 10807L, 
    10807L, 10807L, 10807L, 10807L, 10808L, 10808L, 10808L, 10808L, 
    10808L, 10808L, 10808L, 10808L, 10808L, 10808L, 10808L, 10808L, 
    10808L, 10809L, 10809L, 10809L, 10809L, 10809L, 10809L, 10809L, 
    10809L, 10809L, 10809L, 10809L, 10809L, 10809L, 10810L, 10810L, 
    10810L, 10810L, 10810L, 10810L, 10810L, 10810L, 10810L, 10810L, 
    10810L, 10810L, 10810L), RET = c(0.913192312238358, -0.092526596846852, 
    0.280083863779238, 0.130769874502966, 0.0980937848761638, 
    0.558508839970204, 2.18108376768683, -1.07659440811492, -0.255776524083785, 
    -0.660575251329072, -1.56151829951539, 0.402352610100085, 
    0.0514924859261968, 0.135825001773724, -1.58902343332535, 
    -0.122931603238164, 0.452006216926532, 0.693114146318084, 
    1.92191254928755, -1.46908545801166, 0.913386433733295, 0.58897079826165, 
    -0.734672875439902, -0.316864004253191, -1.5823359900577, 
    -0.640666477381628, 1.08418139895388, -1.14921141675937, 
    0.162609558626544, -1.9716659701064, 0.104005665525902, -0.795358745231712, 
    -0.2893381621628, 0.458189703352807, 0.00872023089334384, 
    0.00814797415178989, -0.67721320720348, 0.204413384165412, 
    -1.076872789543, 0.0945359885708477, -0.930160781560965, 
    -0.105321201055104, -0.315010497487111, -1.61634033850791, 
    2.524839463943, 0.290994112062563, -0.849772864569264, -1.65201573210097, 
    1.89042297661439, 1.33084303096302, 0.161021482912505, -1.31376297235415, 
    0.15668445036454, -0.549574920114175, 1.36530405318811, -0.0940911313529308, 
    -0.870085847088321, -0.388873127012174, -0.19829361512545, 
    -0.615723371631421, -0.290458500882665, -0.87058694446336, 
    0.498150842211666, -1.22058065663026, -1.13199591809083), 
    sigma_t = c(0.997922302223742, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, 0.528896240284666, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, 0.527911589480937, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.760049085598439, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0.531686891801655, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, 
-65L), groups = structure(list(PERMNO = 10806:10810, .rows = structure(list(
    1:13, 14:26, 27:39, 40:52, 53:65), ptype = integer(0), class = c("vctrs_list_of", 
"vctrs_vctr", "list"))), row.names = c(NA, 5L), class = c("tbl_df", 
"tbl", "data.frame"), .drop = TRUE), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"))

Update

If you want a third argument that changes with PERMNO, then

data %>% 
  group_by(PERMNO) %>% 
  mutate(
    sigma_t = accumulate(
      head(RET, -1L), ~..3 * .x * .x + (1 - ..3) * .y * .y, 
      c("10806" = 0.94, "10807" = 0.90)[[as.character(PERMNO[[1L]])]], 
      .init = sigma_t[[1L]]
    )
  )

To make the above pipeline more generic, you only have to replace this part c("10806" = 0.94, "10807" = 0.90) with a variable. Assume that your data has 100 unique PERMNO groups that correspond to lambdas ranging from 1 to 100, then just set

lambdas <- setNames(1:100, unique(df$PERMNO))

and run

data %>% 
  group_by(PERMNO) %>% 
  mutate(
    sigma_t = accumulate(
      head(RET, -1L), ~..3 * .x * .x + (1 - ..3) * .y * .y, 
      lambdas[[as.character(PERMNO[[1L]])]], 
      .init = sigma_t[[1L]]
    )
  )