DataFrames are columns

So far in this track every record has been a dict: one row, many fields. That is the natural way to think about data. It is also the slowest way to compute on it.

Every fast data tool you will use, pandas, Polars, NumPy, Arrow, DuckDB, flips the layout. They store data by column. This lesson shows why, using only the standard library.

Rows versus columns

Here is the same tiny table, two ways:

# row-oriented: a list of records
rows = [
    {"trip_id": 1, "city": "Berlin", "fare": 12.50},
    {"trip_id": 2, "city": "Lagos",  "fare": 8.10},
]

# column-oriented: a dict of lists
cols = {
    "trip_id": [1, 2],
    "city":    ["Berlin", "Lagos"],
    "fare":    [12.50, 8.10],
}

Same information. Different shape. If you read the SQL track, you have seen this before: it is the row store and the column store from the first lesson, now in Python.

The row shape repeats the field names in every record and scatters each row’s values across memory. The column shape stores each name once and keeps all the fares together. For “sum the fares” you want exactly one thing: all the fares, side by side.

Measure the overhead

Start with how much memory each shape takes for 1,000 trips:

import sys

n = 1_000
rows = [{"trip_id": i, "city": "Berlin", "fare": 12.5} for i in range(n)]
cols = {
  "trip_id": list(range(n)),
  "city": ["Berlin"] * n,
  "fare": [12.5] * n,
}

row_bytes = sys.getsizeof(rows) + sum(sys.getsizeof(r) for r in rows)
col_bytes = sys.getsizeof(cols) + sum(sys.getsizeof(c) for c in cols.values())

print(f"rows: {row_bytes:>9,} bytes (a dict per row)")
print(f"cols: {col_bytes:>9,} bytes (a list per column)")
print(f"ratio: {row_bytes / col_bytes:.1f}x")

Both numbers count only the containers, not the values inside. The row version pays for a full hash table per record, about 180 bytes each, just to hold three fields. The column version pays for three lists, total. The gap is around 8x, and it widens as you add columns.

Typed arrays: drop the boxes

A list of floats is still an array of pointers to separate float objects, 8 bytes per pointer plus 24 bytes per float. The array module stores raw machine values instead: 8 bytes per float, packed end to end, no objects.

import sys
from array import array

n = 100_000
fares_list = [float(i % 500) / 10 + 5 for i in range(n)]
fares_arr = array("d", fares_list)       # "d" = 8-byte float, like a DOUBLE column

list_bytes = sys.getsizeof(fares_list) + sum(sys.getsizeof(x) for x in fares_list)
arr_bytes = sys.getsizeof(fares_arr)

print(f"list of floats:  {list_bytes:>10,} bytes")
print(f"array('d'):      {arr_bytes:>10,} bytes")
print(f"per value:       {list_bytes / n:.0f} vs {arr_bytes / n:.0f} bytes")
print("same sum:", round(sum(fares_list), 2) == round(sum(fares_arr), 2))

32 bytes per value down to 8. A column of 100 million fares is 800 MB as a typed array and over 3 GB as a list.

Memory is half the story. The other half is speed. A packed buffer of doubles is what a CPU is built to chew through: it streams contiguous memory into cache and can add several values per instruction. NumPy and pandas run their loops in C over exactly this kind of buffer. df["fare"].sum() is one tight C loop over 8-byte values. sum(r["fare"] for r in rows) is a Python loop doing a dict lookup, a pointer chase and a type check per row. On a laptop the first is typically 50 to 100x faster.

Filters are masks

In a column store you do not filter by looping over rows and copying the ones you want. You compute a mask: one boolean per row. Then every column is filtered by the same mask.

from array import array

cols = {
  "city":   ["Berlin", "Lagos", "Berlin", "Austin", "Lagos", "Berlin"],
  "status": ["completed", "completed", "cancelled", "completed", "completed", "completed"],
  "fare":   array("d", [12.5, 8.1, 0.0, 22.0, 9.4, 15.25]),
}

def mask(col, pred):
  return [pred(v) for v in col]

def take(col, m):
  return [v for v, keep in zip(col, m) if keep]

m = mask(cols["status"], lambda s: s == "completed")   # WHERE status = 'completed'
city = take(cols["city"], m)
fare = take(cols["fare"], m)

totals = {}                                            # GROUP BY city
for c, f in zip(city, fare):
  totals[c] = totals.get(c, 0.0) + f                 # SUM(fare)

for c in sorted(totals):
  print(f"{c:<7} {totals[c]:6.2f}")

That is SELECT city, sum(fare) FROM trips WHERE status = 'completed' GROUP BY city, built from parts you have met: a mask for the filter, a dict for the group-by. pandas writes the same thing as df[df.status == "completed"].groupby("city").fare.sum(). Same steps, but each one is a C loop over a typed array.

Notice the filter only touched the status column, and only the city and fare columns were copied. A table with 40 columns would have ignored the other 37. That is projection, the same trick the SQL engine uses.

What columns are bad at

Columns are not free. Adding one new row to the cols dict means appending to every list, one per column. Changing one record means touching every column it has. For a table with 40 columns, an insert is 40 small writes in 40 places.

That is why the systems that take a constant stream of small writes, like the database behind a checkout page, store rows. And the systems that answer big questions over history store columns. Many pipelines do both: write rows as they arrive, then convert them to columns in batches, every few minutes or every night.

Where this shows up next

Once you see columns, you see them everywhere:

• Parquet files store each column in its own chunks, with min/max stats, so readers can skip whole chunks.
• Apache Arrow is a standard in-memory layout for typed columns, so pandas, Polars and DuckDB can hand data to each other without copying.
• DuckDB runs SQL over columns in vectors of 2,048 values at a time: big enough to be fast, small enough to fit in cache.

They are all the cols dict, done carefully.

Ship it

Write colstore.py, a column store in about 40 lines:

1. A function that takes a list of row dicts and returns a dict of columns, using array("d") for float fields and lists for the rest.
2. mask(col, pred) and take(col, mask) as above.
3. A group_sum(keys, values) function that returns a dict of totals.

Load content/datasets/ridelog/trips.csv with the streaming reader from lesson 2, convert it, and compute completed fares per city. Then run the same query in SQL on the ridelog dataset and check that the five totals match to the cent.