Deliver Data Wrangling

Group 068 — FIT5196 Assignment 2 (Semester 1, 2026)

A comprehensive data wrangling project covering data cleaning, imputation, outlier detection, and data reshaping/transformation for delivery-order and suburb-level datasets.

---

Group Members

#	Full Name	Student ID
1	Tianyang Huang	35310464
2	Yilin Shen	34754954
3	Vincent Ha	32518714
4	Hao Xie	32613571
5	Jingyang Zhang	34680314

---

Repository Structure

deliver-data-wrangling/
├── Group068_ass2_task1_polished_methodology.ipynb   # Task 1: Data Cleaning
├── Group068_ass2_task2_polished_methodology.ipynb   # Task 2: Data Reshaping & Transformation
└── README.md

---

Task 1 — Data Cleaning

File: Group068_ass2_task1_polished_methodology.ipynb

Cleans three delivery-order datasets, each exhibiting a different category of data quality issue, using a rule-based and model-assisted methodology. The datasets share a common schema of 12 columns including order metadata, branch/customer geolocation, order items/pricing, and delivery fee.

1. Missing Data Imputation

Imputes incomplete values in three columns following a dependency-aware order:

Column	Missing Count	Imputation Method
branch_code	100	Deterministic mapping from order_id prefix (e.g., ORDA → BK)
distance_to_customer_KM	50	Dijkstra shortest path on road-network graph (nodes.csv/edges.csv), validated to exact agreement on 450 known-distance records
delivery_fee	50	Branch-specific linear regression models (R²: BK=0.984, NS=0.982, TP=0.973), adjusted for 50% loyalty discount

Output: Group068_missing_data_solution.csv (500 rows × 12 columns, zero missing values)

2. Dirty Data Cleaning

Identifies and corrects semantic errors through cross-field validation, respecting the one-anomaly-per-row rule. Anomalies detected and fixed:

Column	Anomalies Fixed	Detection Rule
branch_code	37	Mismatch with order_id prefix (29 semantic + 8 lowercase)
date	37	Regex YYYY-MM-DD + invalid-month detection (15 DD-MM-YYYY + 22 YYYY-DD-MM)
order_type	37	Inconsistent with time under spec meal-time boundaries
customer_lat / customer_lon	41	Sign-drop on latitude (37) + full lat/lon swap (4)
distance_to_customer_KM	37	Disagreement with Dijkstra shortest path (>0.001 km)
order_items	37	Item outside the meal-specific menu
order_price	37	Disagreement (>$1) with sum(qty × unit_price) using OLS-recovered prices
customerHasloyalty?	37	Wrong-discount hypothesis vs reference delivery-fee model

Total: 300 column-level fixes across 300 unique rows (zero overlap between fix steps).

Output: Group068_dirty_data_solution.csv (500 rows × 12 columns)

3. Outlier Detection

Identifies delivery-fee outliers using residuals against the validated reference model (trained on the clean missing-data file). Threshold: |residual| > 1.0, anchored to the missing-data validation max error of ~0.92.

• 23 outliers detected and removed (12 BK, 8 TP, 3 NS)
• All other columns assumed clean per spec

Output: Group068_outlier_data_solution.csv (477 rows × 12 columns)

Key Dependencies

• pandas, numpy, networkx, scikit-learn, matplotlib
• Reference files: branches.csv, nodes.csv, edges.csv (road-network graph with 17,117 nodes and 25,491 edges)

---

Task 2 — Data Reshaping & Transformation

File: Group068_ass2_task2_polished_methodology.ipynb

Prepares the suburb_info dataset (202 Melbourne suburbs × 8 columns) for linear regression modelling by addressing skewness and scale differences.

Workflow

1. Initial Cleaning — Converts formatted columns ($, %, commas) from object to numeric types
2. EDA — Histograms, boxplots, scatterplots, and skewness analysis of 5 predictors and 1 target
3. Transformation Exploration — Compares Log, Square-Root, Square, and Box-Cox transformations
4. Transformation Selection — Chooses per-variable transformations balancing statistical improvement and interpretability
5. Scaling — Applies Min-Max scaling and Standardisation; selects Standardisation for coefficient interpretability
6. Model-Based Diagnostics — Compares raw vs. transformed data under linear regression
7. Multicollinearity Check — Correlation matrix of transformed predictors

Final Transformation Pipeline

Variable	Transformation	Scaling	Rationale
number_of_houses	Log	Standardisation	Strong right skew (original skew: 2.16 → −0.44)
number_of_units	Log	Standardisation	Strong right skew (original skew: 3.63 → −0.39)
population	Square-Root	Standardisation	Moderate skew, more interpretable than log (original skew: 1.09 → −0.03)
aus_born_perc	None	Standardisation	Bounded percentage; transformation offers limited benefit
median_income	None	Standardisation	Already approximately symmetric (skew: 0.17)
median_house_price (target)	Log	Unscaled	Reduces target skew (1.03 → 0.17)

Key Dependencies

• pandas, numpy, seaborn, matplotlib, scikit-learn

---

Getting Started

Prerequisites

• Python 3.8+
• Jupyter Notebook or Google Colab
• Required packages:

pip install pandas numpy matplotlib seaborn scikit-learn networkx openpyxl

Running the Notebooks

1. Clone this repository
2. Mount Google Drive (if using Colab) or update file paths to point to the data directory containing:
   • Group068_missing_data.csv, Group068_dirty_data.csv, Group068_outlier_data.csv
   • branches.csv, nodes.csv, edges.csv
   • suburb_info.xlsx
3. Run notebooks in order (Task 1 first, then Task 2) — Task 1's delivery-fee models are reused by its own outlier detection section

Output Files

Task 1 produces three cleaned CSV files in the working directory:

• Group068_missing_data_solution.csv
• Group068_dirty_data_solution.csv
• Group068_outlier_data_solution.csv

---

Methodology Notes

• Reproducibility: All cleaning steps follow a consistent structure: detect → explain → correct → validate
• Deterministic rules are preferred over statistical imputation where the correct value can be uniquely recovered
• Model-assisted approaches are used only where deterministic rules are unavailable, with validation on known-good data before application
• One-anomaly-per-row constraint is explicitly verified at the conclusion of dirty-data cleaning
• Outliers are removed, not corrected — only for the outlier-detection task; for the reshaping task, extreme values are handled through transformation rather than deletion