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.

## Comments

0 comments

Article is closed for comments.