Creating new factors using rollapply

Question

I am trying to create a new factor in my dataset using rollapply. I want to create a factor that takes the mean of observations with a different month than the current month, for the previous 240 months (20 years). For this I require atleast the presence of 60 non NA's in a row. The tail of my dataset looks like:

               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 idea is that if 60 non NA's in a row, generate a value that is equal to the mean of other months, for example if the current month == 1 (january), generate mean(df[month != i].

I have tried this so far, this does not involve the 60 non NA's criteria and does not seem to give correct values.

library(data.table)
library(xts)
y <- rollapplyr(sd, 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=240)

A representative example of my data can be created with the dput below, it contains 254 months for 9 series.

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")))

not very clear what you are looking for. what do you expect with the following data: case 1): `x <- 1:250`, case 2): `x <- c(1:100, rep(NA, 150))`, case 3): `c(rep(NA, 150), 1:100)` and case 4): `x <- c(1:60, rep(NA, 150), 1:40)` — chinsoon12, Jul 25 '19 at 02:34
I do not really follow you chinsoon, what I want is that if there at atleast 60 observations that are not NA, that the function takes the mean of those values (where month does not equal the current month) ranging up to 240 previous values. — Bart, Jul 25 '19 at 07:37
It has to take the a ```width``` between 60 and 240 values depending on how many previous values in the column were not ```NA``` — Bart, Jul 25 '19 at 08:01

chinsoon12 · Answer 1 · 2019-07-26T03:01:51.390

Not sure if i fully understand the question fully.

1) One possible approach (caveat: performance might not be optimal) is to

library(xts)
library(data.table)
DT <- as.data.table(DF)
winsz <- 60L

mDT <- melt(DT, id.vars="index", variable.name="ID", variable.factor=FALSE, na.rm=TRUE)[,
    c("index", "ID") := .(as.yearmon(index), as.integer(ID))]

mDT[.(ID=ID, index=index, start=index-240/12, end=index-1/12), 
    on=.(ID, index>=start, index<=end), allow.cartesian=TRUE,
    by=.EACHI, {
        y <- tail(value[month(x.index)!=month(i.index)], winsz)
        if (length(y) == winsz) .(ID=ID, index=i.index, mv=mean(y))
    }][,
        -3L:-1L]

Explanation:

melt into a long format,
use a non-equi join to window the time series from 20Y ago until one month before,
then calculate the mean of the last 60 values after excluding those data of the same month.

output:

         ID    index            mv
  1: 299918 Jan 1911  0.0029369917
  2: 299918 Feb 1911 -0.0005815412
  3: 299918 Mar 1911 -0.0013569112
  4: 299918 Apr 1911 -0.0015295073
  5: 299918 May 1911 -0.0022864365
 ---                              
799: 299927 Aug 1926  0.0080644924
800: 299927 Sep 1926  0.0076899606
801: 299927 Oct 1926  0.0090008220
802: 299927 Nov 1926  0.0066620193
803: 299927 Dec 1926  0.0066605568

If a wide format is needed, a dcast call will do.

2) Another possible approach:

mDT[, {
    idx <- Filter(function(x) x>winsz, seq_len(.N))
    if (length(idx) > 0L) {
        mv <- sapply(idx, function(n) {
            ti <- index[seq_len(n)]
            y <- tail(value[seq_len(n)][
                between(ti, index[n]-240/12, index[n]-1/12) &
                    month(ti) != month(index[n])], winsz)
            if (length(y) == winsz) mean(y) else NA
        })
        .(index=index[idx], mv=mv)
    }
}, .(ID)][!is.na(mv)]

data: DF is as per OP's dput output.

p.s.: The frollmean below can almost achieve all the requirements in one single call except missing the shifting the window to capture latest 60 obs:

as.data.table(frollmean(shift(DT[, -"index"]), 60L, na.rm=TRUE, hasNA=TRUE))

Thank you for answering Chinsoon, however it will not take the mean of the months that are not the current month (if month = january take mean of observations that do not have january as a month) — Bart, Jul 25 '19 at 12:42
Thanks for your effort chinsoon, it however still calculates for the first 5 years as well. I have tried both options and do not get the same output as you do. — Bart, Jul 26 '19 at 09:25

Creating new factors using rollapply

1 Answers1