# User Defined Columns

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

` [sepal`*length] + 2 * [petal*length]

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 allowedInfix notations are evaluated in a “higher precedence to lower, left to right” order. Functions wrapped in parentheses

`()`

are evaluated firstPrefix 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 thingFunctions 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 |