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
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¶

Apply a specified aggregate function to an array of values to get a summary value. Possible functions: average, count, first, last, max, median, min, stdev, sum, variance.

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

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

class AggregateTableTransformation¶

Apply a specified aggregate function to a table to get a summary value. Possible functions: column_count, row_count.

>>> 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¶

Transform an array into a table.

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

class ColumnTransformation¶

Select a single column based on its index or name.

>>> 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¶

Select one or more columns based on their indices or names. Output is always an array of columns, even if only a single column is selected.

>>> 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¶

Construct an array based on: - Values from the previous transformation (input) - Values from other transformations (variables) - Literal numbers - Calculations on these values: add (+), subtract (-), multiply (*), divide (/), floor divide (//), power (**) and modulo (%) - Functions: nps

>>> 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¶

Construct a table based on: - Values from the previous transformation (input). - Values from other transformations (variables) - Literal numbers - Calculations on these values: add (+), subtract (-), multiply (*), divide (/), floor divide (//), power (**) and modulo (%) - Functions: nps The constructed table has the same number of rows as the input table. The ‘input’ variable contains the complete table from the previous transformation, while the ‘row’ variable contains the currently processed row from that table.

>>> 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¶

Construct a value based on: - Values from the previous transformation (input) - Values from other transformations (variables) - Literal numbers - Calculations on these values: add (+), subtract (-), multiply (*), divide (/), floor divide (//), power (**) and modulo (%) - Functions: nps

>>> 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¶

Replace empty values in an array with the provided default value.

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

class DefaultValueTransformation¶

Replace empty value with the provided default value.

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

class FindRowTransformation¶

Find a single row where a certain column contains the provided value. Multiple combinations of column name/index and search value may be specified.

>>> 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¶

Find multiple rows where a certain column contains the provided value. Multiple combinations of column name/index and search value may be specified.

>>> 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¶

Insert data into the provided string. The occurance of ‘{}’ is replaced with the data, the rest of the string is returned as-is.

>>> 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¶

Return the explorertable header as an array of column names.

>>> 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¶

Select a value within an array based on its index.

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

class LabelArrayTransformation¶

Transform labels in an array. * Strip the name and possibly the operator from Explorer group labels. * Wrap long labels into multiple lines if they exceed a given length.

>>> 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¶

Output array is literally what is specified. Any input data is ignored.

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

class LiteralValueTransformation¶

Output value is literally what is specified. Any input data is ignored.

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

class MergeTableTransformation¶

Merge two or more columns into an array.

>>> 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¶

Select a single population row based on its index.

>>> 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¶

Select one or more population rows based on their indices. Output is always an array of population rows, even if only a single population row is selected.

>>> 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¶

Replace all occurences of the provided value with the provided replacement in the table.

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

class ReverseArrayTransformation¶

Reverse the order of items in an array.

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

class RowTransformation¶

Select a single row based on its index.

>>> 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¶

Select one or more rows based on their indices. Output is always an array of rows, even if only a single row is selected.

>>> 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¶

Select values within an array based on slicing. At most 3 parameters can be specified: start, stop, step. If a parameter is not provided, the defaults are 0 for start, the length of the array for stop, and 1 for step.

>>> 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¶

Apply slicing to every row within a table. At most 3 parameters can be specified: start, stop, step. If a parameter is not provided, the defaults are 0 for start, the length of the array for stop, and 1 for step.

>>> 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¶

Apply a smoothing function to an array of values. Possible functions are: average, cumulative_average and moving_average.

>>> 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¶

Select a single explorertable from explorertables based on its index or name.

>>> 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, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)¶

class ValueToArrayTransformation¶

Transform a value into an array.

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