Two-year moving average

[Muhammad Salahuddin]

Hi,

Hope you are doing well. In my panel data, I want to make two-year moving average. For example, for 2010 and 2011, there will be one average point. I was trying by using the code

"generate = (F1.Inflationconsumerpricesannu + Inflationconsumerpricesannu) / 2

bysort id (year): gen MA3=(Inflationconsumerpricesannu[_n]+Inflationconsumerpricesannu[_n-1])/2". However, it does not work for all data point. Thanks in advance for your advice.

* Example generated by -dataex-. For more info, type help dataex
clear
input float id int year double(Inflationconsumerpricesannu GDPconstant2015USNYGDP GDPconstantLCUNYGDPMKTP Unemploymenttotaloftotal IQ)
 1 2010   1.81353438995065  362424005611.4763  326651460000  4.82   2.5288174152374268
 1 2011   3.28657914875376  373016924602.9138  336198820000  4.56    2.290109872817993
 1 2012   2.48567562177017  375555101767.1521  338486470000  4.87   2.4681811332702637
 1 2013   2.00015616900603  375650886014.9998  3.385728e+11  5.34   2.5099539756774902
 1 2014   1.60581182954471  378134963330.6912  340811690000  5.62    2.371476650238037
 1 2015   .896563335260302  381971148530.5428  344269230000  5.72    2.197143793106079
 1 2016   .891591752655335 389570224508.69116  351118250000  6.01   1.9730305671691895
 1 2017   2.08126911385587   398368950204.699  359048510000   5.5   2.2282211780548096
 1 2018   1.99837981429546  408030932405.2575  367756820000  4.85    2.134751319885254
 1 2019   1.53089564152645  414222347188.8122  373337120000  4.49   2.1713881492614746
 1 2020   1.38191063351859  387488567518.4294  349242060000  5.36   2.1555092334747314
 2 2010   2.18929920422458 433403805754.25757  3.906253e+11  8.29    1.555306315422058
 2 2011   3.53208210722744 440747893479.88257  3.972445e+11  7.14   1.6918913125991821
 2 2012   2.83966343445897 444005978083.20294   4.00181e+11  7.54    1.679202675819397
 2 2013   1.11309594027537 446045040873.59357  4.020188e+11  8.43    1.732736349105835
 2 2014   .340002833356965  453086009677.4998  4.083648e+11  8.52   1.3123257160186768
 2 2015   .561429152790106 462335574841.48413  4.167014e+11  8.48   1.4373888969421387
 2 2016   1.97385264653172 468191916732.07007  4.219797e+11  7.83   1.4239447116851807
 2 2017   2.12597086002609  475774660680.4685   4.28814e+11  7.09   1.0068458318710327
 2 2018   2.05316499865181 484305039588.74976  4.365024e+11  5.95    .9699288010597229
 2 2019   1.43681956996435  495257928942.4217  4.463742e+11  5.36    .9889350533485413
 2 2020   .740791812220385 468699296976.95294   4.22437e+11  5.55    .9939078688621521
 3 2010   1.47272727272729  172890126530.8197  4.252881e+12  7.28  -.19599688053131104
 3 2011   1.91721913635547 175933614512.93262  4.327747e+12  6.71  -.04243841394782066
 3 2012   3.28762306610408 174552528075.61365  4.293774e+12  6.98  -.46168413758277893
 3 2013   1.43829787234043  174472402052.9499  4.291803e+12  6.95   -.3863919675350189
 3 2014   .343988589646784  178419147314.3961  4.388888e+12  6.11  -.16727949678897858
 3 2015   .309364548494974  188033050459.8811  4.625378e+12  5.05  .014830995351076126
 3 2016   .683504209385683 192803983942.27353  4.742737e+12  3.95  -.17223206162452698
 3 2017   2.45053398460138  202769490319.6301  4.987876e+12  2.89 -.014884298667311668
 3 2018   2.14949494949492   209298846486.102   5.14849e+12  2.24  -.06527421623468399
 3 2019   2.84787595918047  215640347578.6372  5.304483e+12  2.01   -.2628575265407562
 3 2020   3.16129528497808 203773728339.85474  5.012579e+12  2.55 -.026535015553236008
 4 2010   2.31092436974789  283894168259.7569 1910013514000  7.75   3.1915345191955566
 4 2011   2.75868226051246  287689202382.5883 1935546150000  7.77   3.2100634574890137
 4 2012   2.39791485664639  288340817827.0245 1939930158000   7.8    2.929143190383911
 4 2013   .789071780078061  291032020819.8744 1958036321000  7.38    3.045565605163574
 4 2014   .564020540449518 295744975353.14795 1989744640000  6.93    2.708101987838745
 4 2015   .452034153691623 302673070846.85724 2036356221000  6.28   2.7025082111358643
 4 2016   .249999999999997  312497708532.4633 2102455468000  5.99    2.618206024169922
 4 2017   1.14713216957606  321315569939.8231 2161781218000  5.83   2.5181186199188232
 4 2018   .813609467455633 327708263102.56616 2204790662000  5.13   2.7267978191375732
 4 2019   .758131572511619 332602543274.04944 2237718923000  5.02   2.7743871212005615
 4 2020   .420711974110026  325968427772.8691 2193085212000  5.64    2.949277639389038
 5 2010    1.1841352315465 233646772072.26553   2.10585e+11  8.39   3.3516347408294678
 5 2011   3.41680754255211 239599308730.46863    2.1595e+11  7.78     3.34017276763916
 5 2012   2.80833622561575 236250798826.56238   2.12932e+11  7.69    3.335705518722534
 5 2013   1.47828615688804 234120534076.56238   2.11012e+11  8.19   3.3520102500915527
 5 2014   1.04119621178439 233266209150.78113   2.10242e+11  8.66   3.3396077156066895
 5 2015  -.207928839905218  234534382384.7655   2.11385e+11  9.38    3.099062919616699
 5 2016   .356684500891696 241128217493.74988   2.17328e+11  8.82    3.010528326034546
 5 2017   .754015047084413 248826017928.90613   2.24266e+11  8.64    3.143138885498047
 5 2018   1.08382098409299 251661932877.34366   2.26822e+11  7.36   3.0129237174987793
 5 2019   1.02409392963429  254744159687.4999     2.296e+11  6.69   2.9899885654449463
 5 2020   .290554555653313 248745023487.89053   2.24193e+11  7.76   3.1803348064422607
 6 2010   1.53112270420924 2317567536989.1377 2088815332000  8.87    1.230107069015503
 6 2011   2.11159795174997  2368384855035.173 2134616799000  8.81    .9793891906738281
 6 2012   1.95419531613507 2375801091055.5366 2141301026000   9.4    .9051201343536377
 6 2013   .863715497861826  2389493466482.809 2153641915000  9.92    .9077351689338684
 6 2014   .507758822937957 2412341398046.8433 2174234674000 10.29    .6366958022117615
 6 2015  .0375143805125363 2439188643162.4985  2.198432e+12 10.35    .5732472538948059
 6 2016   .183334861123848 2465909086487.9365 2222515040000 10.05   .35016918182373047
 6 2017   1.03228275064674 2522413420333.7813 2273442194000  9.41    .5508229732513428
 6 2018   1.85081508315494  2569458097202.237 2315843393000  9.02    .5432108640670776
 6 2019   1.10825492288292 2616812485718.0073 2358523734000  8.41    .9021638631820679
 6 2020   .476498852725083 2413104454442.2417 2174922414000  8.01    .3678889572620392
 7 2010   1.10381037789263 3087971590525.6816 2783177750000  6.97    1.876706600189209
 7 2011   2.07517283735872   3209180426118.17 2892422840000  5.82   1.8310794830322266
 7 2012   2.00848884782951  3222610768995.576 2904527560000  5.38    1.839958667755127
 7 2013   1.50472330251883 3236712633454.9043 2917237520000  5.23    1.917170763015747
 7 2014    .90679400043421  3308229204839.041 2981695150000  4.98    2.260538101196289
 7 2015   .514426137125476 3357585719351.5605   3.02618e+12  4.62   1.9935123920440674
 7 2016   .491747008445241  3432459876455.049 3093663810000  4.12   2.0714123249053955
 7 2017   1.50949485109622  3524457734041.385 3176581150000  3.75   1.9721189737319946
 7 2018   1.73216879766946 3559040862512.6543 3.2077508e+12  3.38    1.920536756515503
 7 2019   1.44565976888251  3596645827007.444 3241644020000  3.14   2.0344762802124023
 7 2020   .144877925813972  3463685430302.096 3121807290000  3.86    1.790195107460022
 8 2010   4.71298907760393 239780041723.27414  216112894000 12.71   -2.810462474822998
 8 2011    3.3298532335065   215444010389.589  194178916000 17.86   -3.153510093688965
 8 2012   1.50152696177748 200176146628.86124  180418045000 24.44   -3.513035297393799
 8 2013  -.921269454256898  195139720340.7796  175878732000 27.47  -3.2513248920440674
 8 2014  -1.31226096405516 196067992081.69522  176715380000 26.49  -3.5079383850097656
 8 2015  -1.73588804765278  195683527003.3745  176368863000  24.9   -3.591334104537964
 8 2016  -.825653978391008  194730209118.4659  175509641000 23.54   -3.939177989959717
 8 2017   1.12125452031427 196856953384.06396  177426468000 21.49   -3.801441192626953
 8 2018   .625621413453588 200141371166.33035  180386702000 19.29   -3.255108594894409
 8 2019    .25300752203812 203912718651.87955  183785804000 17.31  -2.9555470943450928
 8 2020  -1.24798355581408 185552328472.83188  167237650000 16.31   -2.538569450378418
 9 2010   4.85555795691951 113077422068.68158 3.1586199e+13 11.17  -1.3914512395858765
 9 2011   3.92992098935075 115188393760.12456 3.2175862e+13 11.03  -1.3651084899902344
 9 2012   5.65214517088651 113748278485.31766 3.1773591e+13    11  -1.7575390338897705
 9 2013   1.73319984983107 115798616344.32082 3.2346317e+13 10.18  -1.7392919063568115
 9 2014  -.227566270988384 120699456740.62273 3.3715281e+13  7.73   -2.001486301422119
 9 2015 -.0616446800641176 125174166987.37169 3.4965213e+13  6.81   -2.145564079284668
 9 2016   .394769306686402 127929252772.23383 3.5734798e+13  5.11  -2.7677407264709473
 9 2017   2.34824281150159 133394359768.37642  3.726138e+13  4.16  -2.1588752269744873
 9 2018   2.85024792594642 140547430034.09918 3.9259465e+13  3.71  -2.3348634243011475
 9 2019    3.3385863538201  147383974295.8661 4.1169134e+13  3.42   -2.510842800140381
 9 2020    3.3267438576673  140699299222.2531 3.9301887e+13  4.25   -2.610917568206787
10 2010  -.922095829024469  211767987813.5506  190865730000 14.53   2.1085612773895264
end

[Clyde Schechter]

There are two, possibly three, problems with your code. You do not specify a name for the variable you want to create. And your data may not have been -xtset- or -tsset-. But those are probably just errors you made in posting your question, because either one would have led you to get an error message and no results at all, not missing values.

The reason

xtset id year
generate wanted = (F1.Inflationconsumerpricesannu + Inflationconsumerpricesannu) / 2

--which is the correct code for a moving 2 year leading average- produces some missing observations is that in the chronologically last observation for any id, there is no F1.Inflationconsumerpricesannu, because there is no next year in the data. So F1.Inflation... is missing value, and hence the moving average is also missing value.

More generally, any time you use lag (resp. lead) operators, you lose observations at the beginning (resp. end) of each group. The number of observations lost is equal to the degree of the lag (resp. lead).

[Muhammad Salahuddin]

Thanks for your reply. the code is working, but what I want does not reflect exactly. In our study, the data points are from 2010 to 2020, there is in total 11 data points, 2-year average will be taken. In that case, first 8-data points (2010 to 2017) will be 2-years average, and only the last 3 years will be taken an average, Therefore, the data point will be reduced from 11 points to only 5 points.

Now, when using your code as like before I am trying, it shows only 1 missing point for each id. However, taking 2-year average, the data point would be 5 points.

Appreciate your help and suggestions

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int year float avg_Inflationconsumerpricesannu
 1 2010   2.550057
 1 2011  2.8861275
 1 2012  2.2429159
 1 2013   1.802984
 1 2014  1.2511876
 1 2015   .8940775
 1 2016  1.4864304
 1 2017  2.0398245
 1 2018  1.7646377
 1 2019   1.456403
 1 2020          .
 2 2010  2.8606906
 2 2011   3.185873
 2 2012  1.9763796
 2 2013   .7265494
 2 2014    .450716
 2 2015   1.267641
 2 2016  2.0499117
 2 2017   2.089568
 2 2018  1.7449923
 2 2019  1.0888057
 2 2020          .
 5 2010  1.6949732
 5 2011   2.602421
 5 2012  2.3629606
 5 2013   .8911432
 5 2014   .3266766
 5 2015   .4964344
 5 2016   1.567019
 5 2017  2.3000145
 5 2018  2.4986854
 5 2019  3.0045855
 5 2020          .
 6 2010  2.5348034
 6 2011  2.5782986
 6 2012  1.5934933
 6 2013   .6765462
 6 2014   .5080274
 6 2015   .3510171
 6 2016   .6985661
 6 2017   .9803708
 6 2018   .7858705
 6 2019  .58942175
 6 2020          .
 7 2010  2.3004713
 7 2011   3.112572
 7 2012  2.1433113
 7 2013  1.2597412
 7 2014   .4166337
 7 2015  .07437783
 7 2016  .55534977
 7 2017    .918918
 7 2018  1.0539575
 7 2019   .6573243
 7 2020          .
 8 2010  1.8213603
 8 2011  2.0328965
 8 2012  1.4089555
 8 2013   .6857371
 8 2014   .2726366
 8 2015  .11042462
 8 2016   .6078088
 8 2017   1.441549
 8 2018   1.479535
 8 2019   .7923769
 8 2020          .
 9 2010  1.5894916
 9 2011  2.0418308
 9 2012   1.756606
 9 2013  1.2057587
 9 2014   .7106101
 9 2015  .50308657
 9 2016   1.000621
 9 2017   1.620832
 9 2018  1.5889143
 9 2019   .7952688
 9 2020          .
10 2010   4.021421
10 2011    2.41569
10 2012  .29012877
10 2013 -1.1167653
10 2014 -1.5240746
10 2015  -1.280771
10 2016  .14780027
10 2017   .8734379
10 2018  .43931445
10 2019   -.497488
10 2020          .
11 2010  4.3927393
11 2011   4.791033
11 2012  3.6926725
11 2013   .7528168
11 2014 -.14460547
11 2015   .1665623
11 2016   1.371506
11 2017   2.599245
11 2018   3.094417
11 2019   3.332665
11 2020          .
12 2010   .8175465
end

[Clyde Schechter]

I'm not sure I understand what you're looking for. In #1 you spoke of two-year moving averages, and that is precisely what the code in #2 gives. What you describe in #3 is quite different from moving averages: it sounds like you want to aggregate the data into two-year groups, except that the final group will be three years. If that is, in fact, what you want:

label define era    1    "2010-2011"    ///
                    2    "2012-2013"    ///
                    3    "2014-2015"    ///
                    4    "2016-2017"    ///
                    5    "2018-2020"
                    
gen byte era:era = min(5, ceil((year-2009)/2))

collapse (mean) avg_Inflationconsumerpricesannu (min) start = year ///
    (max) finish = year, by(id era)

will do that.

If I have misunderstood what you want, please post back, and, in addition to giving a clearer explanation, please show an example of what the result you want would look like.