How to handle missing data with a scatter plot?

[caiusseverus]

I have a scatter plot which should show the amount of heating time in each day on the x axis, and the corresponding amount of gas used on the y axis. I have something adapted from some other examples here, but it produces some spurious results. Because the gas data has only been available for a relatively short period of time in home assistant and the heating has been off over summer, what I was expecting to see was a line of dots down the x=0 line, but with some varying amount of gas used which corresponds to hot water and cooking.

That is mostly what it looks like, but the problem is that there are points plotted indicating days with heating time but with gas quantities which are incorrect, and that means that all of the data is likely incorrect. I think this is because the length of the arrays for each entity are different as they are not paired up with each other, so when there is a day or more with data missing, the alignment is lost.

YAML:

type: custom:plotly-graph
title: Heating Time vs Gas usage
raw_plotly_config: true
hours_to_show: 120d
entities:
  - entity: sensor.heating_today
    internal: true
    statistic: max
    period: day
    fn: $fn ({ xs, ys, vars }) => { vars.heating = ys; vars.xs = xs; }
  - entity: sensor.gas_meter_day
    internal: true
    statistic: state
    period: day
    fn: $fn ({ ys, vars }) => vars.gas = ys
  - entity: sensor.outside_temperature
    internal: true
    statistic: mean
    period: day
    fn: $fn ({ ys, vars }) => vars.color = ys
  - entity: ''
    x: $fn ({ vars }) => vars.heating
    'y': $fn ({ vars }) => vars.gas
    mode: markers
    name: points
    filters:
      - fn: console.log
    marker:
      color: $fn ({ vars }) => vars.color
      colorscale: Viridis
      colorbar:
        title: Av Outside Temperature
      size: 8
      type: scatter
layout:
  margin:
    t: 10
    l: 70
    r: 20
    b: 70
  height: 750
  showlegend: false
  xaxis:
    title:
      text: Heating Time
    dtick: 2
    zeroline: false
    range:
      - 0
      - 15
  yaxis:
    title:
      text: Gas Usage
    dtick: 1
    zeroline: false

How do I go about forcing the data points to be matched with the same dates so that only those with all the data present are plotted?

Edit - answer marked, but note minor correction to include x data:

      $fn ({ xs, ys, vars }) => {
        let i = -1;
        vars.gas = vars.xs.map((axis_x)=>{
            while (xs[i+1] <= axis_x && i<xs.length) i++;
            return ys[i];
        })
      }

[dbuezas]

Hey there 👋
Read about the resample filter, it generates intermediate datapoints every e.g 1 minute (or whatever you configure it to). If you resample both entities, their corresponding dates will match by index and your issue with missing data in one entity should be fixed

[caiusseverus]

Ok, I will give it a go thanks.

[caiusseverus]

I think that solution would work, assuming that all three entities are available for the whole period of the graph. eg if the graph covers a year and there are days missing on one or more of the entities here and there, then resample would fill in those days and things would match up.

The issue here is that one of the entities has only existed for a limited period of time, whereas the others have existed for longer. Setting the graph period so that it only covers the period where all three have existed does as far as I can tell produce sensible output. If I set it to cover a longer period, say a year, it plotly assumes that the entities all begin at the same time and so do not match properly. Checking the array values in the console confirms that.

I could manually adjust the graph period so that it only covers the period where everything is already there, but I'd rather not have to keep going to back to change it all the time. I think a more robust method of matching dates explicitly is needed. I have been playing with chatgpt see if I can generate something useful but it's being stubbornly obtuse about it at the moment. It doesn't help that my knowledge of javascript is pretty limited.

[dbuezas]

I see. Try this instead of the resample.
I should add an "align" filter for these use cases

- entity: sensor.gas_meter_day
    ...
    fn: |-
      $fn ({ ys, vars }) => {
        let i = -1;
        vars.gas = vars.xs.map((axis_x)=>{
            while (xs[i+1] <= axis_x && i<xs.length) i++;
            return ys[i];
        })
      }

[caiusseverus]

Thanks for the suggestion. I couldn't get it to work without throwing an error though.

I did manage to get this suggestion from chatgpt which I think should do the job though. It took quite a lot of attempts to prompt it into explicitly checking the dates against each other:

type: custom:plotly-graph
title: Heating Time vs Gas Usage
raw_plotly_config: true
hours_to_show: 1y
entities:
  - entity: sensor.heating_today
    internal: true
    statistic: max
    period: day
    fn: |
      $fn({ xs, ys, vars }) => {
        vars.heating = ys;
        vars.heating_dates = xs.map(x => new Date(x).toDateString());
      }
  - entity: sensor.gas_meter_day
    internal: true
    statistic: state
    period: day
    fn: |
      $fn({ xs, ys, vars }) => {
        vars.gas = ys;
        vars.gas_dates = xs.map(x => new Date(x).toDateString());
      }
  - entity: sensor.outside_temperature
    internal: true
    statistic: mean
    period: day
    fn: |
      $fn({ xs, ys, vars }) => {
        vars.color = ys;
        vars.temp_dates = xs.map(x => new Date(x).toDateString());
      }
  - entity: ''
    fn: |
      $fn({ vars }) => {
        let filtered_heating = [];
        let filtered_gas = [];
        let filtered_color = [];

        // Match data points by date
        for (let i = 0; i < vars.heating_dates.length; i++) {
          let heating_date = vars.heating_dates[i];
          let gas_index = vars.gas_dates.indexOf(heating_date);
          let color_index = vars.temp_dates.indexOf(heating_date);

          if (gas_index !== -1 && color_index !== -1) {
            filtered_heating.push(vars.heating[i]);
            filtered_gas.push(vars.gas[gas_index]);
            filtered_color.push(vars.color[color_index]);
          }
        }

        vars.filtered_heating = filtered_heating;
        vars.filtered_gas = filtered_gas;
        vars.filtered_color = filtered_color;
      }
  - entity: ''
    x: $fn({ vars }) => vars.filtered_heating
    'y': $fn({ vars }) => vars.filtered_gas
    mode: markers
    name: points
    marker:
      color: $fn({ vars }) => vars.filtered_color
      colorscale: Portland
      colorbar:
        title: Av Outside Temperature
      size: 8
    type: scatter
layout:
  margin:
    t: 10
    l: 70
    r: 20
    b: 70
  height: 750
  showlegend: false
  xaxis:
    title:
      text: Heating Time
    dtick: 2
    zeroline: false
    range:
      - 0
      - 15
  yaxis:
    title:
      text: Gas Usage
    dtick: 1
    zeroline: false

which produces this graph, which is mostly uninteresting at the moment, but does look how it's supposed to:

Once the heating gets turned on again it will be more relevant.

As an example of what happens with the resample filter using a slightly different graph where the main data is available from the start of the period the graph covers, but with some missing:

There was a period where the outside temperature data was missing due to a failed sensor, and the resample filter duplicates the last available value, which gives an obvious anomaly. That's not too useful for this type of graph.

Here's a version using a solution similar to the one above that only includes data points where both x and y data are present and matched with each other:

Code for that one:

type: custom:plotly-graph
title: Heating Time vs Average Outside Temperature
raw_plotly_config: true
hours_to_show: 8760
resample: 1day
entities:
  - entity: sensor.heating_today
    internal: true
    statistic: max
    period: day
    fn: |
      $fn({ xs, ys, vars }) => {
        vars.heating = ys;
        vars.heating_dates = xs.map(x => new Date(x).toDateString());
      }
  - entity: sensor.outside_temperature
    internal: true
    statistic: mean
    period: day
    fn: |
      $fn({ xs, ys, vars }) => {
        vars.outside = ys;
        vars.outside_dates = xs.map(x => new Date(x).toDateString());
      }
  - entity: sensor.gas_meter_day
    internal: true
    statistic: state
    period: day
    fn: |
      $fn({ xs, ys, vars }) => {
        vars.gas = ys;
        vars.gas_dates = xs.map(x => new Date(x).toDateString());
      }
  - entity: ''
    fn: |
      $fn({ vars }) => {
        let filtered_heating = [];
        let filtered_outside = [];
        let filtered_color = [];

        // Match data points by date, allowing gas data to be missing
        for (let i = 0; i < vars.heating_dates.length; i++) {
          let heating_date = vars.heating_dates[i];
          let outside_index = vars.outside_dates.indexOf(heating_date);
          let gas_index = vars.gas_dates.indexOf(heating_date);

          if (outside_index !== -1) {
            filtered_heating.push(vars.heating[i]);
            filtered_outside.push(vars.outside[outside_index]);
            filtered_color.push(gas_index !== -1 ? vars.gas[gas_index] : 0); // Assume 0 if gas data is missing
          }
        }

        vars.filtered_heating = filtered_heating;
        vars.filtered_outside = filtered_outside;
        vars.filtered_color = filtered_color;
      }
  - entity: ''
    x: $fn({ vars }) => vars.filtered_outside
    'y': $fn({ vars }) => vars.filtered_heating
    mode: markers
    name: points
    marker:
      color: $fn({ vars }) => vars.filtered_color
      colorscale: Electric
      colorbar:
        title: Gas usage
      size: 8
    type: scatter
layout:
  margin:
    t: 10
    l: 70
    r: 20
    b: 70
  height: 750
  showlegend: false
  xaxis:
    title:
      text: Outside Temperature
    dtick: 2
    zeroline: false
    range:
      - -5
      - 35
  yaxis:
    title:
      text: Heating Time
    dtick: 1
    zeroline: false

[dbuezas]

What error did you get?

[caiusseverus]

The error I was getting is this:

Error: at [entities.1.fn]: xs is not defined

but I think just adding xs to the $fn fixes it.

      $fn ({ xs, ys, vars }) => {
        let i = -1;
        vars.gas = vars.xs.map((axis_x)=>{
            while (xs[i+1] <= axis_x && i<xs.length) i++;
            return ys[i];
        })
      }

This is a neater solution than the one above I think. I will run them side by side to see if any differences emerge. I can't see any at the moment, but will check again once it gets colder and the heating is turned on again.