PVPC trend graph in visu

[JRP]

Hello

I have a script with the data on the price of electricity in Spain, called PVPC.

Using the esios api I get the price value for each hour and save each price value in a virtual object.

Now I want to make a graph to show the data, in the style of the one shown for the PVPC on this website.

https://www.esios.ree.es/es/pvpc

How could I have that kind of graph in visu?

Thank you very much

[JRP]

In another thread it is suggested to use https://quickchart.io/ for the generation of graphs and I think it can be a solution to my problem of graphing the price/hour data of the PVPC of Spain.

I have the pvpc data in a table like this

Code:

* table:
["indicator"]
  * table:
   ["composited"]
    * bool: false
   ["magnitud"]
    * table:
     [1]
      * table:
       ["name"]
        * string: Precio
       ["id"]
        * number: 23
   ["id"]
    * number: 1001
   ["tiempo"]
    * table:
     [1]
      * table:
       ["name"]
        * string: Hora
       ["id"]
        * number: 4
   ["step_type"]
    * string: linear
   ["short_name"]
    * string: PVPC T. 2.0TD
   ["values"]
    * table:
     [1]
      * table:
       ["datetime_utc"]
        * string: 2022-08-26T22:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 492.82
       ["tz_time"]
        * string: 2022-08-26T22:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T00:00:00.000+02:00
     [2]
      * table:
       ["datetime_utc"]
        * string: 2022-08-26T23:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 497.35
       ["tz_time"]
        * string: 2022-08-26T23:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T01:00:00.000+02:00
     [3]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T00:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 494.44
       ["tz_time"]
        * string: 2022-08-27T00:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T02:00:00.000+02:00
     [4]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T01:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 508.86
       ["tz_time"]
        * string: 2022-08-27T01:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T03:00:00.000+02:00
     [5]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T02:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 531.02
       ["tz_time"]
        * string: 2022-08-27T02:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T04:00:00.000+02:00
     [6]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T03:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 553.12
       ["tz_time"]
        * string: 2022-08-27T03:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T05:00:00.000+02:00
     [7]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T04:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 568.16
       ["tz_time"]
        * string: 2022-08-27T04:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T06:00:00.000+02:00
     [8]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T05:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 557.06
       ["tz_time"]
        * string: 2022-08-27T05:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T07:00:00.000+02:00
     [9]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T06:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 507.94
       ["tz_time"]
        * string: 2022-08-27T06:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T08:00:00.000+02:00
     [10]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T07:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 445.72
       ["tz_time"]
        * string: 2022-08-27T07:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T09:00:00.000+02:00
     [11]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T08:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 405.18
       ["tz_time"]
        * string: 2022-08-27T08:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T10:00:00.000+02:00
     [12]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T09:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 389.13
       ["tz_time"]
        * string: 2022-08-27T09:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T11:00:00.000+02:00
     [13]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T10:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 381.83
       ["tz_time"]
        * string: 2022-08-27T10:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T12:00:00.000+02:00
     [14]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T11:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 381.23
       ["tz_time"]
        * string: 2022-08-27T11:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T13:00:00.000+02:00
     [15]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T12:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 385.12
       ["tz_time"]
        * string: 2022-08-27T12:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T14:00:00.000+02:00
     [16]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T13:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 391.42
       ["tz_time"]
        * string: 2022-08-27T13:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T15:00:00.000+02:00
     [17]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T14:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 400.21
       ["tz_time"]
        * string: 2022-08-27T14:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T16:00:00.000+02:00
     [18]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T15:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 407.64
       ["tz_time"]
        * string: 2022-08-27T15:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T17:00:00.000+02:00
     [19]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T16:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 414.83
       ["tz_time"]
        * string: 2022-08-27T16:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T18:00:00.000+02:00
     [20]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T17:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 491.04
       ["tz_time"]
        * string: 2022-08-27T17:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T19:00:00.000+02:00
     [21]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T18:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 544.13
       ["tz_time"]
        * string: 2022-08-27T18:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T20:00:00.000+02:00
     [22]
      * table:
       ["datetime_utc"]
        * string: 2022-08-27T19:00:00Z
       ["geo_id"]
        * number: 8741
       ["value"]
        * number: 555.8
       ["tz_time"]
        * string: 2022-08-27T19:00:00.000Z
       ["geo_name"]
        * string: Península
       ["datetime"]
        * string: 2022-08-27T21:00:00.000+02:00
     [23]
      * table:
       ["date...

Later with this script I get the URL with the chart based on the configuration selected in chart.

Code:

json = require('json')
http = require('socket.http')
ltn12 = require('ltn12')

url = 'https://quickchart.io/chart/create'
ancho = 500
alto = 300
tipo = line

grafico = "{type:'line',data:{labels:['00','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23'],datasets:[{label:'PVPC',backgroundColor:'rgb(255, 255, 132)',borderColor:'rgb(255, 195, 132)',data:[93, 29, 17, 8, 73, 98, 40],fill:false,},],},options:{title:{display:false,text:'PVPC',},},}"

body = json.encode(
 
  {
  backgroundColor = "transparent",
  width = ancho,
  height = alto,
  format = "png",
  chart = grafico
}
)

log(body)

resp = {}

res, code = http.request({
  url = url,
  method = 'PUT',
  headers = {
    ['Content-Type']  = 'application/json',
    ['Content-Length'] = #body,

  },
  source = ltn12.source.string(body),
  sink = ltn12.sink.table(resp),
})

log(res, code, resp)

I would need to assemble the chart chain with the data separated and declared as variables:
type
data
  labels
datasets
  labels
  backgroundColor
  borderColor
  data (the prices of the table obtained from the api esios PVPC)
 
Just like I do with the url, height and width.

In addition, I would need to be able to use the url that is obtained in the visualization, to be able to see the png that it generates.
Thank you

[admin]

You can use a JS chart library instead of a web service for this. See this example: https://forum.logicmachine.net/showthread.php?tid=3789
The data that you want to display can be saved into storage and loaded via .lp file.

[JRP]

Hi, thanks for the alternative. I've been looking at the suggested thread and I don't quite understand how it works. I would need a more detailed example.
I understand that using JS would be better, but how would it be done as I propose?
Thank you
Greeting

[admin]

Custom JS code, adjust as needed.

Code:

$(function() {
  // load chartist CSS
  $('<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.css" type="text/css">').appendTo('head');

  // load chartist JS
  $.getScript('https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.js', function() {
    // create chart container, add it to plan with ID 8
    var el = $('<div></div>').css({
      position: 'absolute',
      width: '400px',
      height: '300px',
      left: '10px',
      top: '50px',
      zIndex: 999
    }).appendTo('#plan-8');

    // load chart data
    $.getJSON('/user/data.lp', function(data) {
      // create chart
      var chart = new Chartist.Line(
        el.get(0),
        {
          labels: [1, 2, 3, 4, 5, 6, 7, 8],
          series: [ data ]
        },
        {
          low: 0,
          showArea: true
        }  
      );
    });
  });
});

data.lp should be uploaded via FTP using apps login to the user directory.
For this example it simply generates an array of random numbers:

Code:

<?

math.randomseed(os.time())

data = {}
for i = 1, 8 do
  data[ i ] = math.random(0, 10)
end

json.write(data)

You can also output storage variable that another script creates:

Code:

<?
require('apps')
data = storage.get('my_var_name')
json.write(data)

[JRP]

Hello

I've copied the script into custom JS, changing the vis ID to 4.

I have created the .lp file in user and copied the second script.

I have stored a value and with the second script that value appears if I manually load the page ip/user/data.lp

But it happens to me:

If I enter the visualization through ip address//scada-vis the graph is not shown, just a strange symbol and a 0 with a dot.

If I load IP address//scada-vis/#4, I see the grid of the graph, very dimmed and without showing any value.

I would need to keep just the price (value) keys, in the date order 00 first and 23 last and store them in the variable.
Greeting

[admin]

What does you user.lp return? It must be a valid JSON array with up to 24 numbers.
I've modified the example to handle resizing when plan changes. It fixed the issue when chart is not rendered correctly on a plan that is not visible when rendering happens.

Code:

$(function() {
  $('<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.css" type="text/css">').appendTo('head');

  $.getScript('https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.js', function() {
    var el = $('<div></div>').css({
      position: 'absolute',
      width: '600px',
      height: '300px',
      left: '10px',
      top: '50px',
      zIndex: 999
    }).appendTo('#plan-8');

    $.getJSON('/user/data.lp', function(data) {
      var labels = [];
      
      for (var i = 0; i < 24; i++) {
        var label = '';
        
        if ((i % 3) == 0) {
          label = i + ':00';
        }
        
        labels.push(label);
      }
      
      var chart = new Chartist.Line(
        el.get(0),
        {
          labels: labels,
          series: [ data ]
        },
        {
          low: 0,
          showArea: true
        }  
      );
      
      $('body').on('showplan', function() {
        chart.resizeListener();
      });
    });
  });
});

[JRP]

Hello
I'm going to collect the information to try to clarify myself.
1. I get the prices/hour of the PVPC with the esios api. The result is a Lua table that I store in storage with the name pvpc.
2. I create the data.lp file and save it to user
3. I copy the js script and change it to id4

Result

The graph is drawn but the data must be ordered from lowest hour 0 to highest hour 23, and since the price is different every hour, it cannot be that graph that increases.

I want a graph similar to the attached one, which is the one shown by esios.

Thank you

[admin]

Your storage data is sorted by price. You need to use the original data that is sorted by time before it is sorted by price.

[JRP]

Hello, I understand what you are saying, the table was stored out of order when putting the storage instruction, after ordering the table to obtain the minimum and maximum price. My mistake in putting the instruction at that point.
By putting the instruction at the right point, the table is saved in the ordered storage and the graph is displayed correctly.

Now I would like all times to be displayed and the :00 is not required as it is always the hour on the dot.

How do I make the price values divided by 1000?

Thank you very much for your help

[admin]

To change the labels modify lines 17..25 like this:

Code:

for (var i = 0; i < 24; i++) {
  var label = i.toString();
  if (label.length == 1) {
    label = '0' + label;
  }

  labels.push(label);
}

[JRP]

thanks i will try it.

  How can I divide all the values by 1000 so that they are displayed on the graph? And one last request, is it possible for the Y axis labels to show the actual price values of each point, instead of being a scale?

other question

Testing this method on a wiser for KNX HW v2.0 with firmware 2.61, the graph is not drawn correctly.

I attach a screenshot.

I tried all kinds of positions and sizes and it never showed beyond hour 19. The data in the storage is correct and the Json with the data in the data.lp file is fine. And until hour 19 the graph corresponds.

Is it a firmware version issue?

Thank you very much
Greetings

[JRP]

Hello

Any suggestion?

Greeting

[admin]

You can convert the data on the client side:

Code:

for (var i = 0; i < data.length; i++) {
  data[ i ] = data[ i ] / 1000;
}

var chart = ...

This chart library does not work correctly when plan scaling is enabled. See if replacing the URL from "https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.js" to "https://dl.openrb.com/misc/chartist.js" makes it work correctly.

[JRP]

Hello

I don't know what the scaling is enabled plan is, where is it?

In my wiser it works perfectly with the two url's.

I have added the suggested code and the graph is drawn, the data axis is scaled but the graph is empty of data.

full script

Code:

$(function() {
  $('<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.css" type="text/css">').appendTo('head');

  $.getScript('https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.js', function() {
    var el = $('<div></div>').css({
      position: 'absolute',
      width: '600px',
      height: '300px',
      left: '700px',
      top: '50px',
      zIndex: 999
    }).appendTo('#plan-4');

    $.getJSON('/user/data.lp', function(data) {
      var labels = [];   
     
      for (var i = 0; i < 24; i++) {
  var label = i.toString();
  if (label.length == 1) {
    label = '0' + label;
  }

  labels.push(label);
}
for (var i = 0; i < data.length; i++) {
  data[ i ] = data[ i ] / 1000;
}

         var chart = new Chartist.Line(
        el.get(0),
        {
          labels: labels,
          series: [ data ]
        },
        {
          low: 0,
          showArea: true
        } 
      );
     
      $('body').on('showplan', function() {
        chart.resizeListener();
      });
    });
  });
});

Thank you

[JRP]

Hello

Any suggestion?

Greeting

[admin]

Default Visualization settings have scaling enabled. So unless your visualization plan size is the same as the screen size it will be scaled.
Since the library doesn't adjust to scaling by default you will get something this:
   
With the fixed version it works correctly:
   

Replace JS library URL:

Code:

$.getScript('https://cdnjs.cloudflare.com/ajax/libs/chartist/0.11.4/chartist.min.js', function() {

With this:

Code:

$.getScript('https://dl.openrb.com/misc/chartist.js', function() {

These screenshots are done with this data script:

Code:

<?

math.randomseed(os.time())

data = {}
for i = 1, 24 do
  data[ i ] = math.random(100, 300)
end

json.write(data)

The random values between 100 and 300 are divided by 1000 on the client side.

If this still does not work then check browser error console (F12) for any errors.

[JRP]

Hello

Thanks for the reply.

The url works with page scaling.

The data if you put the code to divide it, it still doesn't work.

I attach a screenshot with the console errors.

I do not know how to distinguish if there is one referring to the graph.

Greeting

[admin]

Add console.log(data) like this and post what you get in browser console:

Code:

console.log(data);

for (var i = 0; i < data.length; i++) {

[JRP]

If I add the log without the code to split, the data is displayed in the console and the graph is drawn with the data.

If I add the split code and the console lases an array with NaN

Am I putting the division code in the wrong place?