Updating my IHD to graph device category energy usage and cost

A little while ago, I posted about creating an In Home Display to track our energy usage. It allows us to see an overview of our current and historic usage (much like the IHD supplied with a Smart Meter would, but without all the negatives of having a remotely addressable meter).

I've recently acquired some TP-Link Tapo P110s which I'm using to track energy usage on appliances such as our dishwasher and washing machine.

In my previous post about creating the IHD, I mentioned that in future, I wanted to add an additional page/view to show usage per device.

I'm monitoring consumption on a range of devices and at 748x419 the display's resolution is quite space constrained, so allowing selection of individual devices could be challenging (and realistically, I can always look them up in Chronograf anyway).

I decided instead to add a pane to show usage by category of device

Appliance Category Usage

Because the Tapo (and my older Kasa plugs) don't track cost there were some challenges around implementing the cost per day graph without hardcoding costings.

This post discusses how I constructed the interface and (more importantly) the queries.


Category Definition

I could have started to push a category tag with my writes into InfluxDB, but

  • I want a device to be able to be in more than 1 category
  • I may want to move a device back out of a category at some point
  • The graphs would have ignored all existing data (as they wouldn't have the tags)

So, I decided that I'd define categories at the IHD level.

I did this by creating a simple JSON file (categories.json)

{
 "appliances" : ["dishwasher", "big-fridge", "tumble-dryer", "washing-machine"],
 "food-storage" : ["big-fridge"],
 "laundry" : ["washing-machine","tumble-dryer"],
 "workspace" : ["desk-plug"]
}

And then added a call to retrieve this JSON, parse it, and put the resulting object into a variable.

jQuery.getJSON("categories.json", function(data){
    window.categories = data;
});

Creating the pane and buttons

I added a new pane div

<div id="category_graphs">
    <div id="cat-buttons"></div>
    <div class="graphtitle"><b><span id="cattle1"></span> Consumption (W)</b></div>
    <div id="cat-usage"></div>

    <div class="graphtitle"><b><span id="cattle2"></span> &pound per day</b></div>
    <div id="cat-cost"></div>
    <div id="clickor3" class="clicker" onclick="toggleDisplay()">
     &gt;
    </div>

</div>

and adjusted toggleDisplay() to account for there being a third pane.

Adding the buttons was then as simple as iterating through window.categories and inserting divs with an attached event

function writeCategoryButtons(){
    var k,b;
    var ele = document.getElementById('cat-buttons');
    ele.innerHTML = '';

    // Create buttons with category names in them
    for (k of Object.keys(window.categories)){
        b = document.createElement('div');
        b.className = "cat-btn";
        b.setAttribute("data-my-cat", k);
        b.innerText = k;
        b.addEventListener('click', categoryButtonClick);
        ele.appendChild(b);
    }

    // Rather than having an empty screen, auto-click the first button
    ele.firstChild.click();
}

Constructing the query filter

When a category button is clicked, the function categoryButtonClick() is fired.

This function needs to construct Flux queries to retrieve usage information for multiple devices and combine them. What that means, in practice, is that we need to dynamically construct a statement to pass into Flux's filter() function.

This is achieved with a couple of lines of javascript

// Implode the entries
var f = window.categories[cat].join('" or r.host == "');

// f will be missing the leading statement and a trailing "
// put those back in
f = 'r.host == "' + f + '"';

So, for example, if the value of cat is laundry, we will construct the statement

r.host == "washing-machine" or r.host == "tumble-dryer"

Constructing the Usage query

Collecting raw usage over the last 24 hours is fairly straight forward, it's almost identical to the query used to show overall usage. We just need to include our filter and group the per-device tables together (so that we can sum them).

q = `from(bucket: "Systemstats")
|> range(start: -12h)
|> filter(fn: (r) => r._measurement == "power_watts" and r._field == "consumption")
|> filter(fn: (r) => ` + f + `)
|> group()
|> sort(columns: ["_time"], desc: false)
|> aggregateWindow(every: 5m, fn: mean)
`;

This is then passed into the sendFluxQuery() function defined in my previous post in order to generate a graph show usage for that category over the last 24 hours.

Laundry Consumption

That's the easy bit done.


Displaying Cost Per Day

The second graph should show the category's energy cost per day over the last 30 days.

But, there's an issue with implementing that: the smart plugs do not track cost, so there's no running total like there is for the electicity meter. Whilst the collection script could be amended to write a unit cost in, this'd mean hardcoding the value into them.

It'd also be duplicated data - the clamp meter that tracks energy at our main meter does track cost information (both the per unit cost and the running total).

Rather than hardcoding a unit cost, or duplicating data, it makes more sense to pull the unit cost from the meter's points and then use that to calculate the cost of the category. Cost may change over time though, so rather than selecting a single value, we need to select unit_cost over time and then join() that into the energy consumption result set.

In Flux, the query looks like this

// Get daily average cost from the power meter
unit_costs = from(bucket: "Systemstats/rp_720d")
    |> range(start: -30d, stop: -1d)
    |> filter(fn: (r) => r._measurement == "power_watts" and r._field == "unit_cost")
    |> filter(fn: (r) => r.host == "power-meter")
    |> window(every: 1d)
    |> mean()

// Get daily usage for the devices we want to show cost for
usage = from(bucket: "Systemstats/rp_720d")
    |> range(start: -30d, stop: -1d)
    |> filter(fn: (r) => r._measurement == "power_watts" and r._field == "watts_today")
    |> filter(fn: (r) => r.host == "big-fridge" or r.host == "washing-machine")
    // The counter increases for each poll, so take the max reading
    |> aggregateWindow( every: 1d, fn: max )    
    // Group the devices into a single table
    |> group()
    // Calculate total daily usage
    |> window(every: 1d)
    |> sum()

// Join the result sets
join( tables: {t1: usage, t2: unit_costs}, on: ["_stop"])
   // At this point we have rows with
   //
   // _field, _measurement
   // _start_t1, _start_t2, _stop,
   // _value_t1, _value_t2, host
   //
   // _value_t1 contains watts used
   // _value_t2 contains the unit cost
   //
   // Reconstruct into only the columns we want
   |> map(fn: (r) => ({ 
      _time: r._stop,
      _measurement: r._measurement,
      _field: "cost_today",

      // Convert the W reading to kWH
      // then multiply by unit cost
      _value: (r._value_t1 / 1000.00) * r._value_t2   
   }))

The javascript encapsulated version, of course, concatenates in the device filter. It also inserts a timestamp for the previous midnight so that the final column in the graph is not a partial read.

This gives a graph showing £/day over the last 30 days

Laundry Cost


Well, actually....

Actually, that's only part of the story.

When I first encapsulated the costing query in Javascript, I got a broken graph - dates were present on the X axis, but there were no values.

Laundry Cost

After some confusion, I eventually found a bug in the annotated CSV parsing that I implemented in my last post.

The original parsing looked like this

    lines=text.split("\n");

    data = []
    counter = 0;
    for (var i=0; i<lines.length; i++){
        l = lines[i];
        cols = l.split(",");

        if (l.slice(0,1) == "#"){
            continue;
        }

        if (counter == 0){
            /* First CSV line gives us indices */
            val_idx = cols.indexOf("_value");
            time_idx = cols.indexOf("_time");
            counter++;
            continue;
        }

        time = new Date(cols[time_idx]);
        val = cols[val_idx]*multiplier;

        data.push([time, val]);
    }

It's fairly simple and (I thought) fairly idiot proof.

For some reason, though, the variable val_idx was getting the value -1, meaning _value wasn't being found in the CSV columns.

Imagine my confusion:

#datatype,string,long,string,string,dateTime:RFC3339,double
#group,false,false,true,true,false,false
#default,_result,,,,,
,result,table,_field,_measurement,_time,_value
,,0,cost_today,power_watts,2022-01-20T14:04:37.17864359Z,0.003857000000000011
,,0,cost_today,power_watts,2022-01-22T00:00:00Z,0.0027300000000000076
,,0,cost_today,power_watts,2022-01-23T00:00:00Z,0.0029400000000000086
,,0,cost_today,power_watts,2022-01-24T00:00:00Z,1.115352000000003
,,0,cost_today,power_watts,2022-01-25T00:00:00Z,0.5445300000000015

It's definitely there, and this code works for every other query and every other graph. The difference between this one query and the others is that final map() - I was sure it was related, but couldn't quite put my finger on how.

The answer came when I dumped the value of cols to console

[ "", "result", "table", "_field", "_measurement", "_time", "_value\r" ]

The search for _value was failing because there was a trailing CR. The reason other query responses weren't affected is because _value was one of the middle columns, not the last:

#datatype,string,long,string,string,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,double,string
#group,false,false,true,true,true,true,false,false,true
#default,_result,,,,,,,,
,result,table,_field,_measurement,_start,_stop,_time,_value,host
,,0,today_cost,power_watts,2022-01-20T14:54:49.525241341Z,2022-02-19T00:00:00Z,2022-01-20T14:54:49.525241341Z,0.25366466666666665,power-meter
,,0,today_cost,power_watts,2022-01-20T14:54:49.525241341Z,2022-02-19T00:00:00Z,2022-01-21T00:00:00Z,4.927710666666668,power-meter
,,0,today_cost,power_watts,2022-01-20T14:54:49.525241341Z,2022-02-19T00:00:00Z,2022-01-22T00:00:00Z,4.2097040268456345,power-meter

The fault then, was at the top of the parsing section - I needed to strip Carriage Returns

lines=text.replace(/\r/g,'').split("\n");

Wouldn't have happened if I'd used the official JS client rather than hacking around the edges.


Full Function

When put together, the function categoryButtonClick() looks like this

function categoryButtonClick(e){
    // Get the category from the event
    var cat = e.target.getAttribute("data-my-cat");

    // Implode the entries
    var f = window.categories[cat].join('" or r.host == "');

    // f will be missing the leading statement and a trailing "
    // put those back in
    f = 'r.host == "' + f + '"';

    // Build the query
    q = `from(bucket: "Systemstats")
    |> range(start: -12h)
    |> filter(fn: (r) => r._measurement == "power_watts" and r._field == "consumption")
    |> filter(fn: (r) => ` + f + `)
    |> group()
    |> sort(columns: ["_time"], desc: false)
    |> aggregateWindow(every: 5m, fn: mean)
    `;

    // Populate the graph
    sendFluxQuery(q, updateGraphing, "cat-usage", "Consumption (W)", 1, "line", [], "", 0);

    // Cost is more complex - it's not logged alongside the smart plugs, so we need to pull unit cost from the smart meter and then join it with our result set.

    var d = new Date();
    d.setHours(0,0,0,0);
    midnight = d.toISOString();

    q = `unit_costs = from(bucket: "Systemstats/rp_720d")
            |> range(start: -30d, stop: ` + midnight + `)
            |> filter(fn: (r) => r._measurement == "power_watts" and r._field == "unit_cost")
            |> filter(fn: (r) => r.host == "power-meter")
            |> window(every: 1d)
            |> mean()

        usage = from(bucket: "Systemstats/rp_720d")
            |> range(start: -30d, stop: ` + midnight + `)
            |> filter(fn: (r) => r._measurement == "power_watts" and r._field == "watts_today")
            |> filter(fn: (r) => ` + f + `)
            |> aggregateWindow( every: 1d, fn: max )
            |> group()
            |> window(every: 1d)
            |> sum()

        join( tables: {t1: usage, t2: unit_costs}, on: ["_stop"])
        |> map(fn: (r) => ({ 
            _time: r._stop,
            _measurement: r._measurement,
            _field: "cost_today",

            // Convert the W reading to kWH
            // then multiply by unit cost
            _value: (r._value_t1 / 1000.00) * r._value_t2   
        }))
    `
    sendFluxQuery(q, updateGraphing, "cat-cost", "Daily Cost", 1, "bar", [], "", 2);     

    // Update the graph titles
    document.getElementById('cattle1').innerHTML = cat;
    document.getElementById('cattle2').innerHTML = cat;
}

Conclusion

Implementing this kept me out of trouble for most of the morning, and means that I can see an overview of how different groups of things contribute towards our overall energy costs.

I'm fairly pleased with the success of my join to calculate total costs - I had previously tried to implement something very similar with InfluxQL, but only really succeeded in giving myself a headache.

Ultimately, I'd like almost every fixed device in the house to be individually metered so that I can better understand where our energy usage comes from.