2.1 Data Profiling, Types, and Missingness: Explain Missing Values Before Imputing Them

The temperature column contains blanks, N/A, -999, and genuine zeros. Zero may be a valid reading; the other values count as missing only if the source contract says so. Collapsing markers during parsing and then filling every gap can hide errors behind plausible numbers.

This lesson's objectives

• Create a structured data profiling report;
• Distinguish physical types, logical types, and business constraints;
• Understand MCAR, MAR, MNAR, and structural non-applicability;
• Select delete, impute, flag, or model based on your analysis objectives.

1. Data Quality Goes Beyond Null Counts

Common dimensions:

• completeness: Whether required fields exist;
• validity: whether the value meets the type, range, and enum requirements;
• uniqueness: Whether the business key is unique;
• consistency: whether values agree across fields and tables;
• timeliness: Whether arrival is made within the promised time;
• accuracy: How closely it aligns with real-world facts;
• integrity: whether references and relationships are complete.

Accuracy cannot be verified from in-table statistics alone. For example, a temperature reading of 35 may fall within the allowed range yet still come from a miscalibrated sensor.

2. First preserve the original text before parsing

The Python blocks in this lesson run in order with pandas 3.0 and NumPy. The small CSV is included so the parser behavior can be checked directly.

from io import StringIO
import numpy as np
import pandas as pd

csv_text = """sensor_id,observed_at,temperature,status
A,2026-08-03T10:00:00Z,0,ok
A,2026-08-03T10:01:00Z,,
A,2026-08-03T10:02:00Z,N/A,
B,2026-08-03T10:00:00Z,20,ok
B,2026-08-03T10:01:00Z,-999,
B,bad-date,oops,warning
B,2026-08-03T10:03:00Z,inf,warning
"""
raw = pd.read_csv(StringIO(csv_text), dtype="string", keep_default_na=False)
print(raw.shape)
print(raw.dtypes)
print(raw.head())

First, read the text to observe which missing markers are actually used in the source file, preventing NA, NULL, or empty strings from being prematurely merged by library default rules. The production pipeline can directly declare the schema, but failure records should still retain the original text and the reason for the error.

Create a column-level profile:

def profile_text_column(series: pd.Series) -> pd.Series:
    normalized = series.str.strip()
    return pd.Series({
        "rows": len(series),
        "null": series.isna().sum(),
        "empty": normalized.eq("").sum(),
        "distinct": normalized.nunique(dropna=False),
        "top_values": normalized.value_counts(dropna=False).head(5).to_dict(),
    })

print(profile_text_column(raw["temperature"]))

DataFrame.info() prints and returns None on its own. When structured reports are needed, calculate them yourself, not print(df.info()).

Here, stripping whitespace is a profiling choice for this field. Keep the original text: spaces may be meaningful in other identifiers. The null and empty counts also distinguish library nulls from empty strings.

3. Physical Types and Logical Types

All values in a CSV are text. Parsing them into int64 still can't confirm that a column contains additive numeric values: ZIP codes, order numbers, and phone numbers might simply consist of digits within a character set.

Each column should declare:

physical type: storage representation
logical types: date, amount, category, identifier, etc.
unit: °C, ms, bytes
nullable: When is null allowed?
constraints: range, regex, enumeration, cross-field relationships

The date 01/02/2026 may be interpreted as January 2 or February 1 depending on the region. Don't let the parser guess; the contract should specify ISO 8601 or an explicit format.

parsed_time = pd.to_datetime(
    raw["observed_at"],
    format="%Y-%m-%dT%H:%M:%SZ",
    errors="coerce",
    utc=True,
)
invalid_time = parsed_time.isna() & raw["observed_at"].str.strip().ne("")

errors="coerce" turns errors into NaT, so you must save invalid_time, otherwise format errors will be disguised as ordinary missing values.

A successful parse establishes format and timezone, not whether the timestamp is plausible or follows the previous event.

4. Standardize missing markers while preserving reasons

MISSING_TOKENS = {"", "N/A", "NA", "NULL", "-999"}

temperature_text = raw["temperature"].str.strip()
source_missing = temperature_text.isin(MISSING_TOKENS)
missing_reason = pd.Series(pd.NA, index=raw.index, dtype="string")
missing_reason = missing_reason.mask(source_missing, "source_missing")
temperature = pd.to_numeric(
    temperature_text.mask(source_missing), errors="coerce"
)
parse_failed = temperature.isna() & ~source_missing
missing_reason = missing_reason.mask(parse_failed, "parse_error")
nonfinite = temperature.notna() & ~np.isfinite(temperature)
missing_reason = missing_reason.mask(nonfinite, "nonfinite")
temperature = temperature.mask(nonfinite)

Here, treating -999 as missing is just an example of a data contract. If business allows real -999, this rule would invalidate valid values. Placeholders must be configured by source and field, and cannot be uniformly replaced across the entire table.

source_missing identifies a source marker, not its statistical cause. The original marker remains in raw; inf parses as a number but fails the finite-value rule. Neither a parse error nor infinity should silently receive a replacement temperature.

5. Missing Data Mechanism Determines Bias

Statistics often distinguishes:

• MCAR: Missing data is unrelated to both observed and unobserved values;
• MAR: Given observed variables, missingness becomes independent of the missing values themselves;
• MNAR: Missing data is still related to unobserved values, even after accounting for observed variables.

"Sensor intermittent disconnections" don't automatically prove MCAR; disconnections may be related to temperature, location, or device load. MAR/MNAR is typically not fully verifiable from current data alone and requires data collection mechanisms, domain knowledge, and sensitivity analysis.

There's also structural inapplicability: for example, an order not canceled lacks cancellation_reason. It's not data loss and shouldn't be filled with "average cancellation reason." It's better to explicitly encode not_applicable or let the status field explain it.

6. Handling Strategies Serve Downstream Problems

Delete

Deleting rows or columns alters the sample composition. It's only justified when the missing data mechanism and analysis objectives allow it. There is no universal rule like "delete a column if missing values exceed 70%"; fields with low coverage might be critical for small subgroups, while highly covered fields might be unusable due to bias.

Single Value Imputation

Median imputation is less sensitive to extremes than mean imputation, but it changes variance and correlations. A missingness indicator can retain useful information; whether it improves a model needs validation. Report the imputation policy.

if not temperature.notna().any():
    raise ValueError("No valid training temperature for median imputation")
median_temperature = temperature.median()
clean = raw.assign(
    temperature_raw=raw["temperature"],
    temperature=temperature.mask(source_missing, median_temperature),
    temperature_missing=temperature.isna(),
    temperature_missing_reason=missing_reason,
)

The previous example treats the fixture as training data: valid readings are 0 and 20, so the median is 10. It fills only source-missing values; parse errors and nonfinite values remain missing for review. An all-missing training column requires an explicit fallback or failure policy.

Forward Fill for Time Series

ffill implies "the previous state remains valid" and requires sorting by entity and event time. Direct forward filling across devices will assign a state from one device to another.

frame = raw.assign(
    observed_at=parsed_time,
    status=raw["status"].replace("", pd.NA),
)
invalid_order = frame["observed_at"].isna() | frame["sensor_id"].eq("")
ordering_quarantine = frame.loc[invalid_order].copy()
ordered = frame.loc[~invalid_order].sort_values(["sensor_id", "observed_at"])
if ordered.duplicated(["sensor_id", "observed_at"]).any():
    raise ValueError("Equal event times require a source sequence rule")
ordered["status"] = ordered.groupby("sensor_id")["status"].ffill(limit=3)

limit=3 limits consecutive missing rows, not elapsed time. With irregular sampling, three rows might span hours; enforce a maximum age separately. Invalid timestamps are quarantined here, and equal timestamps need a documented source sequence.

Model or Multiple Imputation

Model-based methods can use other variables, but their usefulness depends on the imputation model and missingness assumptions. Multiple imputation generates several plausible datasets and combines estimates with within- and between-imputation uncertainty; choosing one completed dataset loses that benefit. See the statsmodels MICE guide and scikit-learn imputation guide.

7. Training and evaluation sets must not leak information to each other

Median, mode, encoding tables, and imputation models for the model can only be fitted on the training set and then applied unchanged to validation, test, and online data:

fit(training) → imputation parameters
transform(training / validation / test / online)

Fitting imputation on the full dataset before splitting lets validation data influence training and causes leakage. Time series data must be fitted along temporal boundaries and cannot use future data to fill past values.

Common Misconceptions

• Null values all represent the same thing: unknown, not applicable, parsing failure, and permission masking should be distinguished.
• The missingness rate determines the treatment: Mechanism, purpose, group impact, and uncertainty matter more.
• Median imputation doesn't introduce bias: it still alters the distribution and relationships.
• errors="coerce" completes cleaning: It hides parse failures and must retain error markers.

Practice

1. Design three columns for the temperature column: original value, parsed value, and missing reason.
2. Provide a data collection case that might belong to MCAR, MAR, or MNAR and specify what additional evidence is needed.
3. Find a field with low coverage but high business value that shouldn't be deleted directly.
4. Modify the full-dataset imputation code to fit/transform and verify that the test set is not involved in parameter computation.

Summary

Missing values are not a pandas API issue. First, preserve the original text, declare the logical type, identify the cause of missingness, then choose a handling method compatible with downstream decisions. Imputing yields modeling assumptions, not the true observed values.

The next lesson covers another type of dangerous data: it's not null, and it may even appear perfectly formatted, yet it could be the result of extreme events, sensor failures, or duplicate deliveries.