Wrangle is the domain-specific language used to build transformation recipes in Cloud Dataprep by TRIFACTA®.
A Wrangle recipe is a sequence of transforms, which are applied to your dataset in order to produce your results.
- A transform is a single action applied to your dataset. For most transforms, you can pass one or more parameters to define the context (columns, rows, or conditions) where the transform is applied to your dataset.
- Within some parameters of a transform, you can specify one or more functions. A function is a computational action performed on one or more columns of data in your dataset.
- These terms are described below.
- Recipes are built in the Transformer Page. See Transformer Page.
When you select suggestions in the Transformer Page, your selection is converted into a Wrangle command and added to your recipe.
Tip: Where possible, you should make selections in the data grid to build transform steps. These selections prompt a series of cards to be displayed at the bottom of the screen. You can select different cards to specify a basic transform for your selected data, choose a variant of that transform, and then modify the underlying Wrangle recipe as necessary. For more information, see Overview of Predictive Transformation.
For more information on the suggestion cards, see Suggestion Cards Panel.
Some complex transforms, such as joins and unions, must be created through dedicated screens. See Transformer Page.
NOTE: In the Transformer page, some transforms can induce re-ordering. For example, transforms that use the
group parameter may result in non-deterministic ordering in the data grid. When jobs are executed at scale, any transform can potentially induce unwanted re-ordering of rows due to distributed processing.
Wrangle transform steps follow this general syntax:
(transform) param1:(expression) param2:(expression)
A transform (or verb) is a single keyword that identifies the type of change you are applying to your dataset.
The other elements in each step are contextual parameters for the transform. Some transforms do not require parameters.
Additional parameters may be optional or required for any transform.
NOTE: A parameter is always followed by a colon. A parameter may appear only one time in a transform step.
Depending on the transform, one or more of
row parameters may be used. For example, the
set transform can use all three or just
When present, the
An expression can contain combinations of the following:
When present, the
Some transforms may support multiple columns as a list, as a range of columns (e.g.,
|When present, the |
The following types of parameter inputs may be referenced in a transform's parameters.
Other Cloud Dataprep data types can be referenced as column references. For literal values of these data types, you can insert them into your expressions as strings. Transforms cause the resulting values to be re-inferred for their data type.
A reference to the values stored in a column in your dataset.
Columns can be referenced by the plain-text value for the column name.
|Integer||A valid integer value within the accepted range of values for the Integer datatype. For more information, see Supported Data Types.|
Generates a column called,
|Decimal||A valid floating point value within the accepted range of values for the Decimal datatype. For more information, see Supported Data Types.|
Generates a column of values that computes the approximate circumference of the values in the
If the value in the
A string literal value is the baseline datatype.
String literals must be enclosed in single quotes.
Creates a column called,
|Cloud Dataprep pattern|
Cloud Dataprep by TRIFACTA supports a special syntax, which simplifies the generation of matching patterns for string values.
Patterns must be enclosed in accent marks (
For more information, see Text Matching.
Extracts up to 10 values from the
Regular expressions are a common standard for defining matching patterns. Regex is a very powerful tool but can be easily misconfigured.
Regular expressions must be enclosed in slashes (
Deletes all two-digit numbers from the
A valid date or time value that matches the requirements of the Datetime datatype. See Supported Data Types.
Datetime values can be formatted with specific formatting strings. See DATEFORMAT Function.
Generates a new column containing the values from the
A valid array of values matching the Array data type. Example:
See Supported Data Types.
Generates a column with the number of elements in the listed array (
A valid set of values matching the Object data type. Example:
See Supported Data Types.
Generates separate columns for each of the specified keys in the object (
Interactions between Wrangle and the Application
- As you build Wrangle steps in the Transform Builder, your syntax is validated for you. You cannot add steps containing invalid syntax.
- Error messages are reported back to the application, so you can make immediate modifications to correct the issue.
- Type-ahead support can provide guidance to the supported transforms, functions, and column references.
- For more information, see Transform Builder.
- When you have entered a valid transform step, the results are previewed for you in the data grid.
This preview is generated by applying the transform to the sample in the data grid.
NOTE: The generated output applies only to the values displayed in the data grid. The function is applied across the entire dataset only during job execution.
- If the previewed transform is invalid, the data grid is grayed out.
- For more information, see Transform Preview.
- When you add the transform to your recipe:
- It is applied to the sample in the application, and the data grid is updated to the current state.
- Column histograms are updated with new values and counts.
- Column data types may be re-inferred for affected columns.
- Making changes:
- You can edit any transform step in your recipe whenever needed.
- When you edit a transform step in your recipe, the context of the data grid is changed to display the state of your data up to the point of previewing the step you're editing.
- All subsequent steps are still part of the recipe, but they are not applied to the sample yet.
- You can insert recipe steps between existing steps.
- When you delete a recipe step, the state remains at the point where the step was removed.
- You can insert a new step if needed.
- When you complete your edit, select the final step of the recipe, which displays the results of all of your transform steps in the data grid. Your changes may cause some recipe steps to become invalid.
- See Recipe Panel.
- You can edit any transform step in your recipe whenever needed.
A transform , or verb, is an action applied to rows or columns of your data. Transforms are the essential set of changes that you can apply to your dataset. For more information, see Transforms.
A function is an action that is applied to a set of values as part of a transform step. Functions can apply to the values in a transform for specific data types, such as strings, or to types of transforms, such as aggregate and window function categories. A function cannot be applied to data without a transform.
|These functions are used to perform aggregation calculations on your data, such as sum, mean, and standard deviation.|
|Comparison Functions||Comparison functions enable evaluation between two data elements, which are typically nested (Object or Array) elements.|
|Math Functions||Perform computations on your data using a variety of math functions and numeric operators.|
|Date Functions||Use these functions to extract data from or perform operations on objects of Datetime data type.|
|String Functions||Manipulate strings, including finding sub-strings within a string.|
|Nested Functions||These functions are designed specifically to assist in wrangling nested data, such as Objects, Arrays, or JSON elements.|
|Type Functions||Use the Type functions to identify valid, missing, mismatched, and null values.|
|Window Functions||The Window functions enable you to perform calculations on relative windows of data within your dataset.|
|Other Functions||Miscellaneous functions that do not fit into the other categories|
An operator is a single character that represents an arithmetic function. For example, the Plus sign (
+) represents the add function.
|Logical Operators||and, or, and not operators|
|Numeric Operators||Add, subtract, multiply, and divide|
|Comparison Operators||Compare two values with greater than, equals, not equals, and less than operators|
|Ternary Operators||Use ternary operators to create if/then/else logic in your transforms.|
Documentation for Wrangle is also available through Cloud Dataprep by TRIFACTA. Select Help menu > Product Docs.
Tip: When searching for examples of transforms and functions, try using the following forms for your search terms within the Product Docs site:
- Aggregate Transform
- Case Transform
- Comment Transform
- Countpattern Transform
- Deduplicate Transform
- Delete Transform
- Derive Transform
- Drop Transform
- Extract Transform
- Extractkv Transform
- Extractlist Transform
- Filter Transform
- Flatten Transform
- Header Transform
- Keep Transform
- Merge Transform
- Move Transform
- Nest Transform
- Pivot Transform
- Rename Transform
- Replace Transform
- Set Transform
- Settype Transform
- Split Transform
- Splitrows Transform
- Unnest Transform
- Unpivot Transform
- Valuestocols Transform
- Window Transform
- Aggregate Functions
- ANY Function
- AVERAGE Function
- COUNT Function
- KTHLARGEST Function
- LIST Function
- MAX Function
- MIN Function
- STDEV Function
- SUM Function
- VAR Function
- COUNTA Function
- ANYIF Function
- AVERAGEIF Function
- COUNTAIF Function
- COUNTDISTINCT Function
- COUNTDISTINCTIF Function
- COUNTIF Function
- KTHLARGESTIF Function
- LISTIF Function
- MAXIF Function
- MINIF Function
- STDEVIF Function
- SUMIF Function
- VARIF Function
- KTHLARGESTUNIQUE Function
- MODE Function
- MODEIF Function
- Logical Functions
- Comparison Functions
- Math Functions
- Numeric Operators
- ADD Function
- SUBTRACT Function
- MULTIPLY Function
- DIVIDE Function
- MOD Function
- NEGATE Function
- NUMFORMAT Function
- ABS Function
- EXP Function
- LOG Function
- POW Function
- CEILING Function
- LN Function
- SQRT Function
- FLOOR Function
- ROUND Function
- SIGN Function
- LCM Function
- TRUNC Function
- RADIANS Function
- DEGREES Function
- Date Functions
- DATE Function
- TIME Function
- DATEADD Function
- DATEDIF Function
- DATEFORMAT Function
- UNIXTIMEFORMAT Function
- MONTH Function
- MONTHNAME Function
- YEAR Function
- DAY Function
- WEEKDAY Function
- HOUR Function
- MINUTE Function
- SECOND Function
- UNIXTIME Function
- NOW Function
- TODAY Function
- DATETIME Function
- WEEKNUM Function
- String Functions
- CHAR Function
- UNICODE Function
- UPPER Function
- LOWER Function
- PROPER Function
- TRIM Function
- REMOVEWHITESPACE Function
- REMOVESYMBOLS Function
- LEN Function
- FIND Function
- SUBSTRING Function
- LEFT Function
- RIGHT Function
- MERGE Function
- STARTSWITH Function
- ENDSWITH Function
- SUBSTITUTE Function
- STRINGGREATERTHAN Function
- STRINGGREATERTHANEQUAL Function
- STRINGLESSTHANEQUAL Function
- STRINGLESSTHAN Function
- RIGHTFIND Function
- PAD Function
- REPEAT Function
- EXACT Function
- Nested Functions
- Type Functions
- Window Functions
- PREV Function
- NEXT Function
- FILL Function
- ROLLINGAVERAGE Function
- SESSION Function
- ROLLINGSUM Function
- ROWNUMBER Function
- ROLLINGMODE Function
- ROLLINGMAX Function
- ROLLINGMIN Function
- ROLLINGSTDEV Function
- ROLLINGVAR Function
- ROLLINGCOUNTA Function
- ROLLINGKTHLARGEST Function
- ROLLINGKTHLARGESTUNIQUE Function
- ROLLINGLIST Function
- Other Functions
- Other Language Topics
- Language Index