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.

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.

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.

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.

• 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.

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.

• 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.

• 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).

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.

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