Icon

07 Join and Concatenate

Exercises for joining and concatenating data.

Join tables with different information for the same ID. Concatenate tables with the same information for different IDs.

Exercise: Concatenate1) Read the life expectancy data from two sources: an Excel sheet that contains the data for countries and a database table that contains the datafor continents.2) Concatenate them into one table. Use union of columns. What happens to the Rank column that is missing in the top table? It is when "Use unionof columns" is selected Exercise: Join1) Read the population, life expectancy, geo coordinates, and travel advisories data by executing the reader nodes below2) Join all tables based on the names of the countries. Use inner join. How many rows are there in the final joined table? Were any rows discardedduring the joining operation? 1743) OPTIONAL: The names of the following countries are written differently in the travel advisories table compared to the other tables. The spelling inthe other tables is shown in brackets:- The Gambia (Gambia)- Guinea Bissau (Guinea-Bissau)- The Bahamas (Bahamas)Edit the country names in the travel advisories table manually with the Table Editor node. Join the edited table with the other tables. How many rowsare there in the joined table now? 177Hint! You can drag and drop the files from KNIME Explorer to the workflow editorChange the file paths to workflow relative paths starting with knime://knime.workflow/ Demographics.sqliteLifeExpectancyManual manipulationLE for countriesand continentsdemographics.xlsx(population)demographics.xlsx( life_expectancy)geo_coordinates.tabletravel_advidories.csvInner join on country nameInner join on country nameInner join on country namedemographics.xlsx(life_expectancy)SQLite Connector DB Table Selector DB Reader Table Editor Concatenate Excel Reader Excel Reader Table Reader CSV Reader Joiner Joiner Joiner Excel Reader Exercise: Concatenate1) Read the life expectancy data from two sources: an Excel sheet that contains the data for countries and a database table that contains the datafor continents.2) Concatenate them into one table. Use union of columns. What happens to the Rank column that is missing in the top table? It is when "Use unionof columns" is selected Exercise: Join1) Read the population, life expectancy, geo coordinates, and travel advisories data by executing the reader nodes below2) Join all tables based on the names of the countries. Use inner join. How many rows are there in the final joined table? Were any rows discardedduring the joining operation? 1743) OPTIONAL: The names of the following countries are written differently in the travel advisories table compared to the other tables. The spelling inthe other tables is shown in brackets:- The Gambia (Gambia)- Guinea Bissau (Guinea-Bissau)- The Bahamas (Bahamas)Edit the country names in the travel advisories table manually with the Table Editor node. Join the edited table with the other tables. How many rowsare there in the joined table now? 177Hint! You can drag and drop the files from KNIME Explorer to the workflow editorChange the file paths to workflow relative paths starting with knime://knime.workflow/ Demographics.sqliteLifeExpectancyManual manipulationLE for countriesand continentsdemographics.xlsx(population)demographics.xlsx( life_expectancy)geo_coordinates.tabletravel_advidories.csvInner join on country nameInner join on country nameInner join on country namedemographics.xlsx(life_expectancy)SQLite Connector DB Table Selector DB Reader Table Editor Concatenate Excel Reader Excel Reader Table Reader CSV Reader Joiner Joiner Joiner Excel Reader

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: Maarit Laukkanen
Created at: 2020-04-20
On NodePit since: 2023-03-09
Last update: 2024-05-01
Created with KNIME version: v4.7.0
Tags: joinconcatenate

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!