Reading from Thingspeak: Daily averages are easy. How do I get a monthly average?

I use the REST API to read data from Thingspeak and this works well:

https://api.thingspeak.com/channels/xxxxxx/fields/1.json?average=daily&start=2010-01-01&end=2011-01-01

This request correctly returns one value per day. However I need a monthly average - a single value per month. Ideally something like:

https://api.thingspeak.com/channels/xxxxxx/fields/1.json?average=MONTHLY&start=2010-01-01&end=2011-01-01

Is there a way to achieve something like this?

[Vinod]

One way I can think of doing this is to have MATLAB code that is scheduled to run once a month using the TimeControl app. The MATLAB code in the MATLAB analysis app reads the values form the "raw data" channel and computes the monthly average which gets written into a "derived channel". You can then directly query your "derived channel" to get monthly data.

Can you tell us more about what you are doing? That can help guide us to the appropriate solution.

[Håkon Dahle]

Thanks for replying!

The problem: I have a large dataset (10 years of hourly measurements, i.e. about 90.000 measurements stored in a single Thingspeak channel).

In my mobile app I want to create a single chart in order to show a trend over time. Plotting 90.000 datapoints in a single chart makes no sense. Using daily averages helps a lot of course - it results in about 3600 datapoints. But that's still overkill for my charting needs.

I would like to plot montly averages over 10 years, this should make for a nice and compact chart for a mobile app. Not using all 90.000 datapoints also makes the mobile app a lot faster - fetching and plotting 10 x 12 datapoints is preferable.

I could of course do all the averaging in my app, but that still means I have to fetch all the data from Thingspeak before averaging/reducing the data in the app itself. Ideally I would like to request 10 years of monthly averages (i.e. 120 data points).

If there is a way to accomplish this in Matlab it would be great if there is a pointer to some similar example as I am not familiar with Matlab (I do most of my client and server-side stuff in JavaScript/Node.js).

Answer 1

[Christopher Stapels]

There are built in template codes to help you develop MATLAB code. If you go to the MATLAB analysis or MATLAB Visualizations apps, you can select from the templates after choosing to create a new instance of the app.

Here is a tutorial for using one of the templates to calculate averages.:

For your example, I think you will proably want to preprocess the data and store it in another channel. Reads are limited to 8000 points, so you want be able to read all the data in a single call.

I would reccomend writing a script that reads the data and writes the daily avaerages to another channel. Then your Mobile app can read the values or call the charts API for the derived channel. Going forward, you can set a timeControl to write that daily average to the derived channel every day (as Vinod suggested). Historicall you may have to process them in a few batches.

I can provide more details if you get stuck with the code.

[Håkon Dahle]

Hi -

thanks! I will go with the core of our suggestion - "preprocess the data and store it in another channel". I looked at using Matlab for this, but I fear the learning curve will be a bit steep. I will simply use javascript/node.js on the server side for this task. Scheduling a node.js run once a month to update the "other" channel should be easy. I played around with using javascript for processing the monthly averages (and creating the chart) and I liked the result: https://jsfiddle.net/hdahle/6wLhbc7p/151/

-h

Answer 2

[Christopher Stapels]

I also like the look of your result. But I am afraid that it makes a bunch of API requests to the server in a big burst. Its fine for once in a while, but it you are regularly using this kind of code, you could eventually trigger a rate limiting response from the ThingSpeak server. Your code already generates the monthly data, you can write the historical data to the derived channel, and reduce the future burden.

If you didn't want to use your own server-side resources, you could use TimeControl to schedule the MATLAB code to run at regular intervals. I understand not wanting to use a new language, but MATLAB tries pretty hard to be easy to learn.

Just in case you are considering it, the format would look something like this:

Create a new MATLAB analysis with this pseudocode

edited per comments below

numDays=30; % for monthly
readChannelId =123455;
writeChannelId = 543211;
myReadKey='XXXXXXXXXXXXXXXX';
myWriteKey='yyyyyyyyyyyyyyyy';
myData=thingSpeakRead(readChannelID, 'ReadKey',myReadKey,'OutputFortmat','timetable','numDays',numDays);
aveData=retime(myData,'regular','mean','TimeStep',days(numDays));
thingSpeakWrite(writeChannelId, aveData, 'WriteKey', writeAPIKey);

Then use TimeControl to run the Analysis every numDays.

[Håkon Dahle]

Thanks - you are absolulely right, the code does generate a burst of requests (this example was mainly to play around a bit before I decided on the correct way forward).

My server side has now done the necessary averaging by month, and the ten years of monthly averages are now in a separate ThingSpeak channel. A server side node.js process will do an update every month. The client application can now do a single GET request from Thingspeak ang retrieve 10 years of monthly data which is just 120 data point. Fast and efficient.

Thank you for the example Matlab code. It looks pretty doable even for someone without any Matlab experience. Thanks!

[abba baab]

Thanks for the code. Error on last line. aveCar should be aveData.

"MATLAB tries pretty hard to be easy to learn." So true. It is easily the hardest language so far (coming from HTML, CSS, PHP, Arduino and Python). No copy paste wtf. Documentation is horrible and lacks real examples.

[Christopher Stapels]

Thanks for the syntax suggestion, I have fixed it above.

I'm sorry to hear you are frustrated with our documentation, its realtively well known as very high quality from most reports. If you provide an example that you didnt like, I can look into it.