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
stris 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.pyscript. - In a script: the
timeitmodule.timeit.repeat(f, number=10, repeat=3)runsf10 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
- Why is
iterrowsslow? It creates a Series per row and does Python-level work per value; vectorised operations run in compiled code over whole arrays. - How do you find a DataFrame's real memory use?
df.memory_usage(deep=True).sum()ordf.info(memory_usage="deep"). - When does
categoryhelp, and when does it hurt? Helps for low-cardinality text (few unique values); hurts for near-unique columns. - What is downcasting and its risk? Using smaller numeric types (int8, float32); risk of silent overflow and lost precision.
- 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. - Can you compute a median with chunks? Not by combining chunk medians; you need all values (or an approximate algorithm / a database engine).
- Parquet vs CSV? Parquet: binary, columnar, compressed, typed, can read selected columns; CSV: plain text, row-based, untyped, universal.
- 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.
- What is DuckDB good for? In-process SQL analytics over DataFrames and Parquet/CSV files, including bigger-than-RAM data, with no server.
- 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); usecategoryfor repeated text and downcast numbers, watching for silent overflow. - Too big for RAM?
read_csv(chunksize=)and combine sums and counts (not medians);usecolsanddtype=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/timeitbefore optimising.- Next episode: charts with matplotlib.