Conditional Formatting

Scripting API ››
Parent Previous Next

Conditional Formatting

Conditional formatting is a property that sets how a cell / entry is to be colored, based on a set of rules that the user specifies. Conditional formatting may be applied to Table and Tree data items. Functionality specific to those data items are covered there. Conditional formatting is specified as a string.  This string consists of a list of rules. The list of rules is executed against every cell in the table or tree data item.  The rules allow for the control of the color of the cell text, the background color of the cell, the "bold" font attribute and other visual details. For trees, the conditional formatting can decide if a given node starts as 'open' or 'closed'. This formatting is applied when the HTML for the webpage is generated.

The formatting string

The conditional formatting string consists of a collection of rules. Rules are run sequentially. Each rule decides what cells (if any) it applies to using a "target" function. Each of the potential target cells has the "comparison" function run on the contents of the cell. If the function returns true, the "formatting" function in the rule is executed and the display attributes of the cell are computed.  Note: once a formatting function is executed on a cell, that cell is excluded from processing by any other rules.


The formatting string is encoded as a collection of sub-strings, one for each rule, separated by the '&' character like this:


{Rule}&{Rule}&{Rule}...


Each rule consists of three Python expressions: a target, the comparison and a formatting expression formatted, like this:


{Target expression}:{Comparison expression}|{Formatting expression}


The following is a valid conditional formatting string:


col(1):before()>=5.0|bold()+rgb(0.,1.,0.)&col("*Poly*"):True|bold()+pal("hot", min(), max())&col("Linear"):value>5.5|rgb(0.5,0.5,0.5)+contrast()&col("Random"):fabs(value)>500.|pal("-picnic", -2500, 2500, num_levels=3)+pal("picnic", -2500, 2500, num_levels=5, forecolor=True)&


containing four rules. The string is probably best understood using the Conditional Formatting Editor in the Nexus Template Editor:


Setting the rules inside the Conditional Formatting editor generates a string for the table_cond_format or tree_cond_format properties. One may set this property into a template via the Template API set_property call.  It is also possible to add it to a data item or tree  directly using the External Python API.

For a table:

rule = "col(0):value>1.|bold()"

d = dict(array=array, collbls=col_names, title="Table with embedded conditional formatting", table_cond_format=rule)

item = server.create_item(name="Formatted Table", source="testing", sequence=0)


For a tree (assuming that the child1 and child2 nodes contain two columns of data):

rule = 'col(1):value>before()|pal("Picnic",0.,1.)+openparents()'

tree = dict(name='Top level', key='root', value=['', ''], children=[child1, child2], state='collapsed', tree_cond_format=rule)

item = server.create_item(name="Formatted Tree", source="testing", sequence=0)

item.set_payload_tree([tree])

Nexus Template Editor property editor

 In the property editor, entering table_cond_format (tree_cond_format for tree data items) in the property cell will prompt a button in the Edit column:


Clicking on the '...' button will open an advanced editor specific to this property:


The top section allows the user to enter the set of rules to apply. The "+" and "-" buttons allow for new rules to be added/removed. For each rule, a target and a comparison expression must be entered. If Bold is toggled on, the cell entry will be displayed in bold format. If Contrast is toggled on, the cell text color will be white or black based on the selected cell background color (a light background will select a black font, while a dark background will select a white font).

Note that rules apply in order, from the top to the bottom one. If a cell comparison expression evaluates True for multiple rules, only the first rule for which the expression evaluated as True is applied. The "up" and "down" buttons allow for a rule to be reordered.

Once all the rules have been set, you may click on the "Copy to clipboard" button to obtain the equivalent string that is used by the template editor.

In the lower section of the Conditional Formatting editor, you can test the rules you have set in a sample table. This section allows you to create a table with random values. You can change the column headers by double-clicking on them. You can also load your csv table directly to test via the "Load csv..." button. Remember to click on the Apply button to have the rules applied to the preview table every time you make a change.

Target expressions

The target expression can use the following Python functions:

row(Y)

Select which rows to apply the rule to. Y can be either a number corresponding to the 0-based row index or the row name as a string.  If the string name is used, fnmatch() compliant wildcard expressions can be used.

col(X)

Select which columns to apply the rule to. X can be either a number corresponding to the 0-based column index or the column name as a string.  If the string name is used, fnmatch() compliant wildcard expressions can be used.

Comparison expressions

The comparison expression is a general Python expression that must evaluate to True or False. For the cells for which it evaluates to True, the formatting expression will be evaluated. If you want all the cells from the Target to be evaluated, simply enter True as the comparison expression. Otherwise, standard python expressions are allowed.  For example:


(value > before()) and (value < 10.0)


is a valid Comparison entry. The comparison expression uses the following variables/functions:

value

Numeric value of the cell. Note, the value may be NaN.

svalue

String value of the cell if the cell contains a string element or the string representation of the cell numeric value.

min([name][,use_row=b])

Minimum value of the named row or column. If no name is specified, the row/column selected by the rule target expression will be used.

max([name][,use_row=b])

Maximum value of the named row or column. If no name is specified, the row/column selected by the rule target expression will be used.

avg([name][,use_row=b])

Average value of the named row or column. If no name is specified, the row/column selected by the rule target expression will be used.

var([name][,use_row=b])

Variance of the named row or column. If no name is specified, the row/column selected by the rule target expression will be used.

std([name][,use_row=b])

Standard deviation of the named row or column. If no name is specified, the row/column selected by the rule target expression will be used.

count([name][,use_row=b])

Number of non-NaN entries in the named row or column. If no name is specified, the row/column selected by the rule target expression will be used.

before()

Value of the cell at the left of the current cell if the Target is a column/ the value of the cell on the row above the current row if the Target is a row. If the source table is a string or a table, the value will be a string.

after()

Value of the cell at the right of the current cell if the Target is a column/ the value of the cell on the row below the current row if the Target is a row. If the source table is a string or a table, the value will be a string.

neighbor(name)

For a row Target, the value of the cell in the same column in the row selected by name. For a column Target, the value of the cell in the same row in the column selected by name. If the source table is a string or a table, the value will be a string.

In addition to the above, all of the functions in the Python math module are available as well as most of the functions in the str module. It is possible for any of the above functions to return NaN. The comparison expression: svalue.startswith("P") is a legal comparison expression for a string as well as isnan(value) for checking if a value is a NaN.

The mathematical reduction operations (min/max/avg/var/std/count) operate on a selected row or column of the table/tree.  By default, the array of data will match the row/column specification made by the target expression (e.g. if the target is row(1), the min() function will be performed on the row containing the current cell).  The optional 'name' parameter allows one to select a different row.  For example, with a row(1) target and min(3), the min will be computed on row 3. The use_row keyword can be used to override the default row/column selection.  For example, with a row(1) target and min(use_row=False), the  minimum of the column that contains the target cell will be computed. When computing these functions, all NaN values (and strings that cannot be converted to valid floats) will be ignored.

In the above table, 'name' is a row or column specification, following the same rules as the row() and col() functions in the target expression.  Names in square braces ('[name]') are optional and if not specified, the row/column will be the same as the current cell. For example:  var("Pressure*") would select all of the rows or columns that start with "Pressure".  If multiple rows/columns are selected by these expressions, the first one will be used.


Formatting expressions

For target cells whose comparison evaluates True, it is possible to set the formatting. In the format expression, one can enter one or more of the following (use a + sign in between objects if you want to set more than one):

pal(name, min, max, num_levels=None, forecolor=False)

Select a color based on interpolation into a color palette.  The name argument is a string naming which palette to use (if the name has the '-' prefix, the palette is reversed). The palettes from the plotly library are available. The min and max entry set the minimum and maximum values for the palette. The integer num_levels sets the number of levels to use. It is valid to enter a python expression for the min, max and num_levels argument, such as: pal('-picnic', min()+10., max()-before(), num_levels=count())
If the forecolor argument is set to True, then the coloration resulting from this palette will be applied to the font of the cell, not its background.
Note that only the name argument is needed: all other arguments are not mandatory and all arguments can be Python expressions building on the functions in the comparison expression table.

rgb(r, g, b, forecolor=False)

Set the color for the cell. If forecolor is set to False, the rgb color that is set will be applied to the background of the cell. If forecolor is set to True, it will be applied to the cell font.

contrast()

This function sets the text color to [0, 0, 0] or [1, 1, 1] based on what best contrasts the background color specified by rgb() or pal().  This should be the last function in the format expression.  If no background color is specified, the text color will be [0, 0,  0].

bold()

This function changes the text style to bold.

openparent()

Only applies to Tree items. If included in the expression, openparent() ensures the immediate parent node to the node being processed will be initially presented in 'expanded' form.

openparents()

Only applies to Tree items. If included in the expression, openparents() ensures the immediate parent node to the node being processed and all of its parents, continuing up the tree to the root node, will be initially presented in 'expanded' form.

In addition to the above, all of the functions in the Python math module are available as well as most of the functions in the str module. The functions/variables documented in the condition expressions may also be used in a formatting expression:


pal("picnic", avg("Pressure") - 2.0*std("Pressure"), avg("Pressure) + 2.0*std("Pressure"), 5) + bold() + contrast()


is a legal formatting expression.