Using op.lag on same column

[MagnusHBrunbjerg]

Maybe I am misunderstanding the usecase for the op.lag window function, or window functions in general, but I would like to perform a computation on my table that referenced the current row in a different column and the previous row in the same column.

Essentially I would want to perform a calculation similar to the one I have tried to visualize in the table below. Note that this example is simplified to show the concept of what I am trying to achieve but the actual calculations involve multiple row look-back and not only addition.

index	a	b
0	...	...
1	...	() => a[1] + b[0]

I assumed I could do that with the lag window function like this:

table.derive({ b: d => d.a + op.lag(d.b) })

But it seems like window functions aren't able to use the calculations made for other rows.

How would I approach this problem using arquero, if it is possible at all?

[jheer]

In what way is it failing for you? This following appears to work correctly using the current arquero version.

// lag of 1 row, with default of zero for non-existent rows
const dt = aq
  .table({a: [1, 2, 3], b: [3, 4, 5] })
  .derive({ c: d => d.a + op.lag(d.b, 1, 0) });

Result of dt.toCSV():

a,b,c
1,3,1
2,4,5
3,5,7

If I then change the derive to instead overwrite b (instead of the new column c), I still see correct results:

a,b
1,1
2,5
3,7

[MagnusHBrunbjerg]

Your examples also appear to work as described for me.

I think I might not have correctly expressed the calculations I am trying to perform. In the calculations I am trying to perform the content of b[1] will depend on the content of b[0] but unlike the working results you have shown here I were hoping to make the calculations for b[1] use the result for the calculation of b[0] instead of the original value.

Essentially I have a calculation for row b that depends on all rows above it and the other columns in same row. Due to the calculation depending on all previous rows my plan was to simply depend on the result of the row above.

A more concrete example of this could be calculating the charge of a battery, as a buffer in a grid, based on the electricity used and produced.

const dt = aq
  .table({
    used: [1, 3, 2, 5, 8],
    produced: [4, 3, 0, 2, 9],
    charge: [0, 0, 0, 0, 0],
  })
  .derive({
    charge: d => aq.op.greatest(0, aq.op.lag(d.charge, 1, 0) + d.produced - d.used),
  })

What I am hoping to get:

used,produced,charge
1,4,3
3,3,3
2,0,1
5,2,0
8,9,1

What I'm actually getting:

used,produced,charge
1,4,3
3,3,0
2,0,0
5,2,0
8,4,0

[jheer]

I'm still confused. Your new example produces the following for me:

used,produced,charge
1,4,3
3,3,0
2,0,0
5,2,0
8,9,1

This looks correct and doesn't match either of your outputs. And FWIW since charge is always zero in your example, the lag operation doesn't really do anything. The lag operator will always reference the original column values. This matches how lag works in SQL window functions. It will not access the new values produced in the middle of the calculation. Hopefully that clarifies the situation!

[MagnusHBrunbjerg]

I'm very sorry about the example, I changed the last value in produced from 4 to 9 and forgot to update it in the results.

In relation to charge always being zero in my example, I originally tried to derive the column without initializing it to 0 but that results in an error due to the calculation referencing its own column, which doesn't exist yet.

That definitely clears things up, thank you!
Working with this type of data processing is a new domain to me so I assumed that I might have misunderstood how the window function worked, which turned out to be true.

[MagnusHBrunbjerg]

As a followup, if it's not too much to ask.
Is this type of calculation (one where the calculated value of rows are based on the result of preceding rows) something that is possible with Arquero, and if it is, how?