How to Query Pandas DataFrames with DuckDB

Image by Editor | Midjourney

Pandas is one of the most popular Python libraries for working with structured data. But if you’re comfortable with using SQL for data analysis, you can use DuckDB to query pandas dataframes.

In this article, you’ll learn how to:

Query a pandas dataframe using DuckDB
Perform aggregations and filtering efficiently
Run joins and window functions on dataframes

Let’s get started!

🔗 Link to the code on GitHub

Setting Up DuckDB and Getting Started

Install DuckDB and pandas if you haven’t already:

You can use your Python development environment to follow along. Here’s the Google Colab notebook if you prefer.

Now, let’s create a sample dataset—a customer transactions dataframe:

import pandas as pd
import duckdb

# Create a sample DataFrame (mimicking real-world shopping data)
data = {
	"order_id": [101, 102, 103, 104, 105, 106, 107, 108],
	"customer_name": ["Ethan", "Harper", "Lucas", "Mia", "James", "Amelia", "Henry", "Charlotte"],
	"product": ["Smartphone", "Smartwatch", "Blender", "TV", "Microwave", "Laptop", "Camera", "Headphones"],
	"category": ["Electronics", "Electronics", "Home", "Electronics", "Home", "Electronics", "Electronics", "Accessories"],
	"price": [899, 250, 120, 1500, 200, 1300, 700, 180],
	"quantity": [2, 4, 3, 2, 2, 1, 3, 4],
	"order_date": pd.to_datetime(["2024-03-05 11:10", "2024-03-06 13:50", "2024-03-06 07:30",
                               	"2024-03-07 19:15", "2024-03-08 10:45", "2024-03-09 14:25",
                               	"2024-03-10 09:40", "2024-03-11 16:10"]),
	"payment_method": ["Credit Card", "PayPal", "Debit Card", "Credit Card", "PayPal", "Debit Card", "Credit Card", "PayPal"]
}

df = pd.DataFrame(data)

# Display first few rows
df.head()

This dataframe contains customer orders, with details like product, category, price, quantity, order date, and payment method.

Querying a Pandas DataFrame with DuckDB

DuckDB allows you to query a Pandas DataFrame directly using SQL, treating it as if it were a database table.

Select All Data from the DataFrame

To query a DataFrame with DuckDB, use:

duckdb.query("SELECT * FROM df").df()

This returns the entire dataset in SQL table format.

Performing Sales and Revenue Analysis

Now that we can query our dataframe with SQL, let’s analyze our sales data.

Total Revenue Calculation

To calculate total revenue, we multiply price * quantity and sum it:

duckdb.query("""
    SELECT SUM(price * quantity) AS total_revenue
    FROM df
""").df()

This tells us the total sales revenue from all orders.

total_revenue
0 10678.0

Find the Most Popular Products

Which products are selling the most? Here’s the query you can run to find out:

duckdb.query("""
    SELECT product, SUM(quantity) AS total_quantity
    FROM df
    GROUP BY product
    ORDER BY total_quantity DESC
    LIMIT 5
""").df()

This ranks products by total quantity sold.

 	product 	total_quantity
0 	Smartwatch 	4.0
1 	Headphones 	4.0
2 	Blender 	3.0
3 	Camera 	        3.0
4 	Smartphone 	2.0

Analyzing Customer Behavior

Identify the Top Spending Customers

To see which customers are spending the most, we sum their purchase values:

duckdb.query("""
    SELECT customer_name, SUM(price * quantity) AS total_spent
    FROM df
    GROUP BY customer_name
    ORDER BY total_spent DESC
    LIMIT 5
""").df()

Output:

 customer_name 	total_spent
0 	Mia 	3000.0
1 	Henry 	2100.0
2 	Ethan 	1798.0
3 	Amelia 	1300.0
4 	Harper 	1000.0

Analyze Payment Methods

Which payment method is the most commonly used? Let’s find out:

duckdb.query("""
    SELECT payment_method, COUNT(*) AS order_count
    FROM df
    GROUP BY payment_method
    ORDER BY order_count DESC
""").df()

This provides insights into customer payment preferences.

 	payment_method 	order_count
0 	PayPal 	        3
1 	Credit Card 	3
2 	Debit Card 	2

Going Further

Find Peak Shopping Hours

To see when most purchases occur, extract the hour from order_date and count orders:

duckdb.query("""
    SELECT strftime('%H', order_date) AS order_hour, COUNT(*) AS order_count
    FROM df
    GROUP BY order_hour
    ORDER BY order_hour
""").df()

This shows which hours of the day are busiest for orders.

 order_hour 	order_count
0 	07 	1
1 	09 	1
2 	10 	1
3 	11 	1
4 	13 	1
5 	14 	1
6 	16 	1
7 	19 	1

Running Total

To track revenue accumulation over time, we use a window function:

duckdb.query("""
    SELECT order_date, SUM(price * quantity)
           OVER (ORDER BY order_date) AS running_revenue
    FROM df
""").df()

This helps understand revenue growth over time.

 	order_date 	running_revenue
0 	2024-03-05 11:10:00 	1798.0
1 	2024-03-06 07:30:00 	2158.0
2 	2024-03-06 13:50:00 	3158.0
3 	2024-03-07 19:15:00 	6158.0
4 	2024-03-08 10:45:00 	6558.0
5 	2024-03-09 14:25:00 	7858.0
6 	2024-03-10 09:40:00 	9958.0
7 	2024-03-11 16:10:00 	10678.0

Wrapping Up

In this tutorial, we explored how to query pandas dataframes using DuckDB, covering:

Sales analytics like total revenue, top products, customer spending
Time-based analysis such as peak hours, revenue growth

With DuckDB’s lightweight, high-performance query engine, you can analyze large pandas datasets using SQL efficiently. As a next step, try using DuckDB for your next Python data analysis project.