EXAMPLE - Unnest an Array
This section describes how to unnest the values in an Array into separate columns in your dataset.
Source:
In the following example dataset, students took the same test three times, and their scores were stored in any array in the Scores column.
LastName | FirstName | Scores |
|---|---|---|
Adams | Allen | [81,87,83,79] |
Burns | Bonnie | [98,94,92,85] |
Cannon | Chris | [88,81,85,78] |
Transformation:
When the data is imported, you might have to re-type the Scores column as an array:
Transformation Name | |
|---|---|
Parameter: Columns | Scores |
Parameter: New type | Array |
You can now unnest the Scores column data into separate columns:
Transformation Name | |
|---|---|
Parameter: Column | Scores |
Parameter: Parameter: Paths to elements | [0] |
Parameter: Parameter: Paths to elements | [1] |
Parameter: Parameter: Paths to elements | [2] |
Parameter: Parameter: Paths to elements | [3] |
Parameter: Remove elements from original | true |
Parameter: Include original column name | true |
In the above transformation:
Each path is specified in a separate row.
The
[x]syntax indicates that the path is the xth element of the array.The first element of an array is referenced using
[0].
You can choose to delete the element from the original or not. Deleting the element can be a helpful way of debugging your transformation. If all of the elements are gone, then the transformation is complete.
If you include the original column name in the output column names, you have some contextual information for the outputs.
Results:
LastName | FirstName | Scores_0 | Scores_1 | Scores_2 | Scores_3 |
|---|---|---|---|---|---|
Adams | Allen | 81 | 87 | 83 | 79 |
Burns | Bonnie | 98 | 94 | 92 | 85 |
Cannon | Chris | 88 | 81 | 85 | 78 |