Steps definition

This document describes the steps that can be used in our pipelines. A step is the unit of transformation generated by a user interaction with the visual query builder.

Unless explicitly specified, all steps are supported by all backends.

absolutevalue step

This step is meant to compute the absolute value of a given input column.

{
    name: 'absolutevalue',
    column: 'my-column'
    new_column: 'my-new-column'
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'absolutevalue',
  column: "Value",
  new_column_name: "My-absolute-value",
}

Output dataset:

addmissingdates step

Add missing dates as new rows in a dates column. Exhaustive dates will range between the minimum and maximum date found in the dataset (or in each group if a group by logic is applied - see thereafter).

Added rows will be set to null in columns not referenced in the step configuration.

You should make sure to use a group by logic if you want to add missing dates in independent groups of rows (e.g. you may need to add missing rows for every country found in a “COUNTRY” column). And you should ensure that every date is unique in every group of rows at the specified granularity, else you may get inconsistent results. You can specify “group by” columns in the groups parameter.

An addmissingdates step has the following structure:

{
  name: 'addmissingdates',
  datesColumn: 'DATE', // the dates columns with missing rows
  datesGranularity: 'day', // the granularity of the date column (day, month or year)
  groups: ['COUNTRY'] // (optional) columns used for group by logic if necessary
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: day granularity without groups

Input dataset:

Here the day “2018-01-06” is missing.

Step configuration:

{
  name: 'addmissingdates',
  datesColumn: 'DATE',
  datesGranularity: 'day'
}

Output dataset:

Example 2: day granularity with groups

Input dataset:

Here the day “2018-01-06” is missing for “France” rows, and “2018-01-11” and “2018-01-11” are missing for “USA” rows.

Note that “2018-01-12” will not be considered as a missing row for “France” rows, because the latest date found for this group of rows is “2018-01-11” (even though “2018-01-12” is the latest date found for “USA” rows).

Step configuration:

{
  name: 'addmissingdates',
  datesColumn: 'DATE',
  datesGranularity: 'day',
  groups: 'COUNTRY'
}

Output dataset:

Example 3: month granularity

Input dataset:

Here “2019-05” and “2019-11” are missing.

Step configuration:

{
  name: 'addmissingdates',
  datesColumn: 'DATE',
  datesGranularity: 'month',
}

Output dataset:

aggregate step

Perform aggregations on one or several columns. Available aggregation functions are sum, average, count, count distinct, min, max, first, last.

An aggreation step has the following structure:

{
   name: 'aggregate',
   on: ['column1', 'column2'],
   aggregations:  [
    {
      newcolumns: ['sum_value1', 'sum_value2'], // same length as 'columns'
      aggfunction: 'sum', // the aggregation function that will be applied to the specified columns
      columns: ['value1', 'value2']
    }
    {
      newcolumns: ['avg_value1'],
      aggfunction: 'avg',
      columns: ['value1']
    }
    // ...
  ]
  keepOriginalGranularity: false, // whether to keep the original granularity, in that
                                  // case computed aggregations will be added in new columns
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Deprecation note:

The column and newcolumn (in the singular) properties of the aggregation parameter are deprecated and are supported only for retrocompatibility purposes. When this step was first created, only 1 column at a time could be aggregated for a given aggregation. Now columns and newcolumns are lists of columns, allowing to apply the same aggregation function to several columns at once.

An old-fashioned step looked like this:

{
   name: 'aggregate',
   on: ['column1', 'column2'],
   aggregations:  [
    {
      newcolumn: 'sum_value1'
      aggfunction: 'sum',
      column: 'value1'
    }
    {
      newcolumn: 'sum_value2',
      aggfunction: 'sum',
      column: 'value2'
    }
    {
      newcolumn: 'avg_value1',
      aggfunction: 'avg',
      column: 'value1'
    }
    // ...
  ]
}

Example 1: keepOriginalGranularity set to false

Input dataset:

Step configuration:

{
  name: 'aggregate',
   on: ['Group'],
   aggregations:  [
    {
      newcolumns: ['Sum-Value1', 'Sum-Value2'],
      aggfunction: 'sum',
      columns: ['Value1', 'Value2']
    }
    {
      newcolumns: ['Avg-Value1'],
      aggfunction: 'avg',
      columns: ['Value1']
    }
  ],
  keepOriginalGranularity: false,
}

Output dataset:

Example 2: keepOriginalGranularity set to true

Input dataset:

Step configuration:

{
  name: 'aggregate',
   on: ['Group'],
   aggregations:  [
    {
      newcolumns: ['Total'],
      aggfunction: 'sum',
      columns: ['Value']
    }
  ],
  keepOriginalGranularity: true,
}

Output dataset:

append step

Appends to the current dataset, one or several datasets resulting from other pipelines. WeaverBird allows you to save pipelines referenced by name in the Vuex store of the application. You can then call them by their unique names in this step.

{
  name: 'append',
  pipelines: ['pipeline1', 'pipeline2'] // the name of the pipelines to append
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

dataset1 (saved in the application Vuex store):

dataset2 (saved in the application Vuex store):

Step configuration:

{
  name: 'append',
  pipelines: ['dataset1', 'dataset2']
}

Output dataset:

argmax step

Get row(s) matching the maximum value in a given column, by group if groups is specified.

{
  name: 'argmax',
  column: 'value', // column in which to search for max value
  groups: ['group1', 'group2']
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: without groups

Input dataset:

Step configuration:

{
  name: 'argmax',
  column: 'Value'
}

Output dataset:

Example 2: with groups

Input dataset:

Step configuration:

{
  name: 'argmax',
  column: 'Value',
  groups: ['Group']
}

Output dataset:

argmin step

Get row(s) matching the minimum value in a given column, by group if groups is specified.

{
  name: 'argmin',
  column: 'value', // column in which to search for max value
  groups: ['group1', 'group2'] // optional
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: without groups

Input dataset:

Step configuration:

{
  name: 'argmin',
  column: 'Value'
}

Output dataset:

Example 2: with groups

Input dataset:

Step configuration:

{
  name: 'argmin',
  column: 'Value',
  groups: ['Groups']
}

Output dataset:

comparetext step

Compares 2 string columns and returns true if the string values are equal, and false oteherwise. The comparison is case-sensitive (see examples below).

{
  name: 'comparetext',
  newColumnName: 'NEW', // new column to be created, with the comparison result
  strCol1: 'TEXT_1', // first string column to compare
  strCol2: 'TEXT_2', // second string column to compare
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'split',
  newColumnName: 'RESULT',
  ctrCol1: 'TEXT_1',
  ctrCol2: 'TEXT_2',
}

Output dataset:

concatenate step

This step allows to concatenate several columns using a separator.

{
  name: 'concatenate',
  columns: ['Company', 'Group']
  separator: ' - ' // can be a string of any length
  new_column_name: 'Label' // The new column in which to write the concatenation
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'concatenate',
  columns: ['Company', 'Group']
  separator: ' - '
  new_column_name: 'Label'
}

Output dataset:

convert step

This step allows to convert columns data types.

{
  name: 'convert',
  columns: ['col1', 'col2']
  data_type: 'integer' // The data type to convert the column into. Can be either
                       // 'integer', 'float', 'text', 'date' or 'boolean'
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Pandas (python)
• Snowflake

In a effort to harmonize as much as possible the conversion behaviors, for some cases, the Sql translator implements casting otherwise than the CAST AS method.

Precisely, when casting float to integer, the default behaviour rounds the result, other languages truncate it. That’s why the use of TRUNCATE was implemented when converting float to int. The same implementation was done when converting strings to int (for date represented as string). As for the conversion of date to int, we handled it by assuming the dataset’s timestamp is in TIMESTAMP_NTZ format.

Example

Input dataset:

Step configuration:

{
  name: 'convert',
  columns: ['Value']
  data_type: 'integer'
}

Output dataset:

cumsum step

This step allows to compute the cumulated sum of value columns based on a reference column (usually dates) to be sorted by ascending order for the needs of the computation. The computation can be scoped by group if needed.

The toCumSum parameter takes as input a list of 2-elements lists in the form [‘valueColumn’, ‘newColumn’].

{
  name: 'cumsum',
  toCumSum: [['myValues', 'myCumsum']],
  referenceColumn: 'myDates',
  groupby: ['foo', 'bar'],
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: Basic usage

Input dataset:

Step configuration:

{
  name: 'cumsum',
  toCumSum: [['VALUE', '']]
  referenceColumn: 'DATE',
}

Output dataset:

Example 2: With more advanced options

Input dataset:

Step configuration:

{
  name: 'cumsum',
  toCumSum: [['VALUE', 'MY_CUMSUM']],
  referenceColumn: 'DATE',
  groupby: ['COUNTRY'],
}

Output dataset:

custom step

This step allows to define a custom query that can’t be expressed using the other existing steps.

{
    name: 'custom',
    query: '$group: {_id: ...}'
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6

Example: using Mongo query language

Input dataset:

Step configuration:

{
  name: 'custom',
  query: '$addFields: { Label: { $concat: [ "$Label", " - ", "$Group" ] ] } }'
}

Output dataset:

dateextract step

Extract date information (eg. day, week, year etc.). The following information can be extracted:

• year: extract ‘year’ from date,
• month: extract ‘month’ from date,
• day: extract ‘day of month’ from date,
• week': extract ‘week number’ (ranging from 0 to 53) from date,
• quarter: extract ‘quarter number’ from date (1 for Jan-Feb-Mar)
• dayOfWeek: extract ‘day of week’ (ranging from 1 for Sunday to 7 for Staurday) from date,
• dayOfYear: extract ‘day of year’ from date,
• isoYear: extract ‘year number’ in ISO 8601 format (ranging from 1 to 53) from date.
• isoWeek: extract ‘week number’ in ISO 8601 format (ranging from 1 to 53) from date.
• isoDayOfWeek: extract ‘day of week’ in ISO 8601 format (ranging from 1 for Monday to 7 for Sunday) from date,
• firstDayOfYear: calendar date corresponding to the first day (1st of January) of the year ,
• firstDayOfMonth: calendar date corresponding to the first day of the month,
• firstDayOfWeek: calendar date corresponding to the first day of the week,
• firstDayOfQuarter: calendar date corresponding to the first day of the quarter,
• firstDayOfIsoWeek: calendar date corresponding to the first day of the week in ISO 8601 format,
• currentDay: calendar date of the target date,
• previousDay: calendar date one day before the target date,
• firstDayOfPreviousYear: calendar date corresponding to the first day (1st of January) of the previous year,
• firstDayOfPreviousMonth: calendar date corresponding to the first day of the previous month,
• firstDayOfPreviousWeek: calendar date corresponding to the first day of the previous week,
• firstDayOfPreviousQuarter: calendar date corresponding to the first day of the previous quarter,
• firstDayOfPreviousISOWeek: calendar date corresponding to the first day of the previous ISO week,
• previousYear: extract previous ‘year number’ from date
• previousMonth: extract previous ‘month number’ from date
• previousWeek: extract previous ‘week number’ from date
• previousQuarter: extract previous ‘quarter number’ from date
• previousISOWeek: extract previous ‘week number’ in ISO 8601 format (ranging from 1 for Monday to 7 for Sunday) from date
• hour: extract ‘hour’ from date,
• minutes: extract ‘minutes’ from date,
• seconds: extract ‘seconds’ from date,
• milliseconds: extract ‘milliseconds’ from date,

Here’s an example of such a step:

{
  name: 'dateextract',
  column: 'date',
  dateInfo: ['year', 'month', 'day'],
  newColumns: ['date_year', 'date_month', 'date_day'],
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Deprecation note:

The operation and new_column_name parameters are deprecated and are supported for retrocompatibility purposes only.

An old-fashioned step looks like this:

{
  name: 'dateextract',
  column: 'date',
  operation: 'day',
  new_column_name: 'date_day',
}

Example

Input dataset:

Step configuration:

{
  name: 'dateextract',
  column: 'Date',
  dateInfo: ['year', 'month', 'day'],
  newColumns: ['Date_year', 'Date_month', 'Date_day'],
}

Output dataset:

dategranularity step

Extract date information (eg. day, week, year etc.) in a column intended for aggregation. The following granularities are supported:

• year: calendar date corresponding to the first day (1st of January) of the year
• quarter: calendar date corresponding to the first day of the quarter
• month: calendar date corresponding to the first day of the month
• week: calendar date corresponding to the first day of the week (sunday)
• isoWeek: calendar date corresponding to the first day of the week (monday)
• day: calendar date corresponding to the first hour of the day

Here’s an example of such a step:

{
  name: 'dategranularity',
  column: 'date',
  granularity: 'year',
  newColumn: 'do_the_aggregate_on_this'
}

*This step is supported by the following backends:

• Mongo
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'dategranularity',
  column: 'Date',
  granularity: 'month'
}

Output dataset:

delete step

Delete a column.

{
    name: 'delete',
    columns: ['my-column', 'some-other-column']
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'delete',
  columns: ['Company', 'Group']
}

Output dataset:

dissolve step

Geographically dissolve data

{
    name: 'dissolve',
    groups: ['my-column', 'some-other-column'],
    include_nulls: true,
}

This step is supported by the following backends:

• Pandas (python)

Example without aggregations

Input dataset:

Step configuration:

{
    name: 'dissolve',
    groups: ['Country'],
    include_nulls: true,
}

Output dataset:

Example with aggregations

Input dataset:

Step configuration:

{
    name: 'dissolve',
    groups: ['Country'],
    include_nulls: true,
    aggregations: [
        {
            aggfunction: 'sum',
            columns: ['Population'],
            newcolumns: ['Total population'],
        }
    ]
}

Output dataset:

domain step

This step is meant to select a specific domain (using MongoDB terminology).

{
    name: 'domain',
    domain: 'my-domain'
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

duplicate step

This step is meant to duplicate a column.

{
    name: 'duplicate',
    column: 'my-column'
    new_column_name: 'my-duplicate'
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'duplicate',
  column: "Company",
  new_column_name: "Company-copy",
}

Output dataset:

duration step

Compute the duration (in days, hours, minutes or seconds) between 2 dates in a new column.

{
  name: 'duration',
  newColumnName: 'DURATION', // new column name
  startDateColumn: 'START_DATE', // start date
  endDateColumn: 'END_DATE', // end date
  durationIn: 'days', // duration units (days, hours, minutes or seconds)
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: duration in days

Input dataset:

Step configuration:

{
  name: 'duration',
  newColumnName: 'DURATION',
  startDateColumn: 'START_DATE',
  endDateColumn: 'END_DATE',
  durationIn: 'days',
}

Output dataset:

Example 2: duration in minutes

Input dataset:

Step configuration:

{
  name: 'duration',
  newColumnName: 'DURATION',
  startDateColumn: 'START_HOUR',
  endDateColumn: 'END_HOUR',
  durationIn: 'minutes',
}

Output dataset:

evolution step

Use this step if you need to compute the row-by-row evolution of a value column, based on a date column. It will output 2 columns: one for the evolution in absolute value, the other for the evolution in percentage.

You must be careful that the computation is scoped so that there are no dates duplicates (so that any date finds no more than one previous date). That means that you may need to specify “group by” columns to make any date unique inside each group. You should specify those columns in the indexColumns parameter.

{
  name: 'evolution',
  dateCol: 'DATE',
  valueCol: 'VALUE',
  evolutionType: 'vsLastYear', // or vsLastMonth, vsLastWeek, vsLastDay
  evolutionFormat: 'abs', // or pct
  // optional, if computation has to be performed by group so that any date finds no more than one previous date
  indexColumns: ['COUNTRY'],
  newColumn: 'MY_EVOL', // optional, <originalColumnName>_EVOL_ABS or <originalColumnName>_EVOL_PCT by default
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: Basic configuration - evolution in absolute value

Input dataset:

Step configuration:

{
  name: 'evolution',
  dateCol: 'DATE',
  valueCol: 'VALUE',
  evolutionType: 'vsLastMonth',
  evolutionFormat: 'abs',
  indexColumns: [],
}

Output dataset:

Example 2: Basic configuration - evolution in percentage

Input dataset:

Step configuration:

{
  name: 'evolution',
  dateCol: 'DATE',
  valueCol: 'VALUE',
  evolutionType: 'vsLastMonth',
  evolutionFormat: 'pct',
  indexColumns: [],
}

Output dataset:

Example 3: Error on duplicate dates

If ‘COUNTRY’ is not specified as indexColumn, the computation will not be scoped by country. Then there are duplicate dates in the “DATE” columns which is prohibited and will lead to an error.

Input dataset:

Step configuration:

{
  name: 'evolution',
  dateCol: 'DATE',
  valueCol: 'VALUE',
  evolutionType: 'vsLastYear',
  evolutionFormat: 'abs',
  indexColumns: [],
}

Output dataset:

With the mongo translator, you will get an error at row-level as shown below:

The pandas translator will just return an error, and you will not get any data.

Example 4: Complete configuration with index columns

Input dataset:

Step configuration:

{
  name: 'evolution',
  dateCol: 'DATE',
  valueCol: 'VALUE',
  evolutionType: 'vsLastYear',
  evolutionFormat: 'abs',
  indexColumns: ['COUNTRY'],
  newColumn: 'MY_EVOL',
}

Output dataset:

fillna step

Replace null values by a given value in specified columns.

{
    name: 'fillna',
    columns: ["foo", "bar"],
    value: 0
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Deprecation note:

The column (in the singular) parameter is deprecated and is supported only for retrocompatibility purposes.

An old-fashioned step looks like this:

{
    name: 'fillna',
    column: 'foo',
    value: 0
}

Example

Input dataset:

Step configuration:

{
  name: 'fillna',
  columns: ["Value", "KPI"],
  value: 0
}

Output dataset:

filter step

Filter out lines that don’t match a filter definition.

{
    name: 'filter',
    condition: {
      column: 'my-column',
      value: 42,
      operator: 'ne'
    }
}

operator is optional, and defaults to eq. Allowed operators are eq, ne, gt, ge, lt, le, in, nin, matches, notmatches isnull or notnull.

value can be an arbitrary value depending on the selected operator (e.g a list when used with the in operator, or null when used with the isnull operator).

matches and notmatches operators are used to test value against a regular expression.

Conditions can be grouped and nested with logical operators and and or.

{
    name: 'filter',
    condition: {
      and: [
        {
          column: 'my-column',
          value: 42,
          operator: 'gte'
        },
        {
          column: 'my-column',
          value: 118,
          operator: 'lte'
        },
        {
          or: [
            {
              column: 'my-other-column',
              value: 'blue',
              operator: 'eq'
            },
            {
              column: 'my-other-column',
              value: 'red',
              operator: 'eq'
            }
          ]
        }
      ]
    }
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Relative dates

Date values can be relative to the moment to the moment when the query is executed. This is expressed by using a RelativeDate object instead of the value, of the form:

{
  quantity: Number // can be positive (future) or negative (past)
  duration: 'year' | 'quarter' | 'month' | 'week' | 'day'
}

*This feature is supported by the following backends:

• Mongo 5.0

formula step

Add a computation based on a formula. Usually column names do not need to be escaped, unless they include whitespaces, in which case you’ll need to use brackets ‘[]’ (e.g. [myColumn]). Any string escaped with quotes (', ", ''', """) will be considered a string literal.

{
  {
    name: 'formula',
    new_column: 'result', // if the column already exists, overwrites it
    formula: '(Value1 + Value2) / Value3 - Value4 * 2'
  }
}

Supported operators

The following operators are supported by the formula step (note that a value can be a column name or a literal, such as 42 or foo).

• +: Does an addition of two numeric values. See the concatenate step to append strings
• -: Does an substraction of two numeric values. See the replace step to remove a part of a string
• *: Multiplies two numeric values.
• /: Divides a numeric value by another. Divisions by zero will return null.
• %: Returns the rest of an integer division. Divisions by zero will return null.

Example 1: Basic usage

Input dataset:

Step configuration:

{
  name: 'formula',
  new_column: 'Result',
  formula: '(Value1 + Value2) / Value3 - Value4 * 2'
}

Output dataset:

Example 2: Column name with whitespaces

Input dataset:

Step configuration:

{
  name: 'formula',
  new_column: 'Result',
  formula: '(Value1 + Value2) / Value3 - [Value 4] * 2'
}

Output dataset:

hierarchy step

Hierarchy for geographical data.

This step dissolves data for every hierarchy level, and adds a hierarchy level column containing a level (with 0 being the lowest granularity, i.e. the highest level).

{
    name: 'hierarchy',
    hierarchy: ['Country', 'City'],
    include_nulls: false,
}

*This step is supported by the following backends:

• Pandas (python)

Example

Input dataset:

Step configuration:

    name: 'hierarchy',
    hierarchy: ['Country', 'City'],
    include_nulls: false,

Output dataset:

ifthenelse step

Creates a new column, which values will depend on a condition expressed on existing columns.

The condition is expressed in the if parameter with a condition object, which is the same object expected by the condition parameter of the filter step). Conditions can be grouped and nested with logical operators and and or.

The then parameter only supports a string, that will be interpreted as a formula (cf. formula step). If you want it to be interpreted striclty as a string and not a formula, you must escape the string with quotes (e.g. ‘“this is a text”’).

if...then...else blocks can be nested as the else parameter supports either a string that will be interpreted as a formula (cf. formula step), or a nested if if...then...else object.

{
  name: 'ifthenelse',
  newColumn: '', // the name of the new column to be created
  if: { column: '', value: '', operator: 'eq' }, // a condition, same object as in the filter step
  then: '', // a string that will be interpreted as a formula
  else: '', // a string interpreted as a formula or a nested if...then...else object
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
    name: 'ifthenelse',
    newColumn: 'result',
    if: { column: 'number', value: 0, operator: 'eq' },
    then: '"zero"'
    else: {
      if: { column: 'rel', value: 0, operator: 'lt' },
      then: 'number * -1',
      else: 'number'
    }
}

Output dataset:

join step

Joins a dataset to the current dataset, i.e. brings columns from the former into the latter, and matches rows based on columns correspondance. It is similar to a JOIN clause in SQL, or to a VLOOKUP in excel. The joined dataset is the result from the query of the right_pipeline.

The join type can be:

• ‘left’: will keep every row of the current dataset and fill unmatched rows with null values,

• ‘inner’: will only keep rows that match rows of the joined dataset.

In the on parameter, you must specify 1 or more column couple(s) that will be compared to determine rows correspondance between the 2 datasets. The first element of a couple is for the current dataset column, and the second for the corresponding column in the right dataset to be joined. If you specify more than 1 couple, the matching rows will be those that find a correspondance between the 2 datasets for every column couple specified (logical ‘AND’).

Weaverbird allows you to save pipelines referenced by name in the Vuex store of the application. You can then call them by their unique names in this step.

{
  name: 'join',
  right_pipeline: 'somePipelineReference',
  type: 'left', // or 'inner'
  on: [
    ['currentDatasetColumn1', 'rightDatasetColumn1'],
    ['currentDatasetColumn2', 'rightDatasetColumn2'],
  ]
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: Left join with one column couple as on parameter

Input dataset:

rightDataset (saved in the application Vuex store):

Step configuration:

{
  name: 'join',
  right_pipeline: 'rightDataset',
  type: 'left',
  on: [['Label', 'Label']];
}

Output dataset:

Example 2: inner join with different column names in the on parameter

Input dataset:

rightDataset (saved in the application Vuex store):

Step configuration:

{
  name: 'join',
  right_pipeline: 'rightDataset',
  type: 'inner',
  on: [['Label', 'LabelRight']];
}

Output dataset:

fromdate step

Converts a date column into a string column based on a specified format.

{
    name: 'fromdate',
    column: 'myDateColumn'
    format: '%Y-%m-%d' // For further details on supported format specifiers,
                       // see https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/#datefromstring-format-specifiers
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
  name: 'fromdate',
  column: 'Date',
  format: '%d/%m/%Y'
}

Output dataset:

lowercase step

Converts a string column to lowercase.

{
  name: 'lowercase',
  column: 'foo',
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example:

Input dataset:

Step configuration:

{
  name: 'lowercase',
  column: 'Label',
}

Output dataset:

movingaverage step

Compute the moving average based on a value column, a reference column to sort (usually a date column) and a moving window (in number of rows i.e. data points). If needed, the computation can be performed by group of rows. The computation result is added in a new column.

{
  name: 'movingaverage',
  valueColumn: 'value',
  columnToSort: 'dates'
  movingWindow: 12,
  groups: ['foo', 'bar'] // optional
  newColumName: 'myNewColumn' // optional, <originalColumname>_PCT by default
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: Basic usage

Input dataset:

Step configuration:

{
  name: 'movingaverage',
  valueColumn: 'VALUE',
  columnToSort: 'DATE'
  movingWindow: 2,
}

Output dataset:

Example 2: with groups and custom newColumnName

Input dataset:

Step configuration:

{
  name: 'movingaverage',
  valueColumn: 'VALUE',
  columnToSort: 'DATE'
  movingWindow: 2,
  groups: ['COUNTRY']
  newColumName: 'ROLLING_AVERAGE'
}

Output dataset:

percentage step

Compute the percentage of total, i.e. for every row the value in column divided by the total as the sum of every values in column. The computation can be performed by group if specified. The result is written in a new column.

{
  name: 'percentage',
  column: 'bar',
  group: ['foo'] // optional
  newColumName: 'myNewColumn' // optional, <originalColumname>_PCT by default
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example:

Input dataset:

Step configuration:

  name: 'percentage',
  new_column: 'Percentage_of_total',
  column: 'Value',
  group: ['Group']
  newColumn: 'Percentage'
}

Output dataset:

pivot step

Pivot rows into columns around a given index (expressed as a combination of column(s)). Values to be used as new column names are found in the column column_to_pivot. Values to populate new columns are found in the column value_column. The function used to aggregate data (when several rows are found by index group) must be among sum, avg, count, min or max.

{
 name: 'pivot',
 index: ['column_1', 'column_2'],
 column_to_pivot: 'column_3',
 value_column: 'column_4',
 agg_function: 'sum',
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example:

Input dataset:

Step configuration:

{
 name: 'pivot',
 index: ['Label'],
 column_to_pivot: 'Country',
 value_column: 'Value',
 agg_function: 'sum',
}

Output dataset:

statistics step

Compute statistics of a column.,

{
    name: 'statistics',
    column: 'Value',
    groupby: [],
    statistics: ['average', 'count'],
    quantiles: [{label: 'median', nth: 1, order: 2}],
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example:

Input dataset:

Step configuration:

{
    name: 'statistics',
    column: 'Value',
    groupby: [],
    statistics: ['average', 'count'],
    quantiles: [{label: 'median', nth: 1, order: 2}],
}

Output dataset:

rank step

This step allows to compute a rank column based on a value column that can be sorted in ascending or descending order. The ranking can be computed by group.

There are 2 ranking methods available, that you will understand easily through those examples:

• standard: input = [10, 20, 20, 20, 25, 25, 30] => ranking = [1, 2, 2, 2, 5, 5, 7]
• dense: input = [10, 20, 20, 20, 25, 25, 30] => ranking = [1, 2, 2, 2, 3, 3, 4]

(The dense method is basically the same as the standard method, but rank always increases by 1 at most).

{
  name: 'rank',
  valueCol: 'VALUE', // The value column that will be ordered to determine rank
  order: 'desc', // How to order the value column to determine ranking.
                 // either asc(ending) or desc(ending), 'desc' by default
  method: 'standard', // either 'standard' or 'dense', as explained above
  groupby: ['foo', 'bar'], // specify columns if you want to group the ranking computation
  newColumnName: 'columnA', // specify the new column name to be created (by default '<yourValueColumn>_RANK')
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example 1: Basic usage

Input dataset:

Step configuration:

{
  name: 'rank',
  valueCol: 'VALUE',
  order: 'desc',
  method: 'standard',
}

Output dataset:

Example 2: With more options

Input dataset:

Step configuration:

{
  name: 'rank',
  valueCol: 'VALUE',
  order: 'asc',
  method: 'dense',
  groupby: ['COUNTRY'],
  newColumnName: 'MY_RANK',
}

Output dataset:

rename step

Rename one or several columns. The toRename parameter takes as input a list of 2-elements lists in the form [‘oldColumnName’, ‘newColumnName’].

{
    name: 'rename',
    toRename: [
      ['oldCol1', 'newCol1'] // The first value is the column to be renamed, the second is the new column name
      ['oldCol2', 'newCol2']
    ]
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Deprecation note:

The oldname and newnname parameters are deprecated and are supported only for retrocompatibility purposes. When this step was first created, only 1 column at a time could be renamed, and the step was configured via 2 parameters, oldname and newnname, that are now deprecated and supported only for retrocompatibility purposes. An old-fashioned step with oldname set to ‘old’ and newname set to ‘new’ is equivalent to a new-fashioned step with toRename parameter set to [‘old’, ‘new’].

So an old-fashioned step looks like this:

{
    name: 'rename',
    oldname: 'old-column-name',
    newname: 'new-column-name'
}

Example:

Input dataset:

Step configuration:

{
  name: 'rename',
  oldname: 'Label',
  newname: 'Company'
}

Output dataset:

replace step

Replace one or several values in a column.

A replace step has the following strucure:

{
   name: 'replace',
   search_column: "column_1",
   to_replace: [
     ['foo', 'bar'], // The first value is the one to be replace, the second is the new value
     [42, 0]
   ]
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Example

Input dataset:

Step configuration:

{
   name: 'replace',
   search_column: "COUNTRY",
   to_replace: [
     ['Fr', 'France']
     ['UK', 'United Kingdom']
   ]
}

Output dataset:

replacetext step

Replace a substring in a column.

A replace-text step has the following structure:

{
   name: 'replacetext',
   search_column: "column_1",
   old_str: 'foo',
   new_str: 'bar'
}

*This step is supported by the following backends:

• Mongo 5.0 (python)
• Pandas (python)
• AWS Athena
• Google Big Query
• MySQL
• PostgreSQL
• Redshift
• Snowflake

Example

Input dataset:

Step configuration:

{
   name: 'replacetext',
   search_column: "COUNTRY",
   old_str: 'Fr',
   new_str: 'France',
}

Output dataset:

rollup step

Use this step if you need to compute aggregated data at every level of a hierarchy, specified as a series of columns from top to bottom level. The output data structure stacks the data of every level of the hierarchy, specifying for every row the label, level and parent in dedicated columns.

Aggregated rows can be computed with using either sum, average, count, count distinct, min, max, first or last.

{
   name: 'rollup',
   hierarchy: ['continent', 'country', 'city'], // Hierarchical columns, from top to bottom level
   aggregations: [ // The columns to be aggregated, and how
   {
      newcolumns: ['sum_value1', 'sum_value2'], // same length as 'columns'
      aggfunction: 'sum', // the aggregation function that will be applied to the specified columns
      columns: ['value1', 'value2']
    }
    {
      newcolumns: ['avg_value1'],
      aggfunction: 'avg',
      columns: ['value1']
    }
   ],
   groupby: ['date'], // optional, if needing to segment the rollup computation by group
   labelCol: 'label', // optional, name of the output label column, 'label' by default
   levelCol: 'level', // optional, name of the output level column, 'level' by default
   childLevelCol: 'child_level', // optional, name of the next output level column, 'child_level' by default
   parentLabelCol: 'parent', // optional, name of the output parent column, 'parent' by default
}

*This step is supported by the following backends:

• Mongo 5.0
• Mongo 4.2
• Mongo 4.0
• Mongo 3.6
• Pandas (python)

Deprecation note:

The column and newcolumn (in the singular) properties of the aggregation parameter are deprecated and are supported only for retrocompatibility purposes. When this step was first created, only 1 column at a time could be aggregated for a given aggregation. Now columns and newcolumns are lists of columns, allowing to apply the same aggregation function to several columns at once.

An old-fashioned step looked like this:

{
   name: 'rollup',
   hierarchy: ['continent', 'country', 'city'],
   aggregations:  [
    {
      newcolumn: 'sum_value1'
      aggfunction: 'sum',
      column: 'value1'
    }
    {
      newcolumn: 'sum_value2',
      aggfunction: 'sum',
      column: 'value2'
    }
    {
      newcolumn: 'avg_value1',
      aggfunction: 'avg',
      column: 'value1'
    }
    // ...
  ]
}

Example 1 : Basic configuration

Input dataset:

Step configuration:

{
   name: 'rollup',
   hierarchy: ['CONTINENT', 'COUNTRY', 'CITY'],
   aggregations: [
    {
      newcolumn: ['VALUE'],
      aggfunction: 'sum',
      column: ['VALUE']
    },
   ],
}

Output dataset:

Example 2 : Configuration with optional parameters

Input dataset:

Step configuration:

{
   name: 'rollup',
   hierarchy: ['CONTINENT', 'COUNTRY', 'CITY'],
   aggregations: [
    {
      newcolumn: ['VALUE-sum', 'COUNT']
      aggfunction: 'sum',
      column: ['VALUE', 'COUNT']
    },
    {
      newcolumn: 'VALUE-avg',
      aggfunction: 'avg',
      column: 'VALUE'
    }
   ],
   groupby: ['YEAR'],
   labelCol: 'MY_LABEL',
   levelCol: 'MY_LEVEL',
   childLevelCol: 'MY_CHILD_LEVEL',
   parentLabelCol: 'MY_PARENT',
}

Output dataset: