# 9. Column Data Functions and Equations¶

## 9.1. Column Formulas¶

### Introduction¶

Columns may contain formulas that are evaluated on-demand and result in a value.

### Syntax¶

#### Basic Syntax¶

A formula always starts with an equals sign ‘=’. This signifies that the value in the table cell is a formula.

Example: **=ABS($otherColumn + LOG(10.2))**

The type of the value returned by a formula must match the column type, or be easily converted to the column type. For example, if a formula returns a numeric value in a string column, the numeric value will be converted to a string. However if a formula returns a string value in a numeric column, the string cannot be automatically converted to a number, so it will result in an error being shown.

All numeric values will be accepted for a boolean (or logical) column data where
non-zero will be interpreted as **true** and zero as **false**.
Floating point values will be converted using the rules
of the Excel(tm) **INT** function. Parentheses can be used for grouping
and to change evaluation order.

#### Functions¶

Cytoscape provides a set of functions that perform specific tasks and calculations. A function is written as the name of the function, then an opening bracket ‘(’, then an optional comma-separated list of arguments, then a closing bracket ‘)’.

Example: **=MAX(10, 20)**

Example: **=IF($otherColumn, “UP”, “DOWN”)**

Example: **=NOW()**

#### Attributes¶

Attributes are named references to other columns in the same table as the formula, and result in the value of the column cell for the same node, edge or network.

An attribute reference is written by placing the column name after a dollarsign ($).

Example: **$columnName**

If the column name contains spaces, special characters or a namespace identifier then the name must be placed between curly braces.

Example: **${column name with spaces}**

Example: **${namespace::columnName}**

Special characters such as commas must be escaped with a leading backslash.

Example: **${name with \, comma}**

You may provide a default value that will be used if the column value is blank. Place a colon (:) and the value after the column name.

Example: **${columnName:0.0}**

Finally, column names are case sensitive.

#### Operators¶

An operator is written between two operands.

Example: **=$x + 1**

Numeric operators: **+** addition, **-** subtraction, ***** multiplication, **/** division, **^** exponentiation

Text operators: **&** string concatenation

Logical operators (operate on boolean values true/false): **<** less than, **>** greater than, **>=** greater than or equal,
**<=** less than or equal, **=** equal, **<>** not equal

The operator precedence rules follow those of standard arithmetic.

#### Literal Values¶

String (text) literals are between double quotes, example: **“abc”**

Numeric literals, example: **123**

Floating point literals, example: **123.45**

Boolean (logical) literals: **true, false**

In order to embed a double-quote or a backslash in a string they have to be escaped with a
leading backslash, therefore the string **“\”** must be written as
**“\”\\”**.

#### Conditional IF¶

A conditional is written as a function named ‘IF’ with three arguments: **IF(condition, a, b)**.
If the condition evaluates to true then the value of the **a** argument is returned, otherwise the **b** argument is returned.

Example: **IF($x = $y, “equal”, “different”)**

### Supported Functions¶

Currently we support the following functions:

#### Cytoscape-specific functions¶

- Degree – the degree of a node, meaning how many edges connect to it.
- InDegree – the indegree of a node.
- OutDegree – the outdegree of a node.
- SourceID – the ID of the source node of an edge.
- TargetID – the ID of the target of an edge.

#### Numeric Functions¶

- Abs – Returns the absolute value of a number.
- ACos – Returns the arccosine of a number.
- ASin – Returns the arcsine of a number.
- ATan2 – Returns the arctangent of two numbers x and y.
- Average – Returns the average of a group of numbers.
- Cos – Returns the cosine of an angle given in radians.
- Cosh – Returns the hyperbolic sine of its argument.
- Count – Returns the number of numeric values in a list.
- Degrees – Returns its argument converted from radians to degrees.
- Exp – Returns e raised to a specified number.
- Ln – Returns the natural logarithm of a number.
- Log – Returns the logarithm of a number to a specified base.
- Max – Returns the maximum of a group of numbers.
- Median – Returns the median of a list of numbers.
- Min – Returns the minimum of a group of numbers.
- Mod – Calculates the modulus of a number.
- Pi – Returns an approximation of the value of p.
- Radians – Returns its argument converted from degrees to radians.
- Round – Rounds a number to a specified number of decimal places.
- Sin – Returns the sine of an angle given in radians.
- Sinh – Returns the hyperbolic sine of its argument.
- Sqrt – Calculates the square root of a number.
- Tan – returns the tangent of its argument in radians.
- Tanh – returns the hyperbolic tangent of its argument in radians.
- Trunc – Truncates a number.

#### String Functions¶

- Concatenate – Concatenates two or more pieces of text.
- Left – Returns a prefix of s string.
- Len – Returns the length of a string.
- Lower – Converts a string to lowercase.
- Mid – Selects a substring of some text.
- Right – Returns a suffix of a string.
- Substitute – Replaces some text with other text.
- Text – Format a number using the Java
*DecimalFormat*class’ conventions. - Upper – Converts a string to uppercase.
- Value – Converts a string to a number.

#### Logical/Boolean Functions¶

- And – Returns the logical conjunction of any number of boolean values.
- Not – Returns the logical negation of a boolean value.
- Or – Returns the logical disjunction of any number of boolean values.

#### List Functions¶

- First – Returns the first entry in a list.
- Last – Returns the last entry in a list.
- Nth – Returns the n-th entry in a list.

#### Statistical Functions¶

- Largest – the kth largest value in a list.
- GeoMean – the geometric mean of a set of numbers.
- HarMean – the harmonic mean of a set of numbers.
- Mode – the mode of a set of numbers.
- NormDist – Returns the pdf or CDF of the normal distribution.
- Permut – Returns the number of permutations for a given number of objects.
- StDev - sample standard deviation.
- Var – sample variance.

#### Miscellaneous Functions¶

- Combin - Returns the number of combinations for a given number of objects.
- If – Returns one of two alternatives based on a boolean value.
- ListToString – Returns a string representation of a list.
- Now – Returns a string representation of the current date and time.
- Today – returns a string representation of the current date.

## 9.2. The Formula Builder¶

In order to ease the creation of formulas as well as to facilitate
discovery of built-in functions we provide a **Formula Builder** in the
Table Panel. The Formula Builder
is opened by clicking the
button.

This should bring up the Formula Builder which looks like this:

At the top is a text area called the **Formula Editor**. The text for a formula
may be typed directly into this area. At the top-right of the text area are buttons
for **Undo** and **Redo**.

When the formula is ready click the **Insert Formula** button to insert the formula
into the table.
Depending on what is selected in the combo box, the formula can be inserted
into the selected cell only, the entire column, or just the cells for nodes/edges
that are currently selected.
If there is a syntax error in the formula it will be reported immediately when the
**Insert Formula** button is clicked.

Normally formulas are re-evaluated on demand as needed. For example if a formula contains
an attribute reference to another column, and the value in that column changes, then
the formula is re-evaluated. This re-evaluation sometimes has disadvantages, for example re-evaluation
can sometimes be slow for very large networks. Instead of inserting the formula itself
you may click the **Evaluate and Insert Result** button. This will evaluate the formula
immediately and just insert the result into the selected cells.

Below the **Formula Editor** is an area used for documentation.

- The
**Functions**area lists all functions that are available. Click on a function name to view documentation on what the function does and how it can be used. Click the**insert**link in the documentation area to insert the function into the**Formula Editor**at the location of the cursor. - The
**Attributes**area lists all available columns in the current table that can be referenced from the formula. Click the**insert**link in the documentation area to insert an attribute reference into the Formula Editor at the location of the cursor. - The
**Syntax**area provides documentation on formula syntax.

## 9.3. A Note for App Writers¶

It is relatively easy to add your own built-in formula functions. A
simple function can probably be implemented in 15 to 20 minutes. It can
then be registered via the parser and becomes immediately available to
the user. It will be available in the in the **Formula Builder**.