Creating a Pivot Table
A pivot table aims to arrange a table so that other stakeholders can easily share and understand data. This workflow demonstrates how to create a pivot table using KNIME Analytics Platform.
💡 To view each node's configuration, select the node and see the configuration pane on the right side of the workflow editor.
Let's walk through the different nodes involved in this operation:
Excel Reader node:
Since the folder with the data is already included when you download the workflow, in the "File and Sheet" tab, we choose to "Read from" the "Current workflow data area" and select the dataset.
In the "Data Area" tab, we select to read the "Whole sheet" and unflagged to skip "empty rows". This configuration allows us to read the sheet as it is. The intent is to respect its original structure.
Row Aggregator node:
We aggregate the data to get the total sum of medals won by each country.
We calculate the sum of columns "bronze", "silver", "gold", and "total", using the "country" column as category column.
Expression node:
We want to label the data according to the total number of medals won. For countries that have won more than 600 medals, we assign the country as label; for the other countries that have won less than 600 medals, we assign the label "Other". The aim is to reduce the number of columns shown in the pivot table.
We implement the following expression:
if($["total"]>600, $["country"],
"Other")
We append the label as new column ("country_renamed").
Value Lookup node:
We append the labels of the new column "country_renamed" to the input data table.
Lookup column and key column are both "country", the column in the Input panel is "country_renamed".
Pivot node:
The Pivot node accomplishes the same task as the pivot function in Excel: Creating a pivot table.
Group column: edition (specifies the unique row identifier)
Pivot column: country_renamed (its values will be transformed into columns)
Aggregation method: Sum of totals
The resulting pivot table has 29 rows (all of the Summer Olympic Games editions) and 9 columns (the countries and "Other").
Column Resorter node:
We move the column "Other" to the back of the table so that first the individual country columns are shown.
Missing Value node:
Some countries didn't win any medals in certain years. We replace the missing value by specifying to replace all missing integers with 0.
Excel Writer node:
We append the dataset to a new sheet called "pivot_table" in the existing Excel file located in the workflow data area.
After executing the node, the file will open automatically.