How to Handle Missing Data

A Tuesday morning dashboard shows no new average price-per-square-foot data for three ZIP codes. The numbers look stable, so nobody panics at first. Then an analyst compares the raw feed with the prior day and finds that a vendor has stopped returning last_sold_price for off-market listings.

The pipeline doesn't reject the records. Its mapper converts nulls to zeros, the warehouse accepts them, and downstream SQL calculates plausible-looking medians. Nothing crashes. The dashboard becomes wrong.

That's the dangerous version of missing data. The problem isn't choosing between median filling and multiple imputation after the fact. It's building a system that can distinguish an unavailable value from a real zero, identify why the value is absent, preserve uncertainty, and stop upstream changes from contaminating production outputs. Good data quality best practices start before statistical preprocessing, with schema contracts, validation, and observable pipelines.

This playbook focuses on three failure modes I've seen repeatedly in real estate data systems: silent coercion, biased imputation, and untracked schema drift. Deletion, single imputation, model-based methods, and missingness flags all have legitimate uses. They also all break downstream in different ways when applied without understanding the data-generating process.

When Your Dashboard Breaks Silently

The first clue in the dashboard incident wasn't a database error. It was a flat line.

Average price-per-square-foot had stopped updating in three ZIP codes, while every neighboring area continued to refresh. The ETL job reported success, row counts were within their normal range, and the API response still contained listing objects. The vendor had omitted last_sold_price from off-market records, but the ingestion layer treated the absent field as equivalent to a numeric zero.

That coercion changed the meaning of the data. A missing historical sale price means “unknown” or “not supplied.” Zero means something else entirely. Once those values entered the warehouse, SQL aggregations could process them normally, producing medians and averages that looked credible enough to pass a superficial review.

Production rule: Never let a parser decide that an unavailable business value is zero unless the source contract explicitly defines zero that way.

The incident combined three distinct problems:

• Silent coercion: Nulls became zeros during transformation, so statistical functions had no way to recognize the damaged records.
• Biased imputation: The pipeline filled a meaningful real estate field without checking whether off-market listings differed systematically from other listings.
• Untracked schema drift: A partner changed response behavior without triggering a contract alert or a review of affected fields.

The right response wasn't to pick a more advanced imputer and rerun the dashboard. First, the pipeline needed to preserve the original null, record the source response shape, and identify the affected listing population. Only then could an analyst decide whether deletion, imputation, weighting, or a separate missing category made sense.

For a live property data workflow, missing-data handling crosses several layers:

1. Schema design must distinguish required fields from optional fields.
2. Ingestion must preserve nulls and reject malformed representations.
3. Monitoring must detect abrupt changes in field completeness.
4. Analysis must test whether missingness relates to observed business variables.
5. Modeling must carry the chosen treatment consistently from training to inference.

A useful operational check is the RealtyAPI status page, especially when a sudden completeness change affects only one source or endpoint. The point isn't to blame the provider. It's to separate a temporary service response from a genuine statistical pattern before either one reaches a decision-maker.

Detecting and Diagnosing What's Actually Missing

Before choosing an imputation method, create a missingness profile. In pandas, the fastest first pass is:

missing_counts = (
    df.isna()
      .sum()
      .sort_values(ascending=False)
)

missing_rates = (
    df.isna()
      .mean()
      .sort_values(ascending=False)
)

print(missing_counts)
print(missing_rates)

The count tells you how many values are absent. The rate helps compare columns with different scales, but it shouldn't be the only prioritization measure. A sparsely missing last_sold_price field may matter more to a valuation model than a heavily missing optional marketing description.

A per-row count exposes a different class of defect:

df["missing_field_count"] = df.isna().sum(axis=1)

problem_rows = df[
    df["missing_field_count"] >= 3
]

If price, latitude, property_type, and fetched_at all become null in the same records, that usually looks more like an ingestion or response failure than ordinary statistical missingness. In a production pipeline, I'd inspect request status, response payloads, retry history, and source identifiers before fitting any imputer.

Read the pattern, not just the percentage

The classical mechanisms provide a useful vocabulary:

• MCAR, or Missing Completely At Random: The probability of missingness doesn't depend on observed or unobserved values. A random recording failure might fit this description, but production systems shouldn't assume it without evidence.
• MAR, or Missing At Random: Missingness relates to observed variables. For example, last_sold_price may be absent more often for a known property_type or listing status.
• MNAR, or Missing Not At Random: Missingness relates to the unavailable value itself. Luxury sellers withholding price is a plausible example because the reason for absence may depend on the value being hidden.

A heatmap helps reveal structure:

import missingno as msno
import matplotlib.pyplot as plt

msno.matrix(df)
plt.show()

msno.heatmap(df)
plt.show()

The matrix can expose blocks of missing fields, while the heatmap can show relationships between missingness indicators. Grouping rates by business dimensions adds context:

(
    df.groupby("property_type")["last_sold_price"]
      .apply(lambda s: s.isna().mean())
      .sort_values(ascending=False)
)

That comparison doesn't prove MAR or MNAR, but it tells you where to investigate. The mechanism matters because complete-case analysis can discard a nonrepresentative slice, while imputation can manufacture values that make a subgroup appear more complete and more ordinary than it really is. For reporting teams, the practical goal is accurate agency reporting, which requires preserving the distinction between unavailable source data and a value produced by a transformation.

When diagnosing a source problem, also inspect response and integration behavior in the RealtyAPI status-code documentation. A missing field attached to a known partial response deserves a different treatment from a field that has always been optional.

Deletion vs Imputation Methods Compared

Deletion and imputation aren't interchangeable cleanup steps. They make different assumptions about the records you keep, the uncertainty you report, and the behavior your downstream model sees.

Listwise deletion removes a record when any required analysis field is missing. It's simple, easy to audit, and often fast enough for exploratory work. It can also remove a large or systematically different segment of listings when missingness relates to source, listing status, geography, or the outcome.

Single imputation fills each missing value once. Mean, median, mode, forward-fill, and regression fill are operationally convenient, especially for prediction pipelines that need a rectangular feature matrix. The weakness is that one estimated value is treated like an observed value, which understates uncertainty and can make a model or confidence interval look more certain than the evidence supports.

Multiple imputation creates several plausible completed datasets, analyzes each one, and combines the resulting estimates. Rubin's 1976 paper established the modern missing-data framework and helped create the foundation for workflows that preserve uncertainty rather than treating filled values as observed facts (Rubin's foundational paper).

Method	Bias Risk	Variance Handling	Complexity	Best Used When
Deletion	High when missingness is related to observed or unobserved variables	Discards information and can reduce precision	Low	Records are removable under a defensible missingness assumption
Single Imputation	Can distort relationships and make data look more certain	Usually understates imputation uncertainty	Low to moderate	Fast prediction features or operational defaults are more important than inferential intervals
Multiple Imputation	Lower risk when the model reflects the missingness assumptions and useful auxiliary variables	Carries uncertainty across completed datasets	Moderate to high	Coefficients, confidence intervals, or causal and statistical claims will be reported

A common practical rule is to use deletion only when missingness is plausibly MCAR and below 5%, as described in applied guidance (clinical missing-data guidance). That isn't a universal safety threshold. Evidence shows that the pattern of missingness and available auxiliary information can matter more than the raw fraction missing, and one study reported that multiple imputation reduced bias and was “never detrimental” to efficiency when sufficient auxiliary information was available (evidence on missing information and imputation).

For inference, use multiple imputation when stakeholders will see coefficients, intervals, or causal interpretations. For prediction, a simpler imputer may be acceptable if validation reflects production missingness and the model's calibration remains suitable. The distinction resembles broader feature engineering guidance for CTOs: the feature representation must serve the actual system objective, not an abstract notion of cleanliness.

Model-Based Imputation and Flagging in Python

Model-based imputation estimates a missing feature from other fields in the same record. For property data, useful predictors might include beds, baths, sqft, property_type, latitude, and neighborhood features, provided those predictors are available at the moment the pipeline needs the estimate.

A simple benchmark should hide known values, impute them, and compare predictions with the original observed values. The benchmark must split before fitting so the imputer doesn't learn from values it's supposed to estimate.

import numpy as np
import pandas as pd

from sklearn.impute import SimpleImputer, KNNImputer
from sklearn.experimental import enable_iterative_imputer
from sklearn.impute import IterativeImputer
from sklearn.metrics import mean_squared_error

features = ["beds", "baths", "sqft", "last_sold_price"]
observed = df[features].dropna().copy()

rng = np.random.default_rng(7)
mask = rng.random(len(observed)) < 0.2

validation = observed.copy()
validation.loc[mask, "last_sold_price"] = np.nan

mean_model = SimpleImputer(strategy="median")
knn_model = KNNImputer(n_neighbors=5)
iterative_model = IterativeImputer(random_state=7)

def score(model):
    filled = model.fit_transform(validation)
    estimate = filled[mask, features.index("last_sold_price")]
    actual = observed.loc[mask, "last_sold_price"].to_numpy()
    return mean_squared_error(actual, estimate, squared=False)

print(score(mean_model))
print(score(knn_model))
print(score(iterative_model))

The resulting RMSE is a local diagnostic, not a universal ranking. A benchmark survey tested 19 imputation algorithms on 15 real-world datasets under different mechanisms, missing rates, data types, and model settings, reinforcing that no imputer dominates in every context (benchmark evidence on imputation).

Preserve the fact that the field was absent

A flag can retain information that the imputed value hides:

df["last_sold_price_is_missing"] = (
    df["last_sold_price"].isna().astype("int8")
)

median_price = df["last_sold_price"].median()
df["last_sold_price"] = (
    df["last_sold_price"].fillna(median_price)
)

For inference about price drivers, keep the flag and interpret it cautiously. The indicator may reflect listing status, seller behavior, source limitations, or operational collection problems. Dropping it can erase a meaningful selection signal.

For prediction, the choice depends on validation. A rent forecast may benefit from the flag if missingness itself predicts the target, but the flag can also encode a vendor artifact that disappears after a source fix. Test both versions against realistic holdout data.

Iterative imputation can distort coefficients when the imputation model is misspecified, when predictors leak future information, or when high-dimensional inputs make the fitted relationships unstable. In high-dimensional settings, a simulation study found strong results from lasso-based predictor selection and principal component analysis for auxiliary data, which makes dimensionality reduction an important part of the workflow (high-dimensional imputation study). It remains safer when the predictor set is controlled, the imputation process is fitted only on training data, and sensitivity checks show that conclusions don't hinge on one modeling choice.

Real-Time APIs and Schema Drift

Statistical missingness usually develops as a pattern across records. API failures can arrive as a sharp discontinuity. A partner endpoint times out, a response contains only part of the listing object, or a vendor release introduces an optional field that your parser doesn't understand. Retries can also create duplicate records, making the dataset appear complete while inflating some properties.

That's why a production parser should treat source shape as data worth monitoring. Required fields such as a stable listing identifier, source, and fetch timestamp should be validated before a record enters the ready-to-model table. Optional fields should remain None, empty strings, or empty arrays according to a documented contract, rather than being converted to zeros or guessed values at ingestion.

A Pydantic schema makes that distinction explicit:

from datetime import datetime
from typing import Optional
from pydantic import BaseModel

class Listing(BaseModel):
    listing_id: str
    source: str
    fetched_at: datetime
    price: Optional[float] = None
    last_sold_price: Optional[float] = None
    estimated_value: Optional[float] = None

The schema should reject records missing required identifiers or timestamps, while allowing optional fields to remain null. Records that cannot satisfy the minimum contract belong in a dead-letter queue with the raw payload and failure reason, not in the same table used by analysts.

Make retries safe

An idempotent merge key prevents a timeout retry from producing a second logical listing:

MERGE INTO listings AS target
USING staging_listings AS incoming
ON target.source = incoming.source
AND target.listing_id = incoming.listing_id
WHEN MATCHED THEN UPDATE SET
    price = incoming.price,
    last_sold_price = incoming.last_sold_price,
    fetched_at = incoming.fetched_at
WHEN NOT MATCHED THEN INSERT (
    source,
    listing_id,
    price,
    last_sold_price,
    fetched_at
)
VALUES (
    incoming.source,
    incoming.listing_id,
    incoming.price,
    incoming.last_sold_price,
    incoming.fetched_at
);

The merge key should reflect the source's identity rules. A URL may change, while a source listing identifier may remain stable. If no reliable identifier exists, store ingestion metadata and design deduplication deliberately rather than relying on row position.

RealtyAPI.io provides a unified API layer for property and listing data, with REST, GraphQL, and webhook access. In a real integration, the RealtyAPI integrations documentation is useful for understanding how the upstream contract fits into your own validation and retry strategy.

Schema drift belongs in the same operational workflow as imputation, but it must not be mistaken for an imputation problem. A field disappearing across one batch should trigger an alert and source investigation. Filling that field immediately may hide the incident and turn a recoverable integration defect into a permanent analytical error.

Production Monitoring and Final Checklist

A missing-value strategy is incomplete until it survives deployment. The training notebook may correctly fit a median or iterative imputer, but the deployed service still needs the same transformation artifact. If the model expects mean-filled inputs and a schema change sends raw nulls, predictions can fail loudly or, worse, pass through an unintended default.

Use a checklist that covers both engineering behavior and statistical assumptions:

• Alert on completeness: Track null rates by column, source, endpoint, property type, and ingestion batch. Route meaningful changes to Slack or PagerDuty instead of waiting for a dashboard consumer to notice.
• Version the transformation: Save imputation parameters, feature lists, categorical mappings, and indicator-column decisions alongside the training-data version.
• Freeze a baseline: Compare current null distributions with a stored baseline so a gradual drift doesn't disappear inside a broad aggregate.
• Validate the contract: Check required fields, data types, accepted null representations, and unexpected new fields before warehouse loading.
• Audit the mechanism: Record whether the missingness pattern appears random, related to observed fields, or plausibly tied to the unavailable value.
• Test downstream behavior: Re-run model, SQL, and reporting checks with realistic missingness, including partial API responses and duplicate retries.
• Keep raw evidence: Store the original payload, response metadata, parser version, and validation result so analysts can reconstruct what happened.

Operational principle: An imputer should never be the first monitor that tells you a provider changed its response.

Statistical practice also needs a written decision record. State why deletion was chosen, which fields were imputed, what predictors entered a model-based imputer, whether missingness flags were retained, and how sensitivity analyses changed the conclusion. For inferential work, analyze completed datasets separately and pool estimates, standard errors, and confidence intervals rather than reporting one filled dataset as if it were observed data. The broader framework includes multiple imputation and inverse probability weighting under MAR, followed by sensitivity analysis (statistical framework for missing data).

The RealtyAPI blog can serve as a practical reference point for integration patterns, but your own pipeline must remain the source of truth for alerts, lineage, and model behavior. Missing data isn't a preprocessing footnote. It's a first-class production concern that connects API contracts, warehouse semantics, statistical validity, and the decisions people make from your dashboard.

---

If your real estate pipeline needs consistent listing and market data, RealtyAPI.io provides a unified developer API for retrieving property information across supported sources while preserving structured missing values for downstream validation. Start by reviewing your required fields, null handling, and retry behavior, then use the API in a monitored ingestion path rather than hiding gaps with silent defaults.