Preprocessing Pipeline of CIC‐IDS2017 Dataset(Tuesday Working Hours)
Purpose of this document: This wiki documents every decision made in the BlackTrace preprocessing pipeline — what was done, why it was done, what alternatives were considered, and what each decision leads to downstream. It serves as a technical reference for other developers using the same dataset.
Network traffic data is especially messy with sensors malfunction, calculations produce mathematically undefined values, column names have invisible formatting errors, and the distribution of attack traffic versus normal traffic is wildly unequal. If we skip preprocessing or do it carelessly, models will silently learn the wrong things. They will memorize noise. They will achieve high accuracy numbers that mean nothing. They will fail completely on real data. A badly trained model can sometimes be fixed by tuning but badly preprocessed data almost always requires throwing everything away and starting over.
For BlackTrace, preprocessing serves four specific goals:
| Goal | What It Means in Practice |
|---|---|
| Correctness | Remove or repair values that are mathematically impossible or physically meaningless |
| Completeness | Ensure no values are missing before passing data to sklearn models, which cannot handle NaN |
| Feature Engineering | Add new columns that give models additional signal they would not otherwise have |
| Label Preparation | Convert human-readable attack names into numbers that models can process |
File used: Tuesday-WorkingHours.pcap_ISCX.csv
| Property | Value |
|---|---|
| Total rows | 445,909 |
| Total columns (raw) | 79 |
| Normal traffic (BENIGN) | 432,074 rows (96.9%) |
| FTP-Patator attack | 7,938 rows (1.8%) |
| SSH-Patator attack | 5,897 rows (1.3%) |
| Feature type | Pre-extracted CICFlowMeter statistical features |
| Label column | String: "BENIGN", "FTP-Patator", "SSH-Patator"
|
The following diagram shows the complete preprocessing pipeline — every step, what it receives, and what it produces.
flowchart TD
A([Raw CSV\n445,909 rows × 79 cols]) --> B
B["Step 1 — Load & Normalize\nStrip whitespace from column names"]
B --> C
C["Step 2 — Inspect Zero-Duration Flows\nIdentify flows where Flow Duration = 0\nDiagnose what type they are"]
C --> D
D["Step 3 — Add is_zero_duration Flag\nBinary column: 1 if Duration=0, else 0\nMust happen BEFORE touching throughput"]
D --> E
E["Step 4 — Handle Infinite Values\nStrategy A: zero-duration rows → throughput = 0\nStrategy B: other rows → cap at 99.9th percentile"]
E --> F
F["Step 5 — Inspect Missing Values\nVerify what NaN values remain after Step 4"]
F --> G
G["Step 6 — Handle Missing Values\nFill Flow Bytes/s NaN with median\nDo NOT drop rows"]
G --> H
H["Step 7 — Encode Labels\nCreate y_binary: 0=BENIGN, 1=ATTACK\nCreate y_multiclass: 0/1/2 per class"]
H --> I
I["Validation\nAssert: zero NaN\nAssert: zero Inf\nAssert: required columns present"]
I --> J
J([Processed CSV\n445,909 rows × 82 cols\n+ label_mapping.json])
style A fill:#2d3748,color:#e2e8f0
style J fill:#276749,color:#e2e8f0
style I fill:#744210,color:#e2e8f0
| Property | Raw CSV | Processed CSV |
|---|---|---|
| Rows | 445,909 | 445,909 (unchanged) |
| Columns | 79 | 82 (+3 engineered) |
| Infinite values | 327 | 0 |
| Missing values | 201 | 0 |
| Label format | String | Integer (+ original kept) |
| New columns added | — |
is_zero_duration, y_binary, y_multiclass
|
The raw CSV is loaded with pd.read_csv(). Immediately after loading, every column name is passed through .str.strip() to remove leading and trailing whitespace. CICFlowMeter, the tool that generated this dataset, introduces invisible leading whitespace into column names. The column that should be named Flow Duration is actually stored as " Flow Duration" with a leading space character. This is one of the most common silent failure modes when working with this dataset. If you write df["Flow Duration"] in your code without stripping first, Python raises a KeyError because the actual column name is " Flow Duration". The error message looks confusing because the names appear identical in the terminal. Every subsequent line of code in the pipeline can refer to columns by their clean names without worrying about whitespace. This is a one-time fix that makes all downstream code reliable.
Before modifying anything, I inspected flows where Flow Duration == 0 and print a label distribution showing which attack types appear in those rows.
BENIGN 261
FTP-Patator 3
264 total rows have zero duration. 261 are BENIGN, 3 are FTP-Patator attacks. This is an EDA (Exploratory Data Analysis) step embedded in the pipeline. Knowing that zero-duration flows include actual FTP-Patator rows is critical context for the next three decisions. If I had not inspected first and had simply dropped all zero-duration rows, I would have lost 3 labelled attack examples. That is a small number, but the principle matters. You should never drop data you have not examined. The inspection result directly informs the design of future steps. Because zero-duration rows include real attack flows, we know they cannot be blindly deleted.
A new binary column called is_zero_duration is created. It is 1 for every row where Flow Duration == 0, and 0 for everything else.
264 rows received a flag value of 1.
This is the most important sequencing decision in the entire pipeline. The flag must be created before any throughput values are modified because the Flow Bytes/s and Flow Packets/s columns contain infinity values specifically because Flow Duration is zero (division by zero). In Step 4, we will replace those infinity values. Once we replace them, we permanently lose the information about which rows triggered the infinity unless we have already recorded it in a separate column. If you create the flag after handling infinities, you are flagging rows based on Flow Duration, not based on the infinity values. For this dataset they happen to be the same rows, but the semantic meaning is different and the ordering guarantees correctness.
flowchart LR
A["Attacker launches\nbrute force scan"] --> B
B["Sends rapid connection\nattempts to target"] --> C
C["Most connections\nrejected immediately"] --> D
D["Flows complete in\nmicroseconds or less"] --> E
E["CICFlowMeter records\nFlow Duration = 0"] --> F
F["is_zero_duration = 1\nfor these rows"]
style A fill:#742a2a,color:#fed7d7
style F fill:#276749,color:#c6f6d5
Zero-duration flows are not random noise. In brute force and reconnaissance attacks, the attacker fires connection attempts rapidly. Most of these connections are rejected by the target immediately, producing flows that complete so fast the measurement timer cannot register any duration. A cluster of zero-duration flows from the same source IP in a short time window is one of the strongest signatures of scanning and brute force activity.
By adding is_zero_duration as a feature column, we give both the Isolation Forest and the Random Forest an additional dimension to reason about. When the Isolation Forest sees a point with is_zero_duration = 1, combined with high failed login counts, it has stronger evidence for flagging the flow as anomalous. This column becomes one of the 80 features my models train on. It is expected to appear in the top 15 feature importances in the Random Forest output.
Everything before this step was preparation for this step.
327 rows contain infinite values in Flow Bytes/s and/or Flow Packets/s. These infinities are caused by the formula used to generate these columns:
Flow Bytes/s = Total Bytes / Flow Duration
Flow Packets/s = Total Packets / Flow Duration
When Flow Duration = 0, the result is mathematically undefined division by zero. Depending on how the calculation was implemented in CICFlowMeter, the result was stored as inf (infinity) rather than raising an error. sklearn models cannot train on infinite values. If any infinity reaches a model, it will either crash or produce completely wrong results.
This is an important discrepancy. 264 rows have Flow Duration == 0, but 327 rows have infinite throughput values. The additional 63 rows have a duration that is very small but technically non-zero (for example, 1 microsecond). When a flow transfers a large amount of data in 1 microsecond, Total Bytes / 0.000001 produces an astronomically large number but not technically infinity, but effectively so. Depending on floating-point precision, some of these were stored as inf by the extraction tool. This means we have two distinct populations of problematic rows:
| Population | Size | Cause | Correct Treatment |
|---|---|---|---|
| Zero-duration flows | 264 | Exact division by zero | Set throughput to 0 |
| Near-zero-duration flows | 63 | Near-zero division producing extreme values | Cap at 99.9th percentile |
flowchart TD
A["327 rows with\ninfinite throughput"] --> B{Is this row\nzero-duration?}
B -->|"is_zero_duration = 1\n(264 rows)"| C["Strategy A\nSet throughput to 0"]
B -->|"is_zero_duration = 0\n(63 rows)"| D["Strategy B\nCap at 99.9th percentile"]
C --> E["Mathematically honest:\nno time elapsed,\nno measurable rate"]
D --> F["Preserves extreme nature\nof the value without\nallowing one outlier to\ndistort the entire model"]
E --> G([Both strategies\nproduce 0 inf values])
F --> G
style C fill:#276749,color:#c6f6d5
style D fill:#2c5282,color:#bee3f8
style G fill:#276749,color:#c6f6d5
Strategy A — Zero-duration rows: fill throughput with 0
For flows where Flow Duration = 0, the throughput values are mathematically undefined. There is no correct non-zero value we can substitute. Setting the throughput to 0 is the honest choice. It says "we cannot compute a meaningful rate for this flow." The is_zero_duration flag (created in Step 3) ensures the model still knows something unusual happened here.
Strategy B — Non-zero-duration rows: cap at 99.9th percentile
For the 63 rows with near-zero but non-zero durations, the flows genuinely had extreme throughput. Setting their throughput to 0 would be dishonest. It would make a high-speed anomalous flow look like idle traffic. Instead we cap at the 99.9th percentile of the non-zero-duration, finite rows. The percentile cap is calculated exclusively from rows where is_zero_duration = 0 and the throughput is finite. Including zero-duration rows in the percentile calculation would artificially lower the cap value.
Bytes/s cap = 145,000,000 (145 MB/s)
Packets/s cap = 2,000,000 (2 million packets/s)
These are extreme but physically plausible values for high-speed network attacks. They are preserved as large numbers, not zeroed out, so the Isolation Forest can still recognize them as outliers.
# 1. Calculate caps from clean rows only (before replacing anything)
finite_rows = df["is_zero_duration"] == 0
bytes_cap = df.loc[finite_rows, "Flow Bytes/s"].replace([np.inf, -np.inf], np.nan).quantile(0.999)
packets_cap = df.loc[finite_rows, "Flow Packets/s"].replace([np.inf, -np.inf], np.nan).quantile(0.999)
# 2. Replace all inf with NaN (unified representation before splitting by strategy)
df[throughput_cols] = df[throughput_cols].replace([np.inf, -np.inf], np.nan)
# 3. Strategy A — zero-duration rows
df.loc[df["is_zero_duration"] == 1, throughput_cols] = \
df.loc[df["is_zero_duration"] == 1, throughput_cols].fillna(0)
# 4. Strategy B — non-zero-duration rows
df.loc[df["is_zero_duration"] == 0, "Flow Bytes/s"] = \
df.loc[df["is_zero_duration"] == 0, "Flow Bytes/s"].fillna(bytes_cap)
df.loc[df["is_zero_duration"] == 0, "Flow Packets/s"] = \
df.loc[df["is_zero_duration"] == 0, "Flow Packets/s"].fillna(packets_cap)The sequence matters. Caps must be computed before replacing infinities with NaN, otherwise quantile() would return NaN.
After the infinity cleanup, we inspect the dataset for any remaining NaN values.
missing = df.isnull().sum()
missing = missing[missing > 0]After implementing the two-strategy infinity approach correctly, Step 5 reported No missing values remaining. This is because the 201 NaN values that existed in the raw Flow Bytes/s column were all in zero-duration rows. When Strategy A filled those rows with 0, the NaN values were filled at the same time. Inspection steps are defensive programming. The pipeline runs in sequence and each step can affect what the next step sees. If a future code change introduces a regression, this inspection step will catch it and report exactly which column is affected before any model training begins.
An inspection step that reports "nothing found" is not wasted. It is a passing test.
Any remaining NaN values are filled using the median of Flow Bytes/s from non-zero-duration rows.
if missing_before > 0:
finite_rows = df["is_zero_duration"] == 0
bytes_median = df.loc[finite_rows, "Flow Bytes/s"].median()
df["Flow Bytes/s"] = df["Flow Bytes/s"].fillna(bytes_median)The 201 rows that had NaN in Flow Bytes/s contained valid, meaningful data in all other 77 columns. Only one value was missing out of 79. Dropping the entire row because of one missing value discards 77 columns of perfectly good information.
The principle: drop a row only when the row itself is meaningless (as with the truly-empty zero-packet flows diagnosed earlier). Do not drop a row because one column is missing — fill the missing column instead. Flow Bytes/s has a heavily right-skewed distribution. A small number of flows transfer enormous amounts of data, pulling the mean far above what a typical flow looks like. The median is not affected by these extreme values, it simply represents the middle of the distribution. Using the median to fill missing values produces a more representative substitute than the mean.
Zero-duration rows had their throughput set to 0 in Step 4 (Strategy A). Including them in the median calculation would artificially lower the median toward zero, producing an unrepresentative fill value for rows that are not zero-duration.
The raw Label column contains strings: "BENIGN", "FTP-Patator", "SSH-Patator". Machine learning models in sklearn cannot train on string labels directly. They require numbers.
We create two separate label columns rather than one, because the two models in BlackTrace have different jobs and need different label formats.
flowchart LR
A["Raw Label\nstring column"] --> B["y_binary\n0 = BENIGN\n1 = any attack"]
A --> C["y_multiclass\n0 = BENIGN\n1 = FTP-Patator\n2 = SSH-Patator"]
B --> D["Used by:\nIsolation Forest evaluation\nConfusion matrix\nBinary precision/recall"]
C --> E["Used by:\nRandom Forest training\nMulticlass classification report\nAttack type identification"]
style B fill:#276749,color:#c6f6d5
style C fill:#2c5282,color:#bee3f8
y_binary: Created with a simple boolean comparison:
df["y_binary"] = (df["Label"] != "BENIGN").astype(int)This collapses all attack types into a single 1 value. It is used when the only question is "is this traffic normal or not?" — which is the question the Isolation Forest answers.
y_multiclass: Created with sklearn's LabelEncoder:
encoder = LabelEncoder()
df["y_multiclass"] = encoder.fit_transform(df["Label"])LabelEncoder assigns integers alphabetically to the unique class names. The resulting mapping is:
{
"BENIGN": 0,
"FTP-Patator": 1,
"SSH-Patator": 2
}This is used when the question is "if this is an attack, which specific attack type is it?" — which is the question the Random Forest answers.
The integer-to-label mapping is saved as label_mapping.json alongside the processed CSV. This is necessary for the FastAPI inference endpoint.
When a model predicts 1 for a new network flow, the endpoint needs to return "FTP-Patator" in the API response — not the number 1. Without the saved mapping, the inference service would need to re-fit the LabelEncoder every time it starts, which would require access to the training data at runtime.
{
"BENIGN": 0,
"FTP-Patator": 1,
"SSH-Patator": 2
}The raw string Label column is not dropped from the processed CSV. This is intentional — it makes the processed CSV human-readable and debuggable. When you open the file and see a row, you can immediately read the attack type without decoding an integer.
The Label column is excluded from the feature matrix (X) when loading data in the training scripts using NON_FEATURE_COLS = ["Label", "y_binary", "y_multiclass"].
After the pipeline completes, detection_engine/data/processed/Tuesday-WorkingHours-Processed.csv contains:
| Property | Value |
|---|---|
| Rows | 445,909 |
| Columns | 82 |
| NaN values | 0 |
| Infinite values | 0 |
| Original feature columns | 78 (all raw features from CICFlowMeter) |
| Engineered feature columns | 1 (is_zero_duration) |
| Label columns | 3 (Label, y_binary, y_multiclass) |
Columns 1–78: Original CICFlowMeter network flow features
Column 79: is_zero_duration (engineered — added in Step 3)
Column 80: Label (original string label — preserved for readability)
Column 81: y_binary (0=BENIGN, 1=ATTACK — for Isolation Forest evaluation)
Column 82: y_multiclass (0/1/2 per class — for Random Forest training)
When training scripts load the processed CSV, these three columns are always excluded from the feature matrix:
NON_FEATURE_COLS = ["Label", "y_binary", "y_multiclass"]
X = df.drop(columns=NON_FEATURE_COLS)is_zero_duration is included in the feature matrix — it is an engineered feature, not a label.
