How to calculate rolling mean for each column

user_13549575 0
Viewed 155

I want to calculate mean of every five rows for each column by group, and I tried:

name<-colnames(df[,4:10])

df1<-for (i in name){
    df%>%
      group_by(A)%>%
      summarise(!!paste(i,"mean"):=rollapplyr(get(i),5,mean,fill = NA,by.column=T))
}

result df1 is NULL

then I tried:

for (i in name){
    df%>%
      group_by(A)%>%
      mutate(!!paste(i,"mean"):=rollapplyr(get(i),5,mean,fill = NA,by.column=T))
}

This could run, but nothing happen, df remains the same. And if I assign above code to df1, df1 is still NULL.

I also tried rollmean

df1<- for (i in name){
+     df%>%
+       group_by(CONM)%>%
+       mutate(!!paste(i,"mean"):=rollmean(get(i),5,fill = NA,align = "right"))
+ }

But still get NULL.

My data is like this:

CONM    A  B   C
a       1  2   3
a       2  3   4
a       3  4   5
a       4  5   6
a       5  6   7
a       6  7   8

And I want to get this result for each CONM:

CONM    A  B   C   A_mean  B_mean  C_mean
a       1  2   3    NA       NA      NA
a       2  3   4    NA       NA      NA
a       3  4   5    NA       NA      NA
a       4  5   6    NA       NA      NA
a       5  6   7    3        4       5
a       6  7   8    4        5       6
b       1  2   3    NA       NA      NA

Could someone help me with this? Should I use other packages? Thanks

dplyr r
1 Answers

We can use mutate with across to loop over the columns A to C, specify a lambda function (function(.) or tidyverse shortform ~) to apply the function rollmean on the column

library(dplyr)
library(zoo)
df %>%
   group_by(CONM) %>%
   mutate(across(A:C, ~ rollmean(., 5, fill = NA, align = 'right'), 
             .names = '{col}_mean')) %>%
   ungroup

-output

# A tibble: 7 x 7
#  CONM      A     B     C A_mean B_mean C_mean
#  <chr> <int> <int> <int>  <dbl>  <dbl>  <dbl>
#1 a         1     2     3     NA     NA     NA
#2 a         2     3     4     NA     NA     NA
#3 a         3     4     5     NA     NA     NA
#4 a         4     5     6     NA     NA     NA
#5 a         5     6     7      3      4      5
#6 a         6     7     8      4      5      6
#7 b         1     2     3     NA     NA     NA

Or as @G. Grothendieck mentioned, the rollmeanr would do the right alignment

df %>%
    group_by(CONM) %>%
    mutate(across(A:C, ~ rollmeanr(., 5, fill = NA), .names = '{col}_mean'))

data

df <- structure(list(CONM = c("a", "a", "a", "a", "a", "a", "b"), A = c(1L, 
2L, 3L, 4L, 5L, 6L, 1L), B = c(2L, 3L, 4L, 5L, 6L, 7L, 2L), C = c(3L, 
4L, 5L, 6L, 7L, 8L, 3L)), class = "data.frame", row.names = c(NA, 
-7L))
user_37322710
Related