Day 6: Data Preprocessing — Cleaning the Messy Reality of Enterprise Data

Real-world enterprise data is rarely ready for machine learning. Whether you are analyzing console output from high-performance networking hardware, such as troubleshooting transceiver EEPROM data on a Mellanox SN2100 switch, or aggregating daily trading volumes for Indian REITs and InVITs, the raw data will be full of errors, gaps and anomalies. (These are only examples to illustrate data problems.)

Data preprocessing is the engineering step that turns that chaotic raw information into the clean, numeric format that algorithms need. "Garbage in, garbage out" is the first rule of AI: a model can only learn from what you give it.

Diagram: Data preprocessing is an assembly line. Raw data passes through stations that handle missing values, outliers, duplicates and inconsistent units, and comes out clean. See the animated version.

In plain terms: Think of washing and chopping vegetables before cooking. Nobody enjoys it, but a great recipe cannot rescue a dish made from dirty, half-rotten ingredients. Preprocessing is the washing and chopping.

Why the data is messy in the first place

Enterprise data is rarely produced in one place. It is merged from many systems, for example a legacy HR database combined with a modern cloud ERP, plus sensors and device logs. Every system has its own habits, formats, units and failure modes, and the merge brings all the mess together.

Diagram: Enterprise data comes from many systems. Merging them brings together duplicates, inconsistent formats, gaps and clashing units that must be cleaned up. See the animated version.

1. Handling Missing Values

Incomplete datasets are the most common problem. Suppose a table of dividend yields for KRT REIT or PGINVIT is missing three days of records. Many algorithms cannot simply skip those gaps: they will raise an error, or quietly drop the rows without telling you. You need a deliberate strategy.

• Deletion (listwise or pairwise): remove the whole row that has a missing value. This is only sensible when the dataset is large and the missing data is random, because otherwise you throw away information and can bias the model.
• Imputation (mean, median or mode): replace the gap with a statistical estimate. If a temperature sensor on a server rack goes offline for one minute, replacing the null with the median of the surrounding five minutes lets the model carry on. The median is usually safer than the mean when values are skewed. For a category, such as a city name, the mode (most common value) is used.
• Advanced imputation: use another model, such as linear regression or nearest neighbours, to predict the missing value from the other columns.

Diagram: When a sensor drops out, the gap can be filled in different ways. A single mean ignores the trend, a local median is better, and a model-based fill follows the pattern of the surrounding data. See the animated version.

Diagram: A quick way to choose: delete only when few rows are missing at random in a large dataset, use mean or median for numbers, predict the value when other columns explain it, and use the mode for categories. See the animated version.

One more tip: sometimes the fact that a value is missing is itself a signal, for instance a sensor that keeps failing before a fault. A simple extra column that says "was missing: yes or no" lets the model use that.

2. Managing Outliers and Anomalies

Outliers are data points that sit far from the normal pattern. They can badly skew a model's idea of "normal".

• Identification: statistical boundaries flag the suspects. The Z-score counts how many standard deviations a value is from the mean (above about 3 is unusual). The interquartile range (IQR) rule flags values more than 1.5 times the IQR beyond the middle half of the data.

Diagram: The IQR rule flags a value as an outlier when it sits more than 1.5 times the interquartile range beyond the box. A z-score above about 3 is another common test. See the animated version.

• Resolution: not every outlier is a mistake. If a network switch reports a huge latency spike, that may be a real event, such as a DDoS attack, and you want your model to see it. If it is a data error, a technique called winsorization caps the extreme values at a chosen percentile. That reduces their effect on the model's weights without deleting the record.

Diagram: Not every outlier is an error. If a spike is a data fault, winsorization caps it at a percentile so it cannot skew the model. If it is a real event, such as an attack, keep it and flag it. See the animated version.

3. Deduplication and Resolving Inconsistencies

Merging systems often creates duplicates and format conflicts.

• Deduplication: models learn from observations, so every row should be unique. If the same server crash log appears 50 times, the neural network will over-weight that one error and learn that it is far more common than it really is.

Diagram: Fifty copies of the same error would make a model think that error is far more common than it really is. After deduplication, the one error counts once, and the data shows the true picture. See the animated version.

• Standardization of units and formats: every column must mean one thing. If one system reports network bandwidth in MB/s and another in Gbps, convert them to a single unit first. The same goes for dates, time zones, currencies and text spelling.

Diagram: Two systems can report the same network speed in different units. Converting everything to one unit stops the model from seeing a difference that is not real. See the animated version.

• Feature scaling: a related step. When one feature is in the hundreds of thousands (income) and another is between 0 and 100 (age), the large numbers can dominate. Scaling puts features on a comparable range, using min-max scaling or standardization (subtract the mean and divide by the standard deviation).

Diagram: Features on very different scales let the biggest numbers dominate. Min-max scaling or standardization puts them on a comparable scale so the model can weigh each fairly. See the animated version.

A worked example in Python

Here is a small, runnable example that applies all five fixes with the pandas library: removing the duplicate, converting units, filling the gap, capping the outlier and scaling.

import numpy as np
import pandas as pd

df = pd.DataFrame({
    "server": ["a", "a", "b", "c", "d", "e"],
    "temp_c": [41.0, 41.0, None, 43.0, 44.1, 95.0],   # one gap, one suspicious spike
    "speed":  [1.0, 1.0, 125.0, 0.8, 1.2, 1.1],
    "unit":   ["Gbps", "Gbps", "MB/s", "Gbps", "Gbps", "Gbps"],
})

df = df.drop_duplicates()                                          # 1. remove the repeated row
df["gbps"] = np.where(df["unit"] == "MB/s", df["speed"] * 8 / 1000, df["speed"])  # 2. one unit
df["temp_c"] = df["temp_c"].fillna(df["temp_c"].median())          # 3. fill the gap with the median

q1, q3 = df["temp_c"].quantile([0.25, 0.75])                       # 4. cap outliers with the IQR rule
df["temp_c"] = df["temp_c"].clip(q1 - 1.5 * (q3 - q1), q3 + 1.5 * (q3 - q1))

df["temp_scaled"] = (df["temp_c"] - df["temp_c"].min()) / (df["temp_c"].max() - df["temp_c"].min())  # 5. scale 0 to 1
print(df[["server", "temp_c", "gbps", "temp_scaled"]].round(2).to_string(index=False))

Running it gives clean values: the duplicate is gone, the missing temperature is filled with the median (43.55), the suspicious 95 degrees is capped at 45.75, and the speed is in one unit.

One important rule: split first

There is a subtle trap. If you calculate the median, the mean or the scaling range using the whole dataset, information from the test data seeps into training. This is called data leakage, and it makes a model look better than it really is. The safe order is: split the data first, learn the cleaning rules (the median, the scaling range, the IQR fences) from the training set only, and then apply those same rules to the test set. The short example above uses the whole table for simplicity, so in a real project, do the split first.

Diagram: Split the data first. Learn the cleaning rules, such as the median or the scaling range, from the training set only, then apply them to the test set. Otherwise information leaks from the test data. See the animated version.

Why it matters

By cleaning and standardizing the data first, you make sure the model learns the real business patterns, and not the accidental mistakes of your databases. Most of the quality of an AI system is decided here, before any model is chosen.

Coming Up Next

Day 7: Measuring success: accuracy, precision, recall and F1 scores.

DataScience #MachineLearning #DataPreprocessing #AILearning #TechEducation #DataEngineering #Innovation #EnterpriseAI

---

Originally published at https://sureshpallapothu.in/blog/day-6-data-preprocessing, where this post includes animated diagrams.