Three tools, one question
You have a dataset, somewhere between a few megabytes and tens of gigabytes, stored as a CSV or a Parquet file. You need to load it, clean it up, and compute some aggregates. In the Python data world today there are at least three natural first choices for this: pandas, Polars and DuckDB. All three can do the job. The difference is in how they do it, how fast they do it, and how well the result fits into the rest of your workflow. This article builds intuition for how each one actually works, walks through the exact same task in all three, and ends with a practical guide for choosing between them.
A note on versions before we start: the code below assumes pandas 2.x, Polars 0.20 or later, and DuckDB 0.10 or later. All three libraries are under active development, and method names occasionally change between versions, most notably in Polars. Check what you have installed with a line such as import pandas as pd; print(pd.__version__), and expect small differences if you are on an older or newer release.
What each tool actually is
pandas
pandas is the original general-purpose DataFrame library for Python, built on top of NumPy. A pandas DataFrame is a table where each column is typically backed by a NumPy array, and every row carries a label from a row index, which pandas uses to align data when you combine two objects. pandas is eager: every line of code you write runs immediately and produces a concrete, fully materialized result you can inspect right away. Its biggest practical advantage is not raw speed but ecosystem: scikit-learn, statsmodels, matplotlib, seaborn and the vast majority of data science tutorials and Stack Overflow answers assume pandas DataFrames as the common currency.
Polars
Polars is a DataFrame library written in Rust with Python bindings. It stores data in Apache Arrow's columnar memory format and executes operations across multiple CPU cores automatically, without you having to configure anything. It offers two ways of working: an eager DataFrame API that behaves like pandas, running each step immediately, and a lazy LazyFrame API, where you build up a chain of operations as a query plan and only execute it when you explicitly call .collect(). In lazy mode, Polars looks at your entire chain of steps before running any of it, and can rewrite that plan for efficiency, for example by moving a filter earlier or skipping columns you never use.
DuckDB
DuckDB is an in-process analytical SQL database, sometimes described as "SQLite for analytics". It does not run as a separate server process; it is a library that lives inside your Python process. You write SQL, and DuckDB executes it with a column-store, vectorized engine built in C++. It can read CSV and Parquet files directly from disk, scanning only the columns and row groups it actually needs rather than loading the whole file into memory first. It can also query pandas and Polars DataFrames that already exist in your session by referring to the Python variable name directly inside a SQL statement, without any explicit export step.
The ideas that actually separate them
Eager versus lazy execution
In eager code, every method call does real work immediately and returns a complete result. If you chain five operations in pandas, or in eager Polars, you get five separate passes over the data with intermediate results created along the way, whether or not you end up needing all of that intermediate work. In lazy code, you describe a sequence of steps without running them, and the engine inspects the whole plan before doing anything. If you filter rows after a group-by when you could have filtered first, or if you select only two columns out of twenty, the optimizer can rearrange or trim the plan accordingly. Polars's LazyFrame and DuckDB's SQL engine both work this way: they build a query plan, optimize it, then execute it. pandas has no equivalent planner built into its core API; what you write runs exactly as written, step by step.
Columnar storage and vectorized, multi-threaded execution
All three tools store data column by column rather than row by row, which is efficient for analytical queries that typically touch a few columns across many rows. The real performance difference is not storage layout alone but how operations are carried out. Polars and DuckDB execute operations in a vectorized way, processing batches of values at a time through compiled Rust or C++ code, and they spread that work across all available CPU cores automatically for operations such as group-by, join and sort. pandas executes most of its operations through NumPy, which is itself vectorized, but it runs on a single core for most of the DataFrame API, and it falls back to slow, Python-level, element-by-element execution whenever you use .apply with a Python function, iterate over rows, or work with object-dtype columns such as mixed-type or inconsistently-typed strings.
Index versus no index
A pandas DataFrame carries a row index that is used for alignment: when you add two Series together, pandas lines them up by index label rather than by position, which is powerful but also a common source of subtle bugs when two objects you expected to match actually have different indexes. Polars has no index concept at all; rows are referenced by position within an operation, and there is no automatic label-based alignment. This removes a whole category of pandas-specific bugs but also means that pandas patterns relying on set_index and automatic alignment do not translate directly. DuckDB, being a relational engine, treats a table as a set of rows with no inherent order and no index; if you need a specific row order you must ask for it explicitly with ORDER BY.
A worked example: the same task in three tools
To make this concrete, here is a small orders table: eight rows, with a customer, a country, an amount and a quantity. The task is to compute, per country, the total amount and the number of orders, sorted from highest total to lowest. The same eight rows are used in every example below so you can check the numbers by hand.
import pandas as pd
data = {
"order_id": [1, 2, 3, 4, 5, 6, 7, 8],
"customer": ["Ana", "Ben", "Ana", "Cleo", "Ben", "Ana", "Cleo", "Ben"],
"country": ["PT", "DE", "PT", "ES", "DE", "PT", "ES", "DE"],
"amount": [120.50, 89.99, 45.00, 200.00, 15.75, 60.00, 99.99, 32.10],
"quantity": [2, 1, 1, 4, 1, 1, 2, 1],
}
orders = pd.DataFrame(data)
summary = (
orders.groupby("country", as_index=False)
.agg(total_amount=("amount", "sum"), orders_count=("order_id", "count"))
.sort_values("total_amount", ascending=False)
)
print(summary)
This is ordinary pandas: groupby creates groups by country, agg computes a sum and a count per group, and sort_values orders the result. Working the numbers by hand: PT has orders 120.50, 45.00 and 60.00, totalling 225.50 across 3 orders; DE has 89.99, 15.75 and 32.10, totalling 137.84 across 3 orders; ES has 200.00 and 99.99, totalling 299.99 across 2 orders. The printed table should therefore show ES first with 299.99, then PT with 225.50, then DE with 137.84.
import polars as pl
data = {
"order_id": [1, 2, 3, 4, 5, 6, 7, 8],
"customer": ["Ana", "Ben", "Ana", "Cleo", "Ben", "Ana", "Cleo", "Ben"],
"country": ["PT", "DE", "PT", "ES", "DE", "PT", "ES", "DE"],
"amount": [120.50, 89.99, 45.00, 200.00, 15.75, 60.00, 99.99, 32.10],
"quantity": [2, 1, 1, 4, 1, 1, 2, 1],
}
orders = pl.DataFrame(data)
summary = (
orders.group_by("country")
.agg(
pl.col("amount").sum().alias("total_amount"),
pl.col("order_id").count().alias("orders_count"),
)
.sort("total_amount", descending=True)
)
print(summary)
This is the eager Polars API, and the result is the same three rows and totals as above. Note the method is group_by with an underscore in current Polars versions; older releases used groupby without the underscore and now emit a deprecation warning for it. The expression style, pl.col("amount").sum().alias(...), is how you build computations in Polars: you refer to columns through pl.col and chain operations on them, rather than passing strings into an agg dictionary.
import polars as pl
data = {
"order_id": [1, 2, 3, 4, 5, 6, 7, 8],
"customer": ["Ana", "Ben", "Ana", "Cleo", "Ben", "Ana", "Cleo", "Ben"],
"country": ["PT", "DE", "PT", "ES", "DE", "PT", "ES", "DE"],
"amount": [120.50, 89.99, 45.00, 200.00, 15.75, 60.00, 99.99, 32.10],
"quantity": [2, 1, 1, 4, 1, 1, 2, 1],
}
orders_lazy = pl.LazyFrame(data)
query = (
orders_lazy
.filter(pl.col("amount") > 50)
.group_by("country")
.agg(pl.col("amount").sum().alias("total_amount"))
.sort("total_amount", descending=True)
)
print(query.explain())
result = query.collect()
print(result)
Here nothing runs until query.collect() is called. Calling query.explain() beforehand prints the optimized plan, which is worth reading once: it shows the filter being applied as early as possible, before the group-by, so rows that will not survive the filter are never touched by the aggregation. Filtering amount greater than 50 keeps orders 120.50, 89.99, 200.00, 60.00 and 99.99; grouped by country that gives PT at 180.50 (120.50 plus 60.00), DE at 89.99, and ES at 299.99 (200.00 plus 99.99), so the sorted result should list ES first, then PT, then DE.
import duckdb
import pandas as pd
data = {
"order_id": [1, 2, 3, 4, 5, 6, 7, 8],
"customer": ["Ana", "Ben", "Ana", "Cleo", "Ben", "Ana", "Cleo", "Ben"],
"country": ["PT", "DE", "PT", "ES", "DE", "PT", "ES", "DE"],
"amount": [120.50, 89.99, 45.00, 200.00, 15.75, 60.00, 99.99, 32.10],
"quantity": [2, 1, 1, 4, 1, 1, 2, 1],
}
orders = pd.DataFrame(data)
summary = duckdb.sql(
"""
SELECT country,
SUM(amount) AS total_amount,
COUNT(order_id) AS orders_count
FROM orders
GROUP BY country
ORDER BY total_amount DESC
"""
).df()
print(summary)
This is the part that surprises people coming from other SQL tools: orders is an ordinary pandas DataFrame already sitting in memory, and duckdb.sql can reference it by its Python variable name directly inside the FROM clause, scanning it without a separate import or export step. The .df() call at the end converts DuckDB's result back into a pandas DataFrame. The numbers match the pandas example exactly, as they should, since it is the same data and the same aggregation expressed in SQL instead of method calls.
Why Polars and DuckDB tend to be faster
Several things combine to make Polars and DuckDB faster than pandas on many workloads. Both are written in a compiled, low-level language, so operations run without the per-call overhead of the Python interpreter. Both use multiple CPU cores automatically for operations like group-by, join and sort, while pandas typically uses one core for most of its DataFrame API. Both support lazy evaluation of some form, pandas does not, which means they can eliminate work you never needed in the first place, such as reading columns you never select or computing a filter after a sort when it could run before it. DuckDB additionally can read Parquet files with column and row-group pruning, meaning it can skip entire chunks of a file on disk if it can prove from the file's metadata that they cannot match a filter, something a plain pandas.read_csv or pandas.read_parquet cannot do by itself.
It is worth being cautious about specific speedup numbers you might see quoted elsewhere. How much faster Polars or DuckDB is compared with pandas depends heavily on the size of the data, the data types involved, which operations you use, the hardware you run on, and how idiomatically the pandas code is written. A pandas script built from vectorized operations on numeric columns can be close in speed to Polars on small to medium data. A pandas script that loops over rows or calls .apply with a plain Python function can be far slower than either alternative, not because pandas itself is slow but because that pattern defeats vectorization entirely. If speed genuinely matters for your task, the only reliable approach is to benchmark your actual workload on your actual data and hardware rather than relying on a number from a blog post, including this one.
Where each tool fits best
pandas
- Data comfortably fits in memory, roughly up to a few hundred megabytes to a couple of gigabytes depending on your machine
- You need the surrounding ecosystem: scikit-learn pipelines, statsmodels, matplotlib or seaborn plotting, or you are following a tutorial or textbook that assumes pandas
- You are exploring data interactively in a notebook and value the enormous amount of existing documentation and community answers
- You are working in an existing codebase already built around pandas, where a rewrite is not worth the cost for the speed gained
Polars
- Data ranges from roughly a gigabyte up to tens of gigabytes on a single machine, and loading it fully with pandas is slow or memory-hungry
- You want an expressive, method-chaining Python API rather than SQL, but you want automatic multi-core execution and query optimization through the lazy API
- You are building a data pipeline where the same transformation logic is reused repeatedly and benefits from being defined once as a lazy query
- You are comfortable rewriting some pandas idioms, since Polars has no index and a different approach to missing values and joins
DuckDB
- You think naturally in SQL, or the task is fundamentally a join or aggregation across several tables
- You need to query CSV or Parquet files that are too large to comfortably load into memory, since DuckDB can scan files directly and prune what it does not need
- You want to run a quick SQL query against a DataFrame that already exists in your pandas or Polars session, without exporting it anywhere first
- You are embedding analytics inside an application and want a dependency-free, in-process database rather than a client-server setup
They are not rivals: combining them
In practice these three tools are often used together rather than as exclusive choices. Apache Arrow's columnar format acts as a common currency that lets data move between them with little or no copying. DuckDB can query pandas and Polars DataFrames directly, as shown above, and it can also return its results as a Polars DataFrame instead of a pandas one.
import duckdb
import polars as pl
data = {
"order_id": [1, 2, 3, 4, 5, 6, 7, 8],
"country": ["PT", "DE", "PT", "ES", "DE", "PT", "ES", "DE"],
"amount": [120.50, 89.99, 45.00, 200.00, 15.75, 60.00, 99.99, 32.10],
}
orders_pl = pl.DataFrame(data)
result_pl = duckdb.sql(
"SELECT country, SUM(amount) AS total_amount FROM orders_pl GROUP BY country ORDER BY total_amount DESC"
).pl()
print(result_pl)
Here DuckDB queries a Polars DataFrame directly by name and the .pl() method returns the result as a Polars DataFrame rather than a pandas one. This conversion method depends on your installed DuckDB version supporting it; if .pl() is not available, you can convert through Arrow instead by calling .arrow() on the DuckDB result and then pl.from_arrow() on that. A common and genuinely useful pattern is to let DuckDB do the heavy lifting of reading and filtering a large Parquet file on disk, hand the much smaller filtered result to Polars or pandas for the final shaping, and then plot it with matplotlib, letting each tool do the part it is best at.
Common mistakes
- Treating Polars as a drop-in replacement for pandas. There is no row index, no automatic label-based alignment, and no .loc or .iloc in the pandas sense, so code relying on set_index and alignment needs to be rewritten, not just translated method by method
- Writing row-by-row loops or using .apply with a plain Python function in pandas, which defeats vectorization and can make pandas far slower than it needs to be, often slower than either Polars or DuckDB on the same task for no good reason
- Building a Polars LazyFrame but calling .collect() too early in the chain, before the filters and column selections that would have let the optimizer do useful work, which throws away much of the benefit of using the lazy API in the first place
- Loading an entire CSV or Parquet file into memory with pandas when only a handful of columns or a filtered subset is actually needed, when DuckDB or a lazy Polars scan could read only what is required directly from disk
- Assuming missing values behave the same way everywhere. pandas typically represents a missing numeric value as NaN, which forces an integer column to be upcast to float64 the moment a missing value appears. Polars represents missingness as a proper null marker alongside the data, so an integer column can hold nulls while staying an integer type
- Comparing the speed of two tools with a single run on a dataset too small to matter, or without accounting for disk cache effects, and then treating the result as a general rule rather than a measurement specific to that run
import pandas as pd
import polars as pl
pandas_series = pd.Series([1, 2, None])
print("pandas dtype:", pandas_series.dtype)
polars_series = pl.Series([1, 2, None])
print("polars dtype:", polars_series.dtype)
Running this shows the difference directly: the pandas Series reports a float64 dtype because None forced an upcast from integers, while the Polars Series keeps an integer dtype and simply marks the third value as null. Neither behavior is wrong, but if you move code between the two libraries without knowing this, you can end up with unexpected type changes or comparisons that do not behave the way you expect.
A simple decision guide
- Small dataset, already using scikit-learn, statsmodels or heavy plotting, or following pandas-based material: stay with pandas
- Dataset from roughly a gigabyte to tens of gigabytes, comfortable with a Python method-chaining style, want automatic multi-core speed and query optimization: reach for Polars, and use the lazy API for anything beyond a one-off exploration
- Task is naturally a join, filter or aggregation across tables, or the file is larger than memory, or you just want to run one SQL query against data sitting somewhere on disk: reach for DuckDB
- Already deep inside a pandas-based pipeline that works and is not too slow: there is no obligation to switch; consider using DuckDB or Polars only for the specific steps that are genuinely a bottleneck, then convert back to pandas for the rest
- Working interactively across several CSV or Parquet files and want the fastest route to an answer: DuckDB's ability to query files directly with SQL is often the quickest to write and quick to run, with no loading step needed first
Summary and what's next
pandas, Polars and DuckDB solve the same basic problem, loading and transforming tabular data, with different designs. pandas is eager, NumPy-based, single-core by default, and backed by the largest ecosystem. Polars is eager or lazy, Rust-based, multi-core by default, and built around an Arrow-backed, index-free DataFrame. DuckDB is a SQL-first, in-process analytical database that can query files on disk and DataFrames already in memory without an import step, and benefits from the same kind of query optimization as Polars's lazy mode. None of the three is a universal winner, and they interoperate well enough through Arrow that using more than one in the same project is often the most practical approach. A reasonable next step is to take a dataset you already work with, run the same aggregation in all three as shown here, read the Polars query plan with .explain(), and see for yourself where the differences actually show up on your own machine and your own data.
Comments (0)
No comments yet. Be the first to share your thoughts.