1. How to store a time series without regretting it

Level 5: Storing, updating and scaling

You arrive here with the data already downloaded and already validated. What remains is the decision that looks administrative and is not: where you put it and in what shape. A badly built store almost never throws errors, which would be the convenient outcome. What it does is charge you forty seconds every time you want to test an idea, until you stop testing ideas.

CSV is convenient and that is why it gets expensive

CSV has one enormous virtue: anything opens it, you can inspect it with your eyes and it forces you to install nothing. That is why everybody starts there and they are right to. It has three defects that grow with the project:

It does not store types. A CSV is text, and pandas guesses again on every read. It guesses right almost always, and the "almost" is the problem: Nabors Industries' NA symbol turns into a missing value unless you forbid it, an identifier with leading zeros loses them, and an ambiguous date can be read with day and month swapped without warning.
It does not compress. Every digit of every price takes up a byte, and so do the commas and the line breaks.
It gets read whole. There is no way to read only the close column without decoding the other five, nor only March 2019 without running through the entire file until you find it.

Put numbers on it. Five hundred stocks, twenty-five years, some 252 sessions a year: around 3.1 million OHLCV rows. As a stated order of magnitude, and not as a promise, that is hundreds of megabytes in CSV and tens in a compressed columnar format. The exact ratio depends on your columns; measure it on your own machine before quoting it.

What a compressed columnar format buys you

A columnar file stores each column separately, with its type in the schema and its own compression. Out of that come four advantages that are not cosmetic:

Types come back exactly as you stored them. The date comes back as a date and the integer as an integer, with no guessing and no read arguments you forget.
It compresses far better because neighbors resemble each other. The symbol column repeats AAPL six thousand times in a row and is stored as a dictionary; the price one holds contiguous values that are very similar to one another.
You read only the columns you ask for. Asking for close and volume from a six-column table moves roughly a third of the bytes.
It stores statistics per row block. With the minimum and maximum of each block, the reader discards whole blocks without decoding them.

The price to pay is real: you no longer open it with a text editor, you do not fix a cell by hand and adding a row means rewriting a block rather than appending a line at the end. It is a store, not a notebook.

import pandas as pd

# The table arrives in long format: one row per (simbolo, fecha)
precios = pd.read_csv("precios_crudos.csv", parse_dates=["fecha"],
                      keep_default_na=False, na_values=[""])

# Explicit types: whatever you do not declare, parquet freezes as pandas guessed it
precios = precios.astype({
    "simbolo": "string",
    "cierre": "float64",   # float32 takes up half the space and loses precision when accumulating
    "volumen": "int64",
})

# The partition column is derived from the data, never invented
precios["anio"] = precios["fecha"].dt.year

precios.to_parquet(
    "almacen/precios",
    engine="pyarrow",
    compression="zstd",
    partition_cols=["anio"],
    index=False,
)

Long or wide: the same table with two different costs

In long format there is one row per symbol-and-date combination. The schema never changes: adding stock number five hundred and one means new rows, not new columns. It filters well, it partitions well and it does not force you to fill gaps with missing values for stocks that were not trading yet.

In wide format the index is the date and there is one column per symbol. It is what matrix operations want: returns, correlations, betas, anything that treats the five hundred stocks as a vector. Its problem is that the schema depends on the data, and that with the five OHLCV columns you land on 3,000 columns or on five parallel tables that have to be kept in sync.

The practical rule is simple: store in long format and pivot to wide in memory just before the calculation that needs it. The pivot is cheap compared with what it avoids. A matrix of 6,300 sessions by 500 columns is 3.15 million cells, some 25 MB in eight-byte floating point: it fits with room to spare.

ancho = precios.pivot(index="fecha", columns="simbolo", values="cierre")
retornos = ancho.pct_change()
matriz = retornos.corr()          # 500 x 500, computed on aligned data

Partitioning is writing an index with folders

Partitioning means splitting the store into subfolders according to the value of a column, so that the reader opens only the ones it needs. The choice is not a matter of style, it answers the question of which queries you run most often:

By date favors cross-sectional queries: give me the whole market between 2020 and 2021. It is the usual choice in research, because almost everything you test is tested over a time window.
By symbol favors series queries: give me AAPL's complete history. It is the usual choice in production, when each process handles one stock.

You can nest the two, but mind the arithmetic. Symbol and then year is 500 by 25, that is 12,500 folders with around 250 rows each: files of a few kilobytes in which the metadata weighs more than the data, and thousands of file opens that you feel. Aim for files of tens to hundreds of megabytes; many tiny files perform worse than one big one.

cierres = pd.read_parquet(
    "almacen/precios",
    columns=["simbolo", "fecha", "cierre", "anio"],
    filters=[("anio", ">=", 2023), ("anio", "<=", 2024)],
)

# Partition columns come back as categoricals: comparing with > fails
cierres["anio"] = cierres["anio"].astype("int16")

A case: the same data, two stores

Take the 3.1 million rows from earlier. Stored in a single CSV, any question starts by parsing the entire file: tens of seconds on a normal laptop, as an order of magnitude, with memory usage several times the file size while the types are being built.

Stored in columnar format partitioned by year, the average question about 2024 volume opens one folder out of twenty-five and decodes two columns out of six. The real work drops below one percent of the previous figure, and the read goes from tens of seconds to a fraction of a second. It is not magic in the format: it is that you have stopped reading 99% of the store to answer something that only needed 1%.