Charts and Spreadsheets

This workflow shows how to create charts from spreadsheet data and add them to a new spreadsheet using KNIME Analytics Platform.

The aim is to preprocess a spreadsheet to create charts, such as pie and bar charts. We also want to save the created charts as images
in the spreadsheet. We use the athlete event result data from the Summer Olympic Games of 1896-1912 for Greece, Great Britain and the USA.

Let's walk through the different nodes involved in this operation:

To view each node's configuration, double-click or right-click > "Configuration" to open the configuration dialogue.

Excel Reader nodes:

1 - In the "File and Sheet" tab, the file name and the sheet to read from are already set up (the folder with the data is already included when you download the workflow)
2 - Within the "Data Area" tab, we have selected read the "Whole sheet". This configuration allows us to have the sheet as it is. The intent is to respect its original structure.

Preprocessing (Column Filter, Value Lookup, Pivoting node):

Column Filter node:

We want to clean up the input table from unnecessary columns by manually including the columns "country_noc", "sport", and "athlete_id".

Value Lookup node:

We use the Value Lookup node to replace the "country_noc" values with the actual names of the countries.
1 - As a Lookup column (data table), we are taking the column "country_noc" from the table where we want to append the complete country names containing the country codes.
2 - Our key column (dictionary table) is also "country_noc". The dictionary table contains the country codes and also the full country names.
3 - We select column "country" from the low input table (our dictionary table) with the full country names to be appended.
4 - We delete the lookup column by selecting "Delete lookup column" because we won't need the "country_noc" column anymore.

Duplicate Row Filter node:

Since there are athletes that compete in multiple disciplines, we use the duplicate row filter to remove the additional rows. We do that by manually selecting only the "athlete_id" column.

Table Splitter nodes:

In our data table are entries for Greece, Great Britain, and the USA. We want to split the table according to these countries. We, therefore, use the Table Splitter nodes.
For example, to split all rows with Greece athletes from the table, we choose the "country" column as the lookup column, the matching criteria as "Equals", and the pattern as "Greece". We want to split the rows by the last match and include the matching row in the top table.

Similarly, we use the other Table Splitter nodes to split Great Britain from the USA and the 1896 Greece athletes from 1900-1912 Greece athletes.

Row Aggregator nodes:

We aggregate the " edition " column to get the number of British athletes competing in each Summer Olympic Games edition from 1896-1912 by selecting the "edition" column as a category column and choosing the "Occurrence count" as the aggregation method.

Analogously we do that for the USA, and similarly also to aggregate the Greece athletes from the 1896 Summer Olympic Games.

Pie Chart node:

We set the category dimension to "sport" and the frequency dimension to "OCCURENCE_COUNT" to obtain the pie chart. Since we want to use the image later in the spreadsheet, we also enable image generation by ticking the "Generate image" checkbox.

Bar Chart node:

To obtain the bar chart, we set the category column to "edition" and the frequency dimensions to "Great Britain" and "USA". Since we want to use a stacked bar chart, we also select the "Stacked" radio button in the "Plot" > "Arrange bars" section.

Like before, for the pie chart, we want to use the bar chart image later in the spreadsheet. We enable image generation by ticking the "Generate image" checkbox.

Add images to the spreadsheet (Image To Table, Column Appender, Excel Writer node):

Image To Table node:

To place an image in a table cell, we have to use the Image to Table node, where we give the column the image is placed in the name "Bar Chart" and "Pie Chart", respectively.

Column Appender node:

We want to join the images of the pie chart and bar chart to write them into the spreadsheet at once. That's why we append them using the column appender.

Excel Writer node:

In the last step, we write the images to a separate sheet named "Sheet_2" by appending it to the input excel file.

After executing the node, the file will open automatically.

As you can see from the output, we now have the images in "Sheet_2".