# Take average of previous weeks

5 messages
Open this post in threaded view
|

## Take average of previous weeks

 Dear all, I have weekly data by city (variable citycode). I would like to take the average of the previous two, three, four weeks (without the current week) of the variable called value. This is what I have tried to compute the average of the two previous weeks; df = df %>%   mutate(value.lag1 = lag(value, n = 1)) %>%   mutate(value .2.previous = rollapply(data = value.lag1,                                      width = 2,                                      FUN = mean,                                      align = "right",                                      fill = NA,                                      na.rm = T)) I crated the lag of the variable first and then attempted to compute the average but this does not seem to to what I want. What I am doing wrong? Any help will be appreciated. The data is below. Thank you. Sincerely, Milu dput(droplevels(head(df, 10))) structure(list(year = c(1970L, 1970L, 1970L, 1970L, 1970L, 1970L, 1970L, 1970L, 1970L, 1970L), citycode = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), month = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 3L), week = c(1L, 2L, 3L, 4L, 5L, 5L, 6L, 7L, 8L, 9L), date = structure(c(1L, 2L, 3L, 4L, 5L, 5L, 6L, 7L, 8L, 9L), .Label = c("1970-01-10", "1970-01-17", "1970-01-24", "1970-01-31", "1970-02-07", "1970-02-14", "1970-02-21", "1970-02-28", "1970-03-07"), class = "factor"),     value = c(-15.035, -20.478, -22.245, -23.576, -8.84099999999995,     -18.497, -13.892, -18.974, -15.919, -13.576)), .Names = c("year", "citycode", "month", "week", "date", "tmin"), row.names = c(NA, 10L), class = "data.frame")         [[alternative HTML version deleted]] ______________________________________________ [hidden email] mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|

## Re: Take average of previous weeks

 There is no  `value` column in the `dput` output shown in the question so using `tmin` instead note that the `width=` argument of `rollapply` can be a list containing a vector of offsets (-1 is prior value, -2 is value before that, etc.) and that we can use `rollapplyr` with an `r` on the end to get right alignment.  See `?rollapply`   library(dplyr)   library(zoo)   roll <- function(x, k) rollapplyr(x, list(-seq(1:k)), mean, fill = NA)   df %>%       group_by(citycode) %>%       mutate(mean2 = roll(tmin, 2), mean3 = roll(tmin, 3), mean4 = roll(tmin, 4)) %>%       ungroup (The code above has been indented 2 spaces so you can identify inadvertent line wrapping by the email system.) On Sun, Mar 25, 2018 at 10:48 AM, Miluji Sb <[hidden email]> wrote: > Dear all, > > I have weekly data by city (variable citycode). I would like to take the > average of the previous two, three, four weeks (without the current week) > of the variable called value. > > This is what I have tried to compute the average of the two previous weeks; > > df = df %>% >   mutate(value.lag1 = lag(value, n = 1)) %>% >   mutate(value .2.previous = rollapply(data = value.lag1, >                                      width = 2, >                                      FUN = mean, >                                      align = "right", >                                      fill = NA, >                                      na.rm = T)) > > I crated the lag of the variable first and then attempted to compute the > average but this does not seem to to what I want. What I am doing wrong? > Any help will be appreciated. The data is below. Thank you. > > Sincerely, > > Milu > > dput(droplevels(head(df, 10))) > structure(list(year = c(1970L, 1970L, 1970L, 1970L, 1970L, 1970L, > 1970L, 1970L, 1970L, 1970L), citycode = c(1L, 1L, 1L, 1L, 1L, > 1L, 1L, 1L, 1L, 1L), month = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, > 2L, 3L), week = c(1L, 2L, 3L, 4L, 5L, 5L, 6L, 7L, 8L, 9L), date = > structure(c(1L, > 2L, 3L, 4L, 5L, 5L, 6L, 7L, 8L, 9L), .Label = c("1970-01-10", > "1970-01-17", "1970-01-24", "1970-01-31", "1970-02-07", "1970-02-14", > "1970-02-21", "1970-02-28", "1970-03-07"), class = "factor"), >     value = c(-15.035, -20.478, -22.245, -23.576, -8.84099999999995, >     -18.497, -13.892, -18.974, -15.919, -13.576)), .Names = c("year", > "citycode", "month", "week", "date", "tmin"), row.names = c(NA, > 10L), class = "data.frame") > >         [[alternative HTML version deleted]] > > ______________________________________________ > [hidden email] mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com ______________________________________________ [hidden email] mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-helpPLEASE do read the posting guide http://www.R-project.org/posting-guide.htmland provide commented, minimal, self-contained, reproducible code.
Open this post in threaded view
|