Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
May 22, 2022

Unpivot Monthly Data with a Formula

Note: I know how to unpivot data using PowerQuery and vba.  What I'm interested in with this exercise is using a formula to unpivot monthly data.  I'm interested in using some of the newest functions...
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 24, 2022

SergeiBaklan 

Sergei, perhaps I should send the file to Charles.  Patrick2788 's solution is the fastest.  I was deliberately trying different methods against the problem even where I suspected they would not be efficient.  I was not expecting to get differences in excess of 10,000 though!  Even if I were calculating 2400 terms of an array, selecting 1, and repeating the 2400 selecting the second etc. I would not expect the times to be that bad.  

 

The attached file contains timing information for the methods posted here but with no study of size effects.

lori_m's avatar
lori_m
Iron Contributor
May 24, 2022

Hi Peter, I had a quick look at this after following your other link. Perhaps a simplified version might help highlight the source of the performance issues:

 

=LET(thunks,IF(SEQUENCE(2400),LAMBDA({1,2,3})),
MAKEARRAY(2400,3,LAMBDA(r,c,INDEX(INDEX(thunks,r,1)(),1,c))))

 

Entering the above formula in any cell returns a 2400 x 3 array and takes 10+ seconds to calc. However this single row version calculates near instantaneously:

 

=LET(thunks,IF(SEQUENCE(1),LAMBDA({1,2,3})),
MAKEARRAY(2400,3,LAMBDA(r,c,INDEX(INDEX(thunks,1,1)(),1,c))))

 

It seems that the thunk array might be being re-evaluated for each element of the MAKEARRAY result matrix - presumably this is due to 'lazy evaluation'. I've tended to steer clear of thunks so far but remain hopeful performance will improve over time.

  • lori_m's avatar
    lori_m
    Iron Contributor
    Aug 16, 2023

    I just learned another more efficient way which I'm posting here in case I forget it,

     

    =LET(thunks, IF(SEQUENCE(2400), LAMBDA({1, 2, 3})), MAP(IF(SEQUENCE(, 3), thunks),IF(SEQUENCE(2400), SEQUENCE(, 3)),LAMBDA(thunk, i, INDEX(thunk(),i))))

     

    The MAP method takes only 0.04s compared to over 10s using MAKEARRAY. It seems placing a LAMBDA around 'thunks' in MAKEARRAY wastes many cycles. I was also surprised to find sorting rows on the same size array using,

    =LET(
    thunks, BYROW(
        Data,
        LAMBDA(row, LET(sorted, SORT(row, , , 1), LAMBDA(sorted)))
    ),
    MAP(
        IF(SEQUENCE(, COLUMNS(Data)), thunks),
        IF(SEQUENCE(ROWS(Data)), SEQUENCE(, COLUMNS(Data))),
        LAMBDA(thunk, i, INDEX(thunk(), i))
    )
)

    versus the more standard REDUCE/VSTACK method,

    =DROP(
    REDUCE(
        0,
        SEQUENCE(ROWS(Data)),
        LAMBDA(a, i, VSTACK(a, SORT(CHOOSEROWS(Data, i), , , 1)))
    ),
    1
)

    was around 10 times quicker!

    [~0.1s vs ~1s with Data=RANDARRAY(2400,3,0,100,1)]

     

Resources