2

I have a daily time series and I'm trying to calculate a 10 period moving average on it. The trouble I'm having is that the moving average needs to be on a rolling subset of the data (the 10 periods are not contiguous). I need an average over the previous 10 weeks, from and including today.
The 'today' value would be an average of today/value from 5 days ago/ value from 10 days ago.../value from 45 days ago To calculate 'yesterdays' value, I want yesterday/value from 6 days ago/value from 11 days ago.../value from 46 days ago.

I've tried the following:

WMA(na.omit(data[seq(NROW(data),1,-5)]),n=10, wts=wtzd)

Where wtzd is a vector of weights and data is an xts time series object.

Data contains daily observations from 2006-04-19 to 2017-02-03

That code works in that it returns correct values, but it only returns weekly figures, while I want the average calculated for each day. So, I get data back from the above for 2017-02-03/2017-01-27/2017-01-20.... While I want data back for each day, 2017-02-03/2017-02-02/...

I think there must be a straight forward solution, I just can't quite get it. I've tried running rollapply and a few other apply functions, but no luck.

The weighting vector is generated by:

wtzd<-(1:10/55)

A sample of the correct return values I'm looking for by date are:

2017-02-03->0.04354639 (which is weighted average of every 5th value (so 2017-02-03/2017-01-27...2016/11/29) 2017-02-02->0.04536664 (which is weighted avg of 2017-02-02...2016/11/28)

Here is a sample of the data:

<table border=0 cellpadding=0 cellspacing=0 width=139 style='border-collapse:
 collapse;table-layout:fixed;width:104pt'>
 <col width=75 style='mso-width-source:userset;mso-width-alt:2742;width:56pt'>
 <col width=64 style='width:48pt'>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl154556 width=75 style='height:15.0pt;width:56pt'>Date</td>
  <td class=xl154556 width=64 style='width:48pt'>Data</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/5/2016</td>
  <td class=xl154556 align=right>0.010798</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/8/2016</td>
  <td class=xl154556 align=right>0.011934</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/9/2016</td>
  <td class=xl154556 align=right>0.015761</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/10/2016</td>
  <td class=xl154556 align=right>0.017658</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/11/2016</td>
  <td class=xl154556 align=right>0.018573</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/12/2016</td>
  <td class=xl154556 align=right>0.018512</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/15/2016</td>
  <td class=xl154556 align=right>0.016978</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/16/2016</td>
  <td class=xl154556 align=right>0.012713</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/17/2016</td>
  <td class=xl154556 align=right>0.008406</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/18/2016</td>
  <td class=xl154556 align=right>0.003475</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/19/2016</td>
  <td class=xl154556 align=right>-8E-05</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/22/2016</td>
  <td class=xl154556 align=right>-0.00153</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/23/2016</td>
  <td class=xl154556 align=right>-0.00096</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/24/2016</td>
  <td class=xl154556 align=right>0.000888</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/25/2016</td>
  <td class=xl154556 align=right>0.003899</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/26/2016</td>
  <td class=xl154556 align=right>0.006597</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/29/2016</td>
  <td class=xl154556 align=right>0.006941</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/30/2016</td>
  <td class=xl154556 align=right>0.008899</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>8/31/2016</td>
  <td class=xl154556 align=right>0.009631</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/1/2016</td>
  <td class=xl154556 align=right>0.009314</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/2/2016</td>
  <td class=xl154556 align=right>0.008964</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/6/2016</td>
  <td class=xl154556 align=right>0.007931</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/7/2016</td>
  <td class=xl154556 align=right>0.005969</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/8/2016</td>
  <td class=xl154556 align=right>0.004158</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/9/2016</td>
  <td class=xl154556 align=right>0.004553</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/12/2016</td>
  <td class=xl154556 align=right>0.003861</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/13/2016</td>
  <td class=xl154556 align=right>0.005387</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/14/2016</td>
  <td class=xl154556 align=right>0.004989</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/15/2016</td>
  <td class=xl154556 align=right>0.005372</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/16/2016</td>
  <td class=xl154556 align=right>0.006748</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/19/2016</td>
  <td class=xl154556 align=right>0.007596</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/20/2016</td>
  <td class=xl154556 align=right>0.007595</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/21/2016</td>
  <td class=xl154556 align=right>0.00819</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/22/2016</td>
  <td class=xl154556 align=right>0.007539</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/23/2016</td>
  <td class=xl154556 align=right>0.004659</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/26/2016</td>
  <td class=xl154556 align=right>0.002459</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/27/2016</td>
  <td class=xl154556 align=right>0.000717</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/28/2016</td>
  <td class=xl154556 align=right>0.000757</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/29/2016</td>
  <td class=xl154556 align=right>0.00175</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>9/30/2016</td>
  <td class=xl154556 align=right>0.002898</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/3/2016</td>
  <td class=xl154556 align=right>0.004611</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/4/2016</td>
  <td class=xl154556 align=right>0.004674</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/5/2016</td>
  <td class=xl154556 align=right>0.002337</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/6/2016</td>
  <td class=xl154556 align=right>0.001521</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/7/2016</td>
  <td class=xl154556 align=right>0.000596</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/10/2016</td>
  <td class=xl154556 align=right>-0.00019</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/11/2016</td>
  <td class=xl154556 align=right>0.002114</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/12/2016</td>
  <td class=xl154556 align=right>0.006229</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/13/2016</td>
  <td class=xl154556 align=right>0.008139</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/14/2016</td>
  <td class=xl154556 align=right>0.011209</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/17/2016</td>
  <td class=xl154556 align=right>0.01471</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/18/2016</td>
  <td class=xl154556 align=right>0.01581</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/19/2016</td>
  <td class=xl154556 align=right>0.017636</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/20/2016</td>
  <td class=xl154556 align=right>0.020715</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/21/2016</td>
  <td class=xl154556 align=right>0.023097</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/24/2016</td>
  <td class=xl154556 align=right>0.023854</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/25/2016</td>
  <td class=xl154556 align=right>0.024841</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/26/2016</td>
  <td class=xl154556 align=right>0.024457</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/27/2016</td>
  <td class=xl154556 align=right>0.024512</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/28/2016</td>
  <td class=xl154556 align=right>0.02277</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>10/31/2016</td>
  <td class=xl154556 align=right>0.021808</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/1/2016</td>
  <td class=xl154556 align=right>0.019764</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/2/2016</td>
  <td class=xl154556 align=right>0.018609</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/3/2016</td>
  <td class=xl154556 align=right>0.01617</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/4/2016</td>
  <td class=xl154556 align=right>0.015457</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/7/2016</td>
  <td class=xl154556 align=right>0.017325</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/8/2016</td>
  <td class=xl154556 align=right>0.020514</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/9/2016</td>
  <td class=xl154556 align=right>0.025313</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/10/2016</td>
  <td class=xl154556 align=right>0.030246</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/11/2016</td>
  <td class=xl154556 align=right>0.035005</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/14/2016</td>
  <td class=xl154556 align=right>0.037926</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/15/2016</td>
  <td class=xl154556 align=right>0.040985</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/16/2016</td>
  <td class=xl154556 align=right>0.0421</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/17/2016</td>
  <td class=xl154556 align=right>0.045048</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/18/2016</td>
  <td class=xl154556 align=right>0.048647</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/21/2016</td>
  <td class=xl154556 align=right>0.050855</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/22/2016</td>
  <td class=xl154556 align=right>0.054225</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/23/2016</td>
  <td class=xl154556 align=right>0.059675</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/25/2016</td>
  <td class=xl154556 align=right>0.063056</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/28/2016</td>
  <td class=xl154556 align=right>0.065385</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/29/2016</td>
  <td class=xl154556 align=right>0.067396</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>11/30/2016</td>
  <td class=xl154556 align=right>0.068715</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/1/2016</td>
  <td class=xl154556 align=right>0.067195</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/2/2016</td>
  <td class=xl154556 align=right>0.065036</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/5/2016</td>
  <td class=xl154556 align=right>0.060924</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/6/2016</td>
  <td class=xl154556 align=right>0.057955</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/7/2016</td>
  <td class=xl154556 align=right>0.053682</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/8/2016</td>
  <td class=xl154556 align=right>0.05172</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/9/2016</td>
  <td class=xl154556 align=right>0.052188</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/12/2016</td>
  <td class=xl154556 align=right>0.053812</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/13/2016</td>
  <td class=xl154556 align=right>0.055302</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/14/2016</td>
  <td class=xl154556 align=right>0.059984</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/15/2016</td>
  <td class=xl154556 align=right>0.065521</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/16/2016</td>
  <td class=xl154556 align=right>0.069117</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/19/2016</td>
  <td class=xl154556 align=right>0.07395</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/20/2016</td>
  <td class=xl154556 align=right>0.079027</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/21/2016</td>
  <td class=xl154556 align=right>0.079922</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/22/2016</td>
  <td class=xl154556 align=right>0.078866</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/23/2016</td>
  <td class=xl154556 align=right>0.077589</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/27/2016</td>
  <td class=xl154556 align=right>0.07534</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/28/2016</td>
  <td class=xl154556 align=right>0.073149</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/29/2016</td>
  <td class=xl154556 align=right>0.070329</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>12/30/2016</td>
  <td class=xl154556 align=right>0.066114</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/3/2017</td>
  <td class=xl154556 align=right>0.065532</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/4/2017</td>
  <td class=xl154556 align=right>0.063771</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/5/2017</td>
  <td class=xl154556 align=right>0.059462</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/6/2017</td>
  <td class=xl154556 align=right>0.058926</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/9/2017</td>
  <td class=xl154556 align=right>0.058652</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/10/2017</td>
  <td class=xl154556 align=right>0.055221</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/11/2017</td>
  <td class=xl154556 align=right>0.052785</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/12/2017</td>
  <td class=xl154556 align=right>0.051279</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/13/2017</td>
  <td class=xl154556 align=right>0.047492</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/17/2017</td>
  <td class=xl154556 align=right>0.042687</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/18/2017</td>
  <td class=xl154556 align=right>0.038517</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/19/2017</td>
  <td class=xl154556 align=right>0.036403</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/20/2017</td>
  <td class=xl154556 align=right>0.034359</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/23/2017</td>
  <td class=xl154556 align=right>0.031666</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/24/2017</td>
  <td class=xl154556 align=right>0.03102</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/25/2017</td>
  <td class=xl154556 align=right>0.030191</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/26/2017</td>
  <td class=xl154556 align=right>0.028369</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/27/2017</td>
  <td class=xl154556 align=right>0.02749</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/30/2017</td>
  <td class=xl154556 align=right>0.027657</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>1/31/2017</td>
  <td class=xl154556 align=right>0.024824</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>2/1/2017</td>
  <td class=xl154556 align=right>0.021491</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>2/2/2017</td>
  <td class=xl154556 align=right>0.017533</td>
 </tr>
 <tr height=20 style='height:15.0pt'>
  <td height=20 class=xl634556 align=right style='height:15.0pt'>2/3/2017</td>
  <td class=xl154556 align=right>0.012598</td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=75 style='width:56pt'></td>
  <td width=64 style='width:48pt'></td>
 </tr>
 <![endif]>
</table>

</div>

</body>

</html>
DM2017
  • 21
  • 4
  • It'd be helpful to have a minimal, fully reproducible example here. That would include (fake) data. – BenBarnes Feb 23 '17 at 16:54
  • Ok, I've tried adding some sample data. Couldn't find a way to get data out of R that included the dates, so inserted an html table. – DM2017 Feb 23 '17 at 17:53
  • Then you will be thrilled to know there are many, many ways to do it. See [here](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). – Roman Luštrik Feb 25 '17 at 12:43

2 Answers2

0

Your question looks similar to this one: Conditional rolling mean (moving average) on irregular time series

I like the data table solution where you pad in the missing dates and then use rollmean from zoo package to get the moving average. It is difficult to demonstrate without a sample data set. I created a bogus one and then removes some dates to show how to resolve missing ones.

library(data.table)
library(zoo)

## Convert to data.table

#number of days
dateno<-as.numeric(difftime(as.Date("2017-02-03"),as.Date("2006-04-19"),units="days"))+1

DT<-data.table(ID=seq(1,dateno,by=1),
    val=rnorm(dateno),
    dates=seq(as.Date("2006-04-19"),as.Date("2017-02-03"),by=1),
    missing.dates=rbinom(n=dateno,size=1,prob=.9))

DT<-DT[missing.dates==1][,missing.dates:=NULL]


## Using related questions/response
## reference: https://stackoverflow.com/questions/21372735/conditional-rolling-mean-moving-average-on-irregular-time-series
##Missing DATES will be added in. Value will be set to NA. 
DT<-DT[DT[,.(dates=seq(min(dates),max(dates),by=1))],on="dates"]

## Run your function
DT[, Avg5day:=rollmean(val, 5, na.rm=TRUE,align="right",fill=NA)]

Hopefully I understood your question correctly.

To add weekly averages, and then compute a rolling 10week average using same strategy with rollmean:

#add workweek column to group by
DT[,YYYY.WW:=paste0(year(as.Date(dates)),"-",ifelse(week(as.Date(dates))<10,"0",""),week(as.Date(dates)))]

#calculate weekly average
DT[,YYYY.WW.AVG:=mean(val,na.rm=TRUE),YYYY.WW]
weekly.DT<-unique(DT[,.(YYYY.WW,YYYY.WW.AVG)])

#calculate rolling weekly average
weekly.DT[,WW10AVG:=rollmean(YYYY.WW.AVG,k=10,na.rm=TRUE,align="right",fill=NA)]
DT[weekly.DT,on="YYYY.WW"]
Community
  • 1
  • 1
MelissaG
  • 115
  • 1
  • 11
  • Thanks Andrei...I added more explanation/code using a sample data set I created. – MelissaG Feb 23 '17 at 17:26
  • Thanks! Looking at your code, I don't think you've understood the question, as I don't see anything that's averaging 10 weekly data points. Please do correct me if I'm wrong though. – DM2017 Feb 23 '17 at 18:07
  • Is your data set daily or weekly? If daily, we could create a 'YYYY-WEEK' column, calculate average for that and then use rollmean on that column to get the average? – MelissaG Feb 23 '17 at 18:14
  • It's daily. I've added sample data to the post. – DM2017 Feb 23 '17 at 18:57
  • Thanks DM2017, see new answer for code using your dataset. I think I understand your question better now. – MelissaG Feb 23 '17 at 20:20
0

Thanks for providing sample data set....

You can still use data.table with zoo's rollapply...

To match your expected answer, I added a DateIndex and then performed rollapply on weighted.mean grouping by it.

DT[,DateIndex:=1:5]
DT[,DateIndex.10period.avg:=rollapply(Data,width=10,weighted.mean,
    w=(1:10)/55,na.rm=TRUE,fill=NA,align="right"),by=DateIndex]

Alternatively, you could have added a weekdays column and performed average over it to get last 10 Fridays values...but this didn't align with your expected answer. Providing it here just in case...

DT[,weekday:=weekdays(Date)]
DT[,weekday.10period.avg:=rollapply(Data,width=10,weighted.mean,
    w=(1:10)/55,na.rm=TRUE,fill=NA,align="right"),by=weekday]
MelissaG
  • 115
  • 1
  • 11
  • Thanks. I'm not too familiar with data.tables. I'm getting the following error message when I try to run :DT[,DateIndex:=1:5] Warning message: In `[.data.table`(DT, , `:=`(DateIndex, 1:5)) : Supplied 5 items to be assigned to 9 items of column 'DateIndex' (recycled leaving remainder of 4 items). – DM2017 Feb 23 '17 at 20:57
  • Also, I tried running your rollapply function on it's own and it does not produce the correct answers. I think it's running on continuous 10-day periods. That is, I ran: rollapply(dxycopw,width=10,weighted.mean,w=(1:10)/55,na.rm=TRUE,fill=NA,align="right") – DM2017 Feb 23 '17 at 21:10
  • Sorry, dxycopw is the name of the data set I using, so should be replaced with generic 'data' – DM2017 Feb 23 '17 at 21:12
  • You can ignore the warning, it recycles the items throughout the vector. If you print DT on your console, using head(DT,10) you'll see that 1:5 has been repeated....in your second comment you are missing the group by statement...you needed add a ,DateIndex] to the end of the DT call. I'll reformat code so you don't have to scroll over to see it... – MelissaG Feb 23 '17 at 22:07
  • Here is an example from [datacamp.com](https://www.datacamp.com/community/tutorials/data-table-r-tutorial#gs.GlTqGEk) of using a group by in data.table. I could have been more explicit in my example by prefixing the final argument to data.table with a by=DateIndex...it causes the rowapply to be aggregated by the DateIndex instead of the whole data table.. – MelissaG Feb 23 '17 at 22:14