Icon

Pivoting

Restructuring the shape of a data table is an important part of many KNIME workflows. The Pivoting node allows us to convert a "tall and skinny" table into one that is "short and wide." In other words, the Pivoting node take a table with relatively few columns and many rows and creates one with relatively many columns and few rows.

There are three tabs in its configuration menu that you must understand to use this node effectively:

- Groups: Using the familiar include/exclude column selection boxes, identify which columns, if any, you wish to group by. The selection made here dictates how many rows the output table will contain. If you do not group by any fields, the output table will contain a single row. If you select one or more fields, the number of rows in the output will be equal to the number of unique combinations of values in the selected columns.

- Pivots: Again, using the include/exclude column selection boxes, identify which columns contain the values that will become the column headers in the output table.

- Manual Aggregation: Select the field or fields you wish to aggregate. At least one field must be aggregated. For numeric fields, you may choose from options such as sum, mean, or standard deviation. Qualitative fields can be counted or concatenated.


The Pivoting node returns three outputs:

- Port 0 (the top port): This is the primary output port and returns the pivoted table with all rows, columns, and values present. This is what you will want to use the majority of the time.

- Port 1 (the middle port): Aggregates across columns, returning one column per aggregation from the configuration menu.

- Port 2 (the bottom port): Aggregates across rows, returning a single row but preserving all pivoted columns.

Pivoting Restructuring the shape of a data table is an important part of many KNIME workflows.The Pivoting node allows us to convert a "tall and skinny" table into one that is "shortand wide." In other words, the Pivoting node takes a table with relatively few columnsand many rows and creates one with relatively many columns and few rows.There are three tabs in its configuration menu that you must understand to use thisnode effectively:- Groups: Using the familiar include/exclude column selection boxes, identify whichcolumns, if any, you wish to group by. The selection made here dictates how manyrows the output table will contain. If you do not group by any fields, the output table willcontain a single row. If you select one or more fields, the number of rows in the outputwill be equal to the number of unique combinations of values in the selected columns.- Pivots: Again, using the include/exclude column selection boxes, identify whichcolumns contain the values that will become the column headers in the output table. - Manual Aggregation: Select the field or fields you wish to aggregate. At least one fieldmust be aggregated. For numeric fields, you may choose from options such as sum,mean, or standard deviation. Qualitative fields can be counted or concatenated.The Pivoting node returns three outputs:- Port 0 (the top port): This is the primary output port and returns the pivoted table withall rows, columns, and values present. This is what you will want to use the majority ofthe time.- Port 1 (the middle port): Aggregates across columns, returning one column peraggregation from the configuration menu.- Port 2 (the bottom port): Aggregates across rows, returning a single row butpreserving all pivoted columns. Here, we did not choose any grouping column, so thefinal output will have only one record. We grouped byRegion, so there will be one column per value in theRegion field. Finally, we aggregated Population bytaking the sum of its values for each Region. Night Heron Data, 2023 Group by County, pivot by Region, and take the sumof Population. We now have two records: one for each Countryvalue in the input data set. Notice that Region valuesthat aren't in a given Country are returned as missingvalues. Here, we have aggregated multiple fields. Whendoing multiple aggregations, it is helpful to keep theaggregation type in the column name to easily keeptrack of what each column represents. Input some dataNo groupingfields,one pivotGroup by onefieldMultipleaggregated fields Table Creator Pivoting Pivoting Pivoting Pivoting Restructuring the shape of a data table is an important part of many KNIME workflows.The Pivoting node allows us to convert a "tall and skinny" table into one that is "shortand wide." In other words, the Pivoting node takes a table with relatively few columnsand many rows and creates one with relatively many columns and few rows.There are three tabs in its configuration menu that you must understand to use thisnode effectively:- Groups: Using the familiar include/exclude column selection boxes, identify whichcolumns, if any, you wish to group by. The selection made here dictates how manyrows the output table will contain. If you do not group by any fields, the output table willcontain a single row. If you select one or more fields, the number of rows in the outputwill be equal to the number of unique combinations of values in the selected columns.- Pivots: Again, using the include/exclude column selection boxes, identify whichcolumns contain the values that will become the column headers in the output table. - Manual Aggregation: Select the field or fields you wish to aggregate. At least one fieldmust be aggregated. For numeric fields, you may choose from options such as sum,mean, or standard deviation. Qualitative fields can be counted or concatenated.The Pivoting node returns three outputs:- Port 0 (the top port): This is the primary output port and returns the pivoted table withall rows, columns, and values present. This is what you will want to use the majority ofthe time.- Port 1 (the middle port): Aggregates across columns, returning one column peraggregation from the configuration menu.- Port 2 (the bottom port): Aggregates across rows, returning a single row butpreserving all pivoted columns. Here, we did not choose any grouping column, so thefinal output will have only one record. We grouped byRegion, so there will be one column per value in theRegion field. Finally, we aggregated Population bytaking the sum of its values for each Region. Night Heron Data, 2023 Group by County, pivot by Region, and take the sumof Population. We now have two records: one for each Countryvalue in the input data set. Notice that Region valuesthat aren't in a given Country are returned as missingvalues. Here, we have aggregated multiple fields. Whendoing multiple aggregations, it is helpful to keep theaggregation type in the column name to easily keeptrack of what each column represents. Input some dataNo groupingfields,one pivotGroup by onefieldMultipleaggregated fields Table Creator Pivoting Pivoting Pivoting

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: johnemery
Created at: 2023-05-31
On NodePit since: 2023-06-01
Last update: 2024-05-02
Created with KNIME version: v4.7.2
Tags: pivotingpivotnode bitesnight heron data

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!