4
$\begingroup$

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")))
$\endgroup$
  • $\begingroup$ Is there a question here? Maybe I missed it $\endgroup$ – Chris Jul 24 at 21:57
  • $\begingroup$ it appears you've added a question since my last comment, but as it currently stands, your question is still unclear and filled with generalities. For instance, your first sentence: "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..." I have no idea what this means. You're likely to get better responses if you rethink and distill your current question to the actual problem you're encountering. $\endgroup$ – Chris Jul 28 at 3:23
  • $\begingroup$ Were you looking for a pseudo-code answer? Could any real programming language be used? $\endgroup$ – rajah9 Jul 28 at 11:14
  • $\begingroup$ I am looking for an answer that is exercisable within R $\endgroup$ – Bart Jul 31 at 15:00
3
+50
$\begingroup$

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 NAvalues, 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.

$\endgroup$
  • $\begingroup$ Hi Enrico, did you get any values in here? I did not get values for any of them. My data in XTS is indeed regularly spaced by 1 month. I have tried the solution on a larger dataset and all values were returned NA. I changed ``` same.means <- array(NA_real_, dim = dim(data0)) other.means <- array(NA_real_, dim = dim(data0))``` It does not matter if values are 0, however the last 60 values can't have any NA's so the initial.skip does not skip just NAs, because if the last 90 are NAS it will still take into account 30. $\endgroup$ – Bart Jul 31 at 14:59
  • $\begingroup$ I fixed the line when time is computed (where the dataset is matched to a regular grid). The sample data you provided were not regularly spaced: there were no data for Aug to Dec 1914. $\endgroup$ – Enrico Schumann Aug 1 at 5:29
  • $\begingroup$ How would I deal with my NA criteria? $\endgroup$ – Bart Aug 1 at 11:34
  • $\begingroup$ Do you mean that apart from initial NA values in the series, no NAs should be allowed? Then (as described above) set na.rm to FALSE. In which case a mean will only be computed for complete windows, without missing values. $\endgroup$ – Enrico Schumann Aug 1 at 20:13
  • $\begingroup$ A mean will only be computed if the last 60 values were non NA's $\endgroup$ – Bart Aug 2 at 6:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.