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.