Define windows to compute average in R

Question

I have an issue with R, trying to code something for very large tables.

I want to compute the mean value of $variable_1 and $variable_2 for each window of 500 positions (column $pos) with a step of 500 positions. Let me show you, it might be more understandable !

Input table :

data_FST = data.frame(scaffold=c(rep("Scaffold_1",1000),
                                 rep("Scaffold_2",2000),
                                 rep("Scaffold_3",450)),
                      variable_1=sample(1:5000,3450, replace=TRUE),
                      variable_2=sample(1:5000,3450, replace=TRUE),
                      pos=c(seq(1,2000,2),1:2000,1:450))

Desired output table :

scaffold   pos  variable_1               variable_2
Scaffold_1 500  mean_variable(1:500)     mean_variable(1:500)
Scaffold_1 1000 mean_variable(501:1000)  mean_variable(501:1000)
Scaffold_2 500  mean_variable(1:500)     mean_variable(1:500)
Scaffold_2 1000 mean_variable(500:1000)  mean_variable(500:1000)
Scaffold_2 1500 mean_variable(1000:1500) mean_variable(1000:1500)
Scaffold_2 2000 mean_variable(1500:2000) mean_variable(1500:2000)
Scaffold_3 500  mean_variable(1:500)     mean_variable(1:500)

Many thanks in advance

Answer 1

You can create a new group by dividing pos value by every 500 value and take mean of variable.

library(dplyr)

data_FST %>%
  group_by(scaffold, pos = ceiling(pos/500) * 500) %>%
  summarise(variable_1 = mean(variable))

#  scaffold     pos variable_1
#  <chr>      <dbl>      <dbl>
#1 Scaffold_1   500       126.
#2 Scaffold_1  1000       376.
#3 Scaffold_1  1500       626.
#4 Scaffold_1  2000       876.
#5 Scaffold_2   500      1250.
#6 Scaffold_2  1000      1750.
#7 Scaffold_2  1500      2250.
#8 Scaffold_2  2000      2750.
#9 Scaffold_3   500      3226.