Data Cleaning and Preprocessing

Raw engineering datasets are rarely clean. Sensors malfunction, communication links drop packets, analog-to-digital converters saturate, and manual data entry introduces typos. Before any meaningful analysis or model training can take place, every dataset must pass through a disciplined cleaning and preprocessing pipeline. This process involves detecting and filling gaps, identifying anomalous readings, enforcing correct data types, removing duplicate records, scaling numerical features into comparable ranges, and reshaping time-series data into the temporal resolution the analysis requires. Each of these steps is both a technical operation and an engineering judgment call — the right choice depends on the physical process being measured, the downstream task, and the acceptable trade-off between data retention and data integrity.

Detecting and Handling Missing Values

Missing values appear in a pandas DataFrame as NaN (Not a Number) for numeric columns or None for object columns. The first diagnostic step is always to count them. The method df.isnull().sum() returns the number of missing entries in every column, giving an immediate picture of which signals are most affected.

import pandas as pd
import numpy as np

df = pd.read_csv("sensor_log.csv", parse_dates=["timestamp"], index_col="timestamp")

# Count missing values per column
missing_counts = df.isnull().sum()
print(missing_counts)

# Express as a percentage of total rows
missing_pct = df.isnull().mean() * 100
print(missing_pct.round(2))

A column with fewer than 1–2 % of values missing behaves very differently from one with 40 % missing. The percentage guides the choice of remedy: a tiny fraction can be imputed safely; a large fraction may indicate a faulty sensor whose readings should be discarded entirely for that period.

Visualizing missing data patterns before selecting a strategy is crucial. If gaps are random and short, simple interpolation is appropriate. If an entire sensor channel is missing for a contiguous block of hours, that block almost certainly reflects a sensor fault or communication outage, and filling it with statistics computed from healthy data could introduce fictitious information. Libraries such as missingno provide matrix and heatmap visualizations that make systematic patterns immediately visible.

dropna() removes rows or columns that contain missing values. Its most important parameters are axis (0 to drop rows, 1 to drop columns), how ('any' drops a row if even one value is missing; 'all' drops it only if every value is missing), and thresh, which keeps a row only if it has at least n non-null values.

# Drop rows where any column is NaN
df_clean = df.dropna(axis=0, how="any")

# Drop rows that are almost entirely empty — keep rows with at least 5 valid values
df_clean = df.dropna(thresh=5)

# Drop columns that are entirely missing
df_clean = df.dropna(axis=1, how="all")

fillna() replaces missing values rather than discarding them. Common strategies include filling with a constant (such as zero or a known sensor default), forward-filling the last known value (method="ffill"), backward-filling from the next known value (method="bfill"), or filling with a column's mean or median.

# Fill with column mean
df["temperature"].fillna(df["temperature"].mean(), inplace=True)

# Forward fill — carry the last valid reading forward
df["pressure"].fillna(method="ffill", inplace=True)

# Backward fill — bring the next valid reading backward
df["flow_rate"].fillna(method="bfill", inplace=True)

# Fill with a specific constant
df["valve_state"].fillna(0, inplace=True)

Forward fill is a natural default for sensor data because the physical quantity typically changes continuously — the last measured value is a reasonable estimate for the next instant. However, if the gap is long, forward fill silently introduces a flat signal that may mislead downstream algorithms, so it is good practice to cap the maximum number of consecutive fills: df["pressure"].fillna(method="ffill", limit=5).

interpolate() is the most sophisticated gap-filling option. Linear interpolation draws a straight line between the two flanking valid values and assigns each missing point a proportional position along that line. Polynomial and spline modes fit higher-order curves, which better capture smooth continuous signals such as temperature ramps or pressure waves.

# Linear interpolation along the time axis
df["temperature"].interpolate(method="linear", inplace=True)

# Cubic spline interpolation — smoother for slowly varying signals
df["vibration"].interpolate(method="cubic", inplace=True)

# Time-aware interpolation (respects unequal time spacing)
df["temperature"].interpolate(method="time", inplace=True)

Time-aware interpolation is particularly important when the index is a DatetimeIndex with irregular spacing — it weights intermediate values by actual elapsed time rather than row number.

Identifying and Treating Outliers

An outlier is a data point that lies far from the rest of the distribution. In engineering contexts, outliers have two fundamentally different origins: genuine extreme events (a true pressure spike during a valve closure, for example) and instrument artifacts (a stuck sensor reading zero, a corrupted ADC sample producing a maximum-range value). Treating these two types identically would be a mistake — the first category contains real information; the second is noise to be eliminated. Domain knowledge is therefore irreplaceable: no purely statistical method can reliably distinguish a real alarm event from a sensor glitch without understanding the process.

Visual inspection with box plots and scatter plots is the natural starting point. A box plot immediately reveals the median, the interquartile range, and any individual points that plot beyond the whiskers. A time-series scatter plot shows whether extreme values are isolated spikes (likely artifacts) or part of a sustained transient (likely real).

import matplotlib.pyplot as plt

# Box plot of all numeric columns
df.plot(kind="box", subplots=True, layout=(2, 3), figsize=(12, 8))
plt.tight_layout()
plt.show()

# Time-series scatter for one channel
df["pressure"].plot(style=".", markersize=2, figsize=(14, 4))
plt.title("Pressure over time")
plt.show()

The interquartile range (IQR) method is a robust statistical test for outliers. It computes the 25th percentile (Q1) and 75th percentile (Q3), forms IQR = Q3 − Q1, and flags any value below Q1 − 1.5×IQR or above Q3 + 1.5×IQR as a potential outlier. The multiplier 1.5 is the conventional choice for exploratory analysis; a multiplier of 3.0 is used to detect only extreme outliers.

Q1 = df["pressure"].quantile(0.25)
Q3 = df["pressure"].quantile(0.75)
IQR = Q3 - Q1

lower_fence = Q1 - 1.5 * IQR
upper_fence = Q3 + 1.5 * IQR

# Boolean mask of outliers
outlier_mask = (df["pressure"] < lower_fence) | (df["pressure"] > upper_fence)
print(f"Outliers detected: {outlier_mask.sum()}")

The Z-score method measures how many standard deviations each value is from the column mean. A common threshold is |Z| > 3, meaning values more than three standard deviations from the mean are flagged. This method is sensitive to the presence of outliers in the mean and standard deviation estimates themselves, so it works best when the bulk of the data is approximately normally distributed and the fraction of outliers is small.

from scipy import stats

z_scores = np.abs(stats.zscore(df["pressure"].dropna()))
outlier_mask_z = z_scores > 3

Once outliers are identified, three treatment strategies are available. Removal simply drops the offending rows — acceptable when the number of affected rows is small and the outliers are confirmed artifacts. Capping (Winsorization) replaces extreme values with the fence values rather than removing them, preserving the row count and temporal continuity of the dataset. Flagging adds a separate boolean column marking outlier rows; the original values are retained and the flag is used as a feature or filter in downstream steps.

# Capping (Winsorization)
df["pressure_capped"] = df["pressure"].clip(lower=lower_fence, upper=upper_fence)

# Flagging
df["pressure_outlier"] = outlier_mask.astype(int)

# Removal
df_no_outliers = df[~outlier_mask]

Correcting Data Types

Data loaded from CSV files, databases, or web APIs often arrives with incorrect or suboptimal types. Numeric columns may be read as strings if even one row contains a non-numeric character. Categorical codes may be stored as integers consuming far more memory than necessary. Timestamps may be plain strings, making time arithmetic impossible. Correcting types is therefore not cosmetic — it enables correct computation and can dramatically reduce memory footprint.

astype() is the general-purpose casting method. It converts a Series or an entire DataFrame to a specified dtype.

# Convert a column read as object (string) to float
df["flow_rate"] = df["flow_rate"].astype(float)

# Convert an integer column to a smaller integer type to save memory
df["sensor_id"] = df["sensor_id"].astype("int16")

# Convert a numeric column to boolean
df["alarm"] = df["alarm"].astype(bool)

pd.to_numeric() with errors='coerce' is safer than astype(float) when the column may contain stray non-numeric strings. Instead of raising a ValueError, it silently converts unparseable values to NaN, which can then be handled with the missing-value strategies described above.

# "pressure" column has some entries like "ERR" or "---"
df["pressure"] = pd.to_numeric(df["pressure"], errors="coerce")
# Non-numeric entries are now NaN, ready for imputation

String columns that represent a fixed set of categories — equipment identifiers, operating modes, sensor locations — are best stored as the Categorical dtype. Pandas stores a Categorical column as an integer code array plus a small lookup table of unique strings, which can reduce memory by an order of magnitude for columns with many repeated values.

# Convert operating mode (few unique values) to Categorical
df["operating_mode"] = df["operating_mode"].astype("category")

print(df["operating_mode"].cat.categories)   # ['idle', 'run', 'shutdown']
print(df.memory_usage(deep=True))            # compare before and after

Datetime parsing should be performed as early as possible — ideally at load time using parse_dates and infer_datetime_format arguments of pd.read_csv(), or immediately thereafter with pd.to_datetime(). A proper DatetimeIndex unlocks resampling, rolling windows, and time-zone handling.

# Parse at load time
df = pd.read_csv("log.csv", parse_dates=["timestamp"], index_col="timestamp")

# Or after loading, with explicit format for speed
df["timestamp"] = pd.to_datetime(df["timestamp"], format="%Y-%m-%d %H:%M:%S")
df = df.set_index("timestamp")

Handling Duplicate Records

Duplicate rows arise from many sources: database replication errors, overlapping log files that are concatenated, or message-broker retransmissions that result in the same measurement being stored twice. Unchecked duplicates inflate sample counts, distort statistical summaries, and can cause overfitting in machine learning models by leaking near-identical examples into both training and validation sets.

duplicated() returns a boolean Series that is True for every row that is an exact repeat of an earlier row (by default, all columns are compared). The subset parameter restricts the comparison to specified columns — useful when only the timestamp and sensor ID need to be unique, regardless of the measured value.

# Find fully duplicate rows
dup_mask = df.duplicated()
print(f"Duplicate rows: {dup_mask.sum()}")

# Find rows with the same timestamp and sensor ID (regardless of value)
dup_ts_id = df.duplicated(subset=["sensor_id"], keep=False)
print(df[dup_ts_id])

drop_duplicates() removes duplicate rows. The keep parameter controls which occurrence survives: 'first' retains the earliest row, 'last' retains the latest, and False drops all copies of a duplicated row.

# Keep first occurrence, drop subsequent duplicates
df = df.drop_duplicates(keep="first")

# After deduplication, reset the integer index (if used)
df = df.reset_index(drop=True)

For time-series data, simple row removal is sometimes insufficient. If a sensor writes at nominally 1-second intervals but occasionally emits two readings within the same second, the correct fix is time-based deduplication by resampling: resample to a regular 1-second grid and aggregate duplicate entries within each bin (e.g., by averaging), then re-index onto the regular grid. This simultaneously removes duplicates and repairs any small timing jitter.

# Resample to 1-second bins, average duplicates, and fill the regular grid
df_regular = df.resample("1S").mean()
# Verify the resulting index is truly uniform
assert df_regular.index.is_monotonic_increasing
print(df_regular.shape)

After any deduplication operation, verifying the row count and index integrity is good practice: confirm the count decreased by approximately the expected number, that no timestamps are repeated, and that the index is monotonically increasing.

Normalizing and Scaling Data

Many machine learning algorithms and numerical optimization methods are sensitive to the magnitude and spread of input features. A neural network receiving temperature in Kelvin (≈ 300) alongside normalized efficiency (0 to 1) will have gradients dominated by the larger-magnitude feature. Normalizing all features to comparable ranges ensures that no single variable overwhelms the others simply because of its units.

Unit conversion should be applied before any normalization step. If pressure data from two sensors arrives in bar and kPa respectively, converting both to a common unit (for example, Pa) is a prerequisite for physically meaningful comparison and scaling.

Min-max normalization maps every value in a column to the interval [0, 1] using the formula x′ = (x − x_min) / (x_max − x_min). It preserves the shape of the original distribution and the relative distances between values. Its weakness is sensitivity to outliers: a single extreme value compresses all other values into a tiny portion of the [0, 1] range.

# Manual min-max normalization
x_min = df["temperature"].min()
x_max = df["temperature"].max()
df["temperature_norm"] = (df["temperature"] - x_min) / (x_max - x_min)

# Using scikit-learn for pipeline compatibility
from sklearn.preprocessing import MinMaxScaler
scaler = MinMaxScaler()
df["temperature_norm"] = scaler.fit_transform(df[["temperature"]])

# Save the scaler for later use on new data
import joblib
joblib.dump(scaler, "temperature_scaler.pkl")

Z-score standardization (also called standard scaling) subtracts the mean and divides by the standard deviation, producing a distribution with zero mean and unit variance. It is preferred when the downstream algorithm assumes Gaussian-distributed inputs (e.g., linear regression, PCA, Gaussian naive Bayes) and is more robust to outliers than min-max normalization in the sense that it does not compress the bulk of the data around a single extreme point.

from sklearn.preprocessing import StandardScaler

scaler_z = StandardScaler()
df["pressure_std"] = scaler_z.fit_transform(df[["pressure"]])

# Parameters are stored in the scaler object
print(f"Mean: {scaler_z.mean_[0]:.3f}, Std: {scaler_z.scale_[0]:.3f}")

A critical production-safety rule: scaling parameters must be derived from training data only and then applied identically to validation and test sets. Fitting a scaler on the full dataset before splitting introduces data leakage — the model indirectly sees test-set statistics during training, leading to overly optimistic performance estimates.

from sklearn.model_selection import train_test_split

X_train, X_test = train_test_split(df[["pressure", "temperature"]], test_size=0.2)

scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)   # fit AND transform on train
X_test_scaled  = scaler.transform(X_test)         # transform ONLY on test

Resampling and Aggregating Time-Series Data

Engineering time-series data seldom arrives at a single convenient frequency. High-speed vibration sensors may sample at 10 kHz while temperature loggers record at 1 Hz. Merging these signals for joint analysis, or preparing data for a model that expects a uniform time step, requires resampling — either downsampling (reducing frequency) or upsampling (increasing frequency).

The pandas resample() method groups a DatetimeIndex-based Series or DataFrame into fixed-duration bins, analogous to a groupby() along the time axis. The rule string specifies the bin width: '1T' for one minute, '1H' for one hour, '30S' for 30 seconds, and so on.

# Downsample 1-second data to 1-minute averages
df_1min = df.resample("1T").mean()

# Multiple aggregations in a single pass
df_summary = df.resample("1H").agg({
    "temperature": "mean",
    "pressure":    ["min", "max", "mean"],
    "vibration":   "max"
})

# Sum of energy consumption per day
df_daily_energy = df["power_kw"].resample("1D").sum()

When downsampling, the aggregation function should reflect the physical meaning of the signal: mean is appropriate for slowly varying quantities such as temperature; max is appropriate for peak-value quantities such as vibration amplitude or fault indicator flags; sum is appropriate for cumulative quantities such as energy or flow volume.

Upsampling increases the time resolution — for example, converting hourly averages to per-minute values. The new time points contain no measured data, so they must be filled using an interpolation method.

# Upsample hourly data to per-minute, then interpolate
df_minute = df_1h.resample("1T").interpolate(method="time")

# Or: first resample (creating NaNs), then interpolate separately
df_minute = df_1h.resample("1T").asfreq()         # insert NaN rows
df_minute = df_minute.interpolate(method="linear")

Rolling windows, applied with rolling(), compute statistics over a sliding window of a specified number of periods. They are the primary tool for trend smoothing (reducing high-frequency noise), computing moving averages, and calculating local variance or standard deviation as a measure of signal volatility.

# 5-minute centered moving average
df["temp_smooth"] = df["temperature"].rolling(window=5, center=True).mean()

# 10-sample rolling standard deviation (local volatility)
df["vib_volatility"] = df["vibration"].rolling(window=10).std()

# Exponentially weighted moving average — gives more weight to recent samples
df["temp_ewma"] = df["temperature"].ewm(span=10, adjust=False).mean()

A centered rolling window (center=True) uses future as well as past values to compute each output point, which produces a smoother result but introduces look-ahead bias if the smoothed signal is used as a real-time feature. A trailing window (center=False, the default) uses only past values and is safe for real-time or causal applications.

Taken together, these six families of operations — missing-value handling, outlier treatment, type correction, deduplication, scaling, and temporal resampling — form the complete preprocessing pipeline for engineering data. Executing them in a principled, reproducible order, with careful inspection at each step, is what transforms a noisy raw dataset into a reliable foundation for analysis, visualization, and machine learning.