TBM-data-cleaning

"Data cleaning and preprocessing pipeline for TBM operational parameters — MRT Orange Line, Bangkok"

Folder structure

PROCESSED DATA/ (gitignored — large data files, not committed to the repo) is organized into subfolders by pipeline stage:

Subfolder	Contents
combined_datasets/	Per-ring combined datasets — TBM1 WB (TBM1_WB_combined.csv v1, _v2 v2, _v3 v3 — final, 2,319 rings) and TBM2 EB (TBM2_EB_combined_v3.csv — final, 2,278 rings), the pipeline logs, parameter_inventory.csv, and parameter_summary.txt
visualizations/	Overview / timeseries / histogram / boxplot figures (v1, v2 & v3)
outlier_experiments/	Ring-level outlier-detection-method comparison experiment outputs
outlier_analysis/	(exploratory only) Cross-ring outlier reports, consensus-outlier details, summaries, and plots — produced by the rejected cross-ring approach (see scripts/exploratory/)
Before_After_Plots/	Per-parameter RAW (v1) vs CLEANED comparison plots
tbm_op_extract/	Extracted raw DL#####.Csv operation files — TBM1 WB (see "Local data setup" below)
tbm2_op_extract/	Extracted raw DL#####.Csv operation files — TBM2 EB
slides_qa/	unchanged

scripts/ mirrors the same structure:

Subfolder	Scripts
combined_datasets/	task1_parameter_inventory.py, task2_extract_combine_rings.py (TBM1 WB), task2_extract_combine_rings_TBM2.py (TBM2 EB), task2_v2_pipeline_log_summary.md, task2_v3_pipeline_log_summary.md
visualizations/	task3_visualize_parameters.py, plot_raw_vs_cleaned.py, plot_tbm_split.py
outlier_experiments/	task7_outlier_method_experiment_ring900.py, task7_outlier_method_experiment_multi_ring.py
exploratory/	(explored but rejected — not part of the final pipeline) task4_outlier_detection.py, task5_consensus_outlier_detail.py, task6_clean_dataset.py, tasks3-6_v2_results_comparison.md, tasks2-6_v3_results_comparison.md

Pipeline

The final cleaning pipeline is row-level cleaning only (Tasks 1–3). The final cleaned dataset is the output of Task 2 — TBM1_WB_combined_v3.csv, 2,319 rings, all retained. Cross-ring outlier detection (Tasks 4–6) was explored but rejected; those scripts are preserved under scripts/exploratory/ for reference (see below).

Task	Script	Status	Output
Task 1 — Parameter inventory	scripts/combined_datasets/task1_parameter_inventory.py	current	PROCESSED DATA/combined_datasets/parameter_inventory.csv, parameter_summary.txt
Task 2 — Extract & combine rings (TBM1 WB)	scripts/combined_datasets/task2_extract_combine_rings.py	v3 (current) — final cleaned dataset	PROCESSED DATA/combined_datasets/TBM1_WB_combined_v3.csv (2,319 rings — final)
Task 2 — Extract & combine rings (TBM2 EB)	scripts/combined_datasets/task2_extract_combine_rings_TBM2.py	v3 (current) — final cleaned dataset	PROCESSED DATA/combined_datasets/TBM2_EB_combined_v3.csv (2,278 rings — final)
↳ (superseded) Task 2 v2	—	superseded by v3	PROCESSED DATA/combined_datasets/TBM1_WB_combined_v2.csv (2,319 rings, 3-sigma)
↳ (superseded) Task 2 v1	—	superseded by v2	PROCESSED DATA/combined_datasets/TBM1_WB_combined.csv (2,332 rings)
Task 3 — Visualize parameters	scripts/visualizations/task3_visualize_parameters.py	current	PROCESSED DATA/visualizations/TBM1_WB_overview_v3.png
Task 7 — Outlier-method experiment	scripts/outlier_experiments/task7_outlier_method_experiment_ring900.py, ..._multi_ring.py	experiment	PROCESSED DATA/outlier_experiments/outlier_method_experiment_*.png/csv

Task 2 cleaning pipeline (v3): each ring's SC=2 rows are run through an ordered cleaning pipeline before averaging into one record per ring:

1. Filter System Condition == 2 (excavation rows)
2. Remove row-level outliers via Isolation Forest (multivariate; one IsolationForest(contamination=0.1) fit per ring on all 19 numeric columns jointly; a row is dropped if flagged as an anomaly)
3. Apply EMA smoothing (α = 0.3) to all numeric columns
4. Filter on Net Stroke ([1]Net Stroke, 100–1300 mm), using the smoothed values
5. Average the rows that survive all four steps → one record per ring

The only rings excluded from the final dataset are the 20 rings dropped at the row level — 7 with no SC=2 rows and 13 whose rows were all eliminated by the Net Stroke filter. These are logged in the per-ring pipeline log (TBM1_WB_combined_v3_pipeline_log.csv, gitignored). A markdown summary is committed at scripts/combined_datasets/task2_v3_pipeline_log_summary.md. No rings are removed or flagged based on cross-ring outlier detection.

Task 2 v1 → v2 → v3: v1 averaged SC=2 rows directly. v2 added the ordered row-level cleaning pipeline (3-sigma + EMA + Net Stroke). v3 replaces the per-column 3-sigma step with multivariate Isolation Forest (scripts/combined_datasets/task2_v2_pipeline_log_summary.md documents v2).

TBM2 (East Bound) — processed

The same v3 pipeline has been run on the TBM2 EB drive via scripts/combined_datasets/task2_extract_combine_rings_TBM2.py — a copy of the TBM1 script with identical column logic (TBM2's 2,560-column structure was confirmed compatible: same [4]System Condition, [1]Net Stroke, and 19 MEAN_COLS positions), differing only in input folder, output names, an optional --sample N timing flag, and BLAS threads pinned to 1.

Drive	Ring files	Final rings	Retention	Excluded (no SC=2 / no stroke survivors)
TBM1 WB	2,339	2,319	99.1%	20 (7 / 13)
TBM2 EB	2,326	2,278	97.9%	48 (11 / 37)

Output: TBM2_EB_combined_v3.csv + TBM2_EB_combined_v3_pipeline_log.csv. TBM2 cleans to a near-identical per-ring profile (mean rows/ring SC=2 → final: 69.0 → 57.8, vs TBM1 68.3 → 57.1) but excludes more rings, driven by larger multi-ring stoppage clusters (e.g. 489–494, 964–969, 1354–1364, 1889–1894) on the East Bound drive.

Exploratory — cross-ring outlier detection (rejected)

Tasks 4–6 explored detecting outlier rings by running 3-Sigma / IQR / Isolation Forest across the per-ring averages, then removing or flagging the consensus outliers. This cross-ring approach was rejected: the final dataset keeps all 2,319 rings, with no removal or flagging based on cross-ring detection. The scripts and their v2/v3 comparison write-ups are kept under scripts/exploratory/ for documentation:

Script	What it explored
task4_outlier_detection.py	Flag per-ring outliers via 3-Sigma / IQR / Isolation Forest
task5_consensus_outlier_detail.py	Detail report for rings flagged by all three methods
task6_clean_dataset.py	Remove confirmed-error rings, flag ambiguous ones for review
tasks3-6_v2_results_comparison.md, tasks2-6_v3_results_comparison.md	v1→v2 and v2→v3 result comparisons

Task 7 (experiment): compares 7 row-level outlier detection pipelines (3-Sigma, IQR, Isolation Forest — each on all 19 vs. 4 key columns — plus SC=2 as a reference) on the raw, unfiltered data for ring 900. Outputs two stacked-subplot figures (sorted by Net Stroke, and original CSV row order) and a summary CSV of flagged-row counts/overlaps.

Local data setup

PROCESSED DATA/tbm_op_extract/ is gitignored (raw extracted CSVs are large and not committed to the repo). The processing scripts (e.g. scripts/combined_datasets/task2_extract_combine_rings.py) expect this folder to contain the extracted DL#####.Csv operation files.

If tbm_op_extract/ is missing or empty, re-extract it from the source RAR:

C:\Drive D\KMUTT\Master's\Research\RAW DATA\TBM parameters\TBM parameters\TBM parameter\TBM1\TBM1 Operation WB E1.rar

Extract its contents into:

C:\Drive D\KMUTT\Master's\Research\Clean data\PROCESSED DATA\tbm_op_extract\

The TBM2 EB data follows the same procedure. Its archive (TBM2 Operation EB E1.rar) is located in the same TBM1/ folder as the TBM1 archive, and is extracted into PROCESSED DATA/tbm2_op_extract/TBM2 Operation EB E1/.

Notes:

• Use the UnRAR.exe CLI (e.g. C:\Program Files\WinRAR\UnRAR.exe) — the WinRAR GUI app cannot be driven non-interactively.
• UnRAR.exe fails to parse source/destination paths containing an apostrophe (e.g. Master's). Workaround: copy the RAR to a path without an apostrophe, extract there, then copy/move the results into tbm_op_extract/ (or tbm2_op_extract/).
• Each extracted archive contains ~2,3xx DL#####.Csv ring files (~1.2 GB total). Non-ring files (DLM####.Csv, RPM####_*.Csv) may also be present; the pipeline ignores them (it globs DL\d{5}\.Csv only).