Calculating a logarithmic mean for noise data

Some specialized types of aggregation are not available via Processing & Logic. In these cases, processing data externally in a Google sheet is an option, and the general steps for doing so are covered in this article.

One specific type of aggregation is used to calculate an average for logarithmic values, such as noise data. This article will describe how to perform this aggregation using a Google sheet. The method used comes from this blog post which uses Excel to perform the calculations.

Problem statement

Noise data is measured in decibels, which are expressed on a logarithmic scale. This means that a simple arithmetic average (such as the AVERAGE aggregate which is available via Processing & Logic) is not a suitable way to find the mean value of a set of noise data.

Solution

In basic terms, an appropriate logarithmic average can be achieved by calculating the anti-log of each decibel value, finding the average of all the anti-log values, and then finally calculating the log of that average. And by controlling how many anti-log values are included in the average calculation, this can be achieved in a "rolling" way by having a moving window of raw values included in each iteration.

Raw data

The sample noise data used in the following example will comprise 10 hours of 1-minute values. The average calculation will include 30 minutes of raw data, and produce one output every minute. The timestamp of each average value will be at the start of the 30-minute window, just like the built-in eagle.io aggregates (which in turn are derived from OPC Unified Architecture specifications). This means that the raw values between 00:00 (inclusive) and 00:30 (exclusive) will be aggregated and the resulting logarithmic average will have a timestamp of 00:00. The window will then move to include raw values between 00:01 (inclusive) and 00:31 (exclusive), with the logarithmic average having a timestamp of 00:01, and so on.

Making the raw data public

The first step for external processing is to make the the data public. In this case we only need a single parameter, so in the security configuration of the parameter, enable public access, and set the correct timezone (this should be the same as the data source timezone). The time format should be left as the default of YYYY-MM-DDTHH:mm:ss.SSSZ for best compatibility with the Google sheet.

This will provide a public URL displaying the raw noise data in text form:

Getting the raw data into a Google sheet

Now we create a new Google sheet, and in cell A1 import the parameter data using the importdata formula containing the public URL:

Calculating the logarithmic average using the Google sheet

This is where the actual calculations occur, and there are 3 steps which will use 3 columns, C through E. Note that the raw data starts on row 4 of column B, so the first window of 30 values to be averaged will come from B4 though B33.

Calculation 1: divide the raw data by 10 and anti-log the result

In cell C4, enter =10^(B4/10) and fill down:

Calculation 2: average the first 30 anti-logged values

In cell D4, enter =average(C4:C33) and fill down:

Calculation 3: log the averaged results

In cell E4, enter =10*log(D4) and fill down:

The values in column E are the logarithmic averages, i.e. E4 contains the log average of noise values from B4 though B33, E5 contains the log average of noise values from B5 though B34, E6 contains the log average of noise values from B6 though B35, and so on.

Now that the calculations are complete, add a label in E2 and units in E3 so it's easier to identify when the data is used in eagle.io:

Getting the averaged results back into eagle.io

From the File menu select Share -> Publish to web:

Select Sheet1, with a format of Comma-separated values, and click Publish:

A URL will be generated; copy this so it can be used in eagle.io:

In eagle.io, create a new data source with a transport of Download via HTTP:

Enter the URL that was just created by the Google sheet:

In the parser configuration, set the time format to be YYYY-MM-DDTHH:mm:ss.SSSZ, the labels row to be 2, the units row to be 3, and disable columns 2, 3 and 4 (because we only need the average data in column 5):

This will create a new parameter named LAS10 log avg 30-min:

Charting the result

Now we can create a chart to compare the raw noise data in the LAS10 parameter with the averaged data in the LAS10 log avg 30-min parameter:

The averaged data is clearly less spiky, and hopefully more useful to decision makers, than the original raw noise data.

External calculation with Google sheets can be used for a huge variety of useful purposes; even if you don't need to calculate log averages, you can adopt the general approach shown above and substitute your own calculations, aggregations or formulas as required.