Rolling mean of a partitioned data set

tidyverse
1

Hi all,
I was wondering how we can do a rolling mean of a partitioned data set in R without for loop. For eg. Lets say we have a sample data like this

date item Value
2021-07-22 a 2
2021-07-21 a 1
2021-07-20 a 5
2021-07-19 a 7
2021-07-18 a 9
2021-07-17 a 9
2021-07-16 a 1
2021-07-15 a 9
2021-07-14 a 8
2021-07-13 a 0
2021-07-12 a 9
2021-07-22 b 2
2021-07-21 b 8
2021-07-20 b 5
2021-07-19 b 3
2021-07-18 b 0
2021-07-17 b 4
2021-07-16 b 3
2021-07-15 b 7
2021-07-14 b 1
2021-07-13 b 5
2021-07-12 b 1
2021-07-22 c 3
2021-07-21 c 6
2021-07-20 c 0
2021-07-19 c 2
2021-07-18 c 4
2021-07-17 c 3
2021-07-16 c 8
2021-07-15 c 5
2021-07-14 c 3
2021-07-13 c 5
2021-07-12 c 6
2021-07-22 d 7
2021-07-21 d 6
2021-07-20 d 4
2021-07-19 d 9
2021-07-18 d 5
2021-07-17 d 1
2021-07-16 d 2
2021-07-15 d 8
2021-07-14 d 3
2021-07-13 d 7
2021-07-12 d 3

I have used a for loop to calculated the rolling mean for the last 7 days of each item as per the below mentioned code.

	collated_bucket <- filter(Sample_data, Date == min(Sample_data$date)-1) %>%
	  mutate(bucket_7d = NA)
	for(i in unique(Sample_data$item)){
	  temp_bucket <- Sample_data[Sample_data$item == i, ] %>% 
	    arrange( date) %>%
	      mutate(bucket_7d = rollmean(Value, k=7, fill= NA))
	  collated_bucket <- rbind(temp_bucket,collated_bucket)
	}

But if we have a big data set, then for loop is not efficient in R. So I was wondering if we have any other way to achieve at the same rolling mean by item without using loops.
Thanks in advance

2

what package/namespace is this function from ?

3

it is from zoo package

4 
mysamp_data <- structure(list(date = structure(c(18628, 18628, 18628, 18628, 
                                  18628, 18629, 18629, 18629, 18629, 18629, 18630, 18630, 18630, 
                                  18630, 18630, 18631, 18631, 18631, 18631, 18631, 18632, 18632, 
                                  18632, 18632, 18632, 18633, 18633, 18633, 18633, 18633, 18634, 
                                  18634, 18634, 18634, 18634, 18635, 18635, 18635, 18635, 18635, 
                                  18636, 18636, 18636, 18636, 18636, 18637, 18637, 18637, 18637, 
                                  18637, 18638, 18638, 18638, 18638, 18638, 18639, 18639, 18639, 
                                  18639, 18639, 18640, 18640, 18640, 18640, 18640, 18641, 18641, 
                                  18641, 18641, 18641, 18642, 18642, 18642, 18642, 18642, 18643, 
                                  18643, 18643, 18643, 18643, 18644, 18644, 18644, 18644, 18644, 
                                  18645, 18645, 18645, 18645, 18645, 18646, 18646, 18646, 18646, 
                                  18646, 18647, 18647, 18647, 18647, 18647, 18648, 18648, 18648, 
                                  18648, 18648, 18649, 18649, 18649, 18649, 18649, 18650, 18650, 
                                  18650, 18650, 18650, 18651, 18651, 18651, 18651, 18651, 18652, 
                                  18652, 18652, 18652, 18652, 18653, 18653, 18653, 18653, 18653, 
                                  18654, 18654, 18654, 18654, 18654, 18655, 18655, 18655, 18655, 
                                  18655, 18656, 18656, 18656, 18656, 18656, 18657, 18657, 18657, 
                                  18657, 18657, 18658, 18658, 18658, 18658, 18658, 18659, 18659, 
                                  18659, 18659, 18659, 18660, 18660, 18660, 18660, 18660, 18661, 
                                  18661, 18661, 18661, 18661, 18662, 18662, 18662, 18662, 18662, 
                                  18663, 18663, 18663, 18663, 18663, 18664, 18664, 18664, 18664, 
                                  18664, 18665, 18665, 18665, 18665, 18665, 18666, 18666, 18666, 
                                  18666, 18666, 18667, 18667, 18667, 18667, 18667, 18668, 18668, 
                                  18668, 18668, 18668, 18669, 18669, 18669, 18669, 18669, 18670, 
                                  18670, 18670, 18670, 18670, 18671, 18671, 18671, 18671, 18671, 
                                  18672, 18672, 18672, 18672, 18672, 18673, 18673, 18673, 18673, 
                                  18673, 18674, 18674, 18674, 18674, 18674, 18675, 18675, 18675, 
                                  18675, 18675, 18676, 18676, 18676, 18676, 18676, 18677, 18677, 
                                  18677, 18677, 18677, 18678, 18678, 18678, 18678, 18678, 18679, 
                                  18679, 18679, 18679, 18679, 18680, 18680, 18680, 18680, 18680, 
                                  18681, 18681, 18681, 18681, 18681, 18682, 18682, 18682, 18682, 
                                  18682, 18683, 18683, 18683, 18683, 18683, 18684, 18684, 18684, 
                                  18684, 18684, 18685, 18685, 18685, 18685, 18685, 18686, 18686, 
                                  18686, 18686, 18686, 18687, 18687, 18687, 18687, 18687), class = "Date"), 
               group = c("a", "b", "c", "d", "e", "a", "b", "c", "d", "e", 
                         "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", 
                         "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", 
                         "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", 
                         "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", 
                         "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", 
                         "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", 
                         "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", 
                         "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", 
                         "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", 
                         "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", 
                         "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", 
                         "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", 
                         "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", 
                         "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", 
                         "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", 
                         "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", 
                         "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", 
                         "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", 
                         "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", 
                         "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", 
                         "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", 
                         "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", 
                         "e", "a", "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", 
                         "b", "c", "d", "e", "a", "b", "c", "d", "e", "a", "b", "c", 
                         "d", "e"), value = c(1.37095844714667, -0.564698171396089, 
                                              0.363128411337339, 0.63286260496104, 0.404268323140999, -0.106124516091484, 
                                              1.51152199743894, -0.0946590384130976, 2.01842371387704, 
                                              -0.062714099052421, 1.30486965422349, 2.28664539270111, -1.38886070111234, 
                                              -0.278788766817371, -0.133321336393658, 0.635950398070074, 
                                              -0.284252921416072, -2.65645542090478, -2.44046692857552, 
                                              1.32011334573019, -0.306638594078475, -1.78130843398, -0.171917355759621, 
                                              1.2146746991726, 1.89519346126497, -0.4304691316062, -0.25726938276893, 
                                              -1.76316308519478, 0.460097354831271, -0.639994875960119, 
                                              0.455450123241219, 0.704837337228819, 1.03510352196992, -0.608926375407211, 
                                              0.50495512329797, -1.71700867907334, -0.784459008379496, 
                                              -0.850907594176518, -2.41420764994663, 0.0361226068922556, 
                                              0.205998600200254, -0.361057298548666, 0.758163235699517, 
                                              -0.726704827076575, -1.36828104441929, 0.432818025888717, 
                                              -0.811393176186672, 1.44410126172125, -0.431446202613345, 
                                              0.655647883402207, 0.321925265203947, -0.783838940880375, 
                                              1.57572751979198, 0.642899305717316, 0.0897606465996057, 
                                              0.276550747291463, 0.679288816055271, 0.0898328865790817, 
                                              -2.99309008315293, 0.284882953530659, -0.367234642740975, 
                                              0.185230564865609, 0.581823727365507, 1.39973682729268, -0.727292059474465, 
                                              1.30254263204414, 0.335848119752074, 1.03850609869762, 0.920728568290646, 
                                              0.720878162866862, -1.04311893856785, -0.0901863866107067, 
                                              0.623518161999544, -0.953523357772344, -0.542828814573857, 
                                              0.580996497681682, 0.768178737834591, 0.463767588540167, 
                                              -0.885776297409679, -1.09978089864786, 1.51270700980493, 
                                              0.257921437532031, 0.0884402291595864, -0.120896537539089, 
                                              -1.19432889516053, 0.611996898040387, -0.217139845746521, 
                                              -0.182756706331922, 0.93334632857116, 0.821773110508249, 
                                              1.39211637593427, -0.476173923054674, 0.650348560726305, 
                                              1.39111045639, -1.1107888794479, -0.860792586877842, -1.13173868085377, 
                                              -1.4592139995024, 0.0799825532411612, 0.65320433964919, 1.20096537559849, 
                                              1.04475108716773, -1.00320864683985, 1.84848190167275, -0.666773408757817, 
                                              0.105513812456069, -0.422255881868856, -0.122350171954971, 
                                              0.188193034501498, 0.119160957997006, -0.0250925508674029, 
                                              0.108072727942033, -0.485435235846668, -0.504217130687904, 
                                              -1.66109907991481, -0.382333726873818, -0.5126502578778, 
                                              2.7018910003448, -1.36211623118972, 0.137256218558607, -1.49362506731629, 
                                              -1.4704357414368, 0.124702386197007, -0.996639134884037, 
                                              -0.0018226143047082, -0.428258881425815, -0.613671606449495, 
                                              -2.02467784541911, -1.22474795035999, 0.179516441117938, 
                                              0.567620594423535, -0.492877353553475, 6.28840653511241e-05, 
                                              1.12288964337997, 1.43985574297619, -1.09711376840582, -0.117319560250177, 
                                              1.2014984009197, -0.469729580566301, -0.0524694849389963, 
                                              -0.0861072982370896, -0.887679017906432, -0.444684004884738, 
                                              -0.0294448790882381, -0.413868849057924, 1.1133860233682, 
                                              -0.480992841653982, -0.433169032600729, 0.696862576552103, 
                                              -1.05636841317091, -0.0406984751512149, -1.55154482234759, 
                                              1.16716954923568, -0.273645701374081, -0.467845324672254, 
                                              -1.23825232798621, -0.00776203377732663, -0.80028217795166, 
                                              -0.533492329950436, 1.28767524558459, -0.175525870242127, 
                                              -1.07178238415068, 0.163206882467382, -0.36273841562795, 
                                              0.590013547987339, 1.43242192773099, -0.992692511109493, 
                                              0.454650297580283, 0.0848980586784873, 0.895565582264545, 
                                              -0.229778138946266, 0.836619068460613, -1.74505586133669, 
                                              1.68945892131337, 0.864777978518578, -0.150775988885748, 
                                              -1.44900713013917, 0.643008700041982, 0.483193863814768, 
                                              -0.00635562642138871, 0.151455892862424, -0.584108970349804, 
                                              0.368806732630242, 0.294654339719516, -0.279259373342575, 
                                              -1.33623665489315, 0.700748818440034, 0.554196622274033, 
                                              -0.836306592801415, -1.59458816200624, 0.204958580587634, 
                                              -0.34508797797289, 0.252611703364455, -1.29400246548455, 
                                              -0.959170444380363, 1.0857748536799, 0.403774904715714, 0.586487536719298, 
                                              1.81522844615395, 0.128821428602383, -2.00092923773151, 0.33377719743357, 
                                              1.17132512735879, 2.0595392422993, -1.37686159824052, -1.15085556562711, 
                                              -0.705821394760121, -1.05405578207719, -0.645743723142491, 
                                              -0.185377967676503, -1.20122205073999, 2.03697216698315, 
                                              0.107774744885547, -0.0841081005055806, 0.495619641604594, 
                                              0.0374151861179653, -0.13208803695591, 1.4767874235521, -0.217030210092104, 
                                              -1.28360220409223, 0.385667890443402, -0.351512873529092, 
                                              -0.521796093356269, -1.06813120068717, 0.428365903266692, 
                                              -0.174018234426995, 0.515667728648029, -0.234365277305921, 
                                              -0.658503425821771, 1.25023660407872, -0.271763715111397, 
                                              0.947951995875196, -1.20158243010894, -0.466116096375502, 
                                              -0.26935139515318, -0.390965408130861, 1.34870701199171, 
                                              -0.0227647012984126, 0.24422585110345, -0.942371707863923, 
                                              -0.729217276509574, 0.998068908554848, 1.25848166459556, 
                                              1.2488636888101, -1.38063704952533, 2.04996069363639, 1.01687282979954, 
                                              -0.0267174641382579, 0.703607778798261, -0.971385229151864, 
                                              -1.09615624155886, 0.0490504509375971, -1.19849585655778, 
                                              0.190018998559748, 1.29770589960467, -1.03387372296475, -0.738440754213064, 
                                              0.0465639394508634, -1.01759611982611, -0.383283959886278, 
                                              0.872755411671398, 0.969545013970117, 0.383846665027094, 
                                              -1.85155566306746, -0.0539967368261657, 1.06477321433828, 
                                              0.813195037411605, -0.190816474104282, -2.6999298085637, 
                                              0.0609666388011166, 0.573751697483578, 0.0458035797250989, 
                                              0.157412540175875, 0.431565372868771, -0.396549736050212, 
                                              1.30997822575155, 0.470393399873943, -1.2426702705746, 1.38157545638553, 
                                              1.20445893700982, 0.824073963681881, -1.66262940218559, -0.569306343605481, 
                                              0.635513817289526, 0.0437220075778579, 0.348012303661307, 
                                              2.45959354887041, -0.818380324399669, -2.11320011492914, 
                                              0.273695272430415, -0.687596841243412, 0.446041052961866, 
                                              -0.812384723790597, 2.21205548033514, -0.12370597157743, 
                                              -0.477335506030052, -0.166261491486608, 0.862563383626431, 
                                              0.0973404852028189, -1.62561673921129)), row.names = c(NA, 
                                                                                                     -300L), class = c("tbl_df", "tbl", "data.frame"))


library(tidyverse)
library(zoo)

(mysamp_data_prepped <-
    mysamp_data %>% 
    group_by(group) %>% 
    arrange(group,date))

result <- mutate(mysamp_data_prepped,
                   rm = zoo::rollmean(value,7,fill=NA,align="right"))
1 Like
5

Thanks a lot @nirgrahamuk. This is giving the expected results. I thought this will give values of rollmean when group changed from a to b in row 61 of result but i was wrong. Thanks again

6

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.