Icon

01 Date and Time and Databases

Date and Time and Databases - Exercise

This workflow shows a hands-on exercise in the L2-DS Introduction to KNIME Analytics Platform for Data Scientists - Advanced course

Task 1: Date and Time Manipulation1. Read the New York City Airbnb data by executing the CSV Reader node2. Convert the timestamps in the last_review column from string to Date&Time3. Remove rows with missing values in the last_review column4. Remove the time values from the timestamps5. Extract the day of the week from each timestamp6. Count the number of last reviews on each day of the week Task 2: Database Data Manipulation1. Connect to the Accommodation.sqlite database available in the "data" folder2. Write the Airbnb data provided by the Modify Time node into the database. Create a table calledAB_NYC_2019. Overwrite it if it exists already.3. Perform the following data manipulation tasks with the appropriate DB nodes:- Calculate the average room price in each neighborhood group- Join the price information to the original database table- Filter the data to rooms/apartments with at least one review4. Read the joined and filtered data into KNIME Read AB_NYC_2019dataRemove timeLast review to date&timeRemoves missing valuesModify timestampextract day of weekCount the number of reviewsNode 56AB_NYC_2019 tableNode 58Average price of room for each neighborhoofJoin price info with database tableFilter rooms/apartments with one review or more Read data CSV Reader Modify Time String to Date&Time Missing Value Modify Time Extract Date&TimeFields GroupBy Sorter DB Writer SQLite Connector DB GroupBy DB Joiner DB Row Filter DB Reader Task 1: Date and Time Manipulation1. Read the New York City Airbnb data by executing the CSV Reader node2. Convert the timestamps in the last_review column from string to Date&Time3. Remove rows with missing values in the last_review column4. Remove the time values from the timestamps5. Extract the day of the week from each timestamp6. Count the number of last reviews on each day of the week Task 2: Database Data Manipulation1. Connect to the Accommodation.sqlite database available in the "data" folder2. Write the Airbnb data provided by the Modify Time node into the database. Create a table calledAB_NYC_2019. Overwrite it if it exists already.3. Perform the following data manipulation tasks with the appropriate DB nodes:- Calculate the average room price in each neighborhood group- Join the price information to the original database table- Filter the data to rooms/apartments with at least one review4. Read the joined and filtered data into KNIME Read AB_NYC_2019dataRemove timeLast review to date&timeRemoves missing valuesModify timestampextract day of weekCount the number of reviewsNode 56AB_NYC_2019 tableNode 58Average price of room for each neighborhoofJoin price info with database tableFilter rooms/apartments with one review or more Read dataCSV Reader Modify Time String to Date&Time Missing Value Modify Time Extract Date&TimeFields GroupBy Sorter DB Writer SQLite Connector DB GroupBy DB Joiner DB Row Filter DB 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-10-23
On NodePit since: 2023-03-11
Last update: 2024-05-01
Created with KNIME version: v4.7.0
Tags: datetimestring to date&timemodify timeextract date&time fieldseducationdatabase

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

Try NodePit Runner!