Rolling Seasonal Return Factors with Missing Data
Article Quant Q&A · Author: Bart
Summary
The document describes building monthly seasonal and seasonal reversal signals from up to twenty years of historical returns. For each date and asset, the seasonal measure averages observations from the same calendar month at annual lags, while the reversal measure averages observations from other months. Computation begins after a specified history threshold, and the example uses a minimum of five years of observations.
The proposed implementation first places observations on a regular monthly time grid, then loops over assets and dates to select eligible lags and calculate means. Separate settings control the minimum usable count for same-month and other-month samples, as well as whether recent windows must contain no missing values. The example data show substantial differences in missingness across series, including entirely empty series. Thus, missing-value policy materially affects when signals appear and what they represent; the suggested thresholds are parameters to explore, not universal choices. The document offers an implementation approach, but no empirical evidence about the factor’s returns or investment performance.
Key ideas
- The seasonal signal averages returns from the same calendar month at annual lags.
- The reversal signal averages returns from months other than the current calendar month.
- A rolling history can expand from a minimum of five years to a maximum of twenty years.
- Regular monthly indexing helps align observations before selecting lagged values.
- Missing-value thresholds determine when each signal can be computed.
Tags
Full text
# Rolling seasonal and seasonal reversal patterns factor investing
# Rolling seasonal and seasonal reversal patterns factor investing
I am trying to create a pattern variable that takes the mean of the same month (lag 12, 24... 240) for the last 20 years and the mean of the other months lag (1-11, 13-23, 25-35... 229-239). (Replicating this paper: Are Return Seasonalities Due to Risk or Mispricing? by Matti Keloharju et al. https://papers.ssrn.com/sol3/papers.cfm?abstract_id=3276334)
It should start with calculating after at least 5 years of data are available. (60 months observations that are not na). <- atleast 5 years are required to start it. And it can use up to 20 years of past data. So it looks like a rolling window that uses a minimum of 60 months and a maximum of 240 months. Reversal does the opposite, take the mean of all other months than the current month.
I am trying to create a new xts object called seasonal from this xts data (dput) below:
```
299916 299918 299921 299922 299923 299925 299926 299927 299932
1926-01-15 0.00000000 NA NA 0.00000000 NA 0.10714286 NA 0.03238868 NA
1926-02-15 0.02040818 NA NA 0.00000000 NA 0.00000000 NA 0.00000000 NA
1926-03-15 0.00000000 NA NA 0.00000000 NA 0.00000000 NA 0.00000000 NA
1926-04-15 0.00000000 NA NA 0.00000000 NA 0.02990030 NA 0.00000000 NA
1926-05-15 0.00000000 NA NA 0.00000000 NA 0.00000000 NA 0.00000000 NA
1926-06-15 0.00000000 NA NA 0.02564103 NA 0.00000000 NA -0.03921569 NA
1926-07-15 0.00000000 NA NA 0.00000000 NA 0.00000000 NA 0.03375532 NA
1926-08-15 0.02040820 NA NA 0.00000000 NA 0.00000000 NA 0.00000000 NA
1926-09-15 0.00000000 NA NA 0.00000000 NA 0.00000000 NA 0.00000000 NA
1926-10-15 0.00000000 NA NA 0.00000000 NA 0.02990033 NA 0.00000000 NA
1926-11-15 0.00000000 NA NA 0.00000000 NA 0.00000000 NA 0.00000000 NA
1926-12-15 0.00000000 NA NA 0.05193955 NA 0.00000000 NA 0.00000000 NA
```
The function should take the mean of every different month for the past 240 months, requiring at least 60 months of data. I have tried to do it like this, and then afterwards setting every value that does not have a previous 60 observations back to `NA`
```
reversal <- rollapplyr(data, FUN= function(x) { i <- month(end(x)); xx <- x[month(time(x)) != i]; if (sum(!is.na(xx)) < 60) NA else mean(xx, na.rm = TRUE) },width=60)
```
How could i change this function to do what i wanted or could you specify how i would do it elsewise, it can be done in xts or dataframe/table?
A representative example of my data can be created with the dput below, it contains 254 months for 9 series. My full dataset contains 20.000 series with 2400 months.
```
structure(c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, 0, 0, 0.0204081541882217, 0, 0, 0, 0, 0, 0.0204081632653061,
0, 0, 0, 0, 0, 0.0204081856820741, 0, 0, 0, 0, 0, 0.020408116887505,
0, 0, 0, 0, 0, 0.0204082111076715, 0, 0, 0, 0, 0, 0.0204081773309608,
0, 0, 0, 0, 0, 0.0204082000235501, 0, 0, 0, 0, 0, 0.0245901295401265,
0, -0.02, 0.00411527249537569, 0.0122950819672132, 0, -0.0122448532886309,
-0.00826446280991733, -0.0083333333333333, 0.00847460093125374,
-0.00840336134453779, 0, 0.00427348727226984, -0.0212765957446809,
-0.00869565217391299, 0.0141844418025596, 0.0314685314685315,
-0.00847457627118642, -0.0155440227617525, 0.0175438596491229,
0, -0.00348429426612118, 0.0270979020979021, -0.0127659574468085,
0.00174219306566248, -0.0217391304347826, -0.0222222222222223,
0.0110294230176353, -0.159090909090909, 0, 0.0131434321412625,
0, 0.0594594594594595, 0.0123967291289286, 0.0306122448979591,
0, 0.0120240752232155, 0, 0, 0.0821642999948407, 0, 0, 0.0112359316085653,
0, 0.0462962962962963, -0.0161001910409154, 0.0127272727272727,
-0.0170556552962298, 0.0156971107960928, -0.0181818181818182,
0, 0.0112359150795374, -0.0092592592592593, 0, 0.0113421445835307,
0, 0, 0.0113421590975016, 0, 0, 0.0113421411253651, -0.0373831775700935,
0, -0.0176817136251634, -0.095, -0.00552486187845302, -0.00900899940490696,
0.0227272727272727, 0.0333333333333334, 0.0457516763242793, -0.0104166666666666,
0, 0.0127931856513575, 0, -0.136842105263158, 0.0086633929899842,
-0.00613496932515334, 0.0123456790123457, 0.0272277072419502,
0.0120481927710843, -0.0238095238095238, 0.150990084720904, 0,
-0.010752688172043, 0.00770923535293289, 0, -0.0273224043715847,
0.0136674165716602, 0, 0, 0.0136674003835837, 0.00561797752808979,
-0.0279329608938548, -0.0151515440546275, -0.0118343195266272,
0.0179640718562875, -0.0214797185262542, -0.073170731707317,
-0.0657894736842105, 0.0171919488931722, -0.0422535211267606,
0.0735294117647058, 0.0167131224922661, -0.0136986301369864,
0.0138888888888888, 0.0306406510458519, 0.0405405405405406, 0,
0, 0.0539845862023678, -0.0121951219512195, 0, 0.0275689444179268,
-0.0121951219512195, -0.0185185185185185, 0.0370370373102091,
0.00985221674876846, 0, 0.0272277472321323, 0.0240963855421688,
0, 0.0143198308965067, 0, 0.00588235294117645, 0.01423488305071,
-0.00584795321637432, -0.00352941176470589, -0.00598801382823411,
0.0240963855421688, 0, 0.0011933380682736, -0.034564958283671,
-0.0185185185185185, -0.00383144288671766, 0, -0.0128205128205128,
0.0224274500077706, 0, 0.00645161290322571, 0.0091145910403283,
-0.0258064516129032, -0.0728476821192053, -0.0188953207488923,
0, -0.0222222222222223, -0.0354937987269461, -0.016, -0.00813008130081305,
0.0200668509828716, 0, 0, 0.0200668675946716, 0.0327868852459017,
0.0952380952380953, 0.0176991080240663, 0, 0, 0.032448344590249,
0.0357142857142858, 0.0206896551724138, 0.00961540670946448,
-0.00680272108843538, 0, -0.0373961244386327, 0.0071942446043165,
0, -0.0462427269872232, -0.0909090909090909, -0.0166666666666667,
-0.0154638725652271, -0.0401396160558464, -0.04, 0.0192307884702247,
0, 0, -0.0229885147943931, -0.0196078431372549, 0, -0.00406502219618243,
0.112244897959184, 0.00917431192660545, 0.0332103540692785, -0.0267857142857143,
-0.0825688073394495, -0.0650406737793374, 0.0108695652173914,
0.032258064516129, 0.27118642846326, -0.216666666666667, -0.00638297872340421,
0.132897578730317, 0.00961538461538458, 0.0285714285714285, -0.0112781771244181,
0.0114068441064639, 0.0526315789473684, 0.0326087199461425, 0,
0.0543859649122806, 0.0286678137393122, 0, 0, 0.0132890125985738,
0, 0.0327868852459017, 0.0289388958518526, 0, -0.046875, -0.0365448609059544,
0, 0.0172413793103448, 0.0223367745039953, 0.0184873949579831,
0.0478547854785478, 0.0047846756393235, 0.0158730158730158, 0,
0.0205696447070527, 0.00465116279069777, 0.00308641975308643,
0.0436137444283011, 0.0388059701492538, -0.00862068965517238,
0.0131964522912278, 0, 0.0173661360347324, 0.0791366746140696,
0.18, -0.00677966101694916, 0.018202502844141, 0, 0, 0, 0, 0,
0, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
0, 0, 0.0434782958776339, 0, 0, 0, 0, 0, 0.0434782800406497,
0, 0, 0, 0, 0, 0.0434782103457703, 0, 0, 0, 0, 0, 0.0434782916814265,
0, 0, 0.458333333333333, 0, 0, 0.0294117219026415, 0, 0, 0, -0.0857142857142857,
0, 0.0322580189119368, 0, 0, 0, 0, 0, 0.0322580380086912, 0,
0, 0, 0, 0, 0.0322580416902791, 0, 0, 0, 0, 0, 0, 0.0256410490536543,
0, 0, 0, 0, 0, 0.0256410131897644, 0, 0, 0, 0, 0, 0.0256410519443155,
0, 0, 0, 0, 0, 0.0256409881588386, 0, 0, 0, 0, 0, 0.02564103141131,
0, 0, 0, 0, 0, 0.0256409900095205, 0, 0, 0, 0, 0, 0.0256410420971556,
0, 0, 0, 0, 0, 0.0256410559477311, 0, 0, 0, 0, 0, 0.0256410412846335,
0, 0, 0, 0, 0, 0.112820481561326, 0, 0, 0, 0, 0, 0.0235848931975595,
0, 0, 0, 0, 0, 0.0235849080361152, 0, 0, 0, 0, 0, 0.0235848862220247,
0, 0, 0, 0, 0, 0.0235849631986507, 0, 0, 0, 0, 0, 0.0235849588268833,
0, 0, 0, 0, 0, 0.0235848786075292, NA, NA, NA, 0, 0, 0.0256410428811993,
0, 0, 0, 0, 0, 0.0256410424501945, 0, 0, 0, 0, 0, 0.0256409955946366,
0, 0, 0, 0, 0, 0.0256410256410255, 0, 0, 0, 0, 0, 0.0256410282376482,
0, 0, 0, 0, 0, 0.0256410003239547, 0, 0, 0, 0, 0, 0.0256410256410255,
0, 0, 0, 0, 0, 0.051939553219448, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, 0.0125, 0.00823045267489708, -0.0122448979591837,
0, -0.00000000788160592346543, 0.0166666666666666, 0.00819672131147531,
0.0245901550711649, 0, 0, 0.00806452274871328, -0.016, 0, 0.00409835115967616,
0.0122448979591836, 0, 0.0528455186588044, 0.0231660231660231,
0, 0.00760453793319082, 0.0188679245283019, 0, -0.00373139106268205,
0.0224719101123596, 0, 0.0407407252677641, -0.0142348754448398,
0, 0.0218977592105467, 0.0285714285714285, 0.0104166666666667,
0.0173611064838204, -0.0034129692832765, -0.0102739726027398,
0.0104894967491107, -0.0034602076124568, 0.0243055555555556,
0.00342467186465134, 0.0170648464163823, -0.0134228187919463,
0.0103092607128876, 0.010204081632653, 0, 0.01020406435314, -0.0067340067340067,
0.064406779661017, 0.00964630022955704, -0.00636942675159236,
0.0192307692307692, 0.0158730447127187, -0.00624999999999998,
0, 0.00952382714461875, 0, 0.00314465408805042, 0.0158227588717181,
-0.00934579439252337, 0.00628930817610063, 0.0157729190068479,
0, 0, 0.0188088112555982, 0.0246153846153847, 0, -0.0212121098652664,
0.00309597523219818, 0, 0.0560747433344471, 0, -0.00294985250737467,
0.00895524505807921, 0, 0.0325443786982249, 0.13005777945278,
0.0230179028132993, -0.1375, 0.0175438926021227, 0.0229885057471264,
0, 0, 0.0208333207208933, -0.00583090379008744, 0, 0.00887574455774498,
0, 0, 0.00000000831267255030355, 0, 0, 0.00895518573275456, 0,
0, 0.00895520216038759, -0.00887573964497046, 0, 0.00903615610971986,
0, -0.0029850746268657, 0.00906341238649899, -0.00598802395209586,
0, 0.00911850382350377, 0, 0, 0.0455926784226357, 0, 0, 0.00879765773622498,
0, 0, -0.00293257673181135, 0, 0, -0.0326409596379558, 0, -0.00306748466257667,
0.0124223852596659, 0, 0, 0.00928788593288155, 0, 0, 0.00928796306095081,
-0.0153374233128835, 0, 0.022012613579665, 0, 0, 0.00931673740209726,
0.0153846153846153, -0.0212121212121212, 0.00937500848303263,
0, 0, 0.0000000215501252398553, 0, 0, -0.00630915288849643, 0.0253968253968253,
0, 0.0125096716562463, 0, -0.00928792569659442, 0.0126282128787867,
0, 0, 0.012628215325351, 0.03125, NA, NA, NA, 0, -0.00961461351536286,
0.0225806451612902, 0, 0.0127488972826724, 0, -0.0220820189274448,
0.006504146808052, 0, 0.00649350649350655, 0.0065041420527816,
-0.00649350649350644, 0, 0.00658987286103452, 0, 0, 0.0133000319429604,
0, 0, 0.0332993752478528, 0.0225806451612902, 0, 0.0127489334123358,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 0, 0,
0.0125, 0, 0, 0, 0.0000000470950278774751, 0.0208333333333333,
0, 0, 0, 0, 0.0330578631544169, 0, 0, 0, 0, 0, 0.0162601408552423,
0, 0, 0, 0, 0, 0.0162602044681432, 0, 0, 0, 0, 0, 0.016260156193258,
0.04, 0, 0, 0, 0, 0.015625038088364, 0, 0.115384615384615, 0,
0, 0, 0.0139860178006244, 0, 0, 0, 0, 0, 0, 0.0204081589343259,
0, 0, 0, 0, 0, 0.0544217489712062, 0, 0, 0, 0, 0, 0.0231023394914776,
0, 0, 0, 0, 0, 0.0231023095735579, 0.129032258064516, 0, -0.0571428571428572,
0, 0, 0.0248446769584489, 0.0909090909090908, 0, 0, 0, 0.0833333333333333,
0.0209423894771081, 0, 0.0256410256410255, -0.025, -0.0256410256410257,
0.0263157894736843, 0.0263158335490496, 0, 0, 0, 0, 0, 0.0263157751586089,
0, 0, 0, 0, 0, 0.0263158220190696, 0, 0, 0, 0.0512820512820513,
0, 0.0374999891097409, 0, 0, 0, 0, 0.0120481927710843, 0.0370369997156672,
0, 0, 0, 0, 0, 0.0370370430351146, 0.0142857142857142, -0.0140845070422535,
0, 0, 0, 0.0370370023338744, 0, 0, 0.0952380952380953, 0, 0,
0.033707833020765, 0, 0, 0, 0, 0.0217391304347827, 0.0329670297653515,
0, 0, 0, 0, 0, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, 0.04, 0, 0, 0, 0.0358565635008701, 0, 0, 0.0769230769230769,
0, 0, 0.0332103552644116, 0, 0, 0, 0, 0, 0.0332102827414564,
0, 0, 0, 0, 0, 0.0332103518270421, 0, 0, 0.107142857142857, 0,
0, 0.0299002986155619, 0, 0, 0, 0, 0, 0.0299003344509978, 0,
0, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, -0.00579710144927537,
0.00291545189504383, 0.0145348837209303, 0.0143266475644699,
0.00864557559880841, 0, 0.0171428571428571, 0.00561797752808979,
0.0083798882681565, 0.0055401662049861, 0.0308988813822892, 0.013623978201635,
0, 0.0188172043010753, 0.0105540897097625, 0.0104438642297651,
0.0421052628493059, 0.0378787878787878, 0.00486618004866179,
0, -0.00242130750605329, -0.0194174757281553, 0.0302266962346123,
0.00244498777506119, 0.0170731707317073, 0.0191846522781776,
0.0352941176470589, -0.00454545454545452, 0.0162413134145891,
0.00456621004566204, 0.00909090909090904, -0.00900900900900903,
-0.00454545454545452, -0.0136986301369864, 0.0117647448722245,
0, -0.013953488372093, 0.00235849056603765, 0, -0.0164705882352941,
0.0170316340770984, -0.0167464114832536, -0.0024330900243309,
-0.0146341463414634, -0.00495049504950495, 0.00497512437810954,
0.0125944428735607, 0.0181817692878703, 0.00510204081632648,
-0.00507614213197971, 0.00510204081632648, -0.0101522842639594,
-0.0230769230769231, 0.0160427888756096, -0.0184210526315789,
-0.00268096514745308, -0.010752688172043, 0, -0.00271739130434778,
0.0333333501791231, 0.00268817204301075, -0.0107238605898123,
0, -0.00271002710027102, 0.00271739130434789, 0.00828727571200982,
0, 0.0054794520547945, 0.0108991825613078, 0, 0.0107816711590296,
-0.00271742677638842, -0.00817438692098094, -0.00274725274725274,
-0.00275482093663915, -0.0248618784530387, 0.0113314447592068,
0.020000043317042, 0.00840336134453779, 0, -0.0333333333333333,
0, 0, 0.0263929667780924, -0.0142857142857142, 0.00869565217391299,
0, 0, 0.00574712643678166, 0.029154496606087, -0.0198300283286119,
0.0057803468208093, 0, 0.0172413793103448, -0.00282485875706218,
0.0289017103307714, 0.0112359550561798, 0.0416666666666667, 0,
0.0426666666666666, 0, 0.0416666949099533, 0.0125, 0.0135802469135802,
0, 0.00852618757612666, 0, 0.0417690574456395, -0.00707547169811318,
0.00237529691211402, -0.00236966824644547, -0.0142517814726841,
0.00240963855421694, 0.014669964303506, -0.0240963855421686,
0, -0.00493827160493832, -0.0124069478908189, 0.00502512562814061,
0.0178116811171682, 0.00249999999999995, 0, 0.0099750623441397,
-0.0370370370370371, -0.00512820512820511, 0.0104987110923782,
0, 0.0207792207792208, -0.00254452926208648, 0, 0, 0.0181817993718378,
0, 0.10969387755102, 0, 0.0390804597701149, -0.00442477876106195,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, 0, 0, 0, 0, 0, 0.0249999965160004, 0,
0, 0, 0, 0, 0.0250000254926801, 0, 0, 0, 0, 0, 0.024999970154912,
0, 0, 0, 0, 0, 0.02499999352952, 0, 0, 0, 0, 0, 0.024999977905678,
0, 0, 0, 0, 0, 0.0249999953809779, 0, 0, 0, 0, 0, 0.0250000405572677,
0, 0, 0, 0, 0, 0.0249999824141935, 0, 0, 0, 0, 0, 0.0249999828431153,
0, 0, 0, 0, 0, 0.0249999525744644, 0, 0, 0, 0, 0, 0.0249999673396584,
0, 0, 0, 0, 0, 0.0249999787575008, 0, 0, 0, 0, 0, 0.0250000388582312,
0, 0, 0, 0, 0, 0.0249999823084484, 0, 0.0365853658536586, 0,
0, 0, 0.0240964116847584, 0, 0, 0, 0, 0, 0.0240964029472615,
0, 0, 0, 0, 0, 0.108433759466686, 0, 0, 0, 0, 0, 0.0222222426287435,
-0.0111111497349847, 0, 0, -0.0224719101123596, 0.0229885057471264,
0, 0.0229884845346728, 0, 0, 0, 0, 0, 0.022988524508061, 0, 0,
0, 0, 0, 0.0229885414605784, 0, 0, 0, 0, -0.0224719101123596,
0.023529399210102, 0, 0, 0, 0, 0, 0.0235294476191454, 0, 0, 0,
0, 0, 0.0235294384982783, 0, 0, 0, 0, 0, 0.0235293973542194,
0, 0, 0, 0, 0, 0.0235294152845087, 0, 0, 0, 0, 0, 0.0235294203619254,
0, 0, 0, 0, 0, 0.0406698443321822, 0.0229885057471264, 0, 0,
0, 0, 0.0397196529374826, -0.0224719101123596, 0, 0, 0, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, -0.0114942528735632, 0.0116279069767442,
0.0638297903631726, -0.0111111111111111, 0, 0, 0, 0, 0.0229885180053004,
0, 0, 0, 0, 0, 0.0324825448571429, 0, 0, 0, 0, 0, 0.0324825504526534,
0, 0, -0.0786516853932584, 0.0731707317073171, 0, 0.0141508993175119,
0, 0.0348837209302326, 0.0112359550561798, 0, 0.0444444444444445,
0.0572687088055652, 0, 0, 0, 0, 0.0104166666666667, 0.0297240211418017,
0, 0, 0, 0.0515463917525774, 0, 0.0282257846681111, 0, 0, 0,
0, 0, 0.0323886782773917, 0, 0, 0, 0, -0.0392156862745098, 0.033755315646558,
0, 0, 0, 0, 0, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
), class = c("xts", "zoo"), .indexCLASS = "Date", tclass = "Date", .indexTZ = "UTC", tzone = "UTC", index = structure(c(-2031696000,
-2029017600, -2026425600, -2023747200, -2021155200, -2018476800,
-2015798400, -2013379200, -2010700800, -2008108800, -2005430400,
-2002838400, -2000160000, -1997481600, -1994889600, -1992211200,
-1989619200, -1986940800, -1984262400, -1981843200, -1979164800,
-1976572800, -1973894400, -1971302400, -1968624000, -1965945600,
-1963353600, -1960675200, -1958083200, -1955404800, -1952726400,
-1950220800, -1947542400, -1944950400, -1942272000, -1939680000,
-1937001600, -1934323200, -1931731200, -1929052800, -1926460800,
-1923782400, -1921104000, -1918684800, -1916006400, -1913414400,
-1910736000, -1908144000, -1905465600, -1902787200, -1900195200,
-1897516800, -1894924800, -1892246400, -1889568000, -1887148800,
-1884470400, -1881878400, -1879200000, -1876608000, -1873929600,
-1871251200, -1868659200, -1865980800, -1863388800, -1860710400,
-1858032000, -1855612800, -1852934400, -1850342400, -1847664000,
-1845072000, -1842393600, -1839715200, -1837123200, -1834444800,
-1831852800, -1829174400, -1826496000, -1823990400, -1821312000,
-1818720000, -1816041600, -1813449600, -1810771200, -1808092800,
-1805500800, -1802822400, -1800230400, -1797552000, -1794873600,
-1792454400, -1789776000, -1787184000, -1784505600, -1781913600,
-1779235200, -1776556800, -1773964800, -1771286400, -1768694400,
-1766016000, -1763337600, -1760918400, -1758240000, -1755648000,
-1752969600, -1750377600, -1734480000, -1731801600, -1729382400,
-1726704000, -1724112000, -1721433600, -1718841600, -1716163200,
-1713484800, -1710892800, -1708214400, -1705622400, -1702944000,
-1700265600, -1697760000, -1695081600, -1692489600, -1689811200,
-1687219200, -1684540800, -1681862400, -1679270400, -1676592000,
-1674000000, -1671321600, -1668643200, -1666224000, -1663545600,
-1660953600, -1658275200, -1655683200, -1653004800, -1650326400,
-1647734400, -1645056000, -1642464000, -1639785600, -1637107200,
-1634688000, -1632009600, -1629417600, -1626739200, -1624147200,
-1621468800, -1618790400, -1616198400, -1613520000, -1610928000,
-1608249600, -1605571200, -1603152000, -1600473600, -1597881600,
-1595203200, -1592611200, -1589932800, -1587254400, -1584662400,
-1581984000, -1579392000, -1576713600, -1574035200, -1571529600,
-1568851200, -1566259200, -1563580800, -1560988800, -1558310400,
-1555632000, -1553040000, -1550361600, -1547769600, -1545091200,
-1542412800, -1539993600, -1537315200, -1534723200, -1532044800,
-1529452800, -1526774400, -1524096000, -1521504000, -1518825600,
-1516233600, -1513555200, -1510876800, -1508457600, -1505779200,
-1503187200, -1500508800, -1497916800, -1495238400, -1492560000,
-1489968000, -1487289600, -1484697600, -1482019200, -1479340800,
-1476921600, -1474243200, -1471651200, -1468972800, -1466380800,
-1463702400, -1461024000, -1458432000, -1455753600, -1453161600,
-1450483200, -1447804800, -1445299200, -1442620800, -1440028800,
-1437350400, -1434758400, -1432080000, -1429401600, -1426809600,
-1424131200, -1421539200, -1418860800, -1416182400, -1413763200,
-1411084800, -1408492800, -1405814400, -1403222400, -1400544000,
-1397865600, -1395273600, -1392595200, -1390003200, -1387324800,
-1384646400, -1382227200, -1379548800, -1376956800, -1374278400,
-1371686400, -1369008000, -1366329600, -1363737600, -1361059200,
-1358467200), tzone = "UTC", tclass = "Date"), .Dim = c(252L,
9L), .Dimnames = list(NULL, c("299916", "299918", "299921", "299922",
"299923", "299925", "299926", "299927", "299932")))
```
## Answer by Enrico Schumann (score 3)
https://quant.stackexchange.com/a/46831
As Chris already wrote in his comments: your description is not complete. But I would suggest to write a simple loop over your data matrix. There is no need for working with `zoo`/`xts` while doing these computations.
I use your sample dataset and call it `data0`.
```
library("xts")
library("zoo")
time0 <- index(data0)
assets <- colnames(data0)
data0 <- coredata(data0)
```
From your description, it seems that you work with monthly data. So I first map your data to a regularly spaced time-series matrix `data`.
```
time <- seq(min(time0), max(time0), by = "1 month")
data <- array(NA_real_, dim = c(length(time), length(assets)))
data[match(time0, time), ] <- data0
```
Your series have not only initial missing values, but also within the series. To see the fraction of missing values in a given column, say
```
apply(data, 2, function(x) sum(is.na(x))/length(x))
## [1] 0.8327 0.0778 1.0000 0.1051 0.3346 0.3152 0.4669 0.0623 1.0000
```
It is not clear from your description how you want to handle those missing values. This seems relevant, as there are series with no values at all (others have many zero values; if these are special, you need to handle the specially as well). For the example code here, I assume that you want to require a minimum number of non-missing values:
```
initial.skip <- 60
min.number.same <- 1
min.number.other <- 1
min.nonmissing.window <- 60 ## set to 0 to ignore
```
I strongly suggest to play around with these parameters (see below) and see what effects they have on when means are computed.
The lags.
```
same.months <- seq(from = 12, to = 240, by = 12)
other.months <- 1:240
other.months <- setdiff(other.months, same.months)
```
The results will be stored in two arrays that have the same dimension as data.
```
same.means <- array(NA_real_, dim = dim(data))
other.means <- array(NA_real_, dim = dim(data))
```
It remains to fill the arrays. To be more strict about `NA`values, increase `min.number.same` and `min.number.other`. To allow no missing values at all, set `na.rm` in the mean computations to `FALSE`.
```
for (j in seq_along(assets)) {
for (t in seq_along(time)) {
if (t <= initial.skip || t <= min.nonmissing.window)
next
if (min.nonmissing.window > 0)
if (any(is.na(data[t - seq_len(min.nonmissing.window), j])))
next
lags <- same.months
lags <- lags[t > lags]
tmp <- data[t - lags, j]
if (sum(!is.na(tmp)) >= min.number.same)
same.means[t, j] <- mean(tmp, na.rm = TRUE)
lags <- other.months
lags <- lags[t > lags]
tmp <- data[t- lags, j]
if (sum(!is.na(tmp)) >= min.number.other)
other.means[t, j] <- mean(tmp, na.rm = TRUE)
}
}
```
If required, you can now transform these arrays back into `zoo`/`xts`.
Testing the influence of `NA` values: the parameters `initial.skip`, `min.number.same`, `min.number.other`, and `min.nonmissing.window` determine when a computation is tried (otherwise, the mean becomes `NA`). To test the effects of these parameters, it is best to create a simple dataset such as the following.
```
assets <- letters[1:2]
time <- 1:500
data <- array(c(time, time), dim = c(length(time), 2))
data[300, 1] <- NA
same.means <- array(NA_real_, dim = dim(data))
other.means <- array(NA_real_, dim = dim(data))
```
Now run the nested loops again and look at `same.means` and `other.means`.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.