pandas, polars or DuckDB: a measured guide

For the original million order lines, choose by where the data lives and what happens to it next. pandas is the default for data that fits comfortably in memory and for work that ends in charts, statistics or models, because most Python data libraries accept its DataFrames. polars is a faster DataFrame engine for pipelines of many steps: it can parallelise supported operators, plans lazy queries and can stream supported larger-than-memory workloads subject to operator and state limits. DuckDB is an SQL database that runs inside your Python process: it queries CSV and Parquet files directly, joins and aggregates data bigger than memory, and suits anyone who thinks in SQL. A data hand-off contractdata hand-off contract: Requirements for values, keys, types, missingness, metadata and result ownership when data moves between libraries or representations. states what must survive conversion. All three exchange data through Apache Arrow, so one workflow can use each where it is strongest. Measured on the same 1,000,000 order lines, the fastest engine changed with where the data lived, so treat any single ranking with suspicion.

Engine defaults include indexing and missing-value rules

The table hides two differences that change code, not just speed. pandas aligns rows by their index, so adding two Series matches labels; polars and DuckDB have no index and work by position or by explicit joins. pandas treats NaN as missing, while polars and DuckDB keep NaN as an ordinary floating-point value and use null for missing data, so a file holding both can give each engine a different answer. The Polars guide describes pandas as largely single-threaded and polars as built for many cores on one machine, and DuckDB uses as many threads as the machine has cores unless told otherwise.

Each engine also has a case where it is the wrong first choice. pandas is the wrong start for a file several times larger than memory, because it loads whole tables before it computes anything. polars is a poor fit when every step ends in a library that accepts only pandas, since each hand-over costs a conversion. DuckDB is awkward for logic written row by row or around an index, and a loop of small queries over a DataFrame pays the cost of taking the DataFrame in on every call.

Distinguish index alignment, explicit keys and missing-value conversion before comparing engine outputs.

Measure where the same data is read from

# Writes the seeded order lines this lesson's examples use (never downloaded): lines.parquet and lines.csv with
# line_id, order_date, country (40 values), product (4,000 codes), quantity and price, and products.parquet with
# each product's category.
import numpy as np
import pyarrow as pa
import pyarrow.csv as pacsv
import pyarrow.parquet as pq

CATEGORIES = ["grocery", "home", "toys", "books", "garden", "beauty", "sport", "kitchen", "office", "pets", "music", "travel"]


def make_lines(rows=1_000_000, seed=3, csv=False):
    rng = np.random.default_rng(seed)
    products = np.array([f"P{i:04d}" for i in range(4000)])
    lines = pa.table(
        {
            "line_id": np.arange(1, rows + 1),
            "order_date": pa.array((np.datetime64("2025-04-01") + rng.integers(0, 365, rows).astype("timedelta64[D]")).astype("datetime64[us]")),
            "country": pa.array(np.array([f"Country {i:02d}" for i in range(40)])[rng.integers(0, 40, rows)]),
            "product": pa.array(products[rng.integers(0, 4000, rows)]),
            "quantity": rng.integers(1, 20, rows),
            "price": rng.integers(50, 5000, rows) / 100,
        }
    )
    pq.write_table(lines, "lines.parquet")
    if csv:
        pacsv.write_csv(lines, "lines.csv")
    pq.write_table(pa.table({"product": products, "category": np.array(CATEGORIES)[rng.integers(0, 12, 4000)]}), "products.parquet")

# Revenue by country from 1,000,000 order lines, five ways: each engine on its own in-memory table, each reading
# the Parquet file, and DuckDB querying the pandas DataFrame. One warm-up run, then the median of 5 runs.
import math
import os
import statistics
import time

import duckdb
import pandas as pd
import polars as pl

from make_lines import make_lines

make_lines()
pandas_df = pd.read_parquet("lines.parquet")
polars_df = pl.read_parquet("lines.parquet")
con = duckdb.connect()
con.execute("SET enable_progress_bar = false")
SQL = "SELECT country, sum(quantity * price) AS revenue FROM {} GROUP BY country"

ways = {
    "pandas, its own DataFrame": lambda: pandas_df.assign(revenue=pandas_df["quantity"] * pandas_df["price"])
    .groupby("country")["revenue"].sum(),
    "polars, its own DataFrame": lambda: polars_df.group_by("country").agg((pl.col("quantity") * pl.col("price")).sum().alias("revenue")),
    "polars, reading Parquet": lambda: pl.scan_parquet("lines.parquet").group_by("country")
    .agg((pl.col("quantity") * pl.col("price")).sum().alias("revenue")).collect(),
    "DuckDB, reading Parquet": lambda: con.execute(SQL.format("'lines.parquet'")).df(),
    "DuckDB, the pandas DataFrame": lambda: con.execute(SQL.format("pandas_df")).df(),
}


def median_ms(func):
    func()  # warm-up, not timed
    times = []
    for _ in range(5):
        start = time.perf_counter()
        func()
        times.append(time.perf_counter() - start)
    return statistics.median(times) * 1000


def as_dict(result):
    if isinstance(result, pd.Series):
        return result.to_dict()
    if isinstance(result, pl.DataFrame):
        return dict(zip(result["country"].to_list(), result["revenue"].to_list()))
    return dict(zip(result["country"], result["revenue"]))


def same(a, b):  # the same countries, and revenues equal up to rounding in the last digits
    return a.keys() == b.keys() and all(math.isclose(a[k], b[k], rel_tol=1e-9) for k in a)


print(f"pandas {pd.__version__}, polars {pl.__version__}, DuckDB {duckdb.__version__}, {os.cpu_count()} CPU cores, {len(pandas_df):,} lines")
answers = []
for label, func in ways.items():
    print(f"{label:<30}{median_ms(func):>8.1f} ms")
    answers.append(as_dict(func()))
print("all five agree on revenue per country:", all(same(a, answers[0]) for a in answers))

pandas 3.0.6, polars 1.44.2, DuckDB 1.5.6, 18 CPU cores, 1,000,000 lines
pandas, its own DataFrame         27.5 ms
polars, its own DataFrame         12.6 ms
polars, reading Parquet           26.8 ms
DuckDB, reading Parquet           16.9 ms
DuckDB, the pandas DataFrame      85.3 ms
all five agree on revenue per country: True

All five ways return the same revenue for every country, and the slowest took about seven times as long as the quickest. polars on its own DataFrame was the quickest, using every core for the group-by. pandas on its own DataFrame took about twice as long, still only tens of milliseconds. Reading straight from the Parquet file, DuckDB was quicker than polars. The slowest way was DuckDB querying the pandas DataFrame, the very data pandas had summed in a third of the time: before DuckDB can group the rows, it has to take in the frame’s columns, and pandas 3’s str columns are expensive to take in. The query was the same each time; what changed was where the data lived.

Keep all five original million-row paths and compare their boundary work before using a speed ranking.

A decision record joins workload, evidence and consumers

An engine choice includes the path into and out of the calculation. The application may need an Arrow table, a plotting frame or a scalar count; those are different timed consumers. State the required keys, types and missingness before choosing a hand-off path, then measure that same path on the relevant data. A convenient API can justify a choice even when a microbenchmark ranks its arithmetic differently.

Four ideas carry the rest of the lesson:

• Apache Arrow is a columnar memory format that pandas, polars and DuckDB all read, so data can move between them without being rebuilt value by value.
• Replacement scans let DuckDB query a pandas or polars DataFrame, or an Arrow table, by the name of its Python variable.
• A benchmark harness runs the same tasks in each engine, discards a warm-up run and reports the median of several runs, next to the versions and the hardware.
• A team default is the engine new work starts in, with a short written list of the cases that justify another.

Follow the retained diagram from input location to the result consumer and its decision evidence.

Six everyday tasks measured, and how to read any benchmark Pro: opens with a Pro, Premium or Ultimate pass

One workflow, three engines: what crosses Arrow intact and what changes Pro: opens with a Pro, Premium or Ultimate pass

A team default, decided: eight scenarios and a decision record Pro: opens with a Pro, Premium or Ultimate pass

Practise the preserved performance contracts

Interview questions

Key takeaways

• pandas for data that fits and work that ends in other Python libraries, polars for fast pipelines on one machine, DuckDB for files, SQL and data bigger than memory.
• The fastest engine depends on where the data lives: the same group-by was quickest in polars on its own frame and slowest when DuckDB had to take in a pandas DataFrame.
• pandas treats NaN as missing; polars and DuckDB keep NaN as a value and use null for missing data.
• Arrow is the common format between the three.