Charts and Spreadsheets
This workflow shows how to create charts from spreadsheet data and add them to a new sheet.
The aim is to preprocess the data to create charts (a Pie Chart and a Bar Chart). We also want to save the created charts as images
in a new sheet of the existing spreadsheet. We use the athlete event result data from the Summer Olympic Games of 1896-1912 for Greece, Great Britain and the USA.
💡 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 nodes:
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 read the "Whole sheet". This configuration allows us to have the sheet as it is. The intent is to respect its original structure.
Column Filter node:
We clean the input tables from unnecessary columns, which we don't need.
Value Lookup node:
We add the actual country names ("country" column) to the athlete results data based on the NOC codes ("country_noc" column) and remove the "country_noc" column.
We set "country_noc" both as lookup column and key column.
Under "Output", we define to remove the lookup column ("country_noc") from the output table and move the "country_noc" column of the dictionary in the "Exclude" panel.
Duplicate Row Filter node:
Some athletes took part in multiple disciplines, however, we only want to consider each athlete once per Olympics edition.
We remove duplicate rows by including "athelte_id" and "edition" to identify the duplicates.
Table Splitter node:
The dataset contains results for Greece, Great Britain, and the USA. We split the table so that all results for Greece" are available at the top part, the results for Great Britain and USA at the bottom part.
We choose the "country" column as the lookup column, the matching criteria as "Equals", and the pattern as "Greece". We split the rows by the last match and include the matching row in the top table.
Row Filter + Row Aggregator nodes:
In the top branch, we continue processing the data for Greece.
We first filter the Greek data to the 1896 Olympics edition.
Second, we calculate how many athletes participated in each discipline. Set "sport" as category column and select "Occurrence count" as aggregation method.
Pivot node:
In the bottom branch, we create a Pivot table that sums up the total number of athletes by country (GBR and USA) and Olympics edition.
Pie Chart node:
We set the category dimension to "sport" and the frequency dimension to "OCCURENCE_COUNT".
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:
We set the category column to "edition" and select as frequency dimensions the columns "Great Britain" and "USA". In the "Plot" > "Arrange bars" section we select "Stacked" to create a stacked bar chart.
Like with the Pie Chart, we enable image generation by ticking the "Generate image" checkbox to use the image later in the spreadsheet.
Image to Table + Column Appender nodes:
First, we convert each image into a table using the Image to Table node.
Second, we combine both tables containing the images using the Column Appender node.
Excel Writer node:
We write the images to a new sheet called "Sheet_2" in the existing file "Olympic_Athlete_Event_Results.xlsx".
After executing the node, the file will open automatically.
As you can see from the output, we now have the images in "Sheet_2".