Problem in building a JSON list of data

RalfP · February 18, 2020, 9:19am

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;

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)
the count of available values.
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:

the index of the lowest value including the price reading and the timestamp as templates
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”.

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.

RalfP · February 18, 2020, 1:26pm

Hah, fixed it

#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}}

Der_Fisch001 · January 23, 2021, 1:18am

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?

petro · January 23, 2021, 1:28am

It goes in configuration.yaml in your sensor section

Der_Fisch001 · January 23, 2021, 1:35am

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

Jpsy · August 5, 2021, 6:20am

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

lennardw · August 25, 2021, 1:39pm

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

Riesi · October 19, 2022, 2:58pm

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?

petro · October 19, 2022, 3:04pm

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.

Riesi · October 19, 2022, 3:14pm

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.

petro · October 19, 2022, 3:31pm

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.

petro · October 19, 2022, 5:22pm

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

Riesi · October 19, 2022, 5:54pm

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).

Riesi · October 19, 2022, 6:00pm

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.

petro · October 19, 2022, 6:08pm

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.

Riesi · October 19, 2022, 7:28pm

I’m trying my best. That’s why I try to explain what I mean. No reason to get angry on me. For me, it makes sense. Just not including values from tomorrow is too short-sighted. I think it’s about viewing at a whole day from 0:00 until 23:59.
In general, I want to run - let’s say a heating rod - for the cheapest 2h a day. Or disable the heating pump during the 2 most expensive hours a day.

petro · October 19, 2022, 7:31pm

Right, I get that. But you keep focusing on the data shifting when that won’t impact the result because template searches for the minimum. If the minimum moves position in the list, it’ll still find that minimum.

petro · October 19, 2022, 11:20pm

Here’s it all fixed

Sensor that chops data at midnight.

sensor:
  - 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 }}

Updates whenever the data changes.

template:
- trigger:
  - platform: state
    entity_id: sensor.awattar
    variables:
      the_state: >
        {% if trigger.to_state and trigger.to_state.state not in ['unknown', 'unavailable'] %}
          {% set s, e = trigger.to_state.state.split(',') | map('int', none) | reject('none') %}
          {{ {'s': s, 'e': e, 'data': trigger.to_state.attributes.data } }}
        {% else %}
          {'s': 0, 'e': 0, 'data': []}
        {% endif %}
      time_span: >
        {% set s, e, data = the_state.s, the_state.e, the_state.data %}
        {% set hours = 4 %}
        {% set ns = namespace(windows=[], out=[]) %}
        {% 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) | timestamp_local, 'total':total} ] %}
          {% endfor %}
          {% set item = sub.window | sort(attribute='total') | first %}
          {% set ns.windows = ns.windows + [ (span+1, item) ] %}
        {% endfor %}
        {% set items = dict.from_keys(ns.windows) %}
        {% for i in range(1, hours+1) %}
          {% set ns.out = ns.out + [ (i, items[i]) if i in items else (i,{'timestamp': None, 'total': None}) ] %}
        {% endfor %}
        {{ dict.from_keys(ns.out) }}
  sensor:
  - name: Awatter One Hour Min
    state: "{{ time_span.1.total * 100/1000 * 1.20 }}"
  - name: Awatter One Hour Timestamp
    state: "{{ time_span.1.timestamp | as_datetime }}"
    device_class: timestamp
  - name: Awatter Two Hour Min
    state: "{{ time_span.2.total * 100/1000 * 1.20 }}"
  - name: Awatter Two Hour Timestamp
    state: "{{ time_span.2.timestamp | as_datetime }}"
    device_class: timestamp
  - name: Awatter Three Hour Min
    state: "{{ time_span.3.total * 100/1000 * 1.20 }}"
  - name: Awatter Three Hour Timestamp
    state: "{{ time_span.3.timestamp | as_datetime }}"
    device_class: timestamp
  - name: Awatter Three Hour Min
    state: "{{ time_span.4.total * 100/1000 * 1.20 }}"
  - name: Awatter Three Hour Timestamp
    state: "{{ time_span.4.timestamp | as_datetime }}"
    device_class: timestamp

Updates once at midnight for the day

template:
- trigger:
  - platform: time
    at: "00:00:01"
    variables: &awatter_variables
      the_state: >
        {% set to_state = expand('sensor.awattar') | first %}
        {% if to_state and to_state.state not in ['unknown', 'unavailable'] %}
          {% set s, e = to_state.state.split(',') | map('int', none) | reject('none') %}
          {{ {'s': s, 'e': e, 'data': to_state.attributes.data } }}
        {% else %}
          {'s': 0, 'e': 0, 'data': []}
        {% endif %}
      time_span: >
        {% set s, e, data = the_state.s, the_state.e, the_state.data %}
        {% set hours = 4 %}
        {% set ns = namespace(windows=[], out=[]) %}
        {% 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) | timestamp_local, 'total':total} ] %}
          {% endfor %}
          {% set item = sub.window | sort(attribute='total') | first %}
          {% set ns.windows = ns.windows + [ (span+1, item) ] %}
        {% endfor %}
        {% set items = dict.from_keys(ns.windows) %}
        {% for i in range(1, hours+1) %}
          {% set ns.out = ns.out + [ (i, items[i]) if i in items else (i,{'timestamp': None, 'total': None}) ] %}
        {% endfor %}
        {{ dict.from_keys(ns.out) }}
  - platform: homeassistant
    event: start
    variables: *awatter_variables
  sensor:
  - name: Awatter One Hour Min
    state: "{{ time_span.1.total * 100/1000 * 1.20 }}"
  - name: Awatter One Hour Timestamp
    state: "{{ time_span.1.timestamp | as_datetime }}"
    device_class: timestamp
  - name: Awatter Two Hour Min
    state: "{{ time_span.2.total * 100/1000 * 1.20 }}"
  - name: Awatter Two Hour Timestamp
    state: "{{ time_span.2.timestamp | as_datetime }}"
    device_class: timestamp
  - name: Awatter Three Hour Min
    state: "{{ time_span.3.total * 100/1000 * 1.20 }}"
  - name: Awatter Three Hour Timestamp
    state: "{{ time_span.3.timestamp | as_datetime }}"
    device_class: timestamp
  - name: Awatter Three Hour Min
    state: "{{ time_span.4.total * 100/1000 * 1.20 }}"
  - name: Awatter Three Hour Timestamp
    state: "{{ time_span.4.timestamp | as_datetime }}"
    device_class: timestamp

Keep in mind, the updates once at midnight will update on restart as well. It will not update on template reload. So you have to wait until midnight to see the state or restart the system.

Riesi · October 20, 2022, 6:26am

Thanks for the help. In the meantime I don’t have any glue anymore on what the algorithm does ;-). Runs now and delivers data.
One slight issue: When updated during the day (restart) the api being called without startdate only returns values from now on. That means it show the cheapest hour for today in 13 hours, but it was 6 hours ago (HOURLY Preis Chart - mehr Wind und Sonne, mehr sparen!).
Is it possible to pass today, 00:00 as epochseconds to the script? Syntax is

https://api.awattar.at/v1/marketdata?start=<startdatetime in epochseconds>

Riesi · October 20, 2022, 9:50am

I think I have it.
URL call needs to be (instead of resource):

resource_template: 'https://api.awattar.at/v1/marketdata\start={{today_at().timestamp() * 1000 }}'