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:

_images/table.png

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:

_images/widget_barlinechart2.png

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.

>>> func = 'aggregate_array(max)'
>>> input = [1, 2, 3]
>>> 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.

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

Transform an array into a table.

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

Select a single column based on its index or name.

>>> func = 'column(1)'
>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3], ['2020-02-01', 5]] }
>>> 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.

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

>>> func = 'construct_array(input@0,input@1+input@2)'
>>> input = [5, 6, 7]
>>> transform(func, input)
[5, 13]
>>> func = 'construct_array(20,20-input@0)'
>>> transform(func, input)
[20, 15]
>>> func = 'construct_array((input@2-input@0)/(input@0+input@1+input@2)*100)'
>>> input = [10, 15, 25]
>>> transform(func, input)
[30]
>>> func = 'construct_array(nps(input@0;input@1;input@2))'
>>> input = [10, 15, 25]
>>> 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.

>>> func = 'construct_table(row@0,row@1+row@2)'
>>> input = [[5, 6, 7], [8, 9, 10]]
>>> transform(func, input)
[[5, 13], [8, 19]]
>>> func = 'construct_table(nps(row@0;row@1;row@2))'
>>> input = [[5, 6, 7], [8, 9, 10]]
>>> 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

>>> func = 'construct_value(input+1)'
>>> input = 6
>>> transform(func, input)
7
>>> func = 'construct_value(input/2)'
>>> transform(func, input)
3
>>> func = 'construct_value(nps(input@0;input@1;input@2))'
>>> input = [1, 2, 3]
>>> transform(func, input)
33
class DefaultArrayTransformation

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

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

Replace empty value with the provided default value.

>>> func = 'default_value(Unknown)'
>>> input = ''
>>> 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.

>>> func = 'find_row(Value,5)'
>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 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.

>>> func = 'find_rows(Value,5)'
>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 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.

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

Return the explorertable header as an array of column names.

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

Select a value within an array based on its index.

>>> func = 'index(2)'
>>> input = [3, 5, 7, 9]
>>> 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.

>>> func = 'label_array(strip_grouplabel)'
>>> input = ['Age >= 10', 'Value = 42']
>>> transform(func, input)
['>= 10', '42']
>>> func = 'label_array(wrap,12)'
>>> input = ['short string', 'a longer string']
>>> transform(func, input)
[['short string'], ['a longer', 'string']]
class LiteralArrayTransformation

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

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

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

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

Merge two or more columns into an array.

>>> func = 'merge_table(year_month,0,1)'
>>> input = [[2022, 7, 5], [2023, 9, 13], [2024, 11, 21]]
>>> 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.

>>> func = 'poprow(0)'
>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'poprows': [['All', 234]], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> 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.

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

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

Reverse the order of items in an array.

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

Select a single row based on its index.

>>> func = 'row(0)'
>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> 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.

>>> func = 'rows(0,1)'
>>> input = { 'header': [{'name': 'Date'}, {'name': 'Value'}], 'rows': [['2020-01-01', 3],['2020-02-01', 5]] }
>>> 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.

>>> func = 'slice_array(1,3)'
>>> input = [3, 5, 7, 9]
>>> 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.

>>> func = 'slice_table(1,4,2)'
>>> input = [[1, 3, 5, 7, 9, 11], [2, 4, 6, 8, 10, 12]]
>>> 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.

>>> func = 'smooth_array(average)'
>>> input = [50, 60, 70, 40, 40, 40, 90, 90]
>>> 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.

>>> func = 'table(0)'
>>> input = [{ 'header': [{'name': 'Date'}, {'name': 'Value1'}, {'name': 'Value2'}], 'rows': [['2020-01-01', 3, 30], ['2020-02-01'], 5, 50] }]
>>> 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.

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