The Pain: Three Bottlenecks of Pandas

If you process millions of rows of data daily with Pandas, you’ve experienced these pain points:

1. Memory explosions — pd.read_csv() loads the entire file into memory. A 16GB machine processing 200M rows? OOM crash.
2. Slow performance — .groupby().agg() takes minutes on millions of rows. .apply(axis=1) is a performance killer.
3. Multi-source data integration hell — Merging CSV + MySQL + API data requires repetitive read/write cycles with 50+ lines of glue code.

DuckDB’s solution: Zero-copy queries (no full data loading), vectorized execution engine (10-50x faster than Pandas), and native cross-source JOINs (MySQL + Parquet + CSV in one query).

Why DuckDB is Faster Than Pandas

The core reason is simple: columnar storage + vectorized execution.

Pandas processes row by row — every operation traverses all columns. DuckDB processes column by column, reading only what you need. Plus, DuckDB’s C++ vectorized execution makes memory layouts naturally suitable for analytical queries.

The gap grows larger with more data:

• Columnar storage: Only reads needed columns, skips irrelevant content
• Vectorized parsing: C++ implementation is orders of magnitude faster than Python’s row-by-row loops
• Zero-copy: Parsed results store directly in columnar format, no intermediate conversion

Five Core Scenarios: Pandas to DuckDB Comparison

Scenario 1: Reading CSV with Auto Type Inference

Pandas approach:

import pandas as pd
df = pd.read_csv('orders.csv')
df['amount'] = df['amount'].astype(float)
df['date'] = pd.to_datetime(df['date'])

DuckDB approach:

import duckdb
df = duckdb.sql("SELECT * FROM read_csv_auto('orders.csv')").df()

One line. read_csv_auto() automatically infers every column’s type — no manual type conversion needed.

Scenario 2: Group Aggregation (Replaces groupby)

Pandas approach:

result = (df.groupby('category')
 .agg(total_amount=('amount', 'sum'),
 avg_amount=('amount', 'mean'),
 order_count=('order_id', 'count'))
 .reset_index()
 .sort_values('total_amount', ascending=False))

DuckDB approach:

result = duckdb.sql("""
 SELECT category,
 SUM(amount) AS total_amount,
 ROUND(AVG(amount), 2) AS avg_amount,
 COUNT(*) AS order_count
 FROM read_csv_auto('orders.csv')
 GROUP BY category
 ORDER BY total_amount DESC
""").df()

Same logic, DuckDB uses just 7 lines of SQL. And the DuckDB version is faster and more memory-efficient.

Scenario 3: Multi-Table Joins (Replaces merge)

Pandas approach:

orders = pd.read_csv('orders.csv')
customers = pd.read_csv('customers.csv')
result = orders.merge(customers, on='customer_id', how='left')

DuckDB approach:

result = duckdb.sql("""
 SELECT o.*, c.name, c.tier
 FROM read_csv_auto('orders.csv') o
 LEFT JOIN read_csv_auto('customers.csv') c
 ON o.customer_id = c.id
""").df()

Key insight: DuckDB can read multiple CSV files directly in SQL for JOINs — no need to load files into memory first. For large files, this means streaming reads with minimal memory footprint.

Scenario 4: Conditional Computation (Replaces apply)

Pandas approach:

def classify_order(row):
 if row['amount'] > 1000:
 return 'high'
 elif row['amount'] > 100:
 return 'medium'
 else:
 return 'low'

df['level'] = df.apply(classify_order, axis=1)

DuckDB approach:

result = duckdb.sql("""
 SELECT *,
 CASE WHEN amount > 1000 THEN 'high'
 WHEN amount > 100 THEN 'medium'
 ELSE 'low' END AS level
 FROM read_csv_auto('orders.csv')
""").df()

.apply(axis=1) is Pandas’ performance killer — it本质上 loops in Python. DuckDB’s CASE WHEN executes at the C++ layer, orders of magnitude faster.

Scenario 5: Deduplication and Unique Values

Pandas approach:

unique_customers = df['customer_id'].unique()
clean_df = df.drop_duplicates(subset=['order_id'])

DuckDB approach:

unique_customers = duckdb.sql("""
 SELECT DISTINCT customer_id
 FROM read_csv_auto('orders.csv')
""").df()

clean_df = duckdb.sql("""
 SELECT DISTINCT ON (order_id) *
 FROM read_csv_auto('orders.csv')
""").df()

DISTINCT and DISTINCT ON are native DuckDB syntax with highly optimized execution plans.

Complete Example: Rewriting a Pandas Data Pipeline

Here’s a typical task:

1. Read sales.csv and products.csv
2. Join to get product names for each order
3. Aggregate sales by category and month
4. Find the top 10 combinations

Pandas version (~15 lines):

import pandas as pd
sales = pd.read_csv('sales.csv')
products = pd.read_csv('products.csv')
merged = sales.merge(products, left_on='product_id', right_on='id')
merged['month'] = pd.to_datetime(merged['sale_date']).dt.to_period('M')
result = (merged.groupby(['category', 'month'])
 .agg(total_sales=('amount', 'sum'),
 avg_order=('amount', 'mean'),
 order_count=('amount', 'count'))
 .reset_index()
 .sort_values('total_sales', ascending=False)
 .head(10))

DuckDB version (just 1 SQL statement):

import duckdb
result = duckdb.sql("""
 SELECT
 p.category,
 strftime(s.sale_date, '%Y-%m') AS month,
 COUNT(*) AS order_count,
 ROUND(SUM(s.amount), 2) AS total_sales,
 ROUND(AVG(s.amount), 2) AS avg_order_value
 FROM read_csv_auto('sales.csv') s
 JOIN read_csv_auto('products.csv') p
 ON s.product_id = p.id
 GROUP BY p.category, strftime(s.sale_date, '%Y-%m')
 ORDER BY total_sales DESC
 LIMIT 10
""").df()
print(result)

No .groupby(), no .merge(), no .apply(). Everything expressed in SQL. And whether the data is 1 million rows or 10 billion rows, the code doesn’t change.

Three Migration Tips: From Pandas to DuckDB

Tip 1: Write SQL First, Then Convert to Python

Write your Pandas logic in SQL first — the logic is clearer and performance is better. Then use .df() to convert results back to DataFrames for any downstream processing.

# Write complete logic in DuckDB first
sql = """
 SELECT ... FROM ... WHERE ... GROUP BY ...
"""
# Then convert to Pandas for visualization
df = duckdb.sql(sql).df()
df.plot()

Tip 2: Use duckdb.sql() Instead of pd.read_csv()

Anywhere you previously used pd.read_csv(), switch to duckdb.sql("SELECT * FROM read_csv_auto('file.csv')"). Everything else stays the same.

Tip 3: Fall Back to Pandas Only When Needed

DuckDB covers 90% of data processing scenarios (reading, filtering, aggregation, joining, window functions). Only fall back to Pandas when you truly need its advanced features (time series resampling, complex plotting, custom algorithms).

# DuckDB handles data preparation
df = duckdb.sql("""
 SELECT * FROM read_csv_auto('large_data.csv')
 WHERE date >= '2026-01-01'
 GROUP BY category
 HAVING SUM(amount) > 10000
""").df()

# Pandas handles what it's good at (visualization)
df.groupby('category').plot(kind='bar', y='amount')

Pandas → DuckDB Quick Reference Table

Performance Benchmark: Real-World Comparison

We tested with an e-commerce analytics scenario: 10 million order records (orders.csv, ~4.2GB).

Memory usage comparison:

• Pandas full pipeline: Peak 12.8GB
• DuckDB full pipeline: Peak 2.1GB

Summary

DuckDB isn’t about completely replacing Pandas — it’s about providing a better choice for core data analysis scenarios (reading, filtering, aggregation, joining). Remember:

1. read_csv_auto() replaces pd.read_csv() in one line
2. GROUP BY + aggregates replace .groupby().agg()
3. JOIN replaces .merge(), supporting cross-source queries
4. CASE WHEN replaces .apply(axis=1), avoiding Python loops
5. DISTINCT replaces .drop_duplicates()

Pandas is a Python library. DuckDB is a database engine. When you use Pandas for data analysis, you’re essentially simulating a database in memory. DuckDB was born for analysis — it doesn’t need to simulate, it simply is your analysis engine.

Next time you get data, ask yourself: Can I do this in one line of SQL?

💡 More DuckDB tutorials → duckdblab.org

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 [email protected] 反馈。