Using the previous and the subsequent row value to conditionally interpolate the current value between the two

Hi all,

As the title explains, in a column of my table, I would like to replace a 0 with an interpolated value but only if the next and previous values are both not equal to 0.

I searched alot on conditional mapping but only found conditions using another column in the same row. I need to use another row in the same column. Is this possible in any way (edge cases can simply be ignored)?

Thanks in advance!

Hello @Sander,
You can do this by performing applying a timeShift() function and joining the results to compare the values before and after/ get values on the same row:

Please note that you would have to:

  1. query for your data, store result in a variable (i.e. original)
  2. shift forward once, store the result in a variable (i.e. forward)
  3. join forward and original, store the result in a variable (i.e. first_join)
  4. repeat with shifting back

You also might want to try pivoting().

from(bucket: "noaa")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees")
  |> filter(fn: (r) => r["location"] == "coyote_creek")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> limit(n: 3)
  |> map(fn: (r) => ({ r with X: 1.0 }))
  |> cumulativeSum(columns: ["X"])
  |> drop(columns: ["_start", "_stop", "_measurement"])
  |> pivot(rowKey:["_field"], columnKey: ["X"], valueColumn: "_value")

This approach could work if you window your data so that each table only returns 3 values. Then you could apply your conditional mapping?