Derive column

fast step  derive

Derive a new column with a custom JS script.

Supports any JS script using ECMAScript 2020 syntax. The script should have a return clause returning either a value or null / undefined. The script has access to a row object that represent a row in the dataset and have the column names as keys. Lists are supported both as inputs and outputs. It's important to correctly manage null values by checking for null (e.g. if (row.col != null) { ... }) or using the JS optional chaining operator (?).

Usage

---

The following are the step's expected inputs and outputs and their specific types.

Step signature

derive_column(ds: dataset, {"param": value}) -> (new_col: column)

where the object {"param": value} is optional in most cases and if present may contain any of the parameters described in the corresponding section below.

Example

The following example joins all values in a list of numbers with '|' as separator:

Example call (in recipe editor)

derive_column(ds, {
  "script": "return row.numCol?.join(' | ');",
  "type": "text"
}) -> (ds.new_col)

More examples

The following example computes the sum for a list of numbers:

Example call (in recipe editor)

derive_column(ds, {
  "script": "return row.numCol?.reduce((sum, n) => sum + n, 0);",
  "type": "category"
}) -> (ds.new_col)

The following example adds a prefix to a category:

Example call (in recipe editor)

derive_column(ds, {
  "script": "return row.cat != null ? `Prefix_${row.cat}` : null;"
}) -> (ds.new_col)

The following example extracts a regex from a text:

Example call (in recipe editor)

derive_column(ds, {
  "script": "return row.text?.match(/\d+/);",
  "type": "category"
}) -> (ds.new_col)

The following example extracts the domain from a URL column:

Example call (in recipe editor)

derive_column(ds, {
  "script": "return row.url != null ? new URL(row.url).hostname } : null;",
  "type": "category"
}) -> (ds.new_col)

The following example extracts the year component from a Date column:

Example call (in recipe editor)

derive_column(ds, {
  "script": "return row.dateCol != null ? new Date(row.dateCol).getUTCFullYear() : null;",
  "type": "number"
}) -> (ds.new_col)

Inputs

---

ds: dataset

An input dataset.

Outputs

---

new_col: column

The column resulting from evaluating the script.

Parameters

---

script: string

The javascript code to execute.

Example parameter values:

• For example, to multiply by 2 every row with a value:

  "return row.num * 2;"
  

---

type: string

Output column type. Select the desired type using a shortened yet fully specified name.

Must be one of: "boolean", "category", "date", "number", "text", "url", "list[number]", "list[category]", "list[url]"