| Excerpt |
|---|
Unpacks array data into separate rows for each value. This transform operates on a single column. |
This transform does not reference keys in the array. If your array data contains keys, use the unnest transform. See Unnest Transform.
| D code |
|---|
flatten col: myArray |
Output: Generates a separate row for each value in the array. Values of other columns in generated rows are copied from the source.
| D code |
|---|
flatten: col: column_ref |
| Token | Required? | Data Type | Description |
|---|
| flatten | Y | transform | Name of the transform |
| col | Y | string | Source column name |
Identifies the column to which to apply the transform. You can specify only one column.
| Required? | Data Type |
|---|
| Yes | String (column name) |
In this example, the source data includes an array of scores that need to broken out into separate rows.
Source:
| 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:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | settype col: Scores type: 'Array' |
|---|
| p01Name | Columns |
|---|
| p01Value | Scores |
|---|
| p02Name | New type |
|---|
| p02Value | Array |
|---|
| SearchTerm | Change column data type |
|---|
|
You can now flatten the Scores column data into separate rows:
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | flatten col: Scores |
|---|
| p01Name | Column |
|---|
| p01Value | Scores |
|---|
| SearchTerm | Expand Array into rows |
|---|
|
Results:
| LastName | FirstName | Scores |
|---|
| Adams | Allen | 81 |
| Adams | Allen | 87 |
| Adams | Allen | 83 |
| Adams | Allen | 79 |
| Burns | Bonnie | 98 |
| Burns | Bonnie | 94 |
| Burns | Bonnie | 92 |
| Burns | Bonnie | 85 |
| Cannon | Chris | 88 |
| Cannon | Chris | 81 |
| Cannon | Chris | 85 |
| Cannon | Chris | 78 |
This example is extended below.
While the above example nicely flattens out your data, there are two potential problems with the results:
- There is no identifier for each test. For example, Allen Adams' score of 87 cannot be associated with the specific test on which he recorded the score.
- There is no unique identifier for each row.
The following example addresses both of these issues. It also demonstrates differences between the unnest and the flatten transform, including how you use unnest to flatten array data based on specified keys.
| Include Page |
|---|
| EXAMPLE - Flatten and Unnest Transforms |
|---|
| EXAMPLE - Flatten and Unnest Transforms |
|---|
|
| D s also |
|---|
| label | wrangle_transform_flatten |
|---|
|
