Re: time based moving average (and other newbie mathematica

*To*: mathgroup at smc.vnet.net*Subject*: [mg92767] Re: time based moving average (and other newbie mathematica*From*: Mark Fisher <particlefilter at gmail.com>*Date*: Mon, 13 Oct 2008 06:15:47 -0400 (EDT)*References*: <gcn496$751$1@smc.vnet.net>

On Oct 10, 4:37 am, falcon <shahb... at gmail.com> wrote: > Hi, > I see that Mathematica provides a couple of moving average functions. > As far as I can tell, they are based on the number of elements in an > array. Is there a function for doing moving average based on time? > In other words, if I pass in intra-day data containing prices, times > (up to a millisecond) and some other fields, can I get Mathematica to > to give me a 5 minute moving average rather than moving average of the > last 100 trades? Obviously this five minute window may contain any > number of elements. > > Secondly, along the same idea, I have a file with a large number of > stocks. They are all mixed in (the file is in chronological order). > Is there an sql type 'group by' command that lets me see moving > averages for each stock? > > Third, if I'm able to get moving average for each stock in the list, > can I plot all of them in one command (I guess this technique is > called "small multiples" in charting vernacular). Obviously I will > only have 20 or 30 stocks. > > Thanks Interesting problem. Here's my take: MovingTimeAverage::usage = "MovingTimeAverage[data, lag] takes a list \ of {time, value} pairs and returns a list of {time, avg} pairs where \ avg is an average of the values computed from the window [time - lag, time]. \ MovingTimeAverage assumes the data are sorted."; MovingTimeAverage[data : {{_, _} ..}, lag_] := With[{lagindex = LagIndexCompiled[data[[All, 1]], lag]}, Table[{data[[i, 1]], Mean[data[[lagindex[[i]] ;; i, 2]]]}, {i, Length[data]}] ] LagIndexCompiled = Compile[{{time, _Real, 1}, {lag, _Real, 0}}, Module[{j = 1}, Table[While[time[[i]] - time[[j]] > lag, j++]; j, {i, Length[time]}] ]]; ndraws = 10^4; times = Accumulate[RandomReal[ExponentialDistribution[5], ndraws]]; values = Accumulate[RandomReal[NormalDistribution[], ndraws]]; data = Transpose[{times, values}]; ma = MovingTimeAverage[data, 5]; // Timing ListLinePlot[{data, ma}] On my laptop, it takes about .1 second to compute the moving average. The key is to compute the position of where the window starts for each observation (lagindex). --Mark