Essays/Triangular Data

In some applications, data is best represented as a triangle. For example, if tracking experience for each years block of business, then business done 5 years ago will now have 5 years of data, business done 4 years ago will have 4 years of data, etc. Here are some considerations for working with such triangles.

Definitions used are in script File:Triutil.ijs

Representation

Triangular data can be stored as a boxed list with one cell per start year, as in:

+------------+------------+-----------+------+---+
|45 24 23 3 2|108 88 76 18|169 125 113|202 36|280|
+------------+------------+-----------+------+---+

However, it is usually more convenient to store the data in a table, where rows are padded with 0, e.g.

   TAB
 45  24  23  3 2
108  88  76 18 0
169 125 113  0 0
202  36   0  0 0
280   0   0  0 0

Such a table might represent start years as rows, and durations as columns, e.g.

+----+----------------+
|    |  1   2   3  4 5|
+----+----------------+
|2001| 45  24  23  3 2|
|2002|108  88  76 18 0|
|2003|169 125 113  0 0|
|2004|202  36   0  0 0|
|2005|280   0   0  0 0|
+----+----------------+

Flipped Representation

The data can be flipped so that columns are the attained year:

+----+------------------------+
|    |2002 2003 2004 2005 2006|
+----+------------------------+
|2001|  45   24   23    3    2|
|2002|   0  108   88   76   18|
|2003|   0    0  169  125  113|
|2004|   0    0    0  202   36|
|2005|   0    0    0    0  280|
+----+------------------------+

The verb flip will do this, and flipr the reverse:

flip=: 3 : '(-i.#y) |."0 1 y'
flipr=: 3 : '(i.#y) |."0 1 y'

Many operations on triangular data are easier if the data is flipped. For example, to sum values by attained year, then if the data is in the original form, the sums must be done on diagonals, but if the data is flipped, the sums are on the columns:

   +/TAB               NB. totals by duration, 1 2 3...
804 273 212 21 2

   +/flip TAB          NB. totals by attained year, 2002 2003 2004 ...
45 132 280 406 449

Grid Display

Grid supports data triangles in the jdgrid class, see example script File:Trigrid.ijs. Here, the Flip button toggles the display.

See also the demo: Studio|Demos|Grid|Gallery|Development Triangle.

Square Tables

Sometimes, the numbers of rows and columns are not the same, for example if there are 15 start years, but only 10 years experience is tracked, thus requiring a 15 x 10 table. Usually, it is simplest to pad out values with zeroes so that the data becomes square. If you must work with non-square tables, then make corresponding adjustments to the utilities described here.

Diagonals

The expression (<0 1) |: TAB reads the diagonal of the table from top left to bottom right (the left diagonal). There is no corresponding expression for amending the diagonal, and it is usually easiest to generate the required indices for amend using ,~each, e.g.

   ,~each i.5
+---+---+---+---+---+
|0 0|1 1|2 2|3 3|4 4|
+---+---+---+---+---+

Thus:

   putdiag=: 4 : 'x (,~each i.#y) } y'
   (100*i.5) putdiag i.5 5
 0   1   2   3   4
 5 100   7   8   9
10  11 200  13  14
15  16  17 300  19
20  21  22  23 400

Normally, diagonal values to be read from triangular data are those from top right to bottom left (the right diagonal). To access these, you can flip the table as before and then the right diagonal becomes the last column. Alternatively, reverse each row, and the diagonal then starts from top left to bottom right, i.e. compare:

   TAB ; (flip TAB) ; |."1 TAB
+----------------+------------------+----------------+
| 45  24  23  3 2|45  24  23   3   2|2  3  23  24  45|
|108  88  76 18 0| 0 108  88  76  18|0 18  76  88 108|
|169 125 113  0 0| 0   0 169 125 113|0  0 113 125 169|
|202  36   0  0 0| 0   0   0 202  36|0  0   0  36 202|
|280   0   0  0 0| 0   0   0   0 280|0  0   0   0 280|
+----------------+------------------+----------------+

For example, to get the right diagonal:

   getdiagr=: (<0 1) |: |."1
   getdiagr TAB
2 18 113 36 280

To write new values to the right diagonal:

   new=. 1000 1100 1200 1300 1400
   putdiagr=: 4 : 'x ((,each#-1&+)i.#y) } y'
   new putdiagr TAB
  45   24   23    3 1000
 108   88   76 1100    0
 169  125 1200    0    0
 202 1300    0    0    0
1400    0    0    0    0

Other Utilities

Verb uppertri zeroes out any values not in the upper left triangle:

uppertri=: 3 : 'y * (<:/|.) i.#y'

For example:

   uppertri i. 5 5
 0  1  2 3 4
 5  6  7 8 0
10 11 12 0 0
15 16  0 0 0
20  0  0 0 0

This enables a cumulative sum down the columns:

   uppertri +/\TAB
 45  24  23  3 2
153 112  99 21 0
322 237 212  0 0
524 273   0  0 0
804   0   0  0 0

Contributed by Chris Burke