Recursive Averages in R

[swaheera]

I am working with the R programming language. I have the following data:

library(dplyr)

my_data = data.frame(id = c(1,1,1,1,2,2,2,3,4,4,5,5,5,5,5), var_1 = sample(c(0,1), 15, replace = TRUE) , var_2 =sample(c(0,1), 15 , replace = TRUE) )

my_data = data.frame(my_data %>% group_by(id) %>% mutate(index = row_number(id)))

my_data = my_data[,c(1,4,2,3)]

The data looks something like this:

   id index var_1 var_2
1   1     1     0     1
2   1     2     0     0
3   1     3     1     1
4   1     4     0     1
5   2     1     1     0
6   2     2     1     1
7   2     3     0     1
8   3     1     1     0
9   4     1     0     0
10  4     2     0     0
11  5     1     0     0
12  5     2     1     0
13  5     3     0     1
14  5     4     0     0
15  5     5     0     1

I want to create two new variables (v_1, v_2). For each unique "id":

• v_1: I want v_1 to be the average value of the current, previous and previous-to-previous values of var_1 (i.e. index = n, index = n-1 and index = n-2). When this is not possible (e.g. for index = 2 and index = 1), I want this average to be for as back as you can go.
• v_2: I want v_2 to be the average value of the current, previous and previous-to-previous values of var_2 (i.e. index = n, index = n-1 and index = n-2). When this is not possible (e.g. for index = 2 and index = 1), I want this average to be for as back as you can go.

This would be something like this:

• row 1 (id = 1, index = 1) : v_1 = var_1 (index 1)
• row 2 (id = 1, index = 1 ; id = 1 index = 2) : v_1 = (var_1 (index 1) + var_1 (index 2))/2
• row 3 (id = 1, index = 1 ; id = 1 index = 2; id = 1, index = 3) : v_1 = (var_1 (index 1) + var_1 (index 2) + var_1 (index 3)) /3
• row 4 (id = 1, index = 2 ; id = 1 index = 3; id = 1, index = 4) : v_1 = (var_1 (index 2) + var_1 (index 3) + var_1 (index 4)) /3
• etc.

I tried to do this with the following code:

average_data = my_data %>% 
   group_by(id) %>% 
   summarise(v_1 = mean(tail(var_1, 3)), 
             v_2 = mean(tail(var_2, 3)))

# final_result
final_data =  merge(x = my_data, y = average_data, by = "id", all.x = TRUE)

But I am not sure if this is correct.

Can someone please show me how to do this?

Thanks!

[FJCC]

I doubt this is the best method. I only did var_1 in this example.

library(dplyr)

my_data = data.frame(id = c(1,1,1,1,2,2,2,3,4,4,5,5,5,5,5), var_1 = sample(c(0,1), 15, replace = TRUE) , var_2 =sample(c(0,1), 15 , replace = TRUE) )

my_data = data.frame(my_data %>% group_by(id) %>% mutate(index = row_number(id)))

my_data = my_data[,c(1,4,2,3)]

my_data <- my_data |> group_by(id) |> 
  mutate(var_1_1=lag(var_1),
         var_1_2=lag(var_1,2)) |> 
  rowwise() |> 
  mutate(Mean3_var1 = case_when(
    index == 1 ~ var_1,
    index == 2 ~ mean(c(var_1,var_1_1)),
    TRUE ~ mean(c(var_1,var_1_1,var_1_2))
  ))
my_data
#> # A tibble: 15 x 7
#> # Rowwise:  id
#>       id index var_1 var_2 var_1_1 var_1_2 Mean3_var1
#>    <dbl> <int> <dbl> <dbl>   <dbl>   <dbl>      <dbl>
#>  1     1     1     1     1      NA      NA      1    
#>  2     1     2     0     0       1      NA      0.5  
#>  3     1     3     0     1       0       1      0.333
#>  4     1     4     0     1       0       0      0    
#>  5     2     1     0     0      NA      NA      0    
#>  6     2     2     1     1       0      NA      0.5  
#>  7     2     3     0     0       1       0      0.333
#>  8     3     1     0     1      NA      NA      0    
#>  9     4     1     0     0      NA      NA      0    
#> 10     4     2     0     1       0      NA      0    
#> 11     5     1     1     1      NA      NA      1    
#> 12     5     2     1     0       1      NA      1    
#> 13     5     3     1     0       1       1      1    
#> 14     5     4     0     0       1       1      0.667
#> 15     5     5     1     0       0       1      0.667

^{Created on 2022-06-05 by the reprex package (v2.0.1)}

[nirgrahamuk]

I think combining dplyr with slider is the bests approach for this job.

library(tidyverse)


(my_data <- data.frame(
  id = c(1, 1, 1, 1, 2, 2, 2, 3, 4, 4, 5, 5, 5, 5, 5),
  index = c(1L, 2L, 3L, 4L, 1L, 2L, 3L, 1L, 1L, 2L, 1L, 2L, 3L, 4L, 5L),
  var_1 = c(0, 1, 1, 1, 1, 1, 1, 0, 1, 0, 1, 1, 1, 0, 1),
  var_2 = c(1, 0, 0, 1, 1, 0, 0, 1, 0, 0, 0, 1, 1, 0, 0)
))

library(slider)

#do for 1
my_data %>% group_by(id) %>% 
  mutate(avg_var_1 = slide_index(.x = var_1,
                                 .i = index,
                                 .f = mean,
                                 .before = 2)%>%unlist())

#do for both
my_data %>% group_by(id) %>% 
  mutate(across(.cols = c(var_1,var_2),
                .fns = ~ unlist(slide_index(.x = .x,
                                 .i = index,
                                 .f = mean,
                                 .before = 2)),
                .names = "avg_{.col}"))