Try to calculate kWh/°C of my Heating Pump

Fluxlang
1

Dear everybody,

I’m looking to do something “easy on the paper” but unortunately I don’t suceed after many trials…
I will try to describe it easy.

I have 3 data stored in my database :

  • salon (°C) => Average temperature of my house
  • jardin (°C)=> Average temperature of my garden
  • pompe_a_chaleur (kWh) => Energy consummed by my heating pump by day

I want to calculate the efficiency of my heating system in kWh/°C by doing this calculation :

  • HeatingPumpEfficiency = pompe_a_chaleur / abs( salon - jardin)

Below my current query which works :

import "date"
import "experimental"

DAY_STOP = date.truncate(t: now(), unit: 1d)
DAY_START = experimental.addDuration(d: -7d, to: DAY_STOP)

TABLE1 = from(bucket: "home1_raw_data")
 |> range(start: DAY_START , stop: DAY_STOP)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "energie")
  |> filter(fn: (r) => r["instance"] == "test")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
  |> keep(columns: ["_time", "entity_id", "_value"])

TABLE2 = from(bucket: "home1_raw_data")
 |> range(start: DAY_START , stop: DAY_STOP)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "temperature")
  |> filter(fn: (r) => r["entity_id"] == "jardin")
  |> filter(fn: (r) => r["instance"] == "prod")
  |> filter(fn: (r) => r["source"] == "capteur")
  |> drop(columns: ["domain", "instance", "source", "_start","_stop", "_field", "_measurement"])
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> keep(columns: ["_time", "entity_id", "_value"])


TABLE3 = from(bucket: "home1_raw_data")
 |> range(start: DAY_START , stop: DAY_STOP)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "temperature")
  |> filter(fn: (r) => r["entity_id"] == "salon")
  |> filter(fn: (r) => r["instance"] == "prod")
  |> filter(fn: (r) => r["source"] == "capteur")
  |> drop(columns: ["domain", "instance", "source", "_start","_stop", "_field", "_measurement"])
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> keep(columns: ["_time", "entity_id", "_value"])



UnionTable = union(tables: [TABLE1, TABLE2, TABLE3])
  |> sort(columns: ["_time"])
UnionTable

Result of my query :

2

Hi,

Try adding pivot() followed by map() as illustrated below:

...
...
UnionTable = union(tables: [TABLE1, TABLE2, TABLE3])
  |> sort(columns: ["_time"])
  |> pivot(rowKey:["_time"], columnKey: ["entity_id"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r.pompe_a_chaleur/math.abs(x: (r.salon-r.jardin))}))

Please remember to also import the math package in the beginning - import "math".

Let us know if this works.

Regards
Balaji

1 Like
3

Thanks,

I will try it asap for me and keep you informed !

4

Great, it works like a charm !

Comments :

  • I have changed the unit to Wh/°C

My final query :

import "date"
import "experimental"
import "math"

DAY_STOP = date.truncate(t: now(), unit: 1d)
DAY_START = experimental.addDuration(d: -14d, to: DAY_STOP)

TABLE1 = from(bucket: "home1_raw_data")
 |> range(start: DAY_START , stop: DAY_STOP)
  |> filter(fn: (r) => r["_measurement"] == "kWh")
  |> filter(fn: (r) => r["entity_id"] == "pompe_a_chaleur")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "energie")
  |> filter(fn: (r) => r["instance"] == "test")
  |> aggregateWindow(every: 1d, fn: spread, createEmpty: false)
  |> keep(columns: ["_time", "entity_id", "_value"])

TABLE2 = from(bucket: "home1_raw_data")
 |> range(start: DAY_START , stop: DAY_STOP)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "temperature")
  |> filter(fn: (r) => r["entity_id"] == "jardin")
  |> filter(fn: (r) => r["instance"] == "prod")
  |> filter(fn: (r) => r["source"] == "capteur")
  |> drop(columns: ["domain", "instance", "source", "_start","_stop", "_field", "_measurement"])
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> keep(columns: ["_time", "entity_id", "_value"])


TABLE3 = from(bucket: "home1_raw_data")
 |> range(start: DAY_START , stop: DAY_STOP)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["domain"] == "temperature")
  |> filter(fn: (r) => r["entity_id"] == "salon")
  |> filter(fn: (r) => r["instance"] == "prod")
  |> filter(fn: (r) => r["source"] == "capteur")
  |> drop(columns: ["domain", "instance", "source", "_start","_stop", "_field", "_measurement"])
  |> aggregateWindow(every: 1d, fn: mean, createEmpty: false)
  |> keep(columns: ["_time", "entity_id", "_value"])



UnionTable = union(tables: [TABLE1, TABLE2, TABLE3])
  |> sort(columns: ["_time"])
  |> pivot(rowKey:["_time"], columnKey: ["entity_id"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with delta_temperature: math.abs(x: (r.salon-r.jardin))}))
  |> map(fn: (r) => ({ r with efficacite_chauffage: (r.pompe_a_chaleur/math.abs(x: (r.salon-r.jardin)))*1000.0}))
  |> keep(columns: ["_time", "efficacite_chauffage", "delta_temperature"])

UnionTable

The result :