Reputation: 1331
Below is the sample data.
Month<- c(20185,20186,20187,20189,201810,201811,201812,20191,20192,20193,20194,20195,20196,
20197,20198,20199,201910,201911,201912,20201
,20202,20203,20204,20205,20206,20207
,20208,20209,202010,202011)
emp<-c(2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,36,36,38,40,42,44,46,48,48,50,52,52,54,56)
First question is do I need to use a specified function such as roll_mean or can I simply use an average? Second to have it look for the max date (in this case 202011) and then construct an average of the last 12 months so averaging 202011 on back to 201912, how would I construct such an item?
Upvotes: 1
Views: 1845
Reputation: 19271
This is a base R solution.
First get the dates as class
date, then store the order of the months, define a range (span_month) and finally move over all windows.
Month_t <- as.Date( paste0(
substr(Month,1,4),"-",substr(Month,5,length(Month)),"-1"),
"%Y-%m-%d" )
Month_ord <- order(Month_t)
span_month=12
sapply( 1:((length(Month_ord) + 1) - span_month),
function(x) mean(emp[Month_ord[x]:Month_ord[x + (span_month - 1)]]) )
# [1] 13.00000 15.00000 17.00000 19.00000 21.00000 23.16667 25.16667 27.16667
# [9] 29.16667 31.16667 33.16667 35.16667 37.16667 39.00000 40.83333 42.66667
#[17] 44.33333 45.83333 47.50000
Upvotes: 2
Reputation: 389355
You can build a 12-month moving average using any of the rolling funcitons.
library(dplyr)
df %>%
mutate(date = lubridate::ymd(paste0(ifelse(nchar(Month) == 5,
paste0(substring(Month, 1, 4), '0',
substring(Month, nchar(Month))), Month), '01'))) %>%
arrange(date) %>%
mutate(rolling_mean = zoo::rollmeanr(emp, 12, fill = NA))
If your data is already ordered you don't need to construct the date
variable. You can directly do :
df %>% mutate(rolling_mean = zoo::rollmeanr(emp, 12, fill = NA))
Upvotes: 4