Flatten Column
Collapse a multilevel Array or Object column, either by one level or down to a single level.
Configuration
| Field | Description |
|---|---|
Name default=none | Name of the node: A user-specified node name, displayed in the canvas and in the dataframe as a tab. |
| Select Object Column Required | Select single column of type Array or Object: Select the input column to be flattened in the output dataframe. |
outputColName default=none | Desired output column name: Enter a name for the new column containing the flattened Array or Object. |
Disassemble All Levels of Nested Objects default=On | Degree of flattening of Array or Object: Specify whether to disassemble only the first level of an Array or Object, or disassemble all levels recursively. This setting has no effect for a single level Array or an Object with a single level of key/value pairs. When toggled on, all levels are flattened. When toggled off, only the first level is flattened. |
Drop Original Column default=On | Toggle indicating whether the selected input column should be dropped from the output: Leave the switch on to delete the input column to be flattened. The column information, however, is preserved in the new output column. Toggle the switch off to keep the selected column. |
Node Inputs/Outputs
| Input | A Visual Notebooks dataframe |
|---|---|
| Output | A dataframe with at least one Array or Object column |
Figure 1: Example dataframe output
Examples
The data shown in Figure 2 is used in the following example. We start with a JSON file comprising multiple categories, or levels, of customer details. The file, when loaded using the JSON input node, creates a single Objects column with nested structure representing the various categories of customer information. We would like to flatten this column to enable subsequent data structuring and cleaning steps.
Figure 2: Example input data with a multilevel Object
- Connect a Flatten Column node to an existing node. In this case, it is connected to a JSON node with the example data provided, which includes a multilevel Object.
- In Select Object Column, select "Member" and then click Run.
After running the node, the flattened Object can be seen in the "Member_flatstruct" column as shown in Figure 3. Observe that every key/value pair is listed in a single level Object, and that key names are autogenerated by appending the key name at each level of the original structure. For example, the current city field is nested in the original object as part of the following hierarchy:
- Location
- Current
- City
- Current
In the flattened Object, the key for each value of the current city field is thus: "Location_Current_City."
Figure 3: Flattening all levels of a multilevel Object
Now, re-run the node, except with Disassemble All Levels of Nested Objects toggled off. The flattened Object can again be seen in the "Member_flatstruct" column as shown in Figure 4. This time, however, the Location Object, which has three levels of key/value pairs, has been reduced to two levels instead of one. As a result, only the second level keys, Current and Previous, are appended to the first, Location. The third level keys, City and State, comprise the second level of the resulting Object. Note that after flattening, all other Objects besides Location appear the same as in Figure 2. Since they only have two levels, flattening one level or all levels produces the same result.
Figure 4: Flattening one level of a multilevel Object