Dashboard Charts - 7 day rolling average

General
1

Anyone any idea how to create a 7 day rolling average from an array of objects, to plot in a node-RED chart?

The data arrives as an array of objects, comprising of a value, together with a epoch timestamp.
There is just one object per day, and currently there is about 120 objects (4 months worth of data).

Abbreviated Example;

[{
	"x": 1590105600000,
	"y": 45
}, {
	"x": 1590105600000,
	"y": 53
}, {
	"x": 1589932800000,
	"y": 22
}, {
	"x": 1589932800000,
	"y": 30
}]
2

If you want just latest 7 days and the data is already sorted, you could (in a function node) use the array slice function followed by array reduce function.

Does that help? Need more?

3

No, I want to plot the rolling 7 days average, starting from the earliest datapoint to the latest datapoint, so the chart data will absorb the anomalies.
Example; the daily UK Coronavirus briefing, where accurate data is not received over a weekend, but the missing data is then added to Monday's data, resulting in a false representation. To avoid this, they plot the chart with a 7 day rolling average, which smoothes the chart.
PS - It's not for plotting Coronavirus deaths!

4

the smooth node does an average over however many samples you say...

5

Yes, I've looked at that, but I haven't managed to handle an array of timestamped data using the node.
If I strip out the data from the timestamp, and feed it as consecutive data points, I'm sure it would work, but then I've lost the timestamps, and therefore difficult to chart.

6

I’m not sure if I understand your needs correctly, but have you thought about supplying your data points to a function node with a reducer in it to calculate the rolling average?


The reduce function gives access to current value, index in the array, as well as the full input array. Specifically the index and full input array sounds useful to me. That way you could build your output object but keep the timestamp (current object), while appending the calculated result to the reducer array (initial value/accumulator).
7

There is also a reduce mode within the join node that can be set to do whatever consolidation you want. The default example is average

1 Like
8

I have assumed that for the first 6 samples what is wanted is the average of the samples so far.
There are probably more compact ways of doing it but I imagine this is one of the most efficient techniques as keeps a rolling total rather than adding up seven values each time.

// Expects payload to be an array of objects {x: xvalue, y: yvalue}
// Performs a rolling average of y values over n samples and returns the result
// in payload.
const n = 7     // no of samples to average over
let total = 0
let count = 0   // number of samples accumulated
let answer = []
for (i = 0; i < msg.payload.length; i++) { 
    total += msg.payload[i].y
    if (i >= n) {
        total -= msg.payload[i-n].y
        count = n
    } else {
        count = i+1
    }
    answer[i] = {x: msg.payload[i].x, y: total/count}
}
msg.payload = answer
return msg;
2 Likes
9

The line
answer[i] = {x: msg.payload[i].x, y: total/count}
would have been better as
answer.push( {x: msg.payload[i].x, y: total/count} )

1 Like
10

Very clever @Colin, it's working fine on the live data, and it's also easy to switch the chart between original data and the 7 day rolling average by passing in the value of n via msg.ra with the data feed.

const n = msg.ra || 7;

Thank you :+1:

Edit// @afelix, @dceejay I did try to use both suggestions, but found reduce() difficult to get right, and was just looking at the join node option when Colin's posted. - thanks both.

1 Like
11

looking at your original data - why do you have duplicate x values ?

12

Because it's not the real data, it is just an example to show the format of the data, so it would be clear how the array is structured.

13

OK, NP. Just checking you weren't getting weird feed

2 Likes
16

A post was split to a new topic: Remove duplicate entries from a array