6. Transformations

6.1. From table to widget

Data from one or more Explorer tables need to be converted into a format that is understood by a widget. Transformations allow widgets to perform these conversions. Take for example the following table:

The raw data looks like this:

[
  {
    "header": [
      { "name": "No." },
      { "name": "Referentiedatum" },
      { "name": "Groep grootte" }
    ],
    "poprows": [
      [null, "Alles", 66228]
    ],
    "preset": "DB_VERLOOP",
    "rows": [
      [1, "2016-06-01", 4719],
      [2, "2016-07-01", 4663],
      [3, "2016-08-01", 4714],
      [4, "2016-09-01", 4495],
      [5, "2016-10-01", 4592],
      [6, "2016-11-01", 4582],
      [7, "2016-12-01", 4612],
      [8, "2017-01-01", 4705],
      [9, "2017-02-01", 4998],
      [10, "2017-03-01", 5649],
      [11, "2017-04-01", 6087],
      [12, "2017-05-01", 6163],
      [13, "2017-06-01", 6249]
    ]
  }
]

If we want to display this data in a barline chart, we can configure the widget as follows:

widget_barlinechart:
  title: Voorbeeld staaflijngrafiek
  type: barlinechart
  data:
    app: explorer_patient
    parameters:
      preset: DB_VERLOOP
    transformations:
      bar:
        - table(0)
        - column(2)
      line:
        - table(0)
        - column(2)
        - smooth(moving_average,3)
      labels:
        - table(0)
        - column(1)

This widget needs three items: bar (array of values for the y-axis of the bar), line (array of values for the y-axis of the line) and labels (array of values for the labels on the x-axis).

For bar we select the first (and only) table and then a single column with index 2.

For line we select the same column, but then perform a smoothing function on it, i.e. a moving average with window size 3.

For labels we select a single column with index 1.

When the backend has performed the transformations, the data for the widget looks like this:

{
  "bar": [
    4719,
    4663,
    4714,
    4495,
    4592,
    4582,
    4612,
    4705,
    4998,
    5649,
    6087,
    6163,
    6249
  ],
  "line": [
    4719.00,
    4691.00,
    4698.67,
    4624.00,
    4600.33,
    4556.33,
    4595.33,
    4633.00,
    4771.67,
    5117.33,
    5578.00,
    5966.33,
    6166.33
  ],
  "labels": [
    "2016-06-01",
    "2016-07-01",
    "2016-08-01",
    "2016-09-01",
    "2016-10-01",
    "2016-11-01",
    "2016-12-01",
    "2017-01-01",
    "2017-02-01",
    "2017-03-01",
    "2017-04-01",
    "2017-05-01",
    "2017-06-01"
  ],
  "timestamp": "2023-12-04 15:38:37"
}

Then we get the following widget:

6.2. Transformation variables

Transformations construct_array and construct_value can reference the output of other transformations. Each transformation key that starts with an underscore is treated as a variable. The result of these transformations can be used in other transformations, but are not returned to the widget. Variables must be declared before they are used, i.e. all transformation keys with an underscore should appear before all other transformation keys.

The following example creates two variables (_processed and _total), which are used in the calculations of the actual widget data in percentage and value.

transformations:
  _processed:
    - table(0)
    - row(0)
    - index(2)
  _total:
    - table(0)
    - poprow(0)
    - index(2)
  percentage:
    - construct_value(_processed/_total*100)
  value:
    - construct_value(_processed)

6.3. Available transformations

From	To	Transformations
explorertables	explorertable	table
explorertable	table	columns, find_rows, poprows, rows
explorertable	array	column, find_row, header, poprow, row
explorertable	value
table	table	construct_table, replace_table, slice_table, transpose_table
table	array	merge_table
table	value	aggregate_table
array	table	array_to_table
array	array	default_array, label_array, reverse_array, slice_array, smooth_array
array	value	aggregate_array, index
value	table
value	array	value_to_array
value	value	default_value, format_value
any	table
any	array	construct_array, literal_array
any	value	construct_value, literal_value

class AggregateArrayTransformation

>>> input = [1, 2, 3]
>>> func = 'aggregate_array(max)'
>>> transform(func, input)
3

>>> func = 'aggregate_array(sum)'
>>> transform(func, input)
6

class AggregateTableTransformation

>>> input = [[1, 2, 3], [4, 5, 6]]
>>> func = 'aggregate_table(column_count)'
>>> transform(func, input)
3

>>> func = 'aggregate_table(row_count)'
>>> transform(func, input)
2

class ArrayToTableTransformation

>>> input = [1, 2, 3]
>>> func = 'array_to_table()'
>>> transform(func, input)
[[1, 2, 3]]

class ColumnTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3], ['2020-02-01', 5]] }
>>> func = 'column(1)'
>>> transform(func, input)
[3, 5]

>>> func = 'column(Value)'
>>> transform(func, input)
[3, 5]

class ColumnsTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value1'}, {'name': 'Value2'}], 'rows': [['2020-01-01', 3, 30], ['2020-02-01', 5, 50]] }
>>> func = 'columns(1,2)'
>>> transform(func, input)
[[3, 5], [30, 50]]

>>> func = 'columns(Date,Value2)'
>>> transform(func, input)
[['2020-01-01', '2020-02-01'], [30, 50]]

class ConstructArrayTransformation

>>> input = [5, 6, 7]
>>> func = 'construct_array(input@0,input@1+input@2)'
>>> transform(func, input)
[5, 13]

>>> func = 'construct_array(20,20-input@0)'
>>> transform(func, input)
[20, 15]

>>> input = [10, 15, 25]
>>> func = 'construct_array((input@2-input@0)/(input@0+input@1+input@2)*100)'
>>> transform(func, input)
[30]

>>> func = 'construct_array(nps(input@0;input@1;input@2))'
>>> transform(func, input)
[30]

class ConstructTableTransformation

>>> input = [[5, 6, 7], [8, 9, 10]]
>>> func = 'construct_table(row@0,row@1+row@2)'
>>> transform(func, input)
[[5, 13], [8, 19]]

>>> func = 'construct_table(nps(row@0;row@1;row@2))'
>>> transform(func, input)
[[11], [7]]

class ConstructValueTransformation

>>> input = 6
>>> func = 'construct_value(input+1)'
>>> transform(func, input)
7

>>> func = 'construct_value(input/2)'
>>> transform(func, input)
3

>>> input = [1, 2, 3]
>>> func = 'construct_value(nps(input@0;input@1;input@2))'
>>> transform(func, input)
33

class DefaultArrayTransformation

>>> input = ['Value A', '', 'Value B']
>>> func = 'default_array(Unknown)'
>>> transform(func, input)
['Value A', 'Unknown', 'Value B']

class DefaultValueTransformation

>>> input = ''
>>> func = 'default_value(Unknown)'
>>> transform(func, input)
'Unknown'

class FindRowTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'find_row(Value,5)'
>>> transform(func, input)
['2020-02-01', 5]

>>> func = 'find_row(0,2020-02-01,Value,5)'
>>> transform(func, input)
['2020-02-01', 5]

class FindRowsTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'find_rows(Value,5)'
>>> transform(func, input)
[['2020-02-01', 5]]

>>> func = 'find_rows(0,2020-02-01,Value,5)'
>>> transform(func, input)
[['2020-02-01', 5]]

class FormatValueTransformation

>>> input = 3
>>> func = 'format_value(n = {})'
>>> transform(func, input)
'n = 3'

>>> input = 3.45678
>>> func = 'format_value(n = {.00})'
>>> transform(func, input)
'n = 3,46'

class HeaderTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'poprows': [['All', 234]], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'header()'
>>> transform(func, input)
['Date', 'Value']

class IndexTransformation

>>> input = [3, 5, 7, 9]
>>> func = 'index(2)'
>>> transform(func, input)
7

class LabelArrayTransformation

>>> input = ['Age >= 10', 'Value = 42']
>>> func = 'label_array(strip_grouplabel)'
>>> transform(func, input)
['>= 10', '42']

>>> input = ['short string', 'a longer string']
>>> func = 'label_array(wrap,12)'
>>> transform(func, input)
[['short string'], ['a longer', 'string']]

class LiteralArrayTransformation

>>> input = None
>>> func = 'literal_array(January,February,March)'
>>> transform(func, input)
['January', 'February', 'March']

class LiteralValueTransformation

>>> input = None
>>> func = 'literal_value(January)'
>>> transform(func, input)
'January'

class MergeTableTransformation

>>> input = [[2022, 7, 5], [2023, 9, 13], [2024, 11, 21]]
>>> func = 'merge_table(year_month,0,1)'
>>> transform(func, input)
['2022-07', '2023-09', '2024-11']

>>> func = 'merge_table(year_month_day,0,1,2)'
>>> transform(func, input)
['2022-07-05', '2023-09-13', '2024-11-21']

class PoprowTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'poprows': [['All', 234]], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'poprow(0)'
>>> transform(func, input)
['All', 234]

class PoprowsTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'poprows': [['All', 234], ['Group 1', 180], ['Group 2', 54]], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'poprows(1,2)'
>>> transform(func, input)
[['Group 1', 180], ['Group 2', 54]]

>>> func = 'poprows(2)'
>>> transform(func, input)
[['Group 2', 54]]

class ReplaceTableTransformation

>>> input = [[1, 3, 5], [3, 4, 6]]
>>> func = 'replace_table(3,30)'
>>> transform(func, input)
[[1, 30, 5], [30, 4, 6]]

class ReverseArrayTransformation

>>> input = [1, 2, 3]
>>> func = 'reverse_array()'
>>> transform(func, input)
[3, 2, 1]

class RowTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'row(0)'
>>> transform(func, input)
['2020-01-01', 3]

class RowsTransformation

>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> func = 'rows(0,1)'
>>> transform(func, input)
[['2020-01-01', 3], ['2020-02-01', 5]]

>>> func = 'rows(1)'
>>> transform(func, input)
[['2020-02-01', 5]]

class SliceArrayTransformation

>>> input = [3, 5, 7, 9]
>>> func = 'slice_array(1,3)'
>>> transform(func, input)
[5, 7]

>>> func = 'slice_array(,,2)'
>>> transform(func, input)
[3, 7]

>>> func = 'slice_array(1,,2)'
>>> transform(func, input)
[5, 9]

class SliceTableTransformation

>>> input = [[1, 3, 5, 7, 9, 11], [2, 4, 6, 8, 10, 12]]
>>> func = 'slice_table(1,4,2)'
>>> transform(func, input)
[[3, 7], [4, 8]]

>>> func = 'slice_table(1,,2)'
>>> transform(func, input)
[[3, 7, 11], [4, 8, 12]]

>>> func = 'slice_table(,,2)'
>>> transform(func, input)
[[1, 5, 9], [2, 6, 10]]

class SmoothArrayTransformation

>>> input = [50, 60, 70, 40, 40, 40, 90, 90]
>>> func = 'smooth_array(average)'
>>> transform(func, input)
[60.0, 60.0, 60.0, 60.0, 60.0, 60.0, 60.0, 60.0]

>>> func = 'smooth_array(moving_average,2)'
>>> transform(func, input)
[50.0, 55.0, 65.0, 55.0, 40.0, 40.0, 65.0, 90.0]

class TableTransformation

>>> input = [{ 'header': [{'name': 'Date'}, {'name': 'Value1'}, {'name': 'Value2'}], 'rows': [['2020-01-01', 3, 30], ['2020-02-01'], 5, 50] }]
>>> func = 'table(0)'
>>> transform(func, input)
{'header': [{'name': 'Date'}, {'name': 'Value1'}, {'name': 'Value2'}], 'rows': [['2020-01-01', 3, 30], ['2020-02-01'], 5, 50]}

class Transformation

Base transformation class.

class TransformationDataTypes(value)

class TransposeTableTransformation

>>> input = [[1, 2, 3], [4, 5, 6]]
>>> func = 'transpose_table()'
>>> transform(func, input)
[[1, 4], [2, 5], [3, 6]]

class ValueToArrayTransformation

>>> input = 42
>>> func = 'value_to_array()'
>>> transform(func, input)
[42]