Virtual Table 'Matrix' (List-Of-Lists) Tricks

Showcase Tips and Hacks
1

In case anyone is interested, I have figured out how to create, modify and display virtual tables in Coda. These virtual tables can be useful for certain stastical and noncyclical-graph data analysis.

Virtual Tables

Sometimes it is useful to represent a table as a matrix; M, aka, list-of-lists.
Formulas can address arbitrary values using
M.nth(I).nth(J) which facilitates rapid matrix operations used in maths, stats, AI, and graph-theory.

I use use the Apache Parquet format for virtual tables. Where the first column is the row-identifier and the last column is a list of the column names. This is used in ā€˜big dataā€™ applications to load large SQL tables into memory for faster processing. This format preserves the data labels as well as the values.

I have done this in Coda as follows;

Create a virtual table V from a coda table P which has columns Product, Size, Quantity, Priceā€¦

image
image715Ɨ116 9.67 KB

Which produces a list-of-lists like thisā€¦

image
image1212Ɨ60 9.21 KB

We GET the cell for row & col as follows (where row=id of the row, col=name of the column) as follows;
image

  • first it finds the index of the column using find(col) on the list of column names
  • then it finds the index of the row using find(row) on the column-list
  • finally it uses the nth() formulas to return the cell value

We can MODIFY a cell for row & col using the splice() formula as follows

image
image763Ɨ163 14.7 KB

The result is a new virtual table with the single cell at row & col changed to value
Note that this is NOT an action formula. So it does not need to be in a BUTTON or AUTOMATION.
We can manipulate virtual tables inside ordinary column formulas (or canvas formulas).
The resulting virtual table is updated immidiately without the need to click a button.

We can display the virtual table using this codeā€¦

image
image851Ɨ360 42.4 KB

Which looks like this;
image

I have made a pack for all this (and many more operations) for my clients.
If anyone is interested, I can get permission to publish it to the gallery.

Max

11 Likes
2

This is an innovative solution! Love it!

3

The best choice for AI applications as well.

4

Really quite fascinatingā€¦thanks!

5

Hi Max - Tried to replicate your code and tables for this very interesting solution , but I am unable to ā€œcreate a virtual tableā€. I am too dumb to know what that means or how to do it. Created regular Coda table V, but that didnā€™t help. How do Col and Row get created?

Any pointers gratefully appreciated. Thanks!

-Doug

6

the main purpose of Virtual Tables is to use them WITHIN a bigger formula as a temparory way to store and manipulate a table of data.

However you can also store them in single cell of a table for use later.
So lets assume I have a real Coda table; T1, which has a column for storing a virtual table, called V.
And I have another real Coda table; T2 with columns; A, B, C, D, that I wish to ā€˜pickleā€™ and store as a virtual table in a new row on table T1. The following code will acheive thatā€¦

T2.addrow( // create a new row in table T2
    V, list( T1.A, T1.B, T1.C, T1.D,  // make a list-of-lists for each column in T1
                 split('A B C D', " ")   // and add the column names at the end
     ) // that will save a parquet-formatted version of table T2 in the column V
)

As for the ROW and COL variables used to look-up a given cell in the virtual table; these are provided by the user or by some other part of your workflow. The first column of a parquet virtual table is the unique identifier for each row. So the formulas I gave for getting and setting the cells of the virtual table use the variables ROW & COL to get or set the cell where ROW is set to the id value for the row we want, and COL is set to the name of the column we want.

But ROW and COL need to be set by the user, or by the business logic in your doc.

The simplest use-case for virtual tables is to create a ā€˜snapshotā€™ of the real Coda table at a given point in time, either to log the changes, or to archive the data.

The more complex use-cases we have used involve using the virtual table as a big matrix by the stastical analysis algorithms used in marketing. Or by graph-traversal algorithms used in manufacturing ERP. Or as vector-processing algorithms in certain AI applications. These algorithms do not work with ā€˜namedā€™ columns, but regard the data as a flat 2D array of values.

I am negotiating with a my client to relaese the Coda Pack we developed to simplify all this under a GNU open-source license (currently it is proprietry IP as far as they are concerned). So I hope to be able to publish it soon.

When I am back in my office I will build a demo doc and post it here to show exactly how these techniques work.

Max

4 Likes
7

Thanks you so much, Max! I am travelling the next 2 days, but I will work on this again using your guidance, when Iā€™m left along long enough. Really appreciate the help and the very thorough answer!