derek
Reputation: 47
Rolling statistics with varying window size
I am managing a stock daily dataset. Here is the sample code:
A <- cumsum(rnorm(200))
Date <- Sys.Date() + cumsum(sample(1:5, 200, replace = TRUE)) # unequaly spaced time series
data<-data.frame(Date,A)
data<-data%>%
mutate(Year_Month=as.yearmon(Date))
My objective is to calculate a new monthly variable called B. It is calculated as mean from daily data A over a 12-month period that ends in month. For example, 2021-07-03 belongs to July of 2021, I need the set the window over previous 12 months, from July of 2020 to June of 2021 and I calculate the average using all daily data A within this window. Therefore, for [2021-07-03,2021-07-31], the outcome B is the same.
I've tried to use rollapply and runner function, but the difficulty is the window is not constant since the number of days in each month are not constant. I'd like to achieve this goal under dplyr context.
The expected sample output looks like this:
Date Year_Month A B
2021-07-03 July 2021 3.3 2.3
2021-07-08 July 2021 1.5 2.3
2021-07-11 July 2021 4.3 2.3
...
2021-08-04 Aug 2021 2.2 3.2
2021-08-07 Aug 2021 5.7 3.2
2021-08-09 Aug 2021 4.2 3.2
My new dataset is a pure time series with 348 monthly observations:
Date A
2021-07-01 3.1
2021-08-01 4.5
2021-09-01 5.5
...
2021-10-01 4.4
2021-11-01 2.4
2021-12-01 5.5
I am calculating shock by doing AR(2) model over rolling window of 60 months that end in month.The shock in month n+1 is the difference between the actual value of the series and its predicted value (residuals) using the slope coefficients estimated over the preceding 60 months.
I am writing an function and call it inside the slider.
AR_2<-function(x){
arima(x,order=c(1,0,0))$residuals
}
MILIQ_unexpected<-MILIQ%>%
mutate(Shock= slide_index_dbl(A, Date, AR_2, .before = months(60), .after = months(-1), .complete = T))
I got the following error:
Problem with `mutate()` input `B`.
x In iteration 61, the result of `.f` had size 59, not 1.
i Input `B` is `slide_index_dbl(...)`.
Backtrace:
1. `%>%`(...)
11. slider:::stop_not_all_size_one(61L, 59L)
12. slider:::glubort("In iteration {iteration}, the result of `.f` had size {size}, not 1.")
Upvotes: 2
Views: 899
Answers (2)
AnilGoyal
Reputation: 26218
Explanation in view of edited question.
- You are doing it in wrong way again. For rolling computation i.e. assigning each date a value based on previous n variables (vector of size n) you need a function which returns a scalar (vector of size 1) e.g. mean, sum, etc. and hence rollmean, rollsum etc. But
arima(rnorm(50), order = c(1,0,0))$residualsreturn a vector of length 50. So how can a vector of size n>1 be assigned to single value unless you further summarise it. - If you further apply some aggregation function say
mean, your problem can be solved. - e.g.
data %>%
mutate(dummy = floor_date(Date, 'month'),
B = slide_index_dbl(A, dummy, \(x) mean(arima(x, order = c(1,0,0))$residuals), .before = months(12), .after = months(-1), .complete = T))
This works in my R
ANSWER
A slider approach. However since slider index works on date fields directly, so instead of using your Year_month column I created a dummy column as floor_date.
Explanation of Syntax of slider::slider_index_dbl
- It returns a
doubletype vector and hence suffixdbl - It also uses a
indexyour date column and hence_index_ - First argument is vector from which the value/rolling caluculation is to be performed
- second argument is index, Since you are using months instead of date, i created
dummycolumn - third argument is function i.e.
meanto be calculated - .
beforeis used to take 12 index values preceding the current index .afteris used to exclude current index and hence-1- library(lubridate) may also be loaded as several functions are used here.
library(tidyverse)
library(zoo)
set.seed(123)
A <- cumsum(rnorm(200))
Date <- Sys.Date() + cumsum(sample(1:5, 200, replace = TRUE)) # unequaly spaced time series
data<-data.frame(Date,A)
data<-data%>%
mutate(Year_Month=as.yearmon(Date))
library(slider)
library(lubridate)
data %>%
mutate(dummy = floor_date(Date, 'month'),
B = slide_index_dbl(A, dummy, mean, .before = months(12), .after = months(-1)))
#> Date A Year_Month dummy B
#> 1 2021-07-02 -0.56047565 Jul 2021 2021-07-01 NaN
#> 2 2021-07-03 -0.79065314 Jul 2021 2021-07-01 NaN
#> 3 2021-07-08 0.76805518 Jul 2021 2021-07-01 NaN
#> 4 2021-07-09 0.83856357 Jul 2021 2021-07-01 NaN
#> 5 2021-07-13 0.96785130 Jul 2021 2021-07-01 NaN
#> 6 2021-07-15 2.68291629 Jul 2021 2021-07-01 NaN
#> 7 2021-07-17 3.14383250 Jul 2021 2021-07-01 NaN
#> 8 2021-07-21 1.87877126 Jul 2021 2021-07-01 NaN
#> 9 2021-07-22 1.19191841 Jul 2021 2021-07-01 NaN
#> 10 2021-07-26 0.74625644 Jul 2021 2021-07-01 NaN
#> 11 2021-07-31 1.97033824 Jul 2021 2021-07-01 NaN
#> 12 2021-08-05 2.33015207 Aug 2021 2021-08-01 1.1670340
#> 13 2021-08-08 2.73092352 Aug 2021 2021-08-01 1.1670340
#> 14 2021-08-13 2.84160623 Aug 2021 2021-08-01 1.1670340
#> 15 2021-08-18 2.28576510 Aug 2021 2021-08-01 1.1670340
#> 16 2021-08-21 4.07267823 Aug 2021 2021-08-01 1.1670340
#> 17 2021-08-22 4.57052871 Aug 2021 2021-08-01 1.1670340
#> 18 2021-08-25 2.60391156 Aug 2021 2021-08-01 1.1670340
#> 19 2021-08-29 3.30526746 Aug 2021 2021-08-01 1.1670340
#> 20 2021-08-31 2.83247605 Aug 2021 2021-08-01 1.1670340
#> 21 2021-09-05 1.76465234 Sep 2021 2021-09-01 2.0205342
#> 22 2021-09-06 1.54667743 Sep 2021 2021-09-01 2.0205342
#> 23 2021-09-10 0.52067298 Sep 2021 2021-09-01 2.0205342
#> 24 2021-09-13 -0.20821825 Sep 2021 2021-09-01 2.0205342
#> 25 2021-09-14 -0.83325752 Sep 2021 2021-09-01 2.0205342
#> 26 2021-09-16 -2.51995083 Sep 2021 2021-09-01 2.0205342
#> 27 2021-09-19 -1.68216378 Sep 2021 2021-09-01 2.0205342
#> 28 2021-09-22 -1.52879067 Sep 2021 2021-09-01 2.0205342
#> 29 2021-09-23 -2.66692760 Sep 2021 2021-09-01 2.0205342
#> 30 2021-09-25 -1.41311268 Sep 2021 2021-09-01 2.0205342
#> 31 2021-09-26 -0.98664846 Sep 2021 2021-09-01 2.0205342
#> 32 2021-09-28 -1.28171994 Sep 2021 2021-09-01 2.0205342
#> 33 2021-09-30 -0.38659428 Sep 2021 2021-09-01 2.0205342
#> 34 2021-10-02 0.49153921 Oct 2021 2021-10-01 0.9313728
#> 35 2021-10-07 1.31312029 Oct 2021 2021-10-01 0.9313728
#> 36 2021-10-11 2.00176054 Oct 2021 2021-10-01 0.9313728
#> 37 2021-10-15 2.55567820 Oct 2021 2021-10-01 0.9313728
#> 38 2021-10-20 2.49376648 Oct 2021 2021-10-01 0.9313728
#> 39 2021-10-24 2.18780382 Oct 2021 2021-10-01 0.9313728
#> 40 2021-10-25 1.80733282 Oct 2021 2021-10-01 0.9313728
#> 41 2021-10-27 1.11262584 Oct 2021 2021-10-01 0.9313728
#> 42 2021-10-28 0.90470856 Oct 2021 2021-10-01 0.9313728
#> 43 2021-10-30 -0.36068779 Oct 2021 2021-10-01 0.9313728
#> 44 2021-11-02 1.80826818 Nov 2021 2021-11-01 1.0521616
#> 45 2021-11-04 3.01623018 Nov 2021 2021-11-01 1.0521616
#> 46 2021-11-07 1.89312159 Nov 2021 2021-11-01 1.0521616
#> 47 2021-11-08 1.49023676 Nov 2021 2021-11-01 1.0521616
#> 48 2021-11-12 1.02358140 Nov 2021 2021-11-01 1.0521616
#> 49 2021-11-14 1.80354652 Nov 2021 2021-11-01 1.0521616
#> 50 2021-11-17 1.72017745 Nov 2021 2021-11-01 1.0521616
#> 51 2021-11-22 1.97349597 Nov 2021 2021-11-01 1.0521616
#> 52 2021-11-27 1.94494921 Nov 2021 2021-11-01 1.0521616
#> 53 2021-11-30 1.90207876 Nov 2021 2021-11-01 1.0521616
#> 54 2021-12-02 3.27068104 Dec 2021 2021-12-01 1.2041252
#> 55 2021-12-07 3.04491005 Dec 2021 2021-12-01 1.2041252
#> 56 2021-12-11 4.56138066 Dec 2021 2021-12-01 1.2041252
#> 57 2021-12-13 3.01262785 Dec 2021 2021-12-01 1.2041252
#> 58 2021-12-14 3.59724160 Dec 2021 2021-12-01 1.2041252
#> 59 2021-12-18 3.72109585 Dec 2021 2021-12-01 1.2041252
#> 60 2021-12-19 3.93703742 Dec 2021 2021-12-01 1.2041252
#> 61 2021-12-23 4.31667690 Dec 2021 2021-12-01 1.2041252
#> 62 2021-12-28 3.81435345 Dec 2021 2021-12-01 1.2041252
#> 63 2022-01-01 3.48114606 Jan 2022 2022-01-01 1.5660426
#> 64 2022-01-03 2.46257068 Jan 2022 2022-01-01 1.5660426
#> 65 2022-01-05 1.39077945 Jan 2022 2022-01-01 1.5660426
#> 66 2022-01-09 1.69430809 Jan 2022 2022-01-01 1.5660426
#> 67 2022-01-12 2.14251787 Jan 2022 2022-01-01 1.5660426
#> 68 2022-01-17 2.19552210 Jan 2022 2022-01-01 1.5660426
#> 69 2022-01-19 3.11778957 Jan 2022 2022-01-01 1.5660426
#> 70 2022-01-22 5.16787425 Jan 2022 2022-01-01 1.5660426
#> 71 2022-01-25 4.67684309 Jan 2022 2022-01-01 1.5660426
#> 72 2022-01-28 2.36767421 Jan 2022 2022-01-01 1.5660426
#> 73 2022-01-29 3.37341274 Jan 2022 2022-01-01 1.5660426
#> 74 2022-01-30 2.66421197 Jan 2022 2022-01-01 1.5660426
#> 75 2022-02-02 1.97620336 Feb 2022 2022-02-01 1.7814769
#> 76 2022-02-06 3.00177473 Feb 2022 2022-02-01 1.7814769
#> 77 2022-02-10 2.71700172 Feb 2022 2022-02-01 1.7814769
#> 78 2022-02-15 1.49628401 Feb 2022 2022-02-01 1.7814769
#> 79 2022-02-17 1.67758749 Feb 2022 2022-02-01 1.7814769
#> 80 2022-02-19 1.53869613 Feb 2022 2022-02-01 1.7814769
#> 81 2022-02-23 1.54446031 Feb 2022 2022-02-01 1.7814769
#> 82 2022-02-25 1.92974071 Feb 2022 2022-02-01 1.7814769
#> 83 2022-02-26 1.55908068 Feb 2022 2022-02-01 1.7814769
#> 84 2022-03-02 2.20345723 Mar 2022 2022-03-01 1.7984352
#> 85 2022-03-05 1.98297067 Mar 2022 2022-03-01 1.7984352
#> 86 2022-03-06 2.31475263 Mar 2022 2022-03-01 1.7984352
#> 87 2022-03-11 3.41159164 Mar 2022 2022-03-01 1.7984352
#> 88 2022-03-14 3.84677313 Mar 2022 2022-03-01 1.7984352
#> 89 2022-03-18 3.52084155 Mar 2022 2022-03-01 1.7984352
#> 90 2022-03-20 4.66964917 Mar 2022 2022-03-01 1.7984352
#> 91 2022-03-25 5.66315302 Mar 2022 2022-03-01 1.7984352
#> 92 2022-03-30 6.21154998 Mar 2022 2022-03-01 1.7984352
#> 93 2022-04-01 6.45028172 Apr 2022 2022-04-01 1.9901615
#> 94 2022-04-03 5.82237564 Apr 2022 2022-04-01 1.9901615
#> 95 2022-04-05 7.18302809 Apr 2022 2022-04-01 1.9901615
#> 96 2022-04-06 6.58276850 Apr 2022 2022-04-01 1.9901615
#> 97 2022-04-09 8.77010150 Apr 2022 2022-04-01 1.9901615
#> 98 2022-04-13 10.30271212 Apr 2022 2022-04-01 1.9901615
#> 99 2022-04-16 10.06701176 Apr 2022 2022-04-01 1.9901615
#> 100 2022-04-19 9.04059086 Apr 2022 2022-04-01 1.9901615
#> 101 2022-04-22 8.33018430 Apr 2022 2022-04-01 1.9901615
#> 102 2022-04-26 8.58706801 Apr 2022 2022-04-01 1.9901615
#> 103 2022-04-30 8.34037613 Apr 2022 2022-04-01 1.9901615
#> 104 2022-05-03 7.99283353 May 2022 2022-05-01 2.6463239
#> 105 2022-05-07 7.04121496 May 2022 2022-05-01 2.6463239
#> 106 2022-05-09 6.99618724 May 2022 2022-05-01 2.6463239
#> 107 2022-05-12 6.21128277 May 2022 2022-05-01 2.6463239
#> 108 2022-05-16 4.54334083 May 2022 2022-05-01 2.6463239
#> 109 2022-05-18 4.16311431 May 2022 2022-05-01 2.6463239
#> 110 2022-05-19 5.08211092 May 2022 2022-05-01 2.6463239
#> 111 2022-05-22 4.50676396 May 2022 2022-05-01 2.6463239
#> 112 2022-05-26 5.11472828 May 2022 2022-05-01 2.6463239
#> 113 2022-05-27 3.49684557 May 2022 2022-05-01 2.6463239
#> 114 2022-05-28 3.44128361 May 2022 2022-05-01 2.6463239
#> 115 2022-06-01 3.96069081 Jun 2022 2022-06-01 2.9049216
#> 116 2022-06-05 4.26184417 Jun 2022 2022-06-01 2.9049216
#> 117 2022-06-10 4.36752037 Jun 2022 2022-06-01 2.9049216
#> 118 2022-06-11 3.72681436 Jun 2022 2022-06-01 2.9049216
#> 119 2022-06-15 2.87711001 Jun 2022 2022-06-01 2.9049216
#> 120 2022-06-20 1.85298122 Jun 2022 2022-06-01 2.9049216
#> 121 2022-06-23 1.97062782 Jun 2022 2022-06-01 2.9049216
#> 122 2022-06-25 1.02315321 Jun 2022 2022-06-01 2.9049216
#> 123 2022-06-30 0.53259576 Jun 2022 2022-06-01 2.9049216
#> 124 2022-07-04 0.27650357 Jul 2022 2022-07-01 2.8921496
#> 125 2022-07-08 2.12036558 Jul 2022 2022-07-01 2.8921496
#> 126 2022-07-09 1.46841567 Jul 2022 2022-07-01 2.8921496
#> 127 2022-07-12 1.70380225 Jul 2022 2022-07-01 2.8921496
#> 128 2022-07-15 1.78176310 Jul 2022 2022-07-01 2.8921496
#> 129 2022-07-18 0.81990646 Jul 2022 2022-07-01 2.8921496
#> 130 2022-07-23 0.74859838 Jul 2022 2022-07-01 2.8921496
#> 131 2022-07-25 2.19314923 Jul 2022 2022-07-01 2.8921496
#> 132 2022-07-30 2.64465329 Jul 2022 2022-07-01 2.8921496
#> 133 2022-08-01 2.68588621 Aug 2022 2022-08-01 2.9475552
#> 134 2022-08-02 2.26338938 Aug 2022 2022-08-01 2.9475552
#> 135 2022-08-06 0.21014215 Aug 2022 2022-08-01 2.9475552
#> 136 2022-08-08 1.34147937 Aug 2022 2022-08-01 2.9475552
#> 137 2022-08-10 -0.11916070 Aug 2022 2022-08-01 2.9475552
#> 138 2022-08-13 0.62078681 Aug 2022 2022-08-01 2.9475552
#> 139 2022-08-18 2.52989038 Aug 2022 2022-08-01 2.9475552
#> 140 2022-08-19 1.08599722 Aug 2022 2022-08-01 2.9475552
#> 141 2022-08-22 1.78778155 Aug 2022 2022-08-01 2.9475552
#> 142 2022-08-25 1.52558406 Aug 2022 2022-08-01 2.9475552
#> 143 2022-08-27 -0.04656010 Aug 2022 2022-08-01 2.9475552
#> 144 2022-09-01 -1.56122775 Sep 2022 2022-09-01 2.7883422
#> 145 2022-09-02 -3.16276392 Sep 2022 2022-09-01 2.7883422
#> 146 2022-09-06 -3.69367045 Sep 2022 2022-09-01 2.7883422
#> 147 2022-09-10 -5.15542603 Sep 2022 2022-09-01 2.7883422
#> 148 2022-09-11 -4.46750926 Sep 2022 2022-09-01 2.7883422
#> 149 2022-09-14 -2.36740032 Sep 2022 2022-09-01 2.7883422
#> 150 2022-09-18 -3.65443079 Sep 2022 2022-09-01 2.7883422
#> 151 2022-09-22 -2.86669195 Sep 2022 2022-09-01 2.7883422
#> 152 2022-09-23 -2.09764971 Sep 2022 2022-09-01 2.7883422
#> 153 2022-09-28 -1.76544713 Sep 2022 2022-09-01 2.7883422
#> 154 2022-10-01 -2.77382373 Oct 2022 2022-10-01 2.6820771
#> 155 2022-10-05 -2.89327634 Oct 2022 2022-10-01 2.6820771
#> 156 2022-10-07 -3.17367168 Oct 2022 2022-10-01 2.6820771
#> 157 2022-10-10 -2.61068214 Oct 2022 2022-10-01 2.6820771
#> 158 2022-10-12 -2.98312090 Oct 2022 2022-10-01 2.6820771
#> 159 2022-10-15 -2.00614751 Oct 2022 2022-10-01 2.6820771
#> 160 2022-10-18 -2.38072837 Oct 2022 2022-10-01 2.6820771
#> 161 2022-10-19 -1.32801690 Oct 2022 2022-10-01 2.6820771
#> 162 2022-10-24 -2.37719391 Oct 2022 2022-10-01 2.6820771
#> 163 2022-10-28 -3.63734916 Oct 2022 2022-10-01 2.6820771
#> 164 2022-11-01 -0.39630922 Nov 2022 2022-11-01 2.3431466
#> 165 2022-11-06 -0.81316681 Nov 2022 2022-11-01 2.3431466
#> 166 2022-11-09 -0.51493922 Nov 2022 2022-11-01 2.3431466
#> 167 2022-11-11 0.12163046 Nov 2022 2022-11-01 2.3431466
#> 168 2022-11-15 -0.36215017 Nov 2022 2022-11-01 2.3431466
#> 169 2022-11-20 0.15471187 Nov 2022 2022-11-01 2.3431466
#> 170 2022-11-22 0.52367640 Nov 2022 2022-11-01 2.3431466
#> 171 2022-11-25 0.30829589 Nov 2022 2022-11-01 2.3431466
#> 172 2022-11-28 0.37358893 Nov 2022 2022-11-01 2.3431466
#> 173 2022-11-29 0.33952167 Nov 2022 2022-11-01 2.3431466
#> 174 2022-12-02 2.46797357 Dec 2022 2022-12-01 2.1861398
#> 175 2022-12-04 1.72663748 Dec 2022 2022-12-01 2.1861398
#> 176 2022-12-07 0.63064121 Dec 2022 2022-12-01 2.1861398
#> 177 2022-12-10 0.66842961 Dec 2022 2022-12-01 2.1861398
#> 178 2022-12-11 0.97891036 Dec 2022 2022-12-01 2.1861398
#> 179 2022-12-13 1.41543384 Dec 2022 2022-12-01 2.1861398
#> 180 2022-12-15 0.95706850 Dec 2022 2022-12-01 2.1861398
#> 181 2022-12-17 -0.10625763 Dec 2022 2022-12-01 2.1861398
#> 182 2022-12-21 1.15692755 Dec 2022 2022-12-01 2.1861398
#> 183 2022-12-22 0.80727716 Dec 2022 2022-12-01 2.1861398
#> 184 2022-12-27 -0.05823570 Dec 2022 2022-12-01 2.1861398
#> 185 2023-01-01 -0.29451527 Jan 2023 2023-01-01 1.9647998
#> 186 2023-01-04 -0.49169117 Jan 2023 2023-01-01 1.9647998
#> 187 2023-01-07 0.61822912 Jan 2023 2023-01-01 1.9647998
#> 188 2023-01-12 0.70296641 Jan 2023 2023-01-01 1.9647998
#> 189 2023-01-13 1.45702020 Jan 2023 2023-01-01 1.9647998
#> 190 2023-01-17 0.95772818 Jan 2023 2023-01-01 1.9647998
#> 191 2023-01-19 1.17217349 Jan 2023 2023-01-01 1.9647998
#> 192 2023-01-24 0.84748758 Jan 2023 2023-01-01 1.9647998
#> 193 2023-01-28 0.94207111 Jan 2023 2023-01-01 1.9647998
#> 194 2023-02-02 0.04670775 Feb 2023 2023-02-01 1.7721209
#> 195 2023-02-03 -1.26409378 Feb 2023 2023-02-01 1.7721209
#> 196 2023-02-05 0.73311960 Feb 2023 2023-02-01 1.7721209
#> 197 2023-02-08 1.33382843 Feb 2023 2023-02-01 1.7721209
#> 198 2023-02-09 0.08255706 Feb 2023 2023-02-01 1.7721209
#> 199 2023-02-10 -0.52860885 Feb 2023 2023-02-01 1.7721209
#> 200 2023-02-13 -1.71408894 Feb 2023 2023-02-01 1.7721209
Created on 2021-06-29 by the reprex package (v2.0.0)
As a check of rolling mean, I calculated mean of first 123 rows in excel, which is 2.8921496 and is correctly displayed in July 2022 rows.
As desired in comments, if only complete cases are required -
- use argument
.complete = TRUE
data %>%
mutate(dummy = floor_date(Date, 'month'),
B = slide_index_dbl(A, dummy, mean, .before = months(12), .after = months(-1), .complete = T))
Date A Year_Month dummy B
1 2021-07-03 -0.56047565 Jul 2021 2021-07-01 NA
2 2021-07-04 -0.79065314 Jul 2021 2021-07-01 NA
3 2021-07-09 0.76805518 Jul 2021 2021-07-01 NA
4 2021-07-10 0.83856357 Jul 2021 2021-07-01 NA
5 2021-07-14 0.96785130 Jul 2021 2021-07-01 NA
6 2021-07-16 2.68291629 Jul 2021 2021-07-01 NA
7 2021-07-18 3.14383250 Jul 2021 2021-07-01 NA
8 2021-07-22 1.87877126 Jul 2021 2021-07-01 NA
9 2021-07-23 1.19191841 Jul 2021 2021-07-01 NA
10 2021-07-27 0.74625644 Jul 2021 2021-07-01 NA
11 2021-08-01 1.97033824 Aug 2021 2021-08-01 NA
12 2021-08-06 2.33015207 Aug 2021 2021-08-01 NA
13 2021-08-09 2.73092352 Aug 2021 2021-08-01 NA
14 2021-08-14 2.84160623 Aug 2021 2021-08-01 NA
15 2021-08-19 2.28576510 Aug 2021 2021-08-01 NA
16 2021-08-22 4.07267823 Aug 2021 2021-08-01 NA
17 2021-08-23 4.57052871 Aug 2021 2021-08-01 NA
18 2021-08-26 2.60391156 Aug 2021 2021-08-01 NA
19 2021-08-30 3.30526746 Aug 2021 2021-08-01 NA
20 2021-09-01 2.83247605 Sep 2021 2021-09-01 NA
21 2021-09-06 1.76465234 Sep 2021 2021-09-01 NA
22 2021-09-07 1.54667743 Sep 2021 2021-09-01 NA
23 2021-09-11 0.52067298 Sep 2021 2021-09-01 NA
24 2021-09-14 -0.20821825 Sep 2021 2021-09-01 NA
25 2021-09-15 -0.83325752 Sep 2021 2021-09-01 NA
26 2021-09-17 -2.51995083 Sep 2021 2021-09-01 NA
27 2021-09-20 -1.68216378 Sep 2021 2021-09-01 NA
28 2021-09-23 -1.52879067 Sep 2021 2021-09-01 NA
29 2021-09-24 -2.66692760 Sep 2021 2021-09-01 NA
30 2021-09-26 -1.41311268 Sep 2021 2021-09-01 NA
31 2021-09-27 -0.98664846 Sep 2021 2021-09-01 NA
32 2021-09-29 -1.28171994 Sep 2021 2021-09-01 NA
33 2021-10-01 -0.38659428 Oct 2021 2021-10-01 NA
34 2021-10-03 0.49153921 Oct 2021 2021-10-01 NA
35 2021-10-08 1.31312029 Oct 2021 2021-10-01 NA
36 2021-10-12 2.00176054 Oct 2021 2021-10-01 NA
37 2021-10-16 2.55567820 Oct 2021 2021-10-01 NA
38 2021-10-21 2.49376648 Oct 2021 2021-10-01 NA
39 2021-10-25 2.18780382 Oct 2021 2021-10-01 NA
40 2021-10-26 1.80733282 Oct 2021 2021-10-01 NA
41 2021-10-28 1.11262584 Oct 2021 2021-10-01 NA
42 2021-10-29 0.90470856 Oct 2021 2021-10-01 NA
43 2021-10-31 -0.36068779 Oct 2021 2021-10-01 NA
44 2021-11-03 1.80826818 Nov 2021 2021-11-01 NA
45 2021-11-05 3.01623018 Nov 2021 2021-11-01 NA
46 2021-11-08 1.89312159 Nov 2021 2021-11-01 NA
47 2021-11-09 1.49023676 Nov 2021 2021-11-01 NA
48 2021-11-13 1.02358140 Nov 2021 2021-11-01 NA
49 2021-11-15 1.80354652 Nov 2021 2021-11-01 NA
50 2021-11-18 1.72017745 Nov 2021 2021-11-01 NA
51 2021-11-23 1.97349597 Nov 2021 2021-11-01 NA
52 2021-11-28 1.94494921 Nov 2021 2021-11-01 NA
53 2021-12-01 1.90207876 Dec 2021 2021-12-01 NA
54 2021-12-03 3.27068104 Dec 2021 2021-12-01 NA
55 2021-12-08 3.04491005 Dec 2021 2021-12-01 NA
56 2021-12-12 4.56138066 Dec 2021 2021-12-01 NA
57 2021-12-14 3.01262785 Dec 2021 2021-12-01 NA
58 2021-12-15 3.59724160 Dec 2021 2021-12-01 NA
59 2021-12-19 3.72109585 Dec 2021 2021-12-01 NA
60 2021-12-20 3.93703742 Dec 2021 2021-12-01 NA
61 2021-12-24 4.31667690 Dec 2021 2021-12-01 NA
62 2021-12-29 3.81435345 Dec 2021 2021-12-01 NA
63 2022-01-02 3.48114606 Jan 2022 2022-01-01 NA
64 2022-01-04 2.46257068 Jan 2022 2022-01-01 NA
65 2022-01-06 1.39077945 Jan 2022 2022-01-01 NA
66 2022-01-10 1.69430809 Jan 2022 2022-01-01 NA
67 2022-01-13 2.14251787 Jan 2022 2022-01-01 NA
68 2022-01-18 2.19552210 Jan 2022 2022-01-01 NA
69 2022-01-20 3.11778957 Jan 2022 2022-01-01 NA
70 2022-01-23 5.16787425 Jan 2022 2022-01-01 NA
71 2022-01-26 4.67684309 Jan 2022 2022-01-01 NA
72 2022-01-29 2.36767421 Jan 2022 2022-01-01 NA
73 2022-01-30 3.37341274 Jan 2022 2022-01-01 NA
74 2022-01-31 2.66421197 Jan 2022 2022-01-01 NA
75 2022-02-03 1.97620336 Feb 2022 2022-02-01 NA
76 2022-02-07 3.00177473 Feb 2022 2022-02-01 NA
77 2022-02-11 2.71700172 Feb 2022 2022-02-01 NA
78 2022-02-16 1.49628401 Feb 2022 2022-02-01 NA
79 2022-02-18 1.67758749 Feb 2022 2022-02-01 NA
80 2022-02-20 1.53869613 Feb 2022 2022-02-01 NA
81 2022-02-24 1.54446031 Feb 2022 2022-02-01 NA
82 2022-02-26 1.92974071 Feb 2022 2022-02-01 NA
83 2022-02-27 1.55908068 Feb 2022 2022-02-01 NA
84 2022-03-03 2.20345723 Mar 2022 2022-03-01 NA
85 2022-03-06 1.98297067 Mar 2022 2022-03-01 NA
86 2022-03-07 2.31475263 Mar 2022 2022-03-01 NA
87 2022-03-12 3.41159164 Mar 2022 2022-03-01 NA
88 2022-03-15 3.84677313 Mar 2022 2022-03-01 NA
89 2022-03-19 3.52084155 Mar 2022 2022-03-01 NA
90 2022-03-21 4.66964917 Mar 2022 2022-03-01 NA
91 2022-03-26 5.66315302 Mar 2022 2022-03-01 NA
92 2022-03-31 6.21154998 Mar 2022 2022-03-01 NA
93 2022-04-02 6.45028172 Apr 2022 2022-04-01 NA
94 2022-04-04 5.82237564 Apr 2022 2022-04-01 NA
95 2022-04-06 7.18302809 Apr 2022 2022-04-01 NA
96 2022-04-07 6.58276850 Apr 2022 2022-04-01 NA
97 2022-04-10 8.77010150 Apr 2022 2022-04-01 NA
98 2022-04-14 10.30271212 Apr 2022 2022-04-01 NA
99 2022-04-17 10.06701176 Apr 2022 2022-04-01 NA
100 2022-04-20 9.04059086 Apr 2022 2022-04-01 NA
101 2022-04-23 8.33018430 Apr 2022 2022-04-01 NA
102 2022-04-27 8.58706801 Apr 2022 2022-04-01 NA
103 2022-05-01 8.34037613 May 2022 2022-05-01 NA
104 2022-05-04 7.99283353 May 2022 2022-05-01 NA
105 2022-05-08 7.04121496 May 2022 2022-05-01 NA
106 2022-05-10 6.99618724 May 2022 2022-05-01 NA
107 2022-05-13 6.21128277 May 2022 2022-05-01 NA
108 2022-05-17 4.54334083 May 2022 2022-05-01 NA
109 2022-05-19 4.16311431 May 2022 2022-05-01 NA
110 2022-05-20 5.08211092 May 2022 2022-05-01 NA
111 2022-05-23 4.50676396 May 2022 2022-05-01 NA
112 2022-05-27 5.11472828 May 2022 2022-05-01 NA
113 2022-05-28 3.49684557 May 2022 2022-05-01 NA
114 2022-05-29 3.44128361 May 2022 2022-05-01 NA
115 2022-06-02 3.96069081 Jun 2022 2022-06-01 NA
116 2022-06-06 4.26184417 Jun 2022 2022-06-01 NA
117 2022-06-11 4.36752037 Jun 2022 2022-06-01 NA
118 2022-06-12 3.72681436 Jun 2022 2022-06-01 NA
119 2022-06-16 2.87711001 Jun 2022 2022-06-01 NA
120 2022-06-21 1.85298122 Jun 2022 2022-06-01 NA
121 2022-06-24 1.97062782 Jun 2022 2022-06-01 NA
122 2022-06-26 1.02315321 Jun 2022 2022-06-01 NA
123 2022-07-01 0.53259576 Jul 2022 2022-07-01 2.911490
124 2022-07-05 0.27650357 Jul 2022 2022-07-01 2.911490
125 2022-07-09 2.12036558 Jul 2022 2022-07-01 2.911490
126 2022-07-10 1.46841567 Jul 2022 2022-07-01 2.911490
127 2022-07-13 1.70380225 Jul 2022 2022-07-01 2.911490
128 2022-07-16 1.78176310 Jul 2022 2022-07-01 2.911490
129 2022-07-19 0.81990646 Jul 2022 2022-07-01 2.911490
130 2022-07-24 0.74859838 Jul 2022 2022-07-01 2.911490
131 2022-07-26 2.19314923 Jul 2022 2022-07-01 2.911490
132 2022-07-31 2.64465329 Jul 2022 2022-07-01 2.911490
133 2022-08-02 2.68588621 Aug 2022 2022-08-01 2.939545
134 2022-08-03 2.26338938 Aug 2022 2022-08-01 2.939545
135 2022-08-07 0.21014215 Aug 2022 2022-08-01 2.939545
136 2022-08-09 1.34147937 Aug 2022 2022-08-01 2.939545
137 2022-08-11 -0.11916070 Aug 2022 2022-08-01 2.939545
138 2022-08-14 0.62078681 Aug 2022 2022-08-01 2.939545
139 2022-08-19 2.52989038 Aug 2022 2022-08-01 2.939545
140 2022-08-20 1.08599722 Aug 2022 2022-08-01 2.939545
141 2022-08-23 1.78778155 Aug 2022 2022-08-01 2.939545
142 2022-08-26 1.52558406 Aug 2022 2022-08-01 2.939545
143 2022-08-28 -0.04656010 Aug 2022 2022-08-01 2.939545
144 2022-09-02 -1.56122775 Sep 2022 2022-09-01 2.788698
145 2022-09-03 -3.16276392 Sep 2022 2022-09-01 2.788698
146 2022-09-07 -3.69367045 Sep 2022 2022-09-01 2.788698
147 2022-09-11 -5.15542603 Sep 2022 2022-09-01 2.788698
148 2022-09-12 -4.46750926 Sep 2022 2022-09-01 2.788698
149 2022-09-15 -2.36740032 Sep 2022 2022-09-01 2.788698
150 2022-09-19 -3.65443079 Sep 2022 2022-09-01 2.788698
151 2022-09-23 -2.86669195 Sep 2022 2022-09-01 2.788698
152 2022-09-24 -2.09764971 Sep 2022 2022-09-01 2.788698
153 2022-09-29 -1.76544713 Sep 2022 2022-09-01 2.788698
154 2022-10-02 -2.77382373 Oct 2022 2022-10-01 2.656716
155 2022-10-06 -2.89327634 Oct 2022 2022-10-01 2.656716
156 2022-10-08 -3.17367168 Oct 2022 2022-10-01 2.656716
157 2022-10-11 -2.61068214 Oct 2022 2022-10-01 2.656716
158 2022-10-13 -2.98312090 Oct 2022 2022-10-01 2.656716
159 2022-10-16 -2.00614751 Oct 2022 2022-10-01 2.656716
160 2022-10-19 -2.38072837 Oct 2022 2022-10-01 2.656716
161 2022-10-20 -1.32801690 Oct 2022 2022-10-01 2.656716
162 2022-10-25 -2.37719391 Oct 2022 2022-10-01 2.656716
163 2022-10-29 -3.63734916 Oct 2022 2022-10-01 2.656716
164 2022-11-02 -0.39630922 Nov 2022 2022-11-01 2.343147
165 2022-11-07 -0.81316681 Nov 2022 2022-11-01 2.343147
166 2022-11-10 -0.51493922 Nov 2022 2022-11-01 2.343147
167 2022-11-12 0.12163046 Nov 2022 2022-11-01 2.343147
168 2022-11-16 -0.36215017 Nov 2022 2022-11-01 2.343147
169 2022-11-21 0.15471187 Nov 2022 2022-11-01 2.343147
170 2022-11-23 0.52367640 Nov 2022 2022-11-01 2.343147
171 2022-11-26 0.30829589 Nov 2022 2022-11-01 2.343147
172 2022-11-29 0.37358893 Nov 2022 2022-11-01 2.343147
173 2022-11-30 0.33952167 Nov 2022 2022-11-01 2.343147
174 2022-12-03 2.46797357 Dec 2022 2022-12-01 2.183792
175 2022-12-05 1.72663748 Dec 2022 2022-12-01 2.183792
176 2022-12-08 0.63064121 Dec 2022 2022-12-01 2.183792
177 2022-12-11 0.66842961 Dec 2022 2022-12-01 2.183792
178 2022-12-12 0.97891036 Dec 2022 2022-12-01 2.183792
179 2022-12-14 1.41543384 Dec 2022 2022-12-01 2.183792
180 2022-12-16 0.95706850 Dec 2022 2022-12-01 2.183792
181 2022-12-18 -0.10625763 Dec 2022 2022-12-01 2.183792
182 2022-12-22 1.15692755 Dec 2022 2022-12-01 2.183792
183 2022-12-23 0.80727716 Dec 2022 2022-12-01 2.183792
184 2022-12-28 -0.05823570 Dec 2022 2022-12-01 2.183792
185 2023-01-02 -0.29451527 Jan 2023 2023-01-01 1.964800
186 2023-01-05 -0.49169117 Jan 2023 2023-01-01 1.964800
187 2023-01-08 0.61822912 Jan 2023 2023-01-01 1.964800
188 2023-01-13 0.70296641 Jan 2023 2023-01-01 1.964800
189 2023-01-14 1.45702020 Jan 2023 2023-01-01 1.964800
190 2023-01-18 0.95772818 Jan 2023 2023-01-01 1.964800
191 2023-01-20 1.17217349 Jan 2023 2023-01-01 1.964800
192 2023-01-25 0.84748758 Jan 2023 2023-01-01 1.964800
193 2023-01-29 0.94207111 Jan 2023 2023-01-01 1.964800
194 2023-02-03 0.04670775 Feb 2023 2023-02-01 1.772121
195 2023-02-04 -1.26409378 Feb 2023 2023-02-01 1.772121
196 2023-02-06 0.73311960 Feb 2023 2023-02-01 1.772121
197 2023-02-09 1.33382843 Feb 2023 2023-02-01 1.772121
198 2023-02-10 0.08255706 Feb 2023 2023-02-01 1.772121
199 2023-02-11 -0.52860885 Feb 2023 2023-02-01 1.772121
200 2023-02-14 -1.71408894 Feb 2023 2023-02-01 1.772121
If you want to use custom function inside slider you may use invisible function style there. Like
data %>%
mutate(dummy = floor_date(Date, 'month'),
B = slide_index_dbl(A, dummy, \(x) mean(x, na.rm=T), .before = months(12), .after = months(-1), .complete = T))
Upvotes: 3
G. Grothendieck
Reputation: 269556
These alternatives only use packages that it seems you are already using except for (1) which also uses sqldf. (2) could be easily modified to remove dplyr by using transform instead of mutate and making corresponding alterations.
When using variable windows lengths it is generally desirable to also show the number of points that contribute to each so we have added that to each solution below. If you don't need that then the solutions could be slightly shorter.
Note that Year_Month - 1/12 is the prior year/month and Year_Month - 1 is the year-ago year/month.
1) SQL This can be expressed by a self join with the indicated condition. dplyr joins do not support this and it is a lot easier to just use sql which can express this elegantly.
library(dplyr)
library(zoo)
library(sqldf)
res <- data %>%
do( sqldf("select a.*, avg(b.A) B, count(b.A) n
from [.] a
left join [.] b on b.Year_Month between a.Year_Month - 1. and a.Year_Month - 1./12.
group by a.rowid") )
tail(res)
## Date A Year_Month B n
## 195 2023-02-03 -1.26409378 Feb 2023 1.772121 119
## 196 2023-02-05 0.73311960 Feb 2023 1.772121 119
## 197 2023-02-08 1.33382843 Feb 2023 1.772121 119
## 198 2023-02-09 0.08255706 Feb 2023 1.772121 119
## 199 2023-02-10 -0.52860885 Feb 2023 1.772121 119
## 200 2023-02-13 -1.71408894 Feb 2023 1.772121 119
To check we show the starting row of each year/month:
data[!duplicated(data$Year_Month), ]
## Date A Year_Month
## 1 2021-07-02 -0.56047565 Jul 2021
## 12 2021-08-05 2.33015207 Aug 2021
## 21 2021-09-05 1.76465234 Sep 2021
## 34 2021-10-02 0.49153921 Oct 2021
## 44 2021-11-02 1.80826818 Nov 2021
## 54 2021-12-02 3.27068104 Dec 2021
## 63 2022-01-01 3.48114606 Jan 2022
## 75 2022-02-02 1.97620336 Feb 2022
## 84 2022-03-02 2.20345723 Mar 2022
## 93 2022-04-01 6.45028172 Apr 2022
## 104 2022-05-03 7.99283353 May 2022
## 115 2022-06-01 3.96069081 Jun 2022
## 124 2022-07-04 0.27650357 Jul 2022
## 133 2022-08-01 2.68588621 Aug 2022
## 144 2022-09-01 -1.56122775 Sep 2022
## 154 2022-10-01 -2.77382373 Oct 2022
## 164 2022-11-01 -0.39630922 Nov 2022
## 174 2022-12-02 2.46797357 Dec 2022
## 185 2023-01-01 -0.29451527 Jan 2023
## 194 2023-02-02 0.04670775 Feb 2023
so the last row should be the mean of A between rows 75 and 193 inclusive and 193-75+1 = 119 points contribute:
mean(data$A[75:193])
## [1] 1.772121
2) sapply We can also define an indexes function which given a row number generates the indexes to average over and then use that to generate the means and counts.
library(dplyr)
library(zoo)
indexes <- function(ix, ym, x = ym[ix]) which(ym >= x-1 & ym <= x - 1/12)
res2 <- data %>%
do({ indexList <- lapply(1:nrow(.), indexes, .$Year_Month)
mutate(., B = sapply(indexList, function(x) mean(A[x])), n = lengths(indexList))
})
all.equal(res, res2)
## [1] TRUE
3) rollapply Note that rollapply is not limited to constant window sizes as the width argument can be a vector of widths or a list of offsets. See ?rollapply for more information on that function. Using indexes from (2) which creates a list of indexes subtract the current index from each one to give offsets and then use that as the rollapply width argument.
library(dplyr)
library(zoo)
res3 <- data %>%
do({ width <- lapply(1:nrow(.), function(ix) indexes(ix, .$Year_Month) - ix)
mutate(., B = rollapply(A, width, mean), n = lengths(width))
})
all.equal(res3, res)
## [1] TRUE
4) rollapply - 2 In (3) we used a variable window size directly with rollapply but another way is to reduce the problem to a fixed window size and then use rollapply on that.
library(dplyr)
library(zoo)
roll <- function(x, fill=NA) rollapply(x, list(-seq(12)), sum, partial=TRUE, fill=fill)
summary <- data %>%
group_by(Year_Month) %>%
summarize(s = sum(A), n = n(), .groups = "drop") %>%
mutate(s = roll(s), n = roll(n, fill = 0), B = s/n) %>%
select(Year_Month, B, n)
res4 <- data %>%
left_join(summary, by = "Year_Month")
all.equal(res, res4)
## [1] TRUE
Note
Because the question used random numbers without set.seed, the library statements are missing and it gives a different result each day due to the use of Sys.Date() its code is not reproducible so to be clear we use:
library(dplyr)
library(zoo)
set.seed(123)
A <- cumsum(rnorm(200))
Date <- as.Date("2021-06-29") + cumsum(sample(5, 200, replace = TRUE))
data <- data.frame(Date, A) %>% mutate(Year_Month = as.yearmon(Date))
Upvotes: 2
Related Questions
- Rolling Window based on a fixed time interval
- Computing sd() with irregular rolling windows in r
- Rolling regression with expanding window
- Using Rolling Average to Calculate over Window of Values
- Rolling sum with varying window of a data.table
- Rolling average using groupby and varying window length
- fixed rolling window
- Rolling window in dplyr?
- Rolling window in R
- Rolling mean with different window length