Cleaning Financial Data in pandas

Cleaning is where most of a quant's time goes, and where most silent bugs are born. The insidious thing about data-cleaning bugs is that they don't crash, they produce a clean DataFrame and a beautiful backtest that happens to be trading on information it couldn't have had. This guide is not a pandas tour; it's the handful of patterns that matter for returns-based research, each paired with the specific way it leaks the future if you're careless. If the last guide was about getting data in, this is about turning it into something you can safely backtest.

Computing returns correctly: simple vs log

There are two return definitions, and mixing them up is a classic error. The simple return is the percentage change; the log return is the change in log price:

$r^{\text{simple}}_t = \frac{P_t}{P_{t-1}} - 1, \qquad r^{\text{log}}_t = \ln\frac{P_t}{P_{t-1}}$

The reason log returns matter is additivity: they sum across time, whereas simple returns compound. The multi-period log return is just the sum of single-period log returns, so total growth is a clean cumulative sum:

import numpy as np, pandas as pd
rng = np.random.default_rng(0)
price = pd.Series(100*np.exp(np.cumsum(rng.normal(0.0004, 0.01, 260))),
                  index=pd.bdate_range("2023-01-02", periods=260))

simple = price.pct_change()
logret = np.log(price).diff()

print("sum(log ret)        :", round(logret.sum(), 6))                     # 0.117297
print("log(P_last/P_first) :", round(np.log(price.iloc[-1]/price.iloc[0]), 6))  # 0.117297
print("compounded simple   :", round((1+simple).prod()-1, 6),
      "== exp(sum log)-1 =", round(np.exp(logret.sum())-1, 6))             # 0.124453 both

The sum of log returns (0.1173) equals log(P_last / P_first) exactly, and exponentiating it recovers the compounded simple return (0.1245). Use log returns for time-aggregation, volatility scaling, and statistical modeling (they're closer to symmetric and additive). Use simple returns when you aggregate across assets in a portfolio at a point in time, a portfolio's simple return is the weighted average of its holdings' simple returns, which is not true for log returns. Pick per axis: log across time, simple across the cross-section.

Resampling to bars

Raw data rarely arrives at the frequency you want to trade. Downsampling daily prices to weekly or monthly bars is a resample, and the aggregator you choose matters enormously. For a price, you almost always want .last() (the closing price of the period), never .mean(), a period's average price is not a tradeable quantity:

monthly = price.resample("ME").last()      # month-END close (a real, tradeable price)
m_ret   = monthly.pct_change()
print("monthly bars:", len(monthly), "| first label:", monthly.index[0].date())
# -> monthly bars: 12 | first label: 2023-01-31

Two gotchas. First, resample labels: "ME" (month-end) stamps each bar with the last calendar day of the month, which may be a weekend, the value is the last trading day's close, but the label is 2023-01-31. Compute returns off the resampled series (monthly.pct_change()), never re-derive dates by hand. Second, session boundaries: for intraday data, a naive resample("D") will lump an overnight session and a day session together and can straddle exchange holidays. When session edges matter, resample within the exchange timezone and drop non-session bars explicitly. To build OHLC bars from a price series, use price.resample("W").agg(["first","max","min","last"]).

Aligning multiple assets, and the forward-fill trap

Different assets trade on different calendars: a US stock, a London stock, and a Tokyo stock don't share holidays. Joining them creates holes, and how you fill those holes is one of the most common sources of accidental Look-Ahead Bias. Start by joining on the union of dates so gaps are explicit:

a = pd.Series([10, 11, 12], index=pd.to_datetime(["2023-01-02","2023-01-03","2023-01-04"]))
b = pd.Series([20, 22],     index=pd.to_datetime(["2023-01-02","2023-01-04"]))  # 01-03 missing
joined = pd.concat({"a": a, "b": b}, axis=1)   # outer join -> NaN where b has no print
print(joined.ffill())
#              a     b
# 2023-01-02  10  20.0
# 2023-01-03  11  20.0   <- b's value is STALE: yesterday's 20, carried forward
# 2023-01-04  12  22.0

ffill() (forward fill) is usually correct, carrying the last known price forward is exactly what you observe when a market is closed. The danger is subtler: forward-filling is only honest if the value being carried was known at that timestamp. Two specific failure modes:

1. Filling across a data release. If you forward-fill a fundamental (earnings, index membership) that gets published on a later date, you may carry a value backward or across its publication boundary and use it before it existed.
2. Back-fill, ever. bfill() pulls a future value backward to fill a past hole. That is lookahead by construction. Only forward-fill; never back-fill returns or prices in research data.

The safe default: forward-fill prices for market-closed gaps, but never forward-fill returns (a stale price implies a zero return for the gap, not a repeated return), and never let any fill cross the boundary between your training and test sets.

Handling missing data without leaking

A NaN is a decision point, not a nuisance to silence. Before filling anything, ask why it's missing. A holiday gap, a not-yet-listed asset, and a delisting all look identical (NaN) but demand different treatment: a holiday should forward-fill the price, a pre-IPO date should stay NaN (the asset didn't exist, filling it invents history), and a delisting should terminate the series (forward-filling a dead stock's last price fabricates a flat, riskless holding). Dropping rows with dropna() is fine if you drop only leading/trailing gaps; dropping interior rows silently re-indexes time and breaks return alignment. The rule: make missingness explicit first (reindex to your expected calendar, as in the previous guide), then handle each cause deliberately.

Winsorizing outliers

Financial returns have fat tails, and a single bad print, a fat-fingered tick, a bad vendor value, can dominate a mean, a regression, or a covariance estimate. Winsorizing clips extreme values to a percentile rather than deleting them, keeping the observation count intact while defanging the outlier:

lo, hi = simple.quantile([0.01, 0.99])
wins = simple.clip(lo, hi)
print("pre  min/max:", round(simple.min(),4), round(simple.max(),4))   # -0.0302 0.0315
print("post min/max:", round(wins.min(),4),  round(wins.max(),4))      # -0.0228 0.0239

Two cautions. First, distinguish a bad print from a real crash, winsorizing away a genuine −20% day erases exactly the tail risk you're trying to measure; winsorize inputs to estimators, not the P&L you report. Second, and critically: compute the clip thresholds causally. Using simple.quantile() over the whole sample to clip is a lookahead, the 99th percentile of the full history was not known partway through. In a real pipeline, compute thresholds on a rolling or expanding window, or fit them on training data only.

The subtle lookahead in preprocessing: full-sample normalization

This is the bug that hides in "harmless" feature scaling. Standardizing a feature to a z-score with the full-sample mean and standard deviation leaks the future into every historical point, because those statistics summarize data that hadn't happened yet. It's easiest to see across a volatility regime shift:

rng = np.random.default_rng(1)
calm  = rng.normal(0, 0.01, 250)      # low-vol first half
storm = rng.normal(0, 0.04, 250)      # high-vol second half
ret = pd.Series(np.r_[calm, storm], index=pd.bdate_range("2022-01-03", periods=500))

leak_z   = (ret - ret.mean()) / ret.std()                                 # WHOLE-sample stats
causal_z = (ret - ret.expanding().mean()) / ret.expanding().std()          # only past data

i = 100
print("full-sample std:", round(ret.std(), 4),                            # 0.0264
      "| causal std @100:", round(ret.iloc[:i+1].std(), 4))               # 0.0085
print("leaky z @100:", round(leak_z.iloc[i], 3),                          # -0.249
      "| causal z @100:", round(causal_z.iloc[i], 3))                     # -0.67

At row 100, deep in the calm regime, the honest standard deviation was 0.0085, but the full-sample std is 0.0264 because it already knows about the coming storm. That inflated denominator shrinks a real −0.65% move from a true z-score of −0.67 to a muted −0.25. Every model trained on leak_z has quietly been told which periods will be turbulent. The fix is always the same: normalize with causal statistics, an expanding() or rolling() window, or fit on training data and transform the test set (never fit_transform on the whole thing). This single mistake is responsible for a large share of ML backtests that look brilliant and die live. See Feature Engineering for Finance for causal feature construction and Look-Ahead Bias for the full taxonomy.

Where this breaks

• Log vs simple mismatch. Log returns add across time; simple returns add across the cross-section. Use the right one per axis.
• .mean() when resampling prices. A period's average price isn't tradeable, use .last().
• bfill() or cross-boundary ffill(). Any fill that pulls a value backward, or across a train/test split, is lookahead by construction.
• Full-sample stats for z-scores, winsor thresholds, or scaling. All leak the future. Compute everything causally, expanding, rolling, or train-only fits.
• Silencing NaN instead of diagnosing it. Holiday, pre-IPO, and delisting gaps look identical but need different handling.

Clean data is not the absence of NaN; it's the guarantee that every value at time t was knowable at time t. Hold that line and you're ready to build a backtest whose numbers you can trust.