Forum Discussion
Solved
Alternative formulas for expanding a column of row thunks.
The two options that I tried are
Expandλ = LAMBDA(ϑ,
LET(
n₁, ROWS(ϑ),
n₂, MAX(MAP(ϑ,Columnsλ)),
MAKEARRAY(n₁,n₂,
LAMBDA(i₁,i₂,
INDEX(EXPAND(INDEX(ϑ,i₁,1)(),1,n₂,""),i₂)
)
)
)
);which expands the array thunk, ϑ, by using the MAKEARRAY helper function, first to select a specific thunk from the array then, once the row array is extracted, to use a further INDEX to pick a single value for output,
The second option is
Expand2λ = LAMBDA(arrϑ,
IFERROR(
DROP(
REDUCE(0,arrϑ,
LAMBDA(result,aϑ,
VSTACK(result,aϑ())
)
),
1),
"")
);which uses REDUCE to run through the array thunk, expanding each in turn and appending it the growing array formed of prior rows.
As further background, the relevance of the problem is that the array thunk offers an escape from the "Nested Arrays are not Supported" problem which makes life pretty difficult when the problem specifies a nested array as its solution. The problem I have used to generate the array thunk is based on a random array, and the objective is to filter out duplicates and sort row by row.
Do you have a preference? To be honest, I am not totally enamoured with either solution (though I keep coming back to them) and I have doubts as to their performance as the problem size increases.
Second variant is much faster. 200x90 array
Second variant gives approximately the same time on 1400x900 array
I tested on =SEQUENCE( n, m ) array and on slow PC.
First variant works on per-element basis, second one with arrays loaded into memory as one object. My guess.
Because I have doubts about the efficiency of forming and then expanding thunk arrays, I have tried to reformulate an accumulation problem so that the 2D results array grows throughout the calculation, by using REDUCE and HSTACK. I have yet to perform any timing runs to see whether the problem of O(n²) calculation time is reduced.
The other characteristic of the test problems that may well confuse, is that the Lambda functions that perform the core calculation are passed into the formula as a parameter. Radically different calculations can thus be conducted within the same formula/code framework.
To date, most of my effort has been committed to "Will this work?". "Will it work efficiently as the problem size grows?" is entirely another matter.
Second variant is much faster. 200x90 array
Second variant gives approximately the same time on 1400x900 array
I tested on =SEQUENCE( n, m ) array and on slow PC.
First variant works on per-element basis, second one with arrays loaded into memory as one object. My guess.
I have a suspicion that 'lazy calculation' (that seems to be the case for Thunks) causes the search and filter operations to be repeated for each evaluation along the row. It is easy to visualise the thunk as containing the values it wraps, but the reality appears to be that it contains the formulas that allow the values to be recalculated as needed. If the calculation is computationally expensive, one can easily get to trouble timewise!
Thanks for that. It confirms my observations.
Apart from being slower, the first solution is growing as O(n²), so even at a couple of hundred rows it is passing into the unusable zone. The REDUCE and VSTACK should be good for over 1000 rows. Sad, because I liked the first solution.
I think that removing the nested arrays constraint should be rising to the top of the 'to-do' list. If not universally through Excel, at least for BYROW and BYCOL which should be able to stack arrays just like VSTACK and HSTACK.
In addition I tried to compare your second variant (sortRowsA below) with simple by row number iteration:
fnRow=LAMBDA( x, SORT( UNIQUE(x,1),,,1) ); sortRowsA=LAMBDA( array, fn, LET( fakeVector, LAMBDA( vector, LAMBDA(vector) ), setRow, LAMBDA(x, fakeVector(fn(x))), set2D, BYROW( array, setRow ), DROP( REDUCE( 1 , set2D, LAMBDA(a, v, VSTACK(a, v()) ) ), 1) )); sortRowsB=LAMBDA(array, fn, REDUCE( "", SEQUENCE(ROWS(array)), LAMBDA(a, k, IF( SEQUENCE(k) = k, fn( INDEX(array, k, ) ), a ) ) ) );On the same array average time of calculation
varA - 773 ms
varB - 1303 ms (i.e. two times slowly)
To compare, Solution 1 on the same array gives 57070 ms (or 57 sec) in average. That is huge difference.
