3D graph for sensor per day

[bjkluft]

Hi,
First of all this add-on looks very promising! The amount of options is huge, giving us a lot to play with. However I do have troubles starting with a graph I would love to create. The idea is as the image below. I have the amount of energy generated with my solar panels and i would like to be able to plot them per day over time. So the X-axis should be the days, the Y-axis the years and the Z-axis should be the amount of energy. The data is stored in a MariaDB (SQL) with one value per hour, so two columns: date (YYMMDD hh:mm:ss) and the recorded amount of energy.
Is it possible to create such a graph?

With many thanks,
Jeroen

[dbuezas]

Yes it should be possible.
See this: #298
And this: https://plotly.com/javascript/3d-surface-plots/

Good luck!

[bjkluft]

First of all, thank you for the reply.
My js knowledge is close to nothing and I'm not able to create the correct input. I know this is a large stretch, but maybe somebody can help me out. What I have is a data set of values and dates (every 5minutes a value is stored), what I want to achieve is an 3D overview of the max value per hour per day over the year. So the X-axis would be 24 hours, the Y-axis would be 365 days and the Z-axis the actual value (amount of energy from my solar panels).
I would be very grateful to whoever can get me started.

[dbuezas]

Maybe this gets you started

type: custom:plotly-graph
entities:
  - entity: sensor.electricity_usage
    period: hour
    statistic: max
    fn: |
      $fn ({ xs, ys, vars }) => {
        const hours = Array.from({ length: 24 }, (_, i) => `${i}:00`);
        const days = Array.from({ length: 365 }, (_, i) => i + 1);
        const maxValues = Array.from({ length: 366 }, () => Array(24).fill(null));
        
        for (let i = 0; i < xs.length; i++) {
          const date = new Date(xs[i]);
          const hour = date.getHours();
          const dayOfYear = Math.floor((date - new Date(date.getFullYear(), 0, 0)) / 86400000)-1;
          const currentValue = +ys[i];
          
          if (maxValues[dayOfYear][hour] === null || currentValue > maxValues[dayOfYear][hour]) {
            maxValues[dayOfYear][hour] = currentValue;
          }
        }
        vars.x = hours; 
        vars.y = days;
        vars.z = maxValues;
      }
    x: $fn ({ vars }) => vars.x
    'y': $fn ({ vars }) => vars.y
    z: $fn ({ vars }) => vars.z
    type: surface
raw_plotly_config: true
hours_to_show: 1y
title: Solar Energy Output Over the Year
layout:
  height: 600
  margin:
    t: 50
    l: 50
    r: 50
    b: 50
  scene:
    xaxis:
      title: Hour of Day
    yaxis:
      title: Day of Year
    zaxis:
      title: Max Energy Output (kWh)

let me suggest you to do some iterations with chatGPT, it should be able to explain you what all the gibberish means :)

[dbuezas]

Oh, actually, the 5minute statistic is not stored forever. I think the minimum is hour.

[dbuezas]

Interesting idea by the way. I just put it to use with the mean heating energy per hour and It shows very nicely how in summer it only needs to heat water at 2 specific times of the day.

Kapture.2024-03-21.at.20.24.46.mp4

[bjkluft]

Thank you for the reply and code.
I did some digging and understand the code and what happens where.
However I don't get any output. In the attachment I have added the data of last month (as downloaded from the HA history). Do I have to " tell" the sensor somewhere to use the data of last year?

history.csv

[dbuezas]

Use the standard native card to make sure the data you need is in the HA database. The history and long term statistics are two different dbs

[bjkluft]

Oke, that makes sort of sense...
When I use the Plotly card for a default 2D graph it shows the data for only one week. How do I get the full (historical) data set? I have a mariaDB that stores all data.

[dbuezas]

See my previous answer:)

[bjkluft]

Thank you for the help!
It took a restart to be able to show the data, currently it is about one month back. But this is exactly what I wanted.

[dbuezas]

Very cool, remember that the 5minute period only stores a limited amount of data.

To remove the bar on the right, see https://plotly.com/javascript/reference/surface/#surface-showscale

[bjkluft]

How can I set my default view, regarding panning, tilting etc.?

[dbuezas]

Google "how to xyz in ploylyjs"

[dbuezas]

Kept open so it's easier to find