Scaling Data

Jon Reades - j.reades@ucl.ac.uk

1st October 2025

The Challenge

As the data get bigger
Pandas gets slower.

I don’t consider myself to be someone who works with ‘big data’ any more, but the data sets still range into the tens of millions and there are points where pandas just doesn’t work. It gets slower or even breaks down entirely. So now I’m going to talk about some technologies that have changed my life. Sad as that sounds, it has reduced the time need to load and query large data sets from minutes to milliseconds. And it has solved many of the errors that I just listed above!

The Options

1. Make pandas faster.
2. Make the storage better.
3. Make using more cores easier.

Within each of these options there are several types of solutions:

1. To make pandas faster we can look both at how it runs code and how it manages data internally.
2. We could also store data more intelligently so that pandas can access it more efficiently.
3. Or we could move away from pandas entirely as a tool for managing and analysing data.

There are pros and cons to each of these.

Make Pandas Faster: Code

Introducing:
lazy evaluation and filtergraphs.

This is the approach taken by Polars and Dask. So while something like strong data typing (which all of the tools I’m talking about today do) is an ‘easy’ win because it allows you to plan make a better plan for handling the data, there are bigger gains to be made from ‘lazy evaluation’.

Make Pandas Faster: Storage

Introducing:
Arrow and Parquet.

• Arrow is an in-memory columnar format for data. Data is stored in a structured way in RAM making it blazingly fast for operations.
• Parquet is a highly-compressed columnar file format for data. Data is stored in a structured way on your hard drive.
• Feather is a raw storage format for Arrow.

TL;DR: for most applications Parquet will give nice, small files on disk and the benefits of columnar file storage; for computationally intensive applications where disk space and interoperability with other systems isn’t an issue then Feather might work.

Make Pandas Faster: Scaling

Introducing:
Parallelisation.

We’ve already talked about this a bit, but let’s be explicit: pandas is basically bound to one processor, and if we can find ways to lift that restriction then things are going to run a lot faster. If you build in lazy evaluation, query planning, and better storage, then removing this restriction is a lot easier.

The Tools

Introducing:
Polars, Dask, DuckDB, and Postgres.

What About Polars?

Pros:

• Looks like pandas code¹, doesn’t run like it.
• Multi-threaded execution.
• Arrow behind the scenes.
• Streaming data processing possible.

Cons:

• Easy to miss out on the benefits.
• Designed for single machines².

1. This can be a ‘gotcha’, see migration guide

2. Paid for cloud services gets around this, sort of.

What About the Duck?

Pros:

• Serverless SQL queries against Parquet files.
• Queries can be returned as Pandas data frames.
• Select and filter before loading using Arrow.
• Plugins for geospatial and remote parquet files.

Cons:

• Need to learn SQL.
• Still constrained by available memory.
• Really still optimised for a single machine.

What About Dask?

Pros:

• Distributed and parallel data processing
• Queries returned as Pandas data frames
• Lazy evaluation of code
• Built-in scaling

Cons:

• Lots of overhead for small queries.
• Moving a lot of data around.
• Not all problems readily parallelisable.

Postgres?

Pros:

• Fully-fledged database.
• Industry-leading handling of geospatial data.

Cons:

• Need to learn SQL.
• Need to learn how to design databases to maximise gains.
• Moving lots of data in/out of the database is slow.

A Simple Comparison

import pandas as pd
import polars as pl
import duckdb as duck
import time
import seaborn as sns
sns.load_dataset('titanic').to_parquet('titanic.pq')

	survived	pclass	sex	age	sibsp	parch	fare	embarked	class	who	adult_male	deck	embark_town	alive	alone
0	0	3	male	22.0	1	0	7.2500	S	Third	man	True	NaN	Southampton	no	False
1	1	1	female	38.0	1	0	71.2833	C	First	woman	False	C	Cherbourg	yes	False
2	1	3	female	26.0	0	0	7.9250	S	Third	woman	False	NaN	Southampton	yes	True
3	1	1	female	35.0	1	0	53.1000	S	First	woman	False	C	Southampton	yes	False
4	0	3	male	35.0	0	0	8.0500	S	Third	man	True	NaN	Southampton	no	True

Measuring Performance

We can compare performance using profiling¹:

import timeit

def load_pandas():
    pd.read_parquet('titanic.pq', columns=['age']).age.mean()

def load_polars():
    pl.read_parquet('titanic.pq', columns=['age'])['age'].mean()

def load_duck():
    duck.sql("SELECT MEAN(age) FROM read_parquet('titanic.pq')")

num_reps = 1000

1. See especially the timeit module discussed here

The Results

Pandas

pd_execution_time = timeit.timeit(load_pandas, number=num_reps)
print(f"Pandas execution time: {pd_execution_time:.6f} seconds")

Pandas execution time: 0.784750 seconds

Polars

pl_execution_time = timeit.timeit(load_polars, number=num_reps)
print(f"Polars execution time: {pl_execution_time:.6f} seconds")

Polars execution time: 0.161258 seconds

79.45% faster than pandas.

DuckDB

db_execution_time = timeit.timeit(load_duck, number=num_reps)
print(f"DuckDB execution time: {db_execution_time:.6f} seconds")

DuckDB execution time: 0.100967 seconds

87.13% faster than pandas and 37.39% faster than polars.

Summarising Data

Useful, But Limited?

Method	Achieves
count()	Total number of items
first(), last()	First and last item
mean(), median()	Mean and median
min(), max()	Minimum and maximum
std(), var()	Standard deviation and variance
mad()	Mean absolute deviation
prod()	Product of all items
sum()	Sum of all items

Here are a bunch of pandas functions that have to do with aggregating data in some way. Some of these you’ll have seen before, some you may not. However, up to this point if you wanted to to know the median price of each type of Airbnb listing, or the sum of each type of vehicle sold, you’d have had to select out one listing type or vehicle type, call median or sum, and then remember the result. Let’s change that.

Grouping Operations

In Pandas these follow a split / apply / combine approach:

Note that, for simplicity, I’ve abbreviate the Local Authority names since this is just a simplified example: TH (Tower Hamlets), HAK (Hackney), W (Westminster).

In Practice

grouped_df = df.groupby(<fields>).<function>

For instance, if we had a Local Authority (LA) field:

grouped_df = df.groupby('LA').sum()

Using apply the function could be anything:

def norm_by_data(x): # x is a column from the grouped df
    x['d1'] /= x['d2'].sum() 
    return x

df.groupby('LA').apply(norm_by_data)

Grouping by Arbitrary Mappings

mapping = {'HAK':'Inner', 'TH':'Outer', 'W':'Inner'}
df.set_index('LA', inplace=True)
df.groupby(mapping).sum()

Pivot Tables

A ‘special case’ of Group By features:

• Commonly-used in business to summarise data for reporting.
• Grouping (summarisation) happens along both axes (Group By operates only on one).
• pandas.cut(<series>, <bins>) can be a useful feature here since it chops a continuous feature into bins suitable for grouping.

In Practice

age = pd.cut(titanic['age'], [0, 18, 80])
titanic.pivot_table('survived', ['sex', age], 'class')

Counts

Pivots & Groups

Extracting

Thank You

References

• Life Altering Postgres Patterns
• pandas 2.0 and the Arrow revolution (Part 1)
• What parquet files are my preferred API for bulk open data
• DuckDB Documentation
• Migrating from Pandas to Polars
• Read remote Parquet files with DuckDB