# Try to calculate kWh/°C of my Heating Pump

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 :

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

Thanks,

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

Great, it works like a charm !

• 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
``````