SyntaxPilot
Courses/Data Analytics with Python/Lesson 29
Video coming soonThis lesson’s video is being made

Start with the notes below; the video appears here as soon as it is on YouTube.

Data Analytics with Python · Lesson 29 of 90

Working with bigger data: memory, speed and Parquet

vectorisation recapmemory footprintcategoricals for memorychunked processingParquetcolumnar formatApache Arrowpyarrow dtype backendevalPolars (mention)DuckDB on DataFramesprofiling with %timeit

These are the notes for Data Analytics with Python, episode 29. Code that is fine on ten rows can take minutes, or run out of memory, on a million. Here you invent the habits professionals use for bigger data: vectorisation instead of row loops, measuring the memory footprint, the category dtype and downcasting, chunked processing, the Parquet file format and columnar storage, Apache Arrow and the pyarrow dtype backend, eval, DuckDB on DataFrames, Polars, and measuring speed with %timeit. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5, NumPy 2.5.3, pyarrow 26.0.1, DuckDB 2.0.0, Polars 2.1.0); outputs are unedited. Timings change from run to run and machine to machine; the sizes and results do not.

The problem: a million orders

Ramesh's chai stall became a chain: 50 stalls in 5 cities. Every order is logged with the city, the quantity (cups) and the price per cup. A year of orders is a million rows, and the code he wrote for one stall suddenly crawls. The data used in every example (generated by code, made up for teaching):

import sys; sys.tracebacklimit = 0
import pandas as pd, numpy as np, time
rng = np.random.default_rng(1)
n = 1_000_000
df = pd.DataFrame({"city": rng.choice(["Pune", "Mumbai", "Delhi", "Chennai", "Kolkata"], n),
                   "qty": rng.integers(1, 6, n), "price": rng.choice([10.0, 15.0, 20.0, 25.0], n)})

1_000_000 is just 1000000; Python ignores the underscores, which make big numbers readable. rng.choice picks random values from a list and rng.integers(1, 6, n) gives whole numbers from 1 to 5.

print(df.head(3))
print(df.shape)
print(df.dtypes)
      city  qty  price
0    Delhi    5   10.0
1    Delhi    2   20.0
2  Chennai    5   10.0
(1000000, 3)
city         str
qty        int64
price    float64
dtype: object
  • str is the text dtype pandas 3 uses by default.
  • int64 / float64: whole / decimal numbers stored in 64 bits = 8 bytes each. A bit is a single 0 or 1; a byte is 8 bits.

Loops vs vectorisation

Total revenue = quantity × price, summed. The beginner way loops over rows with iterrows, which hands you each row as a small Series. We time only the first 20,000 rows with time.perf_counter(), a high-resolution stopwatch (call it before and after; the difference is the elapsed seconds).

small = df.head(20_000)
t = time.perf_counter()
total = 0
for _, row in small.iterrows():
    total += row["qty"] * row["price"]
print("loop:", total, round(time.perf_counter() - t, 3), "s")
loop: 1057200.0 0.28 s

Most of a second for 2 % of the data; a million rows would take about 50 times longer (around half a minute). Why so slow? iterrows builds a brand-new Series object for every row, and every multiplication runs through the Python interpreter (the program that reads and runs Python code line by line) one value at a time.

Vectorisation (episode 8) means one operation on whole columns at once; pandas/NumPy run the loop in fast compiled C code:

small = df.head(20_000)
t = time.perf_counter()
total = (small["qty"] * small["price"]).sum()
print("vectorised:", total, round(time.perf_counter() - t, 4), "s")
vectorised: 1057200.0 0.0012 s

Same answer, hundreds of times faster (the exact ratio depends on the machine, and the gap grows with the data). Rule 1 of bigger data: columns, not row loops. If you need a condition, use np.where / np.select (episode 16); apply with axis=1 is also a hidden row loop.

Memory footprint

The memory footprint of a table is how many bytes of RAM (random-access memory, the computer's fast working memory, where pandas keeps DataFrames) it occupies. Measure it with memory_usage(deep=True); deep=True counts the real bytes of text values too. Without it, columns of Python objects report only the size of their pointers (8-byte addresses of where each string lives), which hides the true cost.

print(df.memory_usage(deep=True))
print(round(df.memory_usage(deep=True).sum() / 1e6, 1), "MB")
Index         132
city     13800412
qty       8000000
price     8000000
dtype: int64
29.8 MB

Each number column is exactly 8,000,000 bytes: a million rows × 8 bytes. (Index 132 is the tiny RangeIndex, which stores only start, stop and step.)

The category dtype

Real-life picture: writing "Kolkata" on every one of a million bills wastes ink when there are only five city names. Smarter: draw a legend once (0 = Chennai … 4 = Pune) and write only the small number on each bill. That is the category dtype: each row stores a small integer code, and the unique values (the categories) are stored once.

mb = lambda x: round(x.memory_usage(deep=True) / 1e6, 1)
print("object  ", mb(df["city"].astype(object)), "MB")
print("str     ", mb(df["city"]), "MB")
print("category", mb(df["city"].astype("category")), "MB")
print(df["city"].astype("category").cat.categories.tolist())
object   54.8 MB
str      13.8 MB
category 1.0 MB
['Chennai', 'Delhi', 'Kolkata', 'Mumbai', 'Pune']
Storage MB for 1,000,000 city names
object (Python string objects, the old default) 54.8
str (pandas 3 default, Arrow-backed here) 13.8
category 1.0

Use category when a text column has few unique values that repeat a lot (city, grade, payment method). A column where almost every value is unique (order id, phone number) gains nothing and can even grow (practice question 1).

Downcasting

Downcasting means storing numbers in the smallest type that still fits them. Cups are 1 to 5, so 1 byte (int8, range −128 to 127) is enough. pd.to_numeric(..., downcast="integer") picks the smallest integer type automatically; astype("float32") stores decimals in 4 bytes (about 7 significant digits, enough for prices but not for exact money totals in large sums).

small = df.assign(city=df["city"].astype("category"),
                  qty=pd.to_numeric(df["qty"], downcast="integer"),
                  price=df["price"].astype("float32"))
print(small.dtypes)
print(round(df.memory_usage(deep=True).sum() / 1e6, 1), "MB ->",
      round(small.memory_usage(deep=True).sum() / 1e6, 1), "MB")
city     category
qty          int8
price     float32
dtype: object
29.8 MB -> 6.0 MB

29.8 MB → 6.0 MB with the same information. assign (episode 16) returns a new DataFrame with the replaced columns.

The silent overflow trap

print(np.iinfo("int8"))
s = pd.Series([100, 120], dtype="int8")
print(s + s)
Machine parameters for int8
---------------------------------------------------------------
min = -128
max = 127
---------------------------------------------------------------

0   -56
1   -16
dtype: int8

np.iinfo shows an integer type's range. 100 + 100 = 200 does not fit in int8, so it wraps around to −56 (200 − 256), with no error and no warning. This is integer overflow. Downcast only when the values and anything you compute from them (sums, products) stay well inside the range; otherwise keep int64, or convert back before arithmetic.

Chunked processing

When a file is bigger than RAM (a 50 GB file on an 8 GB laptop), read_csv cannot load it all. Real-life picture: eat a thali one bite at a time. read_csv(..., chunksize=250_000) returns the file piece by piece; each piece (a chunk) is a normal DataFrame. Process each chunk, keep a running result, and let the chunk go. Only one chunk is in memory at a time: chunked processing.

df.to_csv("orders.csv", index=False)
totals = None
for chunk in pd.read_csv("orders.csv", chunksize=250_000):
    part = (chunk["qty"] * chunk["price"]).groupby(chunk["city"]).sum()
    totals = part if totals is None else totals.add(part, fill_value=0)
    print(len(chunk), "rows done")
print(totals)
250000 rows done
250000 rows done
250000 rows done
250000 rows done
city
Chennai    10497885.0
Delhi      10492545.0
Kolkata    10502200.0
Mumbai     10513935.0
Pune       10491425.0
dtype: float64

The totals are exactly what one groupby on the whole table gives. add(part, fill_value=0) adds two Series by city label and treats a city missing from one side as 0.

Which statistics combine across chunks? Sums, counts, minimums and maximums combine directly; a mean combines if you keep sums and counts (practice question 3). A median or other percentile does not: the median of chunk medians is not the overall median. Plan the aggregation before you chunk, or use a tool that handles it for you (DuckDB, below).

Memory savers at read time: usecols=[...] reads only some columns, dtype={"city": "category", "qty": "int8"} sets small types while reading, and nrows= reads only the first rows for a quick look.

Parquet: columnar files

CSV (comma-separated values) has two problems: it is text, so every number must be parsed again on every read, and it stores data row by row. Real-life picture: a school that keeps one report card per student must open every card to average the maths marks; a school that keeps one sheet per subject reads only the maths sheet.

  • Row format (CSV): each line holds all the columns of one row.
  • Columnar format: all values of one column are stored together, so a reader can load only the columns it needs, and similar values next to each other compress (shrink) very well.

Apache Parquet is "an open source, column-oriented data file format designed for efficient data storage and retrieval" (Parquet docs). It also stores each column's data type, so nothing has to be guessed on reading. pandas writes it with to_parquet and reads it with read_parquet (it uses the pyarrow library under the hood).

import os
def timed(label, f):
    t = time.perf_counter(); f(); print(label, round(time.perf_counter() - t, 2), "s")
df.to_csv("orders.csv", index=False)
df.to_parquet("orders.parquet", index=False)
print("csv    ", round(os.path.getsize("orders.csv") / 1e6, 1), "MB")
print("parquet", round(os.path.getsize("orders.parquet") / 1e6, 1), "MB")
timed("read csv    ", lambda: pd.read_csv("orders.csv"))
timed("read parquet", lambda: pd.read_parquet("orders.parquet"))
timed("one column  ", lambda: pd.read_parquet("orders.parquet", columns=["city"]))
csv     13.8 MB
parquet 1.0 MB
read csv     0.22 s
read parquet 0.07 s
one column   0.02 s

13.8 MB of CSV became 1.0 MB of Parquet (the five repeated city names and few distinct prices compress extremely well), and reading is several times faster. columns=[...] reads only the listed columns; the rest of the file is never read. This is why data teams store analytics data as Parquet.

Apache Arrow and the pyarrow dtype backend

Apache Arrow defines an "in-memory columnar format": "a standardized, language-agnostic specification for representing structured, table-like datasets in-memory", part of "a multi-language toolbox for building high performance applications" (Arrow overview). Parquet is the columnar format on disk; Arrow is the columnar format in memory. pyarrow is Arrow's Python library.

A dtype backend is the storage engine behind pandas' column types. dtype_backend="pyarrow" makes every column an Arrow-backed type:

df.to_parquet("orders.parquet", index=False)
a = pd.read_parquet("orders.parquet", dtype_backend="pyarrow")
print(a.dtypes)
city     large_string[pyarrow]
qty             int64[pyarrow]
price          double[pyarrow]
dtype: object

Arrow-backed columns have built-in missing-value support for every type, and tools that speak Arrow (DuckDB, Polars and others) can share the data without converting it.

eval, query, DuckDB and Polars

df.eval("expression") computes an expression written as a string; df.query("condition") (episode 15) filters rows with one. On large tables they can avoid creating big temporary columns for each step.

e = df.eval("qty * price")
print(e.head(3).tolist(), (e == df["qty"] * df["price"]).all())
print(df.query("city == 'Pune' and qty > 4").shape)
[50.0, 40.0, 50.0] True
(39888, 3)

DuckDB is an embedded analytical database, "optimised for OLAP-style workloads" (OpenSource For You, March 2026). Embedded (in-process) means it runs inside your Python program: no server to install. OLAP (online analytical processing) means queries that scan and summarise lots of rows, which is exactly what analysts do. It can query a pandas DataFrame by its variable name, and it can query Parquet and CSV files directly, even bigger-than-RAM ones. SQL is covered in episodes 56 and 57.

import duckdb
print(duckdb.sql("""SELECT city, SUM(qty * price) AS revenue
                    FROM df GROUP BY city ORDER BY revenue DESC""").df())
      city     revenue
0   Mumbai  10513935.0
1  Kolkata  10502200.0
2  Chennai  10497885.0
3    Delhi  10492545.0
4     Pune  10491425.0

.df() converts DuckDB's answer back into a pandas DataFrame.

Polars is a DataFrame library "written in Rust with bindings for Python and other languages" (same source), designed for speed and built on the Arrow memory format. The same revenue per city in Polars:

import polars as pl
p = pl.from_pandas(df)
print(p.group_by("city").agg((pl.col("qty") * pl.col("price")).sum().alias("revenue")).sort("revenue", descending=True))
shape: (5, 2)
┌─────────┬─────────────┐
│ city    ┆ revenue     │
│ ---     ┆ ---         │
│ str     ┆ f64         │
╞═════════╪═════════════╡
│ Mumbai  ┆ 1.0513935e7 │
│ Kolkata ┆ 1.05022e7   │
│ Chennai ┆ 1.0497885e7 │
│ Delhi   ┆ 1.0492545e7 │
│ Pune    ┆ 1.0491425e7 │
└─────────┴─────────────┘

(1.0513935e7 is scientific notation: 1.0513935 × 10⁷ = 10,513,935.)

Which tool when?

Situation Tool
Data fits easily in RAM pandas + vectorised code
Tight on RAM category, downcasting, usecols, dtype= at read, chunksize
Storing/sharing analytics data Parquet instead of CSV
You think in SQL, or the data is bigger than RAM DuckDB
Maximum single-machine speed, new project Polars
Any of the above measure first (memory_usage(deep=True), %timeit)

Measuring speed: %timeit and timeit

One timing can be unlucky (another program was busy). Profiling means measuring where time and memory actually go, so you fix the real bottleneck instead of a guess.

  • In Jupyter/Colab: %timeit (df["qty"] * df["price"]).sum() runs the line many times and reports the mean and spread. Lines starting with % are magic commands: special Jupyter commands, not Python, so they fail in a normal .py script.
  • In a script: the timeit module. timeit.repeat(f, number=10, repeat=3) runs f 10 times per round, for 3 rounds, and returns each round's total; the minimum round divided by 10 is the best time per call.
import timeit
f = lambda: (df["qty"] * df["price"]).sum()
best = min(timeit.repeat(f, number=10, repeat=3)) / 10
print(round(best * 1000, 2), "ms per run")
2.29 ms per run

Common mistakes

Code / habit Real error / symptom Why Fix
for _, row in df.iterrows(): ... no error; ~1 s per 20,000 rows one Python object per row column maths, np.where, groupby
pd.Series([100, 120], dtype="int8") + ... no error; -56 int8 holds −128..127; overflow wraps keep int64 or check np.iinfo
median of each chunk, then median of medians no error; wrong answer medians don't combine combine sums/counts only, or use DuckDB
df.memory_usage() on object text no error; far too small counts pointers only memory_usage(deep=True)
astype("category") on a unique id column no error; memory goes up one category per row keep it as str
%timeit in a .py file SyntaxError: invalid syntax magic commands are Jupyter-only timeit.repeat(...)
saving analytics data as CSV only slow reads, types lost (dates become text) text, row-based to_parquet

Practice

All questions use this table of 200,000 exam results:

import sys; sys.tracebacklimit = 0
import pandas as pd, numpy as np
rng = np.random.default_rng(3)
n = 200_000
m = pd.DataFrame({"grade": rng.choice(["A", "B", "C", "D", "E"], n),
                  "marks": rng.integers(0, 101, n),
                  "roll_no": [f"R{i:06d}" for i in range(n)]})

1. Convert grade and roll_no to category. Which one shrinks, and why?

Solution
for col in ["grade", "roll_no"]:
    before = m[col].memory_usage(deep=True) / 1e6
    after = m[col].astype("category").memory_usage(deep=True) / 1e6
    print(col, round(before, 2), "MB ->", round(after, 2), "MB")
grade 1.8 MB -> 0.2 MB
roll_no 3.0 MB -> 3.83 MB

grade has 5 unique values, so 1-byte codes replace the text. roll_no is unique per row: the categories list holds every value and each row gets a code, so it grows.

2. Marks are 0 to 100. Which integer type fits? Downcast and compare the memory.

Solution
print(np.iinfo("int8").max, np.iinfo("int16").max)
small = pd.to_numeric(m["marks"], downcast="integer")
print(small.dtype, m["marks"].memory_usage() / 1e6, "MB ->", small.memory_usage() / 1e6, "MB")
127 32767
int8 1.600132 MB -> 0.200132 MB

100 ≤ 127, so int8. But the sum of 200,000 marks would overflow int8: convert back (astype("int64")) before summing big totals.

3. Save the table as CSV and compute the mean mark per grade by reading it in chunks of 50,000. Check against a normal groupby.

Solution
m.to_csv("marks.csv", index=False)
sums = None; counts = None
for chunk in pd.read_csv("marks.csv", chunksize=50_000, usecols=["grade", "marks"]):
    g = chunk.groupby("grade")["marks"]
    sums = g.sum() if sums is None else sums.add(g.sum(), fill_value=0)
    counts = g.count() if counts is None else counts.add(g.count(), fill_value=0)
print((sums / counts).round(2))
print(m.groupby("grade")["marks"].mean().round(2))
grade
A    50.00
B    50.07
C    50.03
D    50.06
E    49.86
Name: marks, dtype: float64
grade
A    50.00
B    50.07
C    50.03
D    50.06
E    49.86
Name: marks, dtype: float64

A mean of chunk means would be wrong when chunks have different group sizes; carrying sums and counts is always exact.

4. Save as Parquet and read back only grade and marks.

Solution
m.to_parquet("marks.parquet", index=False)
g = pd.read_parquet("marks.parquet", columns=["grade", "marks"])
print(g.shape, g.columns.tolist())
(200000, 2) ['grade', 'marks']

5. With DuckDB, count students and average marks per grade.

Solution
import duckdb
print(duckdb.sql("SELECT grade, COUNT(*) AS n, ROUND(AVG(marks), 2) AS avg_marks FROM m GROUP BY grade ORDER BY grade").df())
  grade      n  avg_marks
0     A  40330      50.00
1     B  39790      50.07
2     C  39933      50.03
3     D  39991      50.06
4     E  39956      49.86

Quick quiz

Four text columns with 1,000,000 rows each. Which one shrinks the most as a category?

A. city (5 unique values) B. order_id (1,000,000 unique) C. phone (950,000 unique) D. name (600,000 unique)

Answer

A. city. A category stores each unique value once plus a small code per row, so few unique values = big savings. Columns that are nearly unique per row gain nothing and can grow (practice question 1).

Interview questions

  1. Why is iterrows slow? It creates a Series per row and does Python-level work per value; vectorised operations run in compiled code over whole arrays.
  2. How do you find a DataFrame's real memory use? df.memory_usage(deep=True).sum() or df.info(memory_usage="deep").
  3. When does category help, and when does it hurt? Helps for low-cardinality text (few unique values); hurts for near-unique columns.
  4. What is downcasting and its risk? Using smaller numeric types (int8, float32); risk of silent overflow and lost precision.
  5. How do you process a CSV larger than memory in pandas? read_csv(chunksize=...), aggregate each chunk with combinable statistics, combine; or use DuckDB/Polars.
  6. Can you compute a median with chunks? Not by combining chunk medians; you need all values (or an approximate algorithm / a database engine).
  7. Parquet vs CSV? Parquet: binary, columnar, compressed, typed, can read selected columns; CSV: plain text, row-based, untyped, universal.
  8. What is Apache Arrow? A standard in-memory columnar format and toolbox; pandas can use it as a dtype backend and libraries share data through it.
  9. What is DuckDB good for? In-process SQL analytics over DataFrames and Parquet/CSV files, including bigger-than-RAM data, with no server.
  10. How do you benchmark code fairly? Repeat it (%timeit / timeit.repeat), take the best or mean of several runs, and measure before optimising.

Glossary

Term Meaning Example
Bit / byte One 0-or-1 / a group of 8 bits int8 = 1 byte
RAM The computer's fast working memory, where DataFrames live 8 GB laptop
Interpreter Program that runs Python code step by step python3
iterrows Loop over rows as (index, Series) pairs; slow row-by-row revenue
time.perf_counter() High-resolution stopwatch for code elapsed seconds
Vectorisation One operation on whole columns in compiled code df["qty"] * df["price"]
Memory footprint Bytes a table occupies in RAM 29.8 MB
memory_usage(deep=True) Real bytes per column, including text city 13.8 MB
Pointer An address of where an object lives in memory object column internals
object dtype Column of Python objects (old text default) 54.8 MB of city names
str dtype pandas 3 default text type city str
Category dtype Small integer codes + one list of unique values astype("category")
Categories The unique values of a category column ['Chennai', …, 'Pune']
Cardinality Number of unique values in a column city: 5
Downcasting Using the smallest numeric type that fits int64 → int8
int8 / int16 / int64 Whole numbers in 1 / 2 / 8 bytes −128..127 for int8
float32 / float64 Decimal numbers in 4 / 8 bytes prices
np.iinfo Shows an integer type's min and max np.iinfo("int8")
Overflow A result too big for its type wraps around silently 100 + 100 = −56
Chunk A piece of a file read at a time 250,000 rows
chunksize read_csv option that yields chunks chunksize=250_000
Chunked processing Process piece by piece, combining partial results revenue per city
usecols Read only some columns usecols=["city"]
CSV Plain-text, comma-separated, row-based file orders.csv
Row format Each record's fields stored together CSV
Columnar format Each column's values stored together Parquet, Arrow
Compression Encoding data to take less space 13.8 MB → 1.0 MB
Parquet Open-source, column-oriented, compressed, typed file format to_parquet
Apache Arrow Standard in-memory columnar format and toolbox pyarrow
pyarrow Arrow's Python library Parquet reading
Dtype backend The storage engine behind pandas dtypes dtype_backend="pyarrow"
eval Compute a string expression on a DataFrame df.eval("qty * price")
query Filter rows with a string condition df.query("qty > 4")
DuckDB Embedded (in-process) analytical SQL database duckdb.sql(...)
Embedded / in-process Runs inside your program, no server DuckDB
OLAP Analytical queries that scan and summarise many rows revenue per city
Polars Fast DataFrame library written in Rust pl.from_pandas(df)
Profiling Measuring where time and memory go %timeit
Magic command Jupyter-only command starting with % %timeit
timeit Python module for repeated timing timeit.repeat
Scientific notation Number × power of 10 1.05e7 = 10,500,000

Summary

  • Columns, not loops: vectorised code is hundreds of times faster than iterrows.
  • Measure memory with memory_usage(deep=True); use category for repeated text and downcast numbers, watching for silent overflow.
  • Too big for RAM? read_csv(chunksize=) and combine sums and counts (not medians); usecols and dtype= save memory at read time.
  • Parquet is columnar, compressed and typed: smaller files, faster reads, read only the columns you need. Arrow is the matching in-memory format.
  • eval/query, DuckDB (SQL in-process) and Polars are extra tools; profile with %timeit / timeit before optimising.
  • Next episode: charts with matplotlib.