Blog

DuckDB — SQL on Pandas DataFrames

Run SQL directly on pandas DataFrames with DuckDB

What is DuckDB?

DuckDB is an in-process analytical database: it runs inside your Python session, and the only installation step is pip install duckdb.

If you have used SQLAlchemy, you know the routine: create an engine, manage a connection, configure a dialect. DuckDB skips all of that: no server, no credentials, no connection string. You get a SQL engine inside your Python process, ready to query DataFrames that are already in memory.

Why use DuckDB alongside pandas?

If you already know Python and pandas, DuckDB adds a fast, SQL-based way to answer analytical questions:

Pain point	pandas	DuckDB
Large files (1 GB+)	Loads the whole file into memory by default	Can query CSV and Parquet files directly, streaming them in chunks (and, for Parquet, reading only the columns it needs)
Multi-step aggregations	Several chained steps (`groupby`, `agg`, `reset_index`, `sort_values`)	One SQL statement
Multi-table joins	`merge` calls	Standard SQL `JOIN`
Rankings and running totals	`rank`, `cumsum` and `transform`	Window functions such as `RANK() OVER (...)`

Bottom line: DuckDB doesn't replace pandas; it complements it. You keep your DataFrames and your Jupyter workflow, and use SQL for the analytical steps where it reads more clearly.

When to use which?

Task	Recommended tool
Data cleaning, missing values, string operations	pandas
Multi-metric GROUP BY	DuckDB
Window functions and ranking	DuckDB
Multi-step logic (CTEs)	DuckDB
Querying large CSV or Parquet files directly	DuckDB
In-place column updates	pandas
Plotting and visualisation	pandas → matplotlib / seaborn

How does DuckDB work?

When you write (this runs once the data is loaded, below):

Python

duckdb.sql("SELECT * FROM df WHERE Total_Revenue > 500").df()

DuckDB finds the variable df in your Python session and scans the DataFrame directly, without copying it into a database. There is no separate loading step and no data written to disk. .df() returns the result as a pandas DataFrame. (duckdb.query(), used in many older examples, does the same thing.)

The dataset

We use the Online Sales Dataset – Popular Marketplace Data from Kaggle (shreyanshverma27), released into the public domain under CC0. It holds 240 transactions in six product categories across three regions, with these columns: Transaction ID, Date, Product Category, Product Name, Units Sold, Unit Price, Total Revenue, Region and Payment Method.

Download: https://www.kaggle.com/datasets/shreyanshverma27/online-sales-dataset-popular-marketplace-data (a free Kaggle login is needed).

Set-up and imports

Python

# pip install duckdb pandas
import pandas as pd
import duckdb

Load and explore the data

Python

df = pd.read_csv("Online Sales Data.csv")
df.columns = df.columns.str.strip().str.replace(" ", "_")
df.shape          # (240, 9)
df.head(3)

Output

   Transaction_ID        Date Product_Category      Product_Name  Units_Sold  Unit_Price  Total_Revenue         Region Payment_Method
0           10001  2024-01-01      Electronics     iPhone 14 Pro           2      999.99        1999.98  North America    Credit Card
1           10002  2024-01-02  Home Appliances  Dyson V11 Vacuum           1      499.99         499.99         Europe         PayPal
2           10003  2024-01-03         Clothing  Levi's 501 Jeans           3       69.99         209.97           Asia     Debit Card

Each row is one transaction, and Total_Revenue = Units_Sold × Unit_Price. Revenue questions must use this column: summing Unit_Price would ignore how many units were sold.

Query 1: revenue by region and category (GROUP BY)

Question: Which region and product category combination drives the most revenue and volume?

In pandas this takes several chained steps: groupby, then agg with a mapping of columns to functions, then reset_index, then sort_values. DuckDB expresses the same logic in one SQL statement:

Python

q1 = duckdb.sql("""
    SELECT Region, Product_Category,
           COUNT(*)                      AS total_orders,
           CAST(SUM(Units_Sold) AS INTEGER) AS total_units_sold,
           ROUND(SUM(Total_Revenue), 2)  AS total_revenue,
           ROUND(AVG(Total_Revenue), 2)  AS avg_order_value
    FROM df
    GROUP BY Region, Product_Category
    ORDER BY total_revenue DESC
""").df()
q1

Output

          Region Product_Category  total_orders  total_units_sold  total_revenue  avg_order_value
0  North America      Electronics            40                66       34982.41           874.56
1         Europe  Home Appliances            40                59       18646.16           466.15
2           Asia           Sports            40                88       14326.52           358.16
3           Asia         Clothing            40               145        8128.93           203.22
4         Europe  Beauty Products            40                46        2621.90            65.55
5  North America            Books            40               114        1861.93            46.55

Electronics in North America drives the most revenue, even though books in the same region sell far more units: 114 units against 66.

A quick pandas cross-check confirms the totals:

Python

df.groupby(["Region", "Product_Category"])["Total_Revenue"].sum().round(2).sort_values(ascending=False)

Query 2: top 3 products per region (WINDOW + QUALIFY)

Question: What are the three best-selling products by revenue in each region?

Python

q2 = duckdb.sql("""
    SELECT Region, Product_Name, Product_Category,
           ROUND(SUM(Total_Revenue), 2) AS total_revenue,
           CAST(SUM(Units_Sold) AS INTEGER) AS total_units,
           RANK() OVER (PARTITION BY Region ORDER BY SUM(Total_Revenue) DESC) AS revenue_rank
    FROM df
    GROUP BY Region, Product_Name, Product_Category
    QUALIFY revenue_rank <= 3
    ORDER BY Region, revenue_rank
""").df()
q2

Output

          Region                                  Product_Name Product_Category  total_revenue  total_units  revenue_rank
0           Asia                                  Peloton Bike           Sports        1895.00            1             1
1           Asia                           Garmin Fenix 6X Pro           Sports         999.99            1             2
2           Asia  Bowflex SelectTech 1090 Adjustable Dumbbells           Sports         699.99            1             3
3         Europe                                    LG OLED TV  Home Appliances        2599.98            2             1
4         Europe                                    Roomba i7+  Home Appliances        1599.98            2             2
5         Europe                          Blueair Classic 480i  Home Appliances        1199.98            2             3
6  North America                           Canon EOS R5 Camera      Electronics        3899.99            1             1
7  North America                           MacBook Pro 16-inch      Electronics        2499.99            1             2
8  North America                     Apple MacBook Pro 16-inch      Electronics        2399.00            1             3

QUALIFY filters on the result of a window function, much as HAVING filters on an aggregate. It saves you wrapping the query in a subquery.

Data-quality note: "MacBook Pro 16-inch" and "Apple MacBook Pro 16-inch" appear as separate products. They are probably the same item recorded under two names. In a real analysis you would standardise product names before ranking. Merged, the MacBook Pro would total 4,898.99 and rank first in North America, so the cleaning changes the answer.

Query 3: payment method segmentation (CASE WHEN)

Question: How do revenue and order volume break down by payment method, and which value tier does each method fall into?

Python

q3 = duckdb.sql("""
    SELECT Payment_Method,
           COUNT(Transaction_ID)        AS total_orders,
           ROUND(SUM(Total_Revenue), 2) AS total_revenue,
           ROUND(AVG(Total_Revenue), 2) AS avg_order_value,
           ROUND(SUM(Total_Revenue) * 100.0 / SUM(SUM(Total_Revenue)) OVER (), 1) AS revenue_share_pct,
           CASE WHEN AVG(Total_Revenue) >= 400 THEN 'High AOV'
                WHEN AVG(Total_Revenue) >= 250 THEN 'Mid AOV'
                ELSE 'Low AOV' END AS aov_tier
    FROM df
    GROUP BY Payment_Method
    ORDER BY total_revenue DESC
""").df()
q3

Output

  Payment_Method  total_orders  total_revenue  avg_order_value  revenue_share_pct  aov_tier
0    Credit Card           120       51170.86           426.42               63.5  High AOV
1         PayPal            80       21268.06           265.85               26.4   Mid AOV
2     Debit Card            40        8128.93           203.22               10.1   Low AOV

Two techniques combine here. SUM(SUM(Total_Revenue)) OVER () is a window over the grouped result, giving each method's share of total revenue. CASE WHEN assigns each method a tier; the thresholds of 400 and 250 (the dataset does not state a currency) are illustrative, so set your own business rules.

Read the results with care. This dataset is highly structured: each region sells exactly two categories, every debit-card payment is for clothing in Asia, and every PayPal payment is in Europe. The tiers for debit cards and PayPal therefore reflect what was bought and where, not how people pay. Check for this kind of confounding before drawing business conclusions.

Querying the file directly

Everything above ran on a DataFrame we had already loaded with pandas. For large files, DuckDB can skip that step and query the file on disk:

Python

duckdb.sql("""
    SELECT Region, ROUND(SUM("Total Revenue"), 2) AS total_revenue
    FROM read_csv('Online Sales Data.csv')
    GROUP BY Region
    ORDER BY total_revenue DESC
""").df()

Output

          Region  total_revenue
0  North America       36844.34
1           Asia       22455.45
2         Europe       21268.06

read_csv detects the column types automatically, and column names that contain spaces need double quotes in SQL. This is where DuckDB's advantage on large datasets comes from: it streams the file in chunks rather than loading it all into memory first. With columnar formats such as Parquet it goes further, reading only the columns the query needs.

Conclusion

Across three queries on a public marketplace dataset, we used DuckDB for multi-dimensional aggregation, window-based ranking with QUALIFY, revenue shares and CASE WHEN classification. We ran them directly on a pandas DataFrame and directly on a CSV file, with no database to set up.

#	Query	SQL feature	Business question answered
1	Revenue by region and category	GROUP BY	Which market and category drive the most revenue?
2	Top 3 products per region	WINDOW + QUALIFY	Best-sellers by region
3	Payment method segmentation	CASE WHEN + window share	Order-value tiers and payment channel contribution

Two habits matter more than any SQL feature. Make sure each metric uses the right column (revenue, not unit price), and check what the data's structure allows you to conclude.

All code in this article was tested with pandas 3.0.5 and DuckDB 1.5.6 on the Kaggle Online Sales Dataset (240 rows).

Written in conjunction with Sankhya Analytics.