How to make arrays?

[gorkem]

Hello everyone,

I’ve been struggling for the last 2 days with this and I really need your help. I’m trying to convert a table into an array. In the end I wanna be able to compare values of different array indexes.

This query gives me some results but I’m not quite there yet…

getValue = (fld, symb) => {
r = from(bucket: “test-bucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “usdMarket”)
|> yield(name: “testData”)
|> findColumn(fn: (key) => key._field == fld and key.symbol == symb, column: “_value”)
return r
}

data = getValue(fld:“close”,symb: “BTC”)

so basically return values should be like this:

data > [55195.46, 54978.12, 55097.54, … , 54420.89]

data[0] > 55195.46
data[1] > 54978.12
data[3] > 55097.54
…
…
…
data[n] > value of index number n

so in the end I should be able to compare data[n] with data[m] like “if data[4] <= data[20] then do something”

[rickspencer3]

I think you might be looking for array.from(): array.from() function | Flux 0.x Documentation

This will turn a table into an array of records.

[gorkem]

I think it is the other way around. It constructs a table from an array.

[rickspencer3]

Yes of course. This is why I shouldn’t answer questions on a weekend

[rickspencer3]

Can you describe in a little more detail what you are trying to accomplish? I am wondering if you really are looking for reduce()?

[gorkem]

Basically what I’m looking for ‘difference’ function but instead of calculating ‘subsequent’ columns, I want to calculate difference between rowN._value and rowM._value where N and M is the row number. Something like below, but below code wont work since map() function syntax is different.

data = from(bucket: "test-bucket")
  |> range(start: timeRangeStart, stop: timeRangeStop)
  |> filter(fn: (r) => r._measurement == "usdMarket" and r._field == "close" and r.symbol == "ETH")

arr_data = data
  |> findColumn(fn: (key) => true, column: "_value")

data
  |> limit(n:1, offset: 0)
  |> map(fn: (r) => ({
      m = n + 2
      r with differenceValue: arr_data[n] - arr_data[m]
      n = n + 1
    }),
    identity: {n: 0})

[rickspencer3]

I think this does what you want using reduce() and map():

import "array"

 my_reducer = (tables=<-, row1, row2) => tables |>
    reduce(fn: (r, accumulator) => ({   val1: if accumulator.row_index == row1 then r._value else accumulator.val1,
                                        val2: if accumulator.row_index == row2 then r._value else accumulator.val2,
                                        row_index: accumulator.row_index + 1,
                                       }), 
                                      identity: {row_index: 0, val1:0.0, val2:0.0})

 data = array.from(rows: [{_time: 2020-01-01T00:00:00Z, _value: 0.0},
                   {_time: 2020-01-02T00:00:00Z, _value: 1.0},
                   {_time: 2020-01-03T00:00:00Z, _value: 2.0},
                   {_time: 2020-01-04T00:00:00Z, _value: 3.0}])

data 
  |> my_reducer(row1: 0, row2: 3)
  |> map(fn: (r) => ({r with my_diff: r.val2 - r.val1}))
  |> yield(name: "0 and 3")


data 
  |> my_reducer(row1: 1, row2: 2)
  |> map(fn: (r) => ({r with my_diff: r.val2 - r.val1}))
  |> yield(name: "1 and 2")

I think you can modify this to use your own data to get the results you are looking for.

reduce() is covered in depth here: https://influxdata.github.io/book.github.io/docs/part-2/querying-and-data-transformations/#reducing

[scott]

@gorkem

data = from(bucket: "test-bucket")
  |> range(start: timeRangeStart, stop: timeRangeStop)
  |> filter(fn: (r) => r._measurement == "usdMarket" and r._field == "close" and r.symbol == "ETH")

arr_data = data
  |> findColumn(fn: (key) => true, column: "_value")

data
  |> limit(n:1, offset: 0)
  |> map(fn: (r) => ({
      m = n + 2
      r with differenceValue: arr_data[n] - arr_data[m]
      n = n + 1
    }),
    identity: {n: 0})

@gorkem You were really close with this query. In this specific case, I’d suggest creating a custom function that lets you specify the row numbers to use in the operation and then map over input tables (just placeholder data is necessary here) using those custom parameters. Something like this:

import "array"

data = from(bucket: "test-bucket")
    |> range(start: timeRangeStart, stop: timeRangeStop)
    |> filter(fn: (r) => r._measurement == "usdMarket" and r._field == "close" and r.symbol == "ETH")

arr_data = data
    |> findColumn(fn: (key) => true, column: "_value")

rowDiff = (n, m) => array.from(rows: [{placeholder: 0}])
    |>  map(fn: (r) => ({ differenceValue: arr_data[n] - arr_data[m] }))

rowDiff(n:12, m:18)

[gorkem]

Alright we are getting closer I feel it

we need to have dynamic variables in rowDiff function. so it should be similar to this;

rowDiff = (x, n) => array.from(rows: [{placeholder: 0}])
    |>  map(fn: (r) => ({ 
            differenceValue: arr_data[n] - arr_data[n+x]
            n: n + 1
        }))

rowDiff(x:2, n:0) 

// where x is the number of steps between rows that we want to calculate the difference.
// for example built-in function difference() in influx calculate subsequent rows, in other words x is 1 in this function.
// n is the starting index. this should be inceremented by 1 in each iteration. But I dont know if this is a thing.
// I know in reduce function, it is possible to increment accumulator.count variable but I'm not sure if something like this is possible in map() function

example calculation in good old excel:

[scott]

Gotcha. So Flux doesn’t provide row indexes, but you can manually hack/add them. In order to successfully return value differences for rows who’s calculation would reference non-existent indexes, you have to convert the values to floats to get access to the NaN float value.

I’ve tested this against the data in your screenshot and it works:

data = from(bucket: "test-bucket")
    |> range(start: timeRangeStart, stop: timeRangeStop)
    |> filter(fn: (r) => r._measurement == "usdMarket" and r._field == "close" and r.symbol == "ETH")

rowDiff = (x, tables=<-) => {
    _arr_data = tables |> findColumn(fn: (key) => true, column: "_value")
    _max_index = length(arr: _arr_data) - 1
    _indexed_rows = tables
        |> map(fn: (r) => ({ r with index: 1 }))
        |> cumulativeSum(columns: ["index"])
        |> map(fn: (r) => ({ r with index: r.index - 1 }))
    _output = _indexed_rows
        |> map(fn: (r) => ({ r with differenceValue: 
            if (r.index + x) > _max_index or (r.index + x) < 0 then
                float(v: "NaN")            
            else
                float(v: _arr_data[r.index]) - float(v: _arr_data[r.index + x])
        }))
        
    return _output
    }

data
    |> rowDiff(x: 2)