01. The Challenge: Messy Data
Tracking the operational health of a logistics fleet requires analyzing daily breakdown records. However, the raw data exported from the system was highly unstructured. It contained inconsistent date formats, fragmented unit columns, and most importantly, the complaint (Keluhan) column contained free-text inputs where mechanics would lump multiple issues into a single cell using commas or ampersands (e.g., "Ban No. 2, Rem & Suspensi").
This made it impossible to analyze which specific vehicle parts were failing the most or how much downtime each issue caused without intensive manual data entry.
02. The ETL & Text Mining Pipeline
To automate this, I developed a Python (Pandas) ETL pipeline in a Jupyter Notebook:
- Data Cleaning & Standardization: Handled missing columns by merging unit fields, removed anomalous footers, and standardized datetime formats.
- Regex Parsing: Implemented smart string splitting (
re.split) to detect multiple delimiters (commas,&) and standardize abbreviations (e.g., standardizing"No,"to"No."). - Data Exploding: Used the
.explode()function to un-nest rows containing multiple complaints. A single row containing 3 issues was automatically split into 3 distinct rows, allowing accurate aggregation of downtime per specific component.
Raw Data (Before)
| Unit ID | Date | Complaint (Keluhan) |
|---|---|---|
| TRK-001 | 2025-10-14 | Ban No. 2, Rem & Suspensi |
| TRK-002 | 2025-10-15 | Ganti Oli, Filter Udara |
Cleaned & Exploded Data (After)
| Unit ID | Date | Exploded Complaint | Category |
|---|---|---|---|
| TRK-001 | 2025-10-14 | Ban No. 2 | Ban |
| TRK-001 | 2025-10-14 | Rem | Rem |
| TRK-001 | 2025-10-14 | Suspensi | Suspensi |
| TRK-002 | 2025-10-15 | Ganti Oli | Engine |
| TRK-002 | 2025-10-15 | Filter Udara | Filter Udara |
03. Tableau Visualization Output
The cleaned and exploded dataset was connected to a Tableau Dashboard, providing management with a clear, interactive view of breakdown trends, overall downtime duration, and component-level insights.
04. Key Deliverables & Outcomes
This automated text analytics pipeline eliminated the manual data wrangling bottleneck, turning messy operational logs into a structured database in seconds.
With the interactive Tableau dashboard, the maintenance team could easily isolate the most frequent breakdown causes, allocate spare parts more efficiently, and develop targeted maintenance strategies to reduce overall fleet downtime.