Cleaning market data with pandas

Almost every quantitative project starts with a file of prices, and almost every file of prices has problems. Duplicate rows, missing sessions, inconsistent time zones and unadjusted corporate actions are routine. None of them are hard to fix, but each one can silently distort an analysis if you skip it.

This guide walks through a practical cleaning routine for OHLCV (open, high, low, close, volume) bar data using pandas. The goal is not perfection; it is a dataset whose quirks you understand and have documented.

Load and index by time

Parse timestamps when you read the file, sort them, and remove exact duplicates before doing anything else. Sorting matters because rolling windows, percentage changes and resampling all assume chronological order.

import pandas as pd

df = pd.read_csv("prices.csv", parse_dates=["timestamp"])
df = (
    df.sort_values("timestamp")
      .drop_duplicates(subset="timestamp", keep="last")
      .set_index("timestamp")
)
print(df.index.is_monotonic_increasing, df.index.has_duplicates)

Keeping the last duplicate is a reasonable default when a vendor republishes corrected bars, but check your source's documentation. If two rows for the same timestamp disagree, that disagreement is itself worth logging.

Be explicit about time zones

Timestamps without a time zone are a common source of off-by-hours errors, especially when you combine data from different venues. Decide on one convention, typically UTC for storage, and convert to the exchange's local time only when you need session logic such as “first bar of the day.”

# The file stores naive UTC timestamps
df.index = df.index.tz_localize("UTC")
local = df.tz_convert("America/New_York")  # for session-based logic only

Validate the bars themselves

Simple consistency rules catch a surprising number of errors. The high should never be below the open or close, the low should never be above them, prices should be positive, and volume should not be negative.

price_cols = ["open", "high", "low", "close"]
bad = (
    (df["high"] < df[["open", "close"]].max(axis=1))
    | (df["low"] > df[["open", "close"]].min(axis=1))
    | (df[price_cols] <= 0).any(axis=1)
    | (df["volume"] < 0)
)
print(f"{bad.sum()} rows fail basic OHLC checks")

# Flag unusually large one-bar moves for manual review
ret = df["close"].pct_change()
suspects = ret[ret.abs() > 0.25]

The 25% threshold above is arbitrary; tune it to the instrument. Large moves are not automatically errors, since earnings announcements and market shocks are real, but each one deserves a look. A move that exactly reverses on the next bar, or a jump by a clean ratio such as 2:1 or 3:1, often points to a bad tick or an unadjusted split.

Handle gaps deliberately

There are two different kinds of missing data. A missing value is a bar that exists with an empty field. A missing bar is a session or interval that is not in the file at all. Look at the spacing between timestamps to find the second kind.

gaps = df.index.to_series().diff()
print(gaps.value_counts().head())

For daily data, gaps over weekends and exchange holidays are expected. Unexpected gaps should be investigated rather than filled automatically. If you do forward-fill prices, limit how far a value can be carried and never forward-fill volume, because a copied volume figure invents trading that did not happen. Remember that a forward-filled close produces a zero return, which can make a series look calmer than it was.

Understand adjustments

Splits and dividends change the price series. Adjusted prices are convenient for computing returns over long horizons, while unadjusted prices reflect what actually traded on a given day and are what order sizes and price thresholds referred to at the time. Keep both if your source provides them, and record which one each analysis uses.

Resample with the right aggregation

When converting to a lower frequency, each column needs its own rule: the first open, the highest high, the lowest low, the last close and the summed volume.

weekly = (
    df.resample("W-FRI")
      .agg({"open": "first", "high": "max", "low": "min",
            "close": "last", "volume": "sum"})
      .dropna(subset=["close"])
)

Using a plain mean() here would produce bars that never existed. Also check where resampling labels each bar, since an end-of-period label is easy to misread as the start of the period.

Save the result and document it

Write the cleaned data to a columnar format such as Parquet, alongside a short note listing the source, the checks applied, the rows removed and any manual fixes. When a result looks surprising months later, that note is often the fastest way to find out why.

• Sort, deduplicate and index by time.
• Store in UTC and convert only when needed.
• Validate OHLC relationships and review extreme moves.
• Investigate gaps before filling them.
• Know whether each series is adjusted.

These habits are covered hands-on in the Python Programming for Financial Markets course, which takes you from first scripts to analysis-ready market data.