pandas Stock Data: Clean Time Series Before Computing Signals

한국어로 읽기

TECH INSIGHT / EXPLAINED

Before calculating an indicator, make the time axis and missing-data policy explicit. A sorted CSV is a useful starting point, but a strategy also needs a clear distinction between observations, signals and executable orders.

AI-generated educational illustration. Three colleagues sort observations, inspect a moving window and check missing data and drawdown.

Local execution of the actual project

These captures show the existing project UI or functions executed locally with non-private sample inputs. Each caption states the scope of the execution.

Actual execution of the original LLMStockTrading/lib/indicators.py functions on 48 synthetic prices, displayed in a purpose-built browser report rather than a trading screen. The source uses rolling-mean RSI, ddof=1 bands and first-value EWM seeds, so it differs from the article’s Wilder/ddof=0 example. This verifies calculations, not data cleaning or order execution.

Use a small reproducible dataset

The three rows below are synthetic and intentionally unsorted. Reading them produces an index starting at 1.0; the sequence 100→110→99 ends at 0.99 with a 10% drawdown from 110. Including the initial value prevents the first loss from disappearing from the drawdown baseline.

import pandas as pd
from io import StringIO
csv = """date,close,volume
2026-01-07,99,300
2026-01-05,100,100
2026-01-06,110,200
"""
df = pd.read_csv(StringIO(csv))  # replace with a CSV file path
df['date'] = pd.to_datetime(df['date'], errors='raise')
df = df.set_index('date').sort_index()
if not df.index.is_unique:
    raise ValueError('duplicate observation dates')
for col in ['close', 'volume']:
    df[col] = pd.to_numeric(df[col], errors='raise')
if df[['close', 'volume']].isna().any().any() or not (df.close > 0).all():
    raise ValueError('missing or invalid observations')
df['ma2'] = df.close.rolling(2, min_periods=2).mean()
df['ret'] = df.close.pct_change(fill_method=None)
df['index_value'] = df.close / df.close.iloc[0]
df['drawdown'] = df.index_value / df.index_value.cummax() - 1
assert abs(df.index_value.iloc[-1] - .99) < 1e-12
assert abs(df.drawdown.min() + .10) < 1e-12

Validate more than dtypes

Check duplicates, finite numeric values, non-negative volume and, when OHLC is supplied, high/low consistency. A symbol with a leading zero must remain text. For real instruments, document adjustments for splits and distributions. The price index above is not automatically a total-return series.

rolling(20) means twenty observations, not twenty calendar days. Market holidays are not missing trading data. Compare the expected session calendar with the actual index before deciding a gap is an error or forward-filling it.

Avoid future information in filters

For a volume-spike decision at bar t, use a historical baseline such as volume.rolling(20).mean().shift(1). Using the entire file’s mean leaks future observations into earlier decisions. Retrospective descriptive analysis may use a full-period mean, but label that different purpose.

Compute a crossover from both current and previous averages. Keep warm-up values as unavailable and use dropna(subset=[the required indicators]) only where needed; dropping every missing row can silently alter unrelated calculations.

Align the signal with the fill model

A signal based on a completed close is available only after that close is known. Model a later executable price and its costs. shift(1) is an alignment tool, not proof that an assumed next-bar fill is achievable. Also state whether a row label represents bar start, bar end or exchange session date.

Save the cleaned data and the transformation settings together. UTF-8 CSV is suitable for programmatic use; UTF-8 with BOM can help some spreadsheet applications. Keep a pristine source copy so a cleaning choice can be audited.

Sources and further reading

pandas percentage change · pandas time series

Content and examples reviewed: 2026-09-29