Skip to main content

Formula syntax

Datagrok uses a unified formula syntax across the platform for calculated columns, filters, and other expressions.

Referencing data in formulas​

Current row value​

Use ${ColumnName} to reference the value in the current row.
This is the most common case, when a formula is evaluated independently for each row.

The same syntax is used when passing a column to functions that process all values at once and return a result for each row, such as formula equalities.

${AGE} > 18
${PRICE} * ${QUANTITY}
${Chemical Space Y} = ln(${Chemical Space X}) + 5  // formula line equality

Entire column​

Use $[ColumnName] to reference all values in a column at once. This is used when the formula needs column-level information, such as averages, totals, or other aggregated values.

${IC50} / Avg($[IC50])   
${VALUE} > Median($[VALUE])

Row index​

Use row when the formula explicitly requires the row index.

row % 2 == 0

Tables and columns by name​

Use table and column functions to reference a column or a table by name, including other open tables, to look a value up by key, or to compute a running value over a column. The table name is optional and defaults to the table the formula runs on.

${quantity} * Lookup("products", "id", ${productId}, "price")   // Value from another table, matched by key
Avg(Column("price", "products"))                                // Whole column of another table
CumSum(${amount}) / Sum($[amount])                              // Running share of the total
${price} - MovingAvg(${price}, 7)                               // Deviation from the 7-row trailing average
RowCount("products")

Operators, constants, and literals​

Operators​

Datagrok supports arithmetic, comparison, logical, and membership operators.
You can use them with numbers, logical values (true, false), constants like PI and E, function results, or current row values ${ColumnName}.

You can combine operators and functions to build complex expressions.
Use parentheses () to control the order of evaluation.

Sin(PI / 6) * (17 - ${LENGTH}) < 9    // The result is a boolean value

Each operator also has a corresponding function, so you can choose whichever form is more convenient.

Reference: list of operators

Here A is the left operand of the operator and the B is the right operand.

OperatorDescriptionSimilar Function
/The result of dividing A by BDiv(A, B)
*The product of A and BMul(A, B)
%The remainder of dividing A by BMod(A, B)
^Returns A to the power of BPow(A, B)
+The sum of two numbers A and BAdd(A, B)
-The difference between A and BSub(A, B)
==True if A equal to B and false otherwiseEq(A, B)
!=False if A equal to B and true otherwiseNotEq(A, B)
>True if A is greater than B and false otherwiseGreater(A, B)
<True if A is less than B and false otherwiseSmaller(A, B)
>=True if A is greater than or equal to B and false otherwiseNotSmaller(A, B)
\<=True if A is less than or equal to B and false otherwiseNotGreater(A, B)
andLogical conjunction of boolean A and BAnd(A, B)
&&Logical conjunction of boolean A and BAnd(A, B)
orLogical disjunction of boolean A and BOr(A, B)
xorLogical exclusive disjunction of boolean A and BXor(A, B)
notLogical negation of the BNot(B)
!Logical negation of the BNot(B)
inIn operator, A in [A,B] returns trueIn(A, B)

Constants​

Datagrok provides predefined constants that you can use in formulas by simply writing their names.

E * ln(${VALUE}) / SQRT2
Reference: list of constants
NameDescriptionValue
EEuler's number2.718281828459045
LN2Natural logarithm of 20.6931471805599453
LN10Natural logarithm of 102.302585092994046
LOG2EBase-2 logarithm of E1.4426950408889634
LOG10EBase-10 logarithm of E0.4342944819032518
PIPi3.1415926535897932
SQRT1_2Square root of 1/20.7071067811865476
SQRT2Square root of 21.4142135623730951

String literals​

String values can be written using single or double quotes. Both forms are supported and interchangeable.

'Medium'
"Medium"

Whitespace and line breaks​

Spaces around operators are optional, and formulas can include line breaks. Whitespace and formatting do not affect how formulas are evaluated.

The following examples are valid and equivalent:

${A}+${B}

${A} + ${B}

${A}
+
${B}
in(20,60,40)
in(20, 60, 40)

Functions​

Built-in functions​

Datagrok provides built-in functions, including calculations, text manipulation, statistical analysis, date/time operations, conversions, binning, timespan, and table and column operations.

RoundFloat(${IC50} / Median($[IC50]) * E, 3)

Custom functions​

Datagrok allows you to create custom functions in Python, R, Julia, JavaScript, C++, and other languages, as long as they are properly annotated.
See Function annotations for details.

Functions can be written as standalone scripts or included in packages.

Package function​

Use PackageName:FunctionName to call package function.

Chem:getInchis(${Structure})

User-defined function​

Use UserLogin:FunctionName to call user-defined function.

The following example shows a custom function written in Python:

#name: Len
#language: python
#input: string s
#output: int length

length = len(s)

Once the script is saved, the function can be called in formulas like this:

UserName:Len(${ColumnName})

Calculated columns​

Calculated columns generate new columns based on formulas. They can reference other calculated columns and update automatically when the source data changes.

The Add New Column dialog supports creating calculated columns with real-time preview, autocomplete, and formula validation, including checks for syntax errors, missing columns, and type mismatches.
See Add New Column for details.

In-viewer filter expressions can be edited in the Edit Formula dialog, which provides the same enhanced functionality as Add New Column.

Complex calculated columns (multiple outputs)​

In some cases, a single formula can produce multiple columns at once. This is useful when a function returns several related results that should stay synchronized with the source data. When used from Add New Column, all resulting columns are created automatically and remain synchronized with the source data.
For details, see Сomplex calculated columns.

See also​