Icon

03 Aggregation and Data Blending

03 Aggregation and Data Blending - Exercise

This workflow shows a hands-on exercise in the L1-DW KNIME Analytics Platform for Data Wranglers: Basics course

Task 1: Aggregate data with the GroupBy node1. Read the life expectancy data by executing the provided Excel Reader node2. Calculate the number of countries by rank3. Calculate the minimum and maximum overall life expectancy in the data Task 4: Concatenate tables1. Read life expectancy data by executing the provided workflow. The Excel table contains the data for countries and the database table contains thedata for continents.2. Concatenate the tables. Use union of columns. Task 3: Join tables1. Read the population, life expectancy, geo coordinates, and travel advisories data by executing the provided reader nodes2. Join all tables based on the names of the countries. Use inner join.3. Edit the following country names in the travel advisories table manually with the Table Editor node: (The spelling in the other tables is shown inbrackets)- The Gambia (Gambia)- Guinea Bissau (Guinea-Bissau)- The Bahamas (Bahamas) Task 2: Aggregate data with the Pivoting node1. Read the population data by executing the provided metanode2. Create a pivot table that shows the number of countries by the UN continental region and direction. (The direction indicates ifthe population is increasing or decreasing.) Demographics.sqliteLifeExpectancyNode 19demographics.xlsx( life_expectancy)demographics.xlsx(life_expectancy)geo_coordinates.tabletravel_advidories.csvdemographics.xlsx(population)demographics.xlsx(life_expectancy)Node 40Node 41Node 42Node 43Node 44Node 45Node 46Node 47SQLite Connector DB Table Selector DB Reader Excel Reader Excel Reader Population data Table Reader CSV Reader Excel Reader Excel Reader GroupBy GroupBy Pivoting Table Editor Joiner Joiner Joiner Concatenate Task 1: Aggregate data with the GroupBy node1. Read the life expectancy data by executing the provided Excel Reader node2. Calculate the number of countries by rank3. Calculate the minimum and maximum overall life expectancy in the data Task 4: Concatenate tables1. Read life expectancy data by executing the provided workflow. The Excel table contains the data for countries and the database table contains thedata for continents.2. Concatenate the tables. Use union of columns. Task 3: Join tables1. Read the population, life expectancy, geo coordinates, and travel advisories data by executing the provided reader nodes2. Join all tables based on the names of the countries. Use inner join.3. Edit the following country names in the travel advisories table manually with the Table Editor node: (The spelling in the other tables is shown inbrackets)- The Gambia (Gambia)- Guinea Bissau (Guinea-Bissau)- The Bahamas (Bahamas) Task 2: Aggregate data with the Pivoting node1. Read the population data by executing the provided metanode2. Create a pivot table that shows the number of countries by the UN continental region and direction. (The direction indicates ifthe population is increasing or decreasing.) Demographics.sqliteLifeExpectancyNode 19demographics.xlsx( life_expectancy)demographics.xlsx(life_expectancy)geo_coordinates.tabletravel_advidories.csvdemographics.xlsx(population)demographics.xlsx(life_expectancy)Node 40Node 41Node 42Node 43Node 44Node 45Node 46Node 47SQLite Connector DB Table Selector DB Reader Excel Reader Excel Reader Population data Table Reader CSV Reader Excel Reader Excel Reader GroupBy GroupBy Pivoting Table Editor Joiner Joiner Joiner Concatenate

Nodes

Extensions

Links