Skip to main content

Add new column

Adds a column of the specified type to the current table and initializes it using the specified expression ( mathematical function, constants, platform object properties, and functions).

To add new columns, click on the Add New Column icon on the toolbar or go to top menu Edit -> Add New Column. Key features are:

  • Support for functions implemented with Python, R, Julia, JavaScript, C++, and others. To add function to an editor, type it manually, drag and drop from functions registry on the right or use plus icon. You can combine functions written in different languages in one formula. add function
  • Auto-suggested functions based on the column type and semantic type. To use:
    • set functions sorting type in function registry to 'By relevance'
    • select column of interest
    • the functions in functions registry are sorted automatically. More relevant functions are on top of the list.
    • drag and drop function to the editor field or click plus icon. Corresponding parameter is prefilled automatically with selected column. functions suggestions
  • Interactive preview of results as you type
  • Autocompletion for functions (including packages names) and columns. Suggestions appear as you type. The highlighted function shows its signature and description, and the inserted function uses its parameter names as placeholders.
  • Table and column selectors. When an argument takes a table or a column, the open tables or the table's columns are offered as soon as you type the opening parenthesis or a comma.
  • Help for the function under the cursor. The line below the editor shows the signature and description of the function you are in, and clicking a function name shows its details in the Context Panel.
  • Different highlights within the formula for better readability. For instance, column names are highlighted in bold blue font.
  • Validation against various types of mistakes including syntax errors, missing columns detection, incorrect data types, unmatching brackets.
  • Resulting column type autodetection
  • Fast function and column search
  • History, saving and reusing formulas

Adding columns to formulas:

  • scalar functions

    • To reference each row of a column, specify its name in the curly brackets, preceded by the dollar sign: ${Width}. For example you can use this expression in function like that: Round(${Width}).
    • To reference a whole column, specify its name in the square brackets, preceded by the dollar sign: $[Width]. For example you can use this expression in function like that: Avg($[Width]).
    • To reference tables and columns by name, including other open tables, use Table([tableName]), Column(columnName, [tableName]), and Value(columnName, [row], [tableName]). The table name defaults to the current table, and the row to the current row: Avg(Column("Width", "other table")), Table("other table").rowCount, Value("Width", 0). When you type the opening parenthesis, the dialog offers the open tables or the table's columns. See Table and column functions for lookups, running totals and moving averages.

  • vector function

    • To reference a whole column, specify its name in the curly brackets, preceded by the dollar sign: ${molecule}. For example you can use this expression in function like that: Chem:getInchis(${molecule}).

Tip: Some vector functions can return several related columns at once (for example, multiple chemical or statistical properties). These are called complex calculated columns.
When you use such a function in the “Add New Column” dialog, Datagrok automatically adds all resulting columns to your table and keeps them synchronized.

To add a column to a formula, drag it to the editor. Alternatively, use the keyboard:

  1. Open a column list popup by pressing '$'.
  2. Select the column you want using the up and down arrows, then press Enter.

For formulas where row index is required, row variable is available.

Example:

1.57 * RoundFloat(${Weight}, 2) / Avg($[Weight]) - log(${IC50} * PI)

To treat data as strings use quotes, for example:

"Police" + "man"    // "Policeman"

The platform supports a large number of functions, constants and operators. You can find out about them in the corresponding sections of the help system:

Rows that fail​

A formula can fail on some rows, for example when DateParse(${Sample Date}) meets "n/a". Such rows stay empty. To change what happens to them, click the gear icon next to the column type:

  • If a row fails: Leave empty, or Use value to fill the failed rows with a value of the column type.
  • Error column: adds a string column with each failed row's message next to the result.

The gear turns blue when either is set. Then the line below the editor tells you how many preview rows failed and shows the first message, and with an error column, a warning after you click OK reports how many rows failed. Click Change... to edit the setting. The setting is saved with the column, so recalculations, layouts, and projects keep it.

From JavaScript, pass onError to addNewCalculated. {mode: 'stop'} rejects the call on the first failed row and adds no column.

await df.columns.addNewCalculated('parsed', 'DateParse(${Sample Date})',
  {type: 'datetime', onError: {mode: 'value', value: dayjs.utc('1900-01-01'), errorColumn: true}});

Videos​

Add New Columns

See also: