Moving Average Examples
When you want to compute a moving average on your data, the main tools at your disposal will be mutate and roll. Let's go over a few examples of how to use them together.
Moving Average#
Given input data:
Input Data
If we want to compute a 3-item moving average of the value key, we can do so as follows:
Here we're providing a value of 3 for the window width argument of roll and specifying the aggregation to run as the summarizer function mean. This produces the following output:
Output Data
Note that by default roll will only compute the aggregate value if the full window width of data is available. Since the window sizes for the first and second elements are 1 and 2, which are less than our width argument of 3, their movingAvg value is undefined.
To compute the aggregate value even when the window width is less than our desired width, we can pass in an options argument with partial set to true:
With this change, the output becomes:
Output Data with Partial
And that's all there is to doing simple moving averages with tidy.
Moving Average of Rates#
Working with rates is generally a bit more challenging than plain ol' numbers since we want to sum the numerator and denominator separately in each window as opposed to averaging the sum of rates from each item. Here's where the meanRate summarizer comes in handy.
Input Data
If we want to compute a 3-item moving average of the rate defined by numer / denom, we can do so as follows:
Output Data
Note that the incorrect way of doing it would involve averaging the rates:
Output Data with Wrong
| numer | denom | rate | movingAvgWrong | movingAvgRight |
|---|---|---|---|---|
| 1.00 | 2.00 | 0.500 | ||
| 2.00 | 6.00 | 0.333 | ||
| 3.00 | 7.00 | 0.429 | 0.421 | 0.400 |
| 7.00 | 12.0 | 0.583 | 0.448 | 0.480 |
| 5.00 | 6.00 | 0.833 | 0.615 | 0.600 |
Moving Average by Date#
When working with time series data, we often want be able to re-aggregate the data to a variety of different granularities: e.g., a 7-day moving average (7DMA), 45DMA, 5-week moving average (5WMA), daily, weekly, and so on. For this purpose, we can use the summarizeMomentGranularity tidy function. Note this requires our dates to be stored as moment objects and that moment is installed as a dependency in our project.
If projects are not using moment but would like this functionality, please get in touch and we can figure out a reusable solution.
Let's look at an example where we compute a 2-week moving average of value.
Input Data
| date | value |
|---|---|
| "2020-01-01T00:00:00.000Z" | 3.00 |
| "2020-01-02T00:00:00.000Z" | 1.00 |
| "2020-01-03T00:00:00.000Z" | 4.00 |
| "2020-01-04T00:00:00.000Z" | 5.00 |
| "2020-01-05T00:00:00.000Z" | 2.00 |
| "2020-01-06T00:00:00.000Z" | 4.00 |
| "2020-01-07T00:00:00.000Z" | 3.00 |
| "2020-01-08T00:00:00.000Z" | 1.00 |
The first step is to rollup our data from being at a daily granularity to say a weekly granularity before we compute our moving average.
At this point, our data has been summed up per week.
Output Weekly Data
| value | date | timestamp |
|---|---|---|
| 15.0 | 2019-12-30T00:00:00.000Z | 2019-12-30T00:00:00.000Z |
| 21.0 | 2020-01-06T00:00:00.000Z | 2020-01-06T00:00:00.000Z |
| 20.0 | 2020-01-13T00:00:00.000Z | 2020-01-13T00:00:00.000Z |
| 19.0 | 2020-01-20T00:00:00.000Z | 2020-01-20T00:00:00.000Z |
We are now ready to compute our moving average:
Output Data
| value | date | timestamp | movingAvg |
|---|---|---|---|
| 15.0 | 2019-12-23T00:00:00.000Z | 2019-12-23T00:00:00.000Z | |
| 21.0 | 2019-12-30T00:00:00.000Z | 2019-12-30T00:00:00.000Z | 18.0 |
| 20.0 | 2020-01-06T00:00:00.000Z | 2020-01-06T00:00:00.000Z | 20.5 |
| 19.0 | 2020-01-13T00:00:00.000Z | 2020-01-13T00:00:00.000Z | 19.5 |
And we're done, in two steps we've moved from daily values in our input data to weekly values which include a 2-week moving average.
Moving Average by Date with Zerofill#
We often end up with data that is missing values for some dates, which throws off the windows used to compute moving averages. To get around this, we can use complete to expand our data to include zeroes (or other values) where data is missing in a sequence.
Consider the following data:
Input Data
| date | value |
|---|---|
| "2020-01-06T00:00:00.000Z" | 4.00 |
| "2020-01-07T00:00:00.000Z" | 3.00 |
| "2020-01-08T00:00:00.000Z" | 1.00 |
| "2020-01-11T00:00:00.000Z" | 2.00 |
| "2020-01-12T00:00:00.000Z" | 5.00 |
| "2020-01-13T00:00:00.000Z" | 6.00 |
| "2020-01-14T00:00:00.000Z" | 1.00 |
| "2020-01-17T00:00:00.000Z" | 3.00 |
It's missing values for January 9, 10, 15, 16, and 18. If we run our tidy flow on it to compute a 7-day moving average without accounting for these missing values we get
Output Data
| date | value | movingAvg |
|---|---|---|
| 2020-01-06T00:00:00.000Z | 4.00 | 4.00 |
| 2020-01-07T00:00:00.000Z | 3.00 | 3.50 |
| 2020-01-08T00:00:00.000Z | 1.00 | 2.67 |
| 2020-01-11T00:00:00.000Z | 2.00 | 2.50 |
| 2020-01-12T00:00:00.000Z | 5.00 | 3.00 |
| 2020-01-13T00:00:00.000Z | 6.00 | 3.50 |
| 2020-01-14T00:00:00.000Z | 1.00 | 3.14 |
| 2020-01-17T00:00:00.000Z | 3.00 | 3.00 |
As you can see, the moving average doesn't account for the missing days.
To get around this, we can use complete on the data to expand it to include rows for when there is data missing from the expected day-by-day sequence. Since our date column uses moments, we will have to convert it to an ISO timestamp string first so we can use fullSeqDateISOString on it. This can be done via summarizeMomentGranularity as shown above or with a simple mutate since we're going to work with daily data.
Alternative example with summarizeMomentGranularity – granularity should match in fullSeqDateISOString and summarizeMomentGranularity
Output Data
| timestamp | date | value | movingAvg |
|---|---|---|---|
| 2020-01-06T00:00:00.000Z | 2020-01-06T00:00:00.000Z | 4.00 | 4.00 |
| 2020-01-07T00:00:00.000Z | 2020-01-07T00:00:00.000Z | 3.00 | 3.50 |
| 2020-01-08T00:00:00.000Z | 2020-01-08T00:00:00.000Z | 1.00 | 2.67 |
| 2020-01-09T00:00:00.000Z | 0.00 | 2.00 | |
| 2020-01-10T00:00:00.000Z | 0.00 | 1.60 | |
| 2020-01-11T00:00:00.000Z | 2020-01-11T00:00:00.000Z | 2.00 | 1.67 |
| 2020-01-12T00:00:00.000Z | 2020-01-12T00:00:00.000Z | 5.00 | 2.14 |
| 2020-01-13T00:00:00.000Z | 2020-01-13T00:00:00.000Z | 6.00 | 2.43 |
Now we can see that rows have been added with value: 0 for the missing dates in the sequence, which enables our moving average to be computed correctly.