User Defined Columns

New columns may be defined with expressions using existing column names given in brackets. For example:

 [sepallength] + 2 * [petallength]

These columns must have numeric values.

In the editor, entering [ (left bracket) with nothing or a blank following triggers a selection from available column names. Select using the arrow keys and press Enter to accept, or Esc to cancel.

Parentheses and Brackets

  • Use parentheses () to change precedence of evaluation for functions in infix notation, e.g., 4 * (3 + 5)

  • Also use parentheses () to provide arguments for functions in prefix notation. Inside the bracket there must be exactly one or two arguments (separated by comma), e.g., +(2,3), abs([price])

  • Use brackets [] to indicate column names, e.g., [price]

  • Since brackets are used to indicate column names, they are not allowed in column names. For example, [A], [return 3], [price_5?day=9] are allowed, but [col[temp]] is not allowed

Expression Rules

  • Expressions could be either infix notations 3 + 5 or prefix notations +(3,5). Mixed usage is also supported sqrt(5 - round([temp], 0))

  • For standard functions which take one argument, use prefix notation sqrt(4); infix notation sqrt 4 is not allowed

  • Infix notations are evaluated in a “higher precedence to lower, left to right” order. Functions wrapped in parentheses () are evaluated first

  • Prefix notations are evaluate in an “inside to outside” order

  • Expressions may be spread over several lines in the editor.

Standard Functions

  • A list of currently supported standard functions is below.

  • Aliases for functions are supported. For example, 3 + 5, 3 add 5, and 3 plus 5 all do the same thing

  • Functions can take either a column name or a number as its argument(s)

  • Each standard function has a pre-determined precedence. For example, in 1 + 2 3 the product function () will be evaluated first. To avoid ambiguity, it is recommended to add parentheses when necessary, or use prefix notations +(1,*(2,3)) instead

List of Functions

Function Name Aliases # Arguments Example Description
add plus,+ 2 add(3,5) = 8 Addition
subtract minus,- 2 subtract(5,3) = 2 Subtraction
product multiply,* 2 product(3,5) = 15 Multiplication
divide over,/ 2 divide(15,5) = 3 Division
modulo mod,% 2 modulo(20,3) = 2 Modulo
round 2 round(3.1415,3) = 3.142 Rounding to specified decimal places
floor 2 floor(3.1415,3) = 3.141 Rounding down to specified decimal places
ceiling 2 ceiling(3.1415,3) = 3.142 Rounding up to specified decimal places
maximum max,>. 2 maximum(3,5) = 5 Maximum between two numbers
minimum min,<. 2 minimum(3,5) = 3 Minimum between two numbers
greater > 2 greater(3,5) = FALSE Greater than
less < 2 less(3,5) = TRUE Smaller than
greatereq >= 2 greatereq(3,5) = FALSE Greater than or equal to
lesseq <= 2 lesseq(3,5) = TRUE Smaller than or equal to
negation 1 negation(5) = -5 0 - x
reciprocal 1 reciprocal(5) = 0.2 1 / x
sign 1 sign(5) = 1 Positive = 1, Zero = 0 , Negative = -1
base10 1 base10(10101) = 21 Convert binary number to decimal
base2 1 base2(21) = 10101 Convert decimal number to binary
eq equal,==,= 2 eq(3,5) = FALSE If two numbers are equal
neq notequal,!= 2 neq(3,5) = TRUE If two numbers are not equal
sin sine 1 sin(0) = 0 Sine function
cos cosine 1 cos(0) = 1 Cosine function
tan tangent 1 tan(0.7854) = 1 Tangent function
ln 1 ln(2.71829) = 1 Natural logarithm function
log 2 log(32,2) = 5 Logarithm with specified base
exp 1 exp(1) = 2.71829 Natural exponential function
power 2 power(2,5) = 32 Power with specified base
uniform 2 uniform(1,2) = 1.25643 Generate a uniformly distributed random number in range
abs 1 abs(-5) = 5 Absolute value function
odd 1 odd(5) = TRUE If odd integer
even 1 even(5) = FALSE If even integer
square 1 square(5) = 25 Square
sqrt 1 sqrt(25) = 5 Square root
and 2 and(1,0) = FALSE Logical AND
or 2 or(1,0) = TRUE Logical OR
nand 2 nand(1,0) = TRUE Logical NAND
nor 2 nor(1,0) = FALSE Logical NOR
iden 1 iden(1) = TRUE Logical identity
not 1 not(1) = FALSE Logical negation
isnull 1 isnull([1, ,3,4]) = [F,T,F,F] If NULL in data (corresponding to position)
replacenull 2 replacenull([1, ,3,4],0) = [1,0,3,4] Replace NULL in data by specified value
lag 2 lag([1,2,3,4],2) = [ , ,1,2] Lag data by specified steps, used for time series
lead 2 lead([1,2,3,4],2) = [3,4, , ] Lead data by specified steps, used for time series
index 1 index([100,101,102,103]) = [0,1,2,3] Generate row index for data
quantile cut 2 quantile([7,3,5,6,4],3) = [2,0,1,1,0] Assign quantiles into given number of partitions
pick 2 pick([7,3,5,6,4],3) = 6 Pick by position in list (first position is 0)
first 1 first([7,3,5,6,4]) = 7 Pick first position in column
last 1 last([7,3,5,6,4]) = 4 Pick last position in column
sumcol 1 sumcol([7,3,5,6,4]) = 25 Summation of all values in column