Pandas: Deconstructing Small Analytics

Pandas, Polars, and where one machine stops.

Concept. Pandas and Polars load your whole dataset into your machine's memory and work on it there. In-memory work is fast, and it stops the moment the data outgrows the machine.

Intuition. Build a small Spotify and a laptop handles it comfortably. Point the same code at the real one and it stops. Closing that gap is the rest of this course.

Case Study Reading Time: 5 mins

Let's build Spotify. Start small: a million songs, fifty million listens, a few thousand users. Three tables, a few gigabytes, and every tool in this section handles it.

Before you can ask anything of it, it has to be loaded.

One file on disk, listens.csv, written row after row. An arrow labelled read carries it into memory as a DataFrame: named, typed columns user_id, song_id and rating with rows of values. Below, the same DataFrame is shown as it is really held, one contiguous typed array per column.

Figure 1. A CSV on disk or a Parquet file reads into a DataFrame with named, typed columns. The system stores each column as one contiguous array of a single type, which makes column aggregation fast. The DataFrame keeps every column of every row in memory at once, so it runs fast on small data and fails when the data exceeds memory.

A familiar example

The task: the top 10 songs by play count.

In pure Python you would write nested for loops over the rows, build a dictionary of counts, and sort it at the end. That is imperative code: you spell out how to compute the answer, step by step.

Pandas, Polars, and SQL all let you skip the how. Same intent, two surfaces, side by side:

Polars (Python)

import polars as pl

popular = (
    listens_df
    .join(songs_df, left_on='song_id',
          right_on='song_id', how='inner')
    .group_by(['title', 'artist'])
    .agg(pl.len().alias('plays'))
    .sort('plays', descending=True)
    .head(10)
)

SQL

SELECT title, artist, COUNT(*) AS plays
FROM listens
JOIN songs ON listens.song_id = songs.song_id
GROUP BY title, artist
ORDER BY plays DESC
LIMIT 10;

Read them together. Neither one says how: no loops, no counters, no sort algorithm. You declare the join, the grouping, and the order; the engine works out how to walk the rows. That is the declarative style, and SQL is its canonical form.

The SQL version has one extra property: it does not change with scale. It runs unchanged against a thousand rows on your laptop or a hundred billion across two thousand machines; only the execution plan changes. You do not rewrite the query when the data grows. You point it at a bigger engine.

Pandas keeps the whole dataset in memory on one machine: a DataFrame read from CSV or Parquet on disk, grouped, sorted, and joined in memory, returning a result fast, until the data exceeds memory and it crashes.

Figure 2. Group, sort, and join all execute in memory in one process, which is fast while the dataset fits. Past that, the process does not slow down. It crashes, because nothing runs the same computation from disk.

Pandas, Polars, and a database

Now the real one. Spotify's catalogue is about a hundred million songs, a hundred times the one we just built, and its listens run to billions of rows. It does not fit on a laptop, or on a single server. That is where the tools diverge.

One axis of growing data size with three bars showing how far each tool gets. A dashed boundary splits it: to the left, one machine is enough; to the right, the data needs many machines. Pandas, writing DataFrame code, reaches a short way and ends at a hard red wall marked out of RAM, while the file is still smaller than the laptop's RAM. Polars, also DataFrame code, reaches much further and fades out instead of hitting a wall. A distributed database, writing SQL, runs the full width and continues past the boundary. Underneath: one machine is hardware you already own, many machines is a cluster you rent.

Figure 3. Each tick is ten times the last. Pandas copies the data at each step and holds every row in memory, so it hits a wall while the file is still several times smaller than the laptop's RAM. Polars keeps almost the same API but builds a lazy plan and streams from disk on every core, so it fades instead of crashing rather than stopping at a fixed size. Where exactly it gives out depends on the query, which is why its bar is not drawn with an end. Only the distributed database crosses the line, running the query where the data lives. Everything left of that line runs on hardware you already own; everything right of it is a cluster you rent.

Where this leaves Pandas in 2026

When the data fits in memory, a DataFrame on a laptop is the right tool, and the ceiling on one machine rises every year.

Too big for a DataFrame does not mean big enough to need many machines. That gap is wide, and a database covers it.

You meet the first one on the next page, and it is not the machine room you are picturing. It is a single file, running inside an AI coding agent, and it exists because the agent forgets everything the moment its context window fills.

Takeaway

Pandas on a laptop does what a database does, for small amounts of data. The rest of CS145 is what happens when one machine is no longer enough, and that line sits further out than it used to.