The following node is available in the Open Source KNIME predictive analytics and data mining platform version 2.7.1. Discover over 1000 other nodes, as well as enterprise functionality at
http://knime.com.
Pivoting
Performs a pivoting on the given input table using a selected number
of columns for grouping and pivoting. The group columns will result
into unique rows, whereby the pivot values turned into
columns for each set of column combinations together with each
aggregation method. In addition, the node returns the total
aggregation (a) based on only the group columns and (b) based on
only the pivoted columns resulting in a single row; optionally,
with the total aggregation without pivoting.
To change the aggregation method of more than one column select all
columns to change, open the context menu with a right mouse click
and select the aggregation method to use.
A detailed description of the available aggregation methods can be
found on the 'Description' tab in the node dialog.
If the 'Sort in memory' option is checked the complete table is
loaded into the memory to speed up the sorting process.
Dialog Options
Group settings
- Group columns
-
Select one or more columns according to which the group rows
are created.
Pivot settings
- Pivot columns
-
Select one or more columns according to which the pivot columns
are created.
- Ignore missing values
-
Ignore rows containing missing values in pivot column.
- Append overall totals
-
Appends the overall pivot totals with each aggregation performed
together on all selected pivot columns.
- Ignore domain
-
Ignore domain and use only the possible values available in the
input data.
Aggregation settings
- Aggregation methods
-
Select one or more columns for aggregation from the available
columns list. Change the aggregation method in the Aggregation
column of the table. You can add the same column multiple
times. In order to change the aggregation method of more than one
column select all columns to change, open the context menu with a
right mouse click and select the aggregation method to use.
Tick the missing box to include missing values. This option might
be disabled if the aggregation method does not support missing
values.
The parameter column shows an "Edit" button for all
aggregation operators that require additional information.
Clicking on the "Edit" button opens the parameter dialog
which allows changing the operator specific settings.
Advanced settings
- Maximum unique values per group
-
Defines the maximum number of unique values per group to avoid
problems with memory overloading. All groups with more unique
values are skipped during the calculation and a missing value is set
in the corresponding column, and a warning is displayed.
- Value delimiter
-
The value delimiter used by aggregation methods such as concatenate.
- Column naming
-
The name of the resulting aggregation column(s) depends on the
selected naming schema.
- Keep original name(s):
Keeps the original column names.
Note that you can use all aggregation columns only once with
this column naming option to prevent duplicate column names.
- Aggregation method (column name):
Uses the aggregation method first and appends the column name
in brackets
- Column name (aggregation method):
Uses the column name first and appends the aggregation method
in brackets
All aggregation methods get a * appended if the missing value option
is not ticked in the aggregation settings in order to distinguish
between columns that considered missing values in the aggregation
process and columns that does not.
- Enable hiliting
-
If enabled, the hiliting of a group row will hilite all rows of this
group in other views. Depending on the number of rows, enabling this
feature might consume a lot of memory.
- Process in memory
-
Process the table in the memory. Requires more memory but is faster
since the table needs not to be sorted prior aggregation.
The memory consumption depends on the number of unique groups and
the chosen aggregation method. The row order of the input table is
automatically retained.
- Retain row order
-
Retains the original row order of the input table.
Could result in longer execution time.
The row order is automatically retained if the process in memory
option is selected.
- Missing
-
Missing values are considered during aggregation if the missing
option is ticked for the corresponding row in the column
aggregation table.
Some aggregation methods do not support the changing of the missing
option such as means.
Ports
Input Ports
0 |
The input table to pivot.
|
Output Ports
0 |
Pivot table.
|
1 |
A table containing the totals for each defined group. That is, the
aggregation for each group ignoring the pivoting groups. This table
can be joined with the Pivot table; the RowIDs of both tables represent
the same groups). The table will contain as many rows as there are
different groups in the data and as many columns as there are selected
aggregations. The table is identical to the output of a GroupBy node,
in which the group and aggregation columns are chosen accordingly.
|
2 |
A single row table containing the aggregated values of the Pivot table.
The table structure is identical to the Pivot table (possibly enriched
by overall totals if the "Append overall totals" is selected. This
table is usually concatenated with table that results from joining
the Pivot table with the Group table.
|
This node is contained in KNIME Base Nodes
provided by KNIME GmbH, Konstanz, Germany.