KNIME_​project_​exam_​1

Step 1: Access the data from Files

1. Read Dirty Data csv with the CSV Reader node

2. Read Missing data csv with the CSV Reader node

3. Read Warehouses.csv with the CSV Reader node

Step 2: Clean text casing

1. Standardize nearest_warehouse casing (e.g. "nickolson" → "Nickolson")

2. Standardize season casing (e.g. "winter" → "Winter")

Step 3: Fix malformed dates

1. Flag rows with non-standard date formats (space, dash, or slash separators) using Rule Engine

2. Split flagged rows from correctly formatted rows with Row Splitter

3. Reformat flagged dates to yyyy-MM-dd with String Manipulation (regex)

4. Merge fixed and original rows back together with Concatenate

5. Convert the date column from String to Date&Time type

Step 4: Fix geo and outlier errors

1. Correct customer_lat sign errors (positive values flipped to negative) with Math Formula

2. Detect and null out extreme outliers in order_price and distance_to_nearest_warehouse using Numeric Outliers (IQR, multiplier 3.0)

Step 5: Recompute order_total

1. Calculate the correct order_total from order_price, coupon_discount, and delivery_charges

2. Compare the calculated value to the original order_total

3. Flag rows where the difference exceeds 1 as mismatches

4. Overwrite order_total with the corrected value for flagged rows

5. Remove temporary helper columns

Step 6: Impute remaining missing values

1. Fill missing order_price and distance_to_nearest_warehouse values (created by outlier removal) using median imputation

Step 7: Clean and impute missing_data.csv

1. Fill missing nearest_warehouse and season using most-frequent-value imputation

2. Fill missing order_price, order_total, distance_to_nearest_warehouse, customer_lat, and customer_long using median imputation

3. Lowercase the latest_customer_review text with String Manipulation

4. Infer missing is_happy_customer values from review keywords using Rule Engine (negative keywords → False, positive keywords → True, default True)

5. Remove the temporary review_lower helper column

Step 8: Combine cleaned datasets

1. Concatenate the cleaned Dirty Data and Missing Data branches into a single 1000-row table

Step 9: Join warehouse coordinates

1. Join the combined orders table with warehouses.csv on nearest_warehouse = names

2. Append warehouse latitude and longitude to each order row for use in map visualizations

3. Fix date column type consistency using Column Auto Type Cast (yyyy-MM-dd)

4. Remove the duplicate names column (redundant with nearest_warehouse after the join)

5. Rename lat/lon to warehouse_lat/warehouse_lon to distinguish from customer_lat/customer_long

6. Count distinct products in shopping_cart and append as basket_size