DuckDB with Pandas: Replace Slow DataFrames

Q: Do I need to install anything beyond DuckDB?

No. DuckDB automatically detects Pandas DataFrames in scope. Just import duckdb and import pandas.

Q: Can DuckDB modify Pandas DataFrames in place?

No. DuckDB returns new DataFrames. The original DataFrame is not modified.

Q: What's faster: DuckDB or Polars?

Both are dramatically faster than Pandas. DuckDB typically wins on complex joins and aggregations; Polars wins on simple operations with its lazy evaluation. For SQL familiarity, DuckDB wins every time.

Q: Can I use DuckDB in a Jupyter notebook with Pandas?

Yes. DuckDB's Python API works in any Python environment including Jupyter.

Q: How does DuckDB handle Pandas NaN values?

DuckDB maps Pandas NaN to SQL NULL. Use IS NULL / IS NOT NULL for null checks. Ready to try DenchClaw? Install in one command: npx denchclaw. Full setup guide →

DuckDB is 10-20x faster than Pandas for analytical workloads. Here's how to use DuckDB alongside Pandas to speed up your data analysis without rewriting everything.

Mark Rachapoom

March 26, 2026·6 min read

If your Pandas code is slow, DuckDB can fix it without rewriting your entire pipeline. DuckDB queries Pandas DataFrames directly, returns results as DataFrames, and runs 10-20x faster for most analytical operations.

Here's how to drop DuckDB into your existing Pandas workflow.

The Problem with Pandas at Scale#

Pandas is designed for tabular data manipulation, but it has structural performance limits:

Row-oriented storage — Pandas stores data in NumPy arrays row by row. Aggregations that touch entire columns are inefficient.
Single-threaded — By default, most Pandas operations run on one CPU core.
Memory inefficiency — Pandas loads entire DataFrames into memory; there's no lazy evaluation.
GroupBy overhead — df.groupby().agg() chains have significant Python overhead.

For small datasets (<100MB), this doesn't matter. For datasets in the hundreds of MB or GB range, it becomes the bottleneck.

Drop-In DuckDB Replacement#

The key insight: DuckDB can query a Pandas DataFrame directly. Your variable name becomes the table name:

import pandas as pd
import duckdb
 
# Load data with Pandas (your existing code)
df = pd.read_csv('events.csv')
 
# Query with DuckDB — references 'df' directly
result = duckdb.sql("""
    SELECT 
        user_id,
        COUNT(*) AS events,
        SUM(revenue) AS lifetime_value
    FROM df
    WHERE event_type = 'purchase'
    GROUP BY user_id
    ORDER BY lifetime_value DESC
""").df()  # Returns Pandas DataFrame
 
print(result.head(10))

You didn't change how data is loaded. You replaced the slow Pandas groupby with a DuckDB query. The input and output are both DataFrames.

Benchmark: Pandas vs DuckDB#

import pandas as pd
import duckdb
import time
 
# Generate sample data
df = pd.DataFrame({
    'user_id': np.random.choice(['user_' + str(i) for i in range(100000)], 10_000_000),
    'product': np.random.choice(['A', 'B', 'C', 'D', 'E'], 10_000_000),
    'revenue': np.random.uniform(10, 1000, 10_000_000),
    'region': np.random.choice(['US', 'EU', 'APAC'], 10_000_000)
})
 
# Pandas
start = time.time()
result_pd = df.groupby(['product', 'region'])['revenue'].agg(['sum', 'mean', 'count']).reset_index()
print(f"Pandas: {time.time() - start:.2f}s")
 
# DuckDB
start = time.time()
result_duck = duckdb.sql("""
    SELECT product, region, SUM(revenue), AVG(revenue), COUNT(*)
    FROM df
    GROUP BY product, region
""").df()
print(f"DuckDB: {time.time() - start:.2f}s")

Typical results:

Pandas: 7-12 seconds
DuckDB: 0.3-0.8 seconds

Common Pandas Patterns Rewritten with DuckDB#

Filtering#

# Pandas
filtered = df[df['status'] == 'active'][df['revenue'] > 100]
 
# DuckDB
filtered = duckdb.sql("SELECT * FROM df WHERE status = 'active' AND revenue > 100").df()

GroupBy Aggregation#

# Pandas
result = df.groupby('segment').agg({
    'revenue': ['sum', 'mean'],
    'user_id': 'nunique'
}).reset_index()
 
# DuckDB
result = duckdb.sql("""
    SELECT 
        segment,
        SUM(revenue) AS total_revenue,
        AVG(revenue) AS avg_revenue,
        COUNT(DISTINCT user_id) AS unique_users
    FROM df
    GROUP BY segment
""").df()

Merge / Join#

# Pandas
result = pd.merge(orders, customers, left_on='customer_id', right_on='id', how='left')
 
# DuckDB
result = duckdb.sql("""
    SELECT o.*, c.name, c.email, c.segment
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.id
""").df()

Window Functions#

# Pandas (verbose)
df['rank'] = df.groupby('segment')['revenue'].rank(ascending=False)
df['running_total'] = df.sort_values('date').groupby('user_id')['revenue'].cumsum()
 
# DuckDB (clean SQL)
result = duckdb.sql("""
    SELECT 
        *,
        RANK() OVER (PARTITION BY segment ORDER BY revenue DESC) AS rank,
        SUM(revenue) OVER (PARTITION BY user_id ORDER BY date) AS running_total
    FROM df
""").df()

Pivot Tables#

# Pandas
pivot = df.pivot_table(values='revenue', index='month', columns='segment', aggfunc='sum')
 
# DuckDB
pivot = duckdb.sql("""
    PIVOT (
        SELECT 
            DATE_TRUNC('month', occurred_at) AS month,
            segment,
            SUM(revenue) AS revenue
        FROM df
    )
    ON segment
    USING SUM(revenue)
    GROUP BY month
""").df()

String Operations#

# Pandas
result = df[df['name'].str.contains('Corp', case=False)]
 
# DuckDB
result = duckdb.sql("SELECT * FROM df WHERE name ILIKE '%Corp%'").df()

Using a Persistent Connection#

For multiple queries on the same data, a persistent connection is more efficient:

import duckdb
 
# Create connection and register DataFrame
con = duckdb.connect()
con.register('events', events_df)
con.register('users', users_df)
 
# Multiple queries against registered tables
monthly_revenue = con.execute("""
    SELECT DATE_TRUNC('month', occurred_at) AS month, SUM(revenue) AS revenue
    FROM events
    GROUP BY month ORDER BY month
""").df()
 
top_users = con.execute("""
    SELECT u.name, SUM(e.revenue) AS ltv
    FROM events e JOIN users u ON e.user_id = u.id
    GROUP BY u.name ORDER BY ltv DESC LIMIT 10
""").df()

Handling Large DataFrames#

For DataFrames that are too large for Pandas to manipulate, use DuckDB to pre-aggregate before loading:

# Don't load 10GB CSV into Pandas
# df = pd.read_csv('huge_file.csv')  # ❌ OOM
 
# Load aggregated result instead
df = duckdb.sql("""
    SELECT 
        segment,
        month,
        SUM(revenue) AS revenue
    FROM read_csv_auto('huge_file.csv')
    GROUP BY segment, month
""").df()
 
# Now df is small (just the aggregates) and fast to work with

DuckDB Reads Parquet Too#

# Pandas reads Parquet into memory first
df = pd.read_parquet('events.parquet')  # Loads entire file
 
# DuckDB reads only the columns and rows it needs
result = duckdb.sql("""
    SELECT user_id, SUM(revenue)
    FROM read_parquet('events.parquet')
    WHERE occurred_at > '2026-01-01'
    GROUP BY user_id
""").df()  # Only reads 'user_id', 'revenue', 'occurred_at' columns

For wide Parquet files (many columns), this can reduce I/O by 90%+.

DenchClaw Data Analysis#

DenchClaw stores all CRM data in DuckDB. You can analyze it directly with the Python DuckDB client:

import duckdb
import pandas as pd
import matplotlib.pyplot as plt
 
# Connect to DenchClaw workspace (read-only)
con = duckdb.connect(
    '/Users/you/.openclaw-dench/workspace/workspace.duckdb', 
    read_only=True
)
 
# Pull deal data
deals = con.execute("""
    SELECT 
        "Stage",
        "Deal Value",
        "Company",
        "Created At"
    FROM v_deals
    WHERE "Status" = 'Active'
""").df()
 
# Analyze with Pandas
pipeline_by_stage = deals.groupby('Stage')['Deal Value'].agg(['sum', 'count'])
pipeline_by_stage.plot(kind='bar', subplots=True, figsize=(10, 6))
plt.suptitle('Pipeline by Stage')
plt.tight_layout()
plt.savefig('pipeline_analysis.png')