Missing data and alignment: decide what was actually observable

A missing value is not a zero

A blank price, a zero return and an unavailable observation are different states. Zero asserts a measured quantity; missingness says that the quantity is not supplied under the current contract. Preserve reasons where possible, such as a closed market, a failed download or a field that does not apply. Those reasons can imply different handling rules even when their serialized value is the same null. Rejecting incomplete input is often appropriate for a small deterministic reference, but an upstream estimator may have a documented missing-data method. The essential requirement is that the treatment remains explicit rather than disappearing during import.

Separate event time from availability

An observation can describe an earlier economic event while becoming available only later. Joining on the event date alone can therefore use information that was not yet observable at a calculation's decision time. Retain event time, availability time and retrieval time as distinct fields when the source supports them. A revised statistic also needs a vintage identifier if the question concerns what was known previously. Retrieving the latest file does not reconstruct its former contents. For a synthetic fixture, label all times as invented and specify a timezone so the alignment rule can be tested without implying a historical market record.

Use directional joins with an age limit

The pandas merge_asof documentation distinguishes backward, forward and nearest matching. A backward match chooses a key no later than the left-hand key; nearest can choose a future key. For a hypothetical decision at 10:00 UTC, require an availability time at or before 10:00 and an age no greater than five minutes. The table tests four independent records against that rule. A valid 09:59 value passes, a 10:01 value is future information, a 09:40 value is too old, and a null at 09:59 still fails. Time eligibility cannot turn an absent measurement into an observation.

Do not invent return intervals

Suppose consecutive scheduled prices are 100, missing and 104. The endpoint ratio gives a 4% change across two scheduled intervals, not two observed one-interval returns. Forward-filling the missing price creates a zero followed by 4%, a particular assumption about the unobserved path. It changes volatility and covariance inputs even though the endpoint wealth is unchanged. pandas documents that pct_change computes fractional change, despite its name. Decide whether to preserve the gap, reject the window or calculate a longer-interval return with an explicit interval label. Removing the missing row and retaining a daily label silently changes the meaning of the data.

Choose the covariance sample consciously

Pairwise deletion estimates each covariance from the rows available for that particular pair. It can retain more observations, but different matrix entries then describe different samples. The pandas covariance documentation warns that the resulting matrix need not be positive semidefinite. Complete-case deletion uses a common set of rows, yet may discard much of the sample and can still leave a singular matrix when information is insufficient. Neither choice repairs selective missingness. Record pairwise counts or the common retained-row count, the minimum sample rule and any later regularization. A filled-looking matrix is not proof that its estimation problem was well posed.

Align identity and calendars as well as clocks

Matching date strings is insufficient when assets close in different timezones or on different calendars. A date can denote an exchange session rather than an instant. Define the intended observation boundary and avoid aligning a later close with an earlier decision merely because both carry the same calendar date. Instrument identifiers also matter: a renamed symbol, different currency or changed adjustment convention can create a false continuous series. Duplicate keys require a stated resolution policy, preferably based on source identity and revision status. Sorting records can prepare a join, but sorting cannot decide which of two conflicting observations is authoritative.

Keep an alignment receipt

A useful calculation receipt reports rows supplied, rows retained, rejection reasons, the oldest accepted observation and the join policy. Preserve the selected observation identifiers so another reader can reconstruct the exact input set. Test the age boundary itself, an exact-time observation, a future observation, a duplicate and a missing value. If exact-time matches require processing latency, tighten the policy rather than assuming simultaneity is usable. These checks establish the behavior of the preparation step. They do not prove that the selected sample represents a market or that the downstream estimate remains stable under a different missingness pattern.

Primary references

These sources support methods and conventions. The example values are original constructions, not observations extracted from these documents.

• pandas: merge_asof direction, tolerance and sorting requirements
• pandas: fractional change and explicit missing-value treatment
• pandas: pairwise covariance and positive-semidefinite limitations

Related investigations