Okay this worked:
import "math"
import outer "join"
import "internal/debug"
data = from(bucket: "noaa")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "h2o_feet")
|> filter(fn: (r) => r["_field"] == "water_level")
|> filter(fn: (r) => r["location"] == "coyote_creek")
|> keep(columns: ["_time", "_value"])
|> limit(n: 400)
// |> yield(name: "raw")
derr = data
// unit for the time in between timestamps
|> derivative(unit: 6m)
|> keep(columns: ["_time", "_value"])
// |> yield(name: "derr")
derr_shift = derr
|> timeShift(duration: 6m)
|> keep(columns: ["_time", "_value"])
// |> yield(name: "derr_shift")
inflection_time = join(tables: {derr: derr |> debug.pass(), derr_shift: derr_shift|> debug.pass()} , on: ["_time"])
// change to just one side of the or expression if you want to find a true period in a sin wave
|> filter(fn: (r) => r._value_derr >= 0.0 and r._value_derr_shift <= 0.0 or r._value_derr <= 0.0 and r._value_derr_shift >= 0.0 )
// to account for when the derivative does == 0 and define inflection point from either side of crossing from pos to neg or neg to pos
// also helps us make sure that local max and min are looking right/adequate time between them
|> elapsed(unit: 6m)
|> filter(fn: (r) => r.elapsed > 1)
// |> yield(name: "inflection points")
|> map(fn: (r) => ({r with _value: 1.0}))
|> cumulativeSum()
|> keep(columns: ["_time", "_value"])
outer.time(left: data |> debug.pass(), right: inflection_time |> debug.pass(), as: (l, r) => ({l with label: r._value}), method: "full" )
|> fill(column: "label", usePrevious: true)
|> group(columns: ["label"], mode:"by")
// |> yield(name: "_result")
Maybe you can start by working off of that?
I’m using this dataset
In case you want to try the example for yourself
Basically what i’m doing is:
- calculating the derivative (in “derr”)
- shifting the results by one timestamp (“derr_shift”) and joining them
- filtering for when an inflection point happens (defined as by when the point before is <= 0 and point after >= 0 or the inverse)
- using the elapse function to account for when the derrivative actually equals 0 (… hmm actually just realized we don’t need to use elapsed here if we change the expression to not = 0 perhaps…I’ll have to play around with it)
- adding an index to those timestamps where there are inflection points
- then performing an outer join and filling previous so we can determine which values in our original data are part of which “period”
- grouping by the label (or index) to verify that our result is as expected
Data before:
Data after:
