Forum Discussion

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 20, 2024

Hi Lori

I think my experimentation may have more significance in terms of my working than I realised!

The pairing of a text string and a thunk allows me to build a data dictionary that provides a mechanism to pass complex data structures between Lambda function (in particular to return multiple data objects from a sub-function).

So far I have written a DICTIONARYλ constructor function that builds the data structure

/*  NAME:           DICTIONARYλ
    REVISIONS:      Date        Developer           Description  
                    Dec 19 2024 Peter Bartholomew   Original Development
*/
DICTIONARYλ 
= LAMBDA( _key01, object01, [_key02], [object02],  ...
    // Check inputs - Omitted required arguments
    Help?, OR(
        ISOMITTED(_key01),
        ISOMITTED(object01),
    ),

    NULLOMMITTED, LAMBDA(x, IF(ISOMITTED(x), "", x)),
    _key02, NULLOMMITTED(_key02),
    ...

    //  Procedure
    //  Build array of unique keys
        uniqueKeys, VSTACK(_key01, _key02,_...),
    
    //  Build array of thunked arrays (also applicable to scalars)
        objectArray, VSTACK(LAMBDA(object01), LAMBDA(object02), ...  ), 

    //  Combine unique keys with references to data objects
        result, FILTER(HSTACK(uniqueKeys, objectArray), uniqueKeys<>""),
       
    CHOOSE(Help? + 1, Result, Help)
    )
);

as well as generic and dictionary-specific functions to return data from dictionaries

GETλ
= LAMBDA(dict, LAMBDA(key,
    LET(
        uniqueKeys, TAKE(dict,,1),
        objects,    DROP(dict,,1),
        result,     XLOOKUP(key, uniqueKeys, objects, "keyword not found"),
        (@result)()
    )
));

GETSERIESDATAλ
= GETλ(seriesDataDictionary)
;

and the ability to return a list of keys

KEYSλ = LAMBDA(dict, TAKE(dict,,1));

Within a functional programming environment, each dictionary is write once, read many since all objects are immutable, but I should be able to combine two directories to give a fresh data object.

That might sound extravagant in terms of memory commitment, but since I am only storing thunks (references to memory allocated to a LET variable) the burden might be modest.

The Excel failure to return arrays of arrays makes it a little difficult to dump an image of the dictionary to the worksheet for checking or to provide persistence but I think my MAPλ function should work.

I have addressed these thoughts to you, as a Python programmer, in the hope that you might have additional insight that you could draw upon to educate me🙂!  Of course, others are welcome to contribute.

lori_m's avatar
lori_m
Iron Contributor
Dec 21, 2024

Hi Peter - I think a natural way to define a data dictionary in Excel is as an (n x 2)-array consisting of key/value pairs. Given definitions as in the earlier attachment, a data dictionary could be defined pairwise,

dic = WRAPROWS(VSTACK(
       "United Kingdom", LAMBDA(Table1),
       "Germany", LAMBDA(Table2),
       "France", LAMBDA(Table3),
       "Italy", LAMBDA(Table4)
),2)

or alternatively as a pair of lists,

dic = LET(
    keys, FILTER(TAKE(allocatedRange,,1),ISTEXT(markers)),
    values, StructureDataλ(markers, allocatedRange),
    HSTACK(keys, values)
)

In either case TAKE(dic,,1) returns the keys and (@VLOOKUP("United Kingdom",dic,2,0))() returns the values corresponding to the key. This appears much like the GETλ / KEYSλ functions in your comment. However, I wouldn't advise creating DICTIONARYλ as I don't know of any way to specify an extensible list of arguments within a lambda definition - the first method above seems like a reasonable compromise in my view. 

For comparison the equivalent definitions in Python would be,

dic={"United Kingdom":Table1,"Germany":Table2,"France":Table3,"Italy":Table4}

or otherwise utilising tuples,

dic = dict(zip(
    ("United Kingdom","Germany","France","Italy"),
    (Table1,Table2,Table3,Table4)
))

dic.keys() and dic.get("United Kingdom") return keys / values. A custom data dictionary could be defined by passing arguments using unpacking operators (*args / **kwargs) - something Excel currently lacks

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 22, 2024

    I see your point about DICTIONARYλ, in that it limits the VSTACK formulas it uses rather than increasing the scope of application.  I set it up for a data dictionary length longer than required and discarded the pairs that were not populated using IFOMITTED.  This is very limited functionality but it more than met my current requirements. 

    At the moment, the function that I am planning to convert calculates 24 time series across 360 time periods.  To pass the results back, it stacks the 24 individual arrays into an 24 x 360 array which is returned to the calling function.  There, yet another copy of the data follows as each series of the array is singled out with CHOOSEROWS and assigned a new name.  Mistakes at that stage are possible and may not be detected immediately.  Then it remains to use VSTACK once more to arrange the arrays to match the user-expectation for the output, with tastefully placed blank rows and some arrays appearing more than once.

    My hope is that, by turning the arrays into a data dictionary that uses thunks, I am only replicating references to the data, not the arrays themselves.  I plan to generate the presentation layer by referencing arrays from the returned data dictionary directly, based upon the keywords that come as part of the dictionary.

    Hopefully I will have reduced both the data storage requirement and the likelihood of error.  These are all very new ideas for me and I certainly haven't worked through the pros and cons!

Resources