GeinQSAR Studio
Home › Formula columns

Formula columns

Columns computed from other columns with a small expression language: ligand efficiencies, unit conversions, classes for a classifier, combined scores.

A formula column holds the result of an expression evaluated for every row, such as [pIC50] - [Wildman-Crippen logP]. It is recalculated when a value it reads is edited, when descriptors are recomputed, and on Data > Recompute formulas (F5). A formula column behaves like any other numeric column: it can be sorted and filtered, used as the activity of a model, exported, and read by another formula.

Adding a formula column

Data > Add formula column, or Formula on the Data tab, opens the editor. To change a formula later, right-click the header of its column and choose Edit formula, or use the gear button in the column manager; the dialog is then called Edit formula column and its button Apply.

The empty editor on the steroid dataset. The Column title and Units boxes are at the top, the Expression box shows an example as its placeholder, the Columns list on the left holds the numeric columns of the dataset (Flag, pK_CBG, pK_TBG, pK_CBG_coats_eq1, Wildman-Crippen logP), and the Preview on the right says No expression.
The empty editor on the steroid dataset. The Column title and Units boxes are at the top, the Expression box shows an example as its placeholder, the Columns list on the left holds the numeric columns of the dataset (Flag, pK_CBG, pK_TBG, pK_CBG_coats_eq1, Wildman-Crippen logP), and the Preview on the right says No expression.
ControlMeaning
Column titleThe name of the new column. Left empty, it is called Formula.
UnitsOptional; shown in the header tooltip and in exported files.
ExpressionThe formula. It is checked as you type; the help mark next to the label lists the operators and functions.
ColumnsThe numeric columns of the dataset, with their units. A click inserts a reference to the column at the end of the expression. Find a column narrows the list, which helps among hundreds of descriptors.
PreviewThe result for the first six rows, labelled by name. n/a marks a row where the result is missing.
A valid expression, pActivity([IC50 (nM)], 'nM'). The line under the box reads Valid · 1 column reference, and the preview shows acetic acid 7, benzene 6, toluene 7.398, and n/a for three compounds without an IC50.
A valid expression, pActivity([IC50 (nM)], "nM"). The line under the box reads Valid · 1 column reference, and the preview shows acetic acid 7, benzene 6, toluene 7.398, and n/a for three compounds without an IC50.
An expression that names a column the dataset does not have. The message unknown column 'No such column' appears in red under the box and the preview stays empty; Add column refuses until the error is fixed.
An expression that names a column the dataset does not have. The message unknown column 'No such column' appears in red under the box and the preview stays empty; Add column refuses until the error is fixed.

Referring to columns

Write a column title in square brackets: [IC50 (nM)], [Topological polar surface area (N,O)]. The title is matched without regard to case. A single word made of letters, digits, underscores and full stops can be written without brackets, so MW * 2 and [MW] * 2 are the same; anything with a space, a bracket, a comma or a sign needs the brackets.

Operators

OperatorsMeaning
+ - * /Arithmetic.
%Remainder of a division.
^Power; it binds from right to left, so 2^3^2 is 29.
< <= > >= == !=Comparisons, yielding 1 or 0. = is accepted for == and <> for !=.
&& || !And, or, not. Any value other than 0 counts as true.
( )Grouping.

The order of precedence, from the tightest, is: unary minus and !, then ^, then * / %, then + -, then the comparisons, then &&, then ||. Use brackets when in doubt.

Functions

FunctionMeaning
abs(x), sqrt(x), cbrt(x), exp(x)Absolute value, square root, cube root, ex.
ln(x), log10(x), log2(x)Natural, decadic and binary logarithm.
log(x, base)Logarithm to any base. With one argument, log(x) is the natural logarithm, not the decadic one.
pow(x, y), mod(x, y)Power and remainder, as functions.
round(x), round(x, digits)Round to a whole number or to that many decimals.
floor(x), ceil(x), trunc(x), sign(x)Round down, round up, cut off the decimals, the sign (-1, 0 or 1).
min(...), max(...), mean(...), sum(...)Over the listed arguments, up to 64. Missing arguments are left out; the result is missing only when all are. avg is the same as mean.
if(condition, a, b)a when the condition is true, otherwise b; missing when the condition is missing.
clamp(x, lo, hi)x limited to the range from lo to hi.
isnan(x), isfinite(x)1 when x is missing, or when it is an ordinary number; otherwise 0.
nvl(x, fallback)x, or the fallback when x is missing. ifnull is the same.
atan2(y, x)The angle of the point (x, y), in radians.
pActivity(c, "unit")-log10 of the molar concentration c given in the unit, one of "M", "mM", "uM" (or µM), "nM", "pM", "fM". Without a unit, c is taken as nanomolar. pIC50, pEC50, pKi and pKd are the same function under other names.

Function names are not case sensitive. The constants pi, e, nan (a missing value), inf, true (1) and false (0) are available.

Missing values

Arithmetic with a missing value gives a missing value, so a row without the input stays empty rather than showing a wrong number. A result that is not a finite number, such as the logarithm of zero or a division by zero, is also stored as missing.

A comparison with a missing value is false, not missing. if([pIC50] >= 7, 1, 0) gives 0 for a compound without a pIC50, which a classifier would read as "inactive". Write if(isnan([pIC50]), nan, if([pIC50] >= 7, 1, 0)) to keep such rows empty.

Examples

The examples assume the columns they name exist. The descriptor titles are those of the Physicochemical properties block (Descriptors > Physicochemical properties) and, for the heavy-atom count, the 2D descriptor set.

PurposeExpression
pIC50 from an IC50 in nanomolarpIC50([IC50 (nM)])
pKi from a Ki in micromolarpKi([Ki], "uM")
A micromolar column in nanomolar[IC50 (uM)] * 1000
Lipophilic ligand efficiency, LLE = pIC50 - logP[pIC50] - [Wildman-Crippen logP]
Ligand efficiency in kcal/mol per heavy atom, LE = 1.37 pIC50 / HA1.37 * [pIC50] / [Number of heavy atoms]
Mean of replicate measurements, ignoring missing runsmean([pIC50 run 1], [pIC50 run 2], [pIC50 run 3])
Spread of the replicatesmax([pIC50 run 1], [pIC50 run 2]) - min([pIC50 run 1], [pIC50 run 2])
Active (1) or inactive (0) at 1 µM, for a classifier, keeping unmeasured compounds emptyif(isnan([pIC50]), nan, [pIC50] >= 6)
A polar-surface cut-off as a 0/1 columnif([Topological polar surface area (N,O)] > 140, 1, 0)
Selectivity over an anti-target, in log units[pIC50 target] - [pIC50 off-target]
Activity rounded to two decimalsround([pIC50], 2)
A censored range pulled into the assay windowclamp([pIC50], 4, 10)
A missing logP replaced by the series mediannvl([Wildman-Crippen logP], 2.5)
Use a formula column, not an edited copy of a column, whenever a value is derived from another. The formula records how the value was obtained, it follows corrections of its inputs, and it travels with the dataset in a saved project.