Icon

06_​Data_​Aggregation_​Solution

Data Aggregation - Solution

Solution to exercise 7 for the KNIME Analytics Platform for Data Wranglers course
- Calculate multiple aggregation using the Pivoting and GroupBy node
- Join grouped data
- Extract date fields from a DateTime value
- Handle missing values
- Manipulate strings
- Calculate new features using the Math Formula node




Exercise 6: Data AggregationIn this exercise you generate new columns by aggregating data by groups and create pivot tables that showaggregated values by two or more groups Optional3a) Calculate the number of purchased products by order number and transaction type3b) Create a pivot table that shows the numbers of orders by basket size and transactiontype3c) Set missing numbers of orders in the pivot table to zero3d) Calculate the proportions of the different basket sizes among each transaction type(Math Formula (Multi Column) node) 2a) Calculate the total purchase amount by year, quarter and transaction type (Pivotingnode)2b) Set missing purchase amounts to zero2c) Combine the year and quarter information to a single column. Use "/" as a separator.(String Manipulation node) 1a) Calculate the sum of product prices in 2019 by customer ID, newsletter, and birthday(GroupBy node)1b) Rename the aggregated column to "Basket Sum 2019"1c) Repeat steps 1a and 1b for the purchases overall. Rename the aggregated column to"Basket Sum Overall".1d) Join the aggregated columns on the CustomerID. Use inner join. Integer: 0Double: 0.0BasketSumper CustomerBasketSumper CustomerInteger: 0Share of column total"Year/Quarter"on Customer IDTop 2019Bottom before 2019 Pivoting Missing Value GroupBy GroupBy Column Rename Column Rename GroupBy Pivoting Missing Value Math Formula(Multi Column) String Manipulation Joiner Read joined andpreprocessed data Row Splitter Exercise 6: Data AggregationIn this exercise you generate new columns by aggregating data by groups and create pivot tables that showaggregated values by two or more groups Optional3a) Calculate the number of purchased products by order number and transaction type3b) Create a pivot table that shows the numbers of orders by basket size and transactiontype3c) Set missing numbers of orders in the pivot table to zero3d) Calculate the proportions of the different basket sizes among each transaction type(Math Formula (Multi Column) node) 2a) Calculate the total purchase amount by year, quarter and transaction type (Pivotingnode)2b) Set missing purchase amounts to zero2c) Combine the year and quarter information to a single column. Use "/" as a separator.(String Manipulation node) 1a) Calculate the sum of product prices in 2019 by customer ID, newsletter, and birthday(GroupBy node)1b) Rename the aggregated column to "Basket Sum 2019"1c) Repeat steps 1a and 1b for the purchases overall. Rename the aggregated column to"Basket Sum Overall".1d) Join the aggregated columns on the CustomerID. Use inner join. Integer: 0Double: 0.0BasketSumper CustomerBasketSumper CustomerInteger: 0Share of column total"Year/Quarter"on Customer IDTop 2019Bottom before 2019 Pivoting Missing Value GroupBy GroupBy Column Rename Column Rename GroupBy Pivoting Missing Value Math Formula(Multi Column) String Manipulation Joiner Read joined andpreprocessed data Row Splitter

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: 2019-10-18
On NodePit since: 2023-04-05
Last update: 2024-11-04
Created with KNIME version: v4.7.2
Tags: aggregateGroupBypivotdate&timeETLeducation

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

Try NodePit Runner!