Pivot Table
A pivot table aims to arrange a table so that other stakeholders can easily share and understand data.
Let's walk through the different nodes involved in this operation:
Excel Reader node
Double-click or right-click > "Configure" to open the configuration dialogue:
1 - In the "File and Sheet" tab, the file name and the sheet to read from are already set up (when you download the workflow, it's also included the folder with the data)
2 - Within the "Data Area" tab, we have selected read the "whole sheet" and unflagged skip "empty rows". This configuration allows us to have the sheet as it is. The intent is to respect its original structure.
Using the Row Aggregator node allows us to aggregate the data and create an aggregate value of the medals won by each country. That's the typical structure of a pivot table.
The rule engines perform a differentiated task to bin the number of medals won into classes. We want to group all the countries that have won less than 600 medals (total) in a new category called "Other".
The aim here is to reduce the number of columns shown by the Pivot Table that we are creating and to focus only on the countries with more medals:
$total$ >600 => $country$
TRUE => "Other"
We append a new column: "country_renamed".
Within a Value Lookup node, we append the new column containing the label "Other" for the countries with less than 600 medals, using the "country" column as a key value.
KNIME offers a native node (Pivoting) that accomplishes the same task as the pivot function available in Excel.
The pivoting node allows the user to specify the group column, which will be the unique row identifier for the output table, the pivot columns whose categories will be transformed into single columns, and finally, the aggregation method value rendered into the table cells.
Having the above, we group by "edition", pivot by "country_renamed", and sum the column "total". The output is a Pivot table with 29 rows (all of the Summer Olympic Games editions) and 9 columns (the countries with the "Other" aggregated group). As cell values, we sum the total medals by edition and country.
After the pivoting, we use a couple of nodes to fill the missing values with 0 and arrange the column order differently.
The Excel Writer node appends the Pivot Table to a new sheet in the original file. Execute the node, and the excel file will open.