asmith
July 23, 2020, 12:49pm
1
I have a _value field and using map() have created an additional field (_value_2)
|_time|_value|_value_2|
But only _value appears on the chart.
The chart obviously defaults to displaying _value. But when you click on the option you can select to display _value_2. But you can’t select both. Surely users are going to want to do this?
The code I’m using is…
from(bucket: “Power_Meter”)
Hello @asmith ,
opened 06:27PM - 20 Feb 20 UTC
closed 04:24PM - 10 Nov 22 UTC
team/query
Every now and then, I run into a use case where it would be really useful to hav… e the ability to unpivot data. For example, I defined this custom `minMaxMean()` function that uses `reduce()` to output the min, max, and mean values for each table. The issue is that, as-is, I can't 1) visualize the results in the UI or 2) write them back to the db because the output schema doesn't meet the requirements for writing back into InfluxDB.
```js
import "experimental"
minMaxMean = (tables=<-) =>
tables
|> reduce(
identity: {count: 0, sum: 0.0, min: 0.0, max: 0.0, mean:0.0},
fn: (r, accumulator) => ({ r with
count: accumulator.count + 1,
sum: r._value + accumulator.sum,
min: if accumulator.count == 0 then r._value else if r._value < accumulator.min then r._value else accumulator.min,
max: if accumulator.count == 0 then r._value else if r._value > accumulator.max then r._value else accumulator.max,
mean: if accumulator.count == 0 then r._value else (r._value + accumulator.sum) / float(v: accumulator.count + 1)
})
)
|> drop(columns: ["count", "sum"])
```
To accomplish those things, I have to create multiple filtered streams, then union them back together:
```js
data = from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == "mem")
|> filter(fn: (r) => r._field == "used_percent")
|> window(every: v.windowPeriod)
|> minMaxMean()
|> duplicate(column: "_stop", as: "_time")
|> window(every: inf)
min = data |> map(fn: (r) => ({r with _value: r.min, _metricType: "min"})) |> drop(columns: ["max", "mean"])
max = data |> map(fn: (r) => ({r with _value: r.max, _metricType: "max"})) |> drop(columns: ["min", "mean"])
mean = data |> map(fn: (r) => ({r with _value: r.mean, _metricType: "mean"})) |> drop(columns: ["min", "max"])
union(tables: [min, max, mean])
|> experimental.group(columns: ["_metricType"], mode: "extend")
```
This could be simplified with an `unpivot()` function.
```
unpivot(
columns: ["col1", "col2", "col3"],
columnDst: "_metricType",
valueDst: "_value"
)
```
So given the following input data:
| _time | col1 | col2 | col3 |
| ----- | ---- | ---- | ---- |
| 0001 | val1.1 | val2.1 | val3.1 |
| 0002 | val1.2 | val2.2 | val3.2 |
| 0003 | val1.3 | val2.3 | val3.3 |
`unpivot()` would output:
| _time | _metricType | _value |
| ----- | ----------- | ------ |
| 0001 | col1 | val1.1 |
| 0001 | col2 | val2.1 |
| 0001 | col3 | val3.1 |
| 0002 | col1 | val1.2 |
| 0002 | col2 | val2.2 |
| 0002 | col3 | val3.2 |
| 0003 | col1 | val1.3 |
| 0003 | col2 | val2.3 |
| 0003 | col3 | val3.3 |
However, here is a workaround:
data = from(bucket: "Power_Meter")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._field =~ /KWH/)
value_1 = data |> difference(nonNegative: false)
value_2 = data
|> map(fn: (r) => ({ r with
_value: r._value / 1000.0,
_field: "KWH/1k"
}))
|> increase()
union(tables: [value_1, value_2])
asmith
July 24, 2020, 8:39pm
3
Yes I can see what you are doing and I wondered how to do it.
Thanks so much!
Can different cells share the same ‘data’ variable?
That way I can read ‘data’ once, but can have different cells which display different aspects of it.
Also I would like to up-vote the need for an unpivot() function.
Unpivot() is EXACTLY what I searched for and would be a much more elegant way to manipulate the table.
Better still, a chart should be able to display multiple columns!
That would deliver such powerful functionality no?
1 Like
asmith
August 4, 2020, 10:25am
4
Hi @Anaisdg , I’m still very much interested in my follow-on question …
The use case :
Would this work? Or is the data variable on the server not the client?
Does that makes sense?
Cheers
Andrew
1 Like
