Problem in building a JSON list of data

Hi folks,

after a long time of stability in my config I’m trying to do a new move.
As I’m changing my power supplier to aWattar I can read the hourly prices of electricity. So far, so good.

Now I want to put a part of the values to a simple list like this:

{"data":[21.2,22.3,17.5,...]} 

I want to use this list as JSON table in Node-Red later on.
I have all the single values and the calculation model is right.
It’s just a problem in building the list…

#aWattar Strompreise
  - platform: rest
    resource: https://api.awattar.de/v1/marketdata
    name: "awattar"
    value_template: "{{ (value_json.data[0]) }}"
    json_attributes:
      - data
    scan_interval: 60
  - platform: template
    sensors:
      awattar_json:
        friendly_name: "aWattar JSON table"
        value_template: >-
          {% set awattar_json =
            {"data":
            {
            ((((state_attr('sensor.awattar', 'data')[0].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),
            ((((state_attr('sensor.awattar', 'data')[1].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),
            ((((state_attr('sensor.awattar', 'data')[2].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),
            ((((state_attr('sensor.awattar', 'data')[3].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),
            ((((state_attr('sensor.awattar', 'data')[4].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2)
            }}
          %}

I’ve been fiddeling around with this now for two days in a row but I only get any result if I build up the list like this:

{"0":"22.3",'"1":"23.8","2":"17.5",...}

This format I can’t get my head around in Node-Red for further processing. Basically I want to search for minima, 2 hours in a row minima, 3 hours in a row minima, …

With the current extract from my config I get the following error message (not unexpected):

Failed config
  sensor.template: 
    - Invalid config for [sensor.template]: invalid template (TemplateSyntaxError: expected token ':', got ',') for dictionary value @ data['sensors']['awattar_json']['value_template']. Got '{% set awattar_json =\n  {"data":\n  {\n  ((((state_attr(\'sensor.awattar\', \'data\')[0].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),\n  ((((state_attr(\'sensor.awattar\', \'data\')[1].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),\n  ((((state_attr(\'sensor.awattar\', \'data\')[2].marketprice)|float*100)/1000) | multiply(1.19)  + 19.22) |round(2),\n  ((((state_attr(\'sensor.awattar\', \'data\')[3].marketprice)|float*100)/1000) | multiply(1.19)  +.... (See ?, line ?).

Any simple example in how to build a list is highly appreciated, I’ve read the doc on templates over and over again now. When the list is filled I’ll probably have to use the “to_json” filter. But that’s part two of the cake…

Best regards
Ralf

1 Like

well, it’s hard to help when we can’t see the shape of sensor.awattar.attributes.data, but i’ll try.

  - platform: template
    sensors:
      awattar_json:
        friendly_name: "aWattar JSON table"
        value_template: >-
          {%- set awattar_json = state_attr('sensor.awattar', 'data') %}
          {%- set awattar_list = awattar_json | map(attribute='marketprice') | list %}
          {%- set values = namespace(awattar=[]) %}
          {%- for v in awattar_list %}
            {%- set values.awattar = values.awattar + [ (v | float * 100/1000 * 1.19 + 19.22) | round(2) ] %}
          {%- endfor %}
          {{ {'data':values.awattar} }}

EDIT: Made it a dict like you wanted… missed that first go

Crazy shit!

I can say I do not really fully understsand it but first of all: it’s working with some result.

{‘data’: [22.48, 21.95, 21.75, 21.46, 21.07, 21.95, 21.96, 22.1, 22.21, 21.81, 20.2, 19.41, 19.69, 19.22]}

Is it what I wanted? Well, as I said, I don’t understand it right, I think.

So, to make me better understand, I’ll try to sort my brain a little bit.

As mentioned by petro, here is the output of the rest sensor.

{
  "object": "list",
  "data": [
    {
      "start_timestamp": 1581757200000,
      "end_timestamp": 1581760800000,
      "marketprice": 27.41,
      "unit": "Eur/MWh"
    },
    {
      "start_timestamp": 1581760800000,
      "end_timestamp": 1581764400000,
      "marketprice": 22.96,
      "unit": "Eur/MWh"
    },

The timestamps are epoch times, market price is the value I’m locking for and the unit is given, then values for several hours in the future.

With the math done to the market price values I get the value in Cent/kWh (as this is the value given on my bill, just for comparison reasons.)

What I want to do is to find the cheapest hour in the given data, probably also the cheapest 2, 3 or 4 hours in a row. This search I would like to do in Node-Red.

With the data set that is coming out the solution of pedro I do get the values in Node-Red.

Here is the debug output from the sensor in Node-Red:

sensor.awattar_json : msg : Object
object
_msgid: "d7d6f92d.53f988"
topic: "sensor.awattar_json"
payload: "{'data': [22.48, 21.95, 21.75, 21.46, 21.07, 21.95, 21.96, 22.1, 22.21, 21.81, 20.2, 19.41, 19.69, 19.22]}"
data: object
entity_id: "sensor.awattar_json"
state: "{'data': [22.48, 21.95, 21.75, 21.46, 21.07, 21.95, 21.96, 22.1, 22.21, 21.81, 20.2, 19.41, 19.69, 19.22]}"

So far, everything fine.

Now comes the thing with searching for min value, 2 lowest in a row…

To make it right I think I just need a list of values, meaning something like this:
{22.48, 21.95, 21.75, 21.46, 21.07, 21.95, 21.96, 22.1, 22.21, 21.81, 20.2, 19.41, 19.69, 19.22}

And now I’m back to the point that I don’t understand pedros great work :frowning:

Feeling a little bit dumb, but could someone with better understanding in programming push me over the edge?

Help really appreciated.
Ralf

personally, i’d change your main sensor to out put the index of the 2 minimum. Then just simply grab the values in another template sensor.

  - platform: rest
    resource: https://api.awattar.de/v1/marketdata
    name: awattar
    value_template: >
      {%- set awattar_list = value_json.data | map(attribute='marketprice') | list %}
      {%- set values = namespace(min=[]) %}
      {% for i in range(awattar_list | length) %}
        {% if i + 1 < awattar_list | length %}
          {%- set v = (awattar_list[i] | float * 100/1000 * 1.19 + 19.22) | round(2) %}
          {%- set next = (awattar_list[i+1] | float * 100/1000 * 1.19 + 19.22) | round(2) %}
          {%- set values.min = values.min + [ v + next ] %}
        {% endif %}
      {%- endfor %}
      {{ values.min.index(values.min | min)  }}
    json_attributes:
      - data
    scan_interval: 60
  - platform: template
    sensors:
      lowest_awattar:
        friendly_name: "Lowest awattar"
        value_template: >-
          {% set index = states('sensor.awattar_table') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ (table[index].marketprice | float * 100/1000 * 1.19 + 19.22) | round(2) }}
      lowest_awattar_timestamp:
        friendly_name: "Lowest awattar timestamp"
        device_class: timestamp
        value_template: >-
          {% set index = states('sensor.awattar_table') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ table[index].start_timestamp | int / 1000 }}
      lowest_awattar_2:
        friendly_name: "Second lowest awattar"
        value_template: >-
          {% set index = states('sensor.awattar_table') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ (table[index+1].marketprice | float * 100/1000 * 1.19 + 19.22) | round(2) }}
      lowest_awattar_timestamp_2:
        friendly_name: "Second lowest awattar timestamp"
        device_class: timestamp
        value_template: >-
          {% set index = states('sensor.awattar_table') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ table[index+1].start_timestamp | int / 1000 }}

Breakdown:

Takes data (which is a list of dictionaries) and filters it to a list marketprice values.

{%- set awattar_list = value_json.data | map(attribute='marketprice') | list %}

Creates an empty list that we can use to store values, it’s named values.min

{%- set values = namespace(min=[]) %}

Loop through your data, using i, where i is a indexer (position in the list)

{% for i in range(awattar_list | length) %}

check if we have an position in the list after the current position

{% if i + 1 < awattar_list | length %}

get our current math value

{%- set v = (awattar_list[i] | float * 100/1000 * 1.19 + 19.22) | round(2) %}

get our next math value

{%- set next = (awattar_list[i+1] | float * 100/1000 * 1.19 + 19.22) | round(2) %}

add the 2 values together and add it to the list

{%- set values.min = values.min + [ v + next ] %}

end the if statement and the for loop

{% endif %}
{%- endfor %}

Get the lowest par, then find it’s position in the list, output that position

{{ values.min.index(values.min | min)  }}

The template sensors just use that position to output the data:

calculates the first item in the lowest pair (using the index)

{{ (table[index].marketprice | float * 100/1000 * 1.19 + 19.22) | round(2) }}

calculates the first items timestamp in the lowest pair (using the index)

{{ table[index].start_timestamp | int / 1000 }}

calculates the second item in the lowest pair (using the index)

{{ (table[index+1].marketprice | float * 100/1000 * 1.19 + 19.22) | round(2) }}

calculates the second items timestamp in the lowest pair (using the index)

{{ table[index+1].start_timestamp | int / 1000 }}

1 Like

Wow!!!

My programming times were back in the 80s. That time you could not do stuff like that.

petro: BIG KUDOS!!!

It will take me at least two days to really understand it, but hey, that’s really great!

Thanks
Ralf

OK.

After digging deeper and playing around with the provided solution I possibly can now better explain what I would like to do and how:

IMHO the main sensor should be the full dataset.
From that I want to derive in template sensors;

  1. the market prices in one list to find the minimum and 2, 3, 4 hours of minimum price (most probably individual template sensors), including the respective timestamp of the starting hour of 1, 2, 3, 4 hours time frame)
  2. the count of available values.
  3. at least for the 4 hour range I would like to see the range before 6 a.m. of the next day (charging my EV over night, takes some hours).

What I found so far was a small mistake in petros solution. Please see the orig post and my changes here. In the template sensor the set index line needs to be changed:
Orig from petro

  - platform: template
    sensors:
      lowest_awattar:
        friendly_name: "Lowest awattar"
        value_template: >-
          {% set index = states('sensor.awattar_table') | int %}

changed

  - platform: template
    sensors:
      lowest_awattar:
        friendly_name: "Lowest awattar"
        value_template: >-
          {% set index = states('sensor.awattar') | int %} #changed the sensor name

So with this I perfectly get as individual sensors:

  1. the index of the lowest value including the price reading and the timestamp as templates
  2. the same for the two lowest values in a row.

With all my playing around I confused myself into deep dark mess.
Here is what I crafted as additional sensor (keeping the other two untouched for the time being):

#aWattar Strompreise
  - platform: rest
    resource: https://api.awattar.de/v1/marketdata
    name: awattar_all_values
    json_attributes:
      - data
    scan_interval: 60
    value_template: >
      {%- set awattar_all_list = value_json.data | list %}
      {{ awattar_all_list }}
  - platform: template
    sensors:
      awattar_count:
        friendly_name: "Count of available prices"
        value_template: >-
          {% set count = state_attr('sensor.awattar_all_values', 'data') | list | length %}
          {{ count }}
      awattar_values:
        friendly_name: "All aWattar values"
        value_template: >-
          {%- set awattar_all_list = value_json.data | map(attribute='marketprice') | list %}
          {%- set values_all = namespace(all=[]) %}
          {% for i in range(awattar_all_list | length) %}
           {%- set v = (awattar_all_list[i] | float * 100/1000 * 1.19) | round(2) %}
            {%- set values_all.all = values_all.all + [ v ] %}
          {%- endfor %}
          {{ values_all.all }}

Totally unexpected at all I don’t get a sensor with the name “awattar_all_values” at all in my entities list.
As this sensor is not there the two template sensors give the reading “unavailable”.

:woozy_face: :woozy_face: :woozy_face:

As I said before, my programming skills are really not great and from a long time ago.

Maybe digging deeper here can help other people later on as I expect this power provider to become more popular in the future.

Hah, fixed it :slight_smile:

#aWattar Strompreise
  - platform: rest
    resource: https://api.awattar.de/v1/marketdata
    name: awattar
    json_attributes:
      - data
    scan_interval: 60
    value_template: "{{ (value_json.data[0]) }}"
  - platform: template
    sensors:
      awattar_count:
        friendly_name: "Count of available prices"
        value_template: >-
          {% set count = state_attr('sensor.awattar', 'data') | list | length %}
          {{ count }}
      awattar_values:
        friendly_name: "All aWattar values"
        value_template: >-
          {%- set awattar_all_list = state_attr('sensor.awattar', 'data') | map(attribute='marketprice') | list %}
          {%- set values_all = namespace(all=[]) %}
          {% for i in range(awattar_all_list | length) %}
           {%- set v = (awattar_all_list[i] | float * 100/1000 * 1.19) | round(2) %}
            {%- set values_all.all = values_all.all + [ v ] %}
          {%- endfor %}
          {{ values_all.all }}
      awattar_one_hour_min:
        friendly_name: "aWattar one hour minimum value index in available dataset"
        value_template: >-
          {%- set awattar_list = state_attr('sensor.awattar', 'data') | map(attribute='marketprice') | list %}
          {%- set values = namespace(min=[]) %}
          {% for i in range(awattar_list | length) %}
           {%- set v = (awattar_list[i] | float * 100/1000 * 1.19) | round(2) %}
            {%- set values.min = values.min + [ v ] %}
          {%- endfor %}
          {{ values.min.index(values.min | min) }}
      awattar_one_hour_min_value:
        friendly_name: "aWattar one hour minimum value in available dataset"
        value_template: >-
          {%- set awattar_list = state_attr('sensor.awattar', 'data') | map(attribute='marketprice') | list %}
          {%- set values = namespace(min=[]) %}
          {% for i in range(awattar_list | length) %}
           {%- set v = (awattar_list[i] | float * 100/1000 * 1.19) | round(2) %}
            {%- set values.min = values.min + [ v ] %}
          {%- endfor %}
          {{ values.min | min }}
      awattar_one_hour_timestamp:
        friendly_name: "aWattar one hour minimum value timestamp in available dataset"
        device_class: timestamp
        value_template: >-
          {% set index = states('sensor.awattar_one_hour_min') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ ( table[index].start_timestamp | int / 1000 ) | timestamp_local}}
      awattar_two_hour_min:
        friendly_name: "aWattar two hour minimum start index"
        value_template: >-
          {%- set awattar_list = state_attr('sensor.awattar', 'data') | map(attribute='marketprice') | list %}
          {%- set values = namespace(min=[]) %}
          {% for i in range(awattar_list | length) %}
            {% if i + 1 < awattar_list | length %}
              {%- set v = (awattar_list[i] | float * 100/1000 * 1.19 ) | round(2) %}
              {%- set next = (awattar_list[i+1] | float * 100/1000 * 1.19 ) | round(2) %}
              {%- set values.min = values.min + [ v + next ] %}
            {% endif %}
          {%- endfor %}
          {{ values.min.index(values.min | min)  }}
      awattar_two_hour_timestamp:
        friendly_name: "aWattar two hour minimum value start timestamp in available dataset"
        device_class: timestamp
        value_template: >-
          {% set index = states('sensor.awattar_two_hour_min') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ ( table[index].start_timestamp | int / 1000 ) | timestamp_local}}
      awattar_four_hour_min:
        friendly_name: "aWattar four hour minimum start index"
        value_template: >-
          {%- set awattar_list = state_attr('sensor.awattar', 'data') | map(attribute='marketprice') | list %}
          {%- set values = namespace(min=[]) %}
          {% for i in range(awattar_list | length) %}
            {% if i + 3 < awattar_list | length %}
              {%- set v1 = (awattar_list[i] | float * 100/1000 * 1.19 ) | round(2) %}
              {%- set v2 = (awattar_list[i+1] | float * 100/1000 * 1.19 ) | round(2) %}
              {%- set v3 = (awattar_list[i+2] | float * 100/1000 * 1.19 ) | round(2) %}
              {%- set v4 = (awattar_list[i+3] | float * 100/1000 * 1.19 ) | round(2) %}
              {%- set values.min = values.min + [ v1 + v2 + v3 + v4 ] %}
            {% endif %}
          {%- endfor %}
          {{ values.min.index(values.min | min)  }}
      awattar_four_hour_timestamp:
        friendly_name: "aWattar four hour minimum value start timestamp in available dataset"
        device_class: timestamp
        value_template: >-
          {% set index = states('sensor.awattar_four_hour_min') | int %}
          {% set table = state_attr('sensor.awattar', 'data') %}
          {{ ( table[index].start_timestamp | int / 1000 ) | timestamp_local}}
1 Like

Sorry to jump into this rather old topic. As another Awattar User I’d like to use this functionality as well but my programming skills are non-existent.

As it looks that you’ve figured out how to get the data I’ve tried to integrate your code in my configuration.yaml but failed miserably with various error messages. Is configuration.yaml the right place at all?

It goes in configuration.yaml in your sensor section

Ok. Got it. It’s working. As always the problem was before the screen. Forgot that I moved the sensor section into a separate yaml. Placed code there, worked instantly like a charm.

Thanks again for pointing me in the right direction

1 Like

For the Node RED afficionados:
I just wrote a HA sensor for the EPEX Spot data from aWATTar in Node RED:

Hi,

May I be so bold to do a feature request?
A sensor that gives the ‘current’ price (preferably in eur/kWh)? (like @Jpsy created in Node RED)

Thanks :slight_smile:

1 Like

Thanks for this great solution. I see one drawback: It runs each hour and retrieves the prices for current hour + 24h (if available). This means the lowest hours can move each call and lead to devices not only running 2h per day but possibly longer (or even not at all).
Solution idea would be to call the api with a startdate today at 0:00. The awattar spec allows to enter a startdate in the format epochseconds including miliseconds. So the algorithm should take today’s date without seconds, transfer it to epoch seconds and pass it over to the api.
Only question is: How exactly?

This runs every 60 seconds, not each hour. The calculation is instantaneous, so it doesn’t run for 2 hours a day. Every 60 seconds, it looks that the endpoint and outputs the result.

Yes, you are right. Every 60 seconds. But it doesn’t change the statement. After each hour the reported timeframe is shifted by 1h and that means the 1-2-4 reported cheapest hours can shift each hour. I think they should be calculated once per day for the day. Because you usually want to switch on devices a certain timeframe (namely 1, 2 or 4h) per day and want to be sure they run that amount and not longer or shorter.

The minimum is calculated. If it shifts, it’s still the minimum and the index shifts with it. The time doesn’t change neither does the value.

Anyways, I reviewed whats going on here and I see ways to optimize this so you can get your timeframe and the lowest x hours as sensors

Put this in your sensor section

  - platform: rest
    resource: https://api.awattar.de/v1/marketdata
    name: awattar
    json_attributes:
      - data
    scan_interval: 60
    value_template: >
      {% set start = (today_at().timestamp() * 1000) | int %}
      {% set end = ((today_at() + timedelta(days=1)).timestamp() * 1000) | int %}
      {% set values_today = value_json.data | selectattr('start_timestamp', '>=', start) | selectattr('end_timestamp', '<=', end) | list %}
      {% set min_today = values_today | sort(attribute='marketprice') | first %}
      {% set i = values_today.index(min_today) %}
      {% set hours = 4 %}
      {% set search = hours - 1 %}
      {% set s = i - search if i - search >= 0 else 0 %}
      {% set s = value_json.data.index(values_today[s]) %}
      {% set e = i + search if i + search < values_today | length else values_today | length - 1 %}
      {% set e = value_json.data.index(values_today[e]) %}
      {{ s }},{{ e }},{{ value_json.data[s].start_timestamp | multiply(0.001) | timestamp_local }}

Put this in configuration.yaml

template:
- trigger:
  - platform: state
    entity_id: sensor.awattar
    variables:
      value: >
        {{ trigger.to_state.state if trigger.to_state.state not in ['unavailable', 'unknown'] else '0,0,0' }}
      time_span: >
        {% set set s, e = value | split(',')[:2] | map('int')  %}
        {% set hours = 4 %}
        {% set ns = namespace(windows=[]) %}
        {% for span in range(e - s) %}
        {% set roll = (e - s) - span %}
        {% set sub = namespace(window=[]) %}
        {% for i in range(roll) %}
        {% set total = data[s+i:s+span+i+1] | selectattr('marketprice', 'defined') | map(attribute='marketprice') | sum %}
        {% set sub.window = sub.window + [ {'timestamp':data[s+i].start_timestamp//1000, 'total':total} ] %}
        {% endfor %}
        {% set item = sub.window | sort(attribute='total') | first %}
        {% set ns.windows = ns.windows + [ (span+1, item) ] %}
        {% endfor %}
        {{ dict.from_keys(ns.windows) }}
  sensor:
  - name: Awatter One Hour Min
    state: "{{ time_span.1.total }}"
  - name: Awatter One Hour Timestamp
    state: "{{ time_span.1.timestamp | timestamp_local | as_datetime }}"
    device_class: timestamp
  - name: Awatter Two Hour Min
    state: "{{ time_span.2.total }}"
  - name: Awatter Two Hour Timestamp
    state: "{{ time_span.2.timestamp | timestamp_local | as_datetime }}"
    device_class: timestamp
  - name: Awatter Three Hour Min
    state: "{{ time_span.3.total }}"
  - name: Awatter Three Hour Timestamp
    state: "{{ time_span.3.timestamp | timestamp_local | as_datetime }}"
    device_class: timestamp
  - name: Awatter Three Hour Min
    state: "{{ time_span.4.total }}"
  - name: Awatter Three Hour Timestamp
    state: "{{ time_span.4.timestamp | timestamp_local | as_datetime }}"
    device_class: timestamp

Might have some issues, lemme know if it does

Thanks Petro.

indeed there seem to be some small issues. First, there are in line 9 2x “set”. I removed one. Still there is a message that some block end is missing. It seems to be around that line as well…

Logger: homeassistant.config
Source: config.py:929
First occurred: 19:51:31 (1 occurrences)
Last logged: 19:51:31

Invalid config for [template]: invalid template (TemplateSyntaxError: expected token 'end of statement block', got '[') for dictionary value @ data['trigger'][0]['variables']['time_span']. Got "{% set s, e = value | split(',')[:2] | map('int') %} {% set hours = 4 %} {% set ns = namespace(windows=[]) %} {% for span in range(e - s) %} {% set roll = (e - s) - span %} {% set sub = namespace(window=[]) %} {% for i in range(roll) %} {% set total = data[s+i:s+span+i+1] | selectattr('marketprice', 'defined') | map(attribute='marketprice') | sum %} {% set sub.window = sub.window + [ {'timestamp':data[s+i].start_timestamp//1000, 'total':total} ] %} {% endfor %} {% set item = sub.window | so.... (See /config/configuration.yaml, line 26).

Imagine a list of prices, let’s say at 00:00 that go straight down: 50,49,…,26 for the first 24 hours. Cheapest hour: At 23:00 with 26 ct.
Then, one later, at 1:00 the new call shifts the list. So it starts now with 49 and goes down to 25. The new cheapest hour is at 00:00 (next day) with 26 ct.
That means the first day will never “reach” the cheapest hour.

What you’re saying doesn’t make sense. If the values change, the new cheapest hour is found. If the values are static and shift one hour. The cheapest value will still be the cheapest value. If you’re simply trying to say “I don’t want it to find values from tomorrow” then just say that. Otherwise the shifting of values has no impact on the calculation as it searches for the smallest and reports the index.