DuckDB to Pandas: Seamless Data Transfer

From DuckDB Query to Pandas DataFrame

As you've already seen, getting the result of a DuckDB query into a pandas DataFrame is straightforward. When you execute a query, DuckDB returns a "relation" object. This object is a symbolic representation of your query, and it doesn't actually compute the full result until you ask for it. One of the most common ways to "materialize" this result is by calling the .df() method.

Let's formalize this. Suppose you have a connection con and you run a query:

import duckdb
import pandas as pd




# Create some sample data for demonstration
sample_df = pd.DataFrame({
    'category': ['A', 'B', 'A', 'B', 'A', 'C'],
    'value': [10, 15, 8, 22, 12, 5]
})

con = duckdb.connect()




# Execute a query against the pandas DataFrame (more on this shortly!)
result_relation = con.execute("SELECT category, SUM(value) AS total_value FROM sample_df GROUP BY category")




# Convert the result relation to a pandas DataFrame
summary_df = result_relation.df()

print(summary_df)

This will output:

  category  total_value
0        A           30
1        B           37
2        C            5

The .df() method is an efficient way to get your data into pandas for further analysis, plotting, or exporting to Excel. DuckDB is smart about this conversion, often using the Apache Arrow format under the hood for zero-copy transfers, which means it's very fast and memory-efficient.

DuckDB provides several methods for fetching data. The official documentation gives a concise overview.

While other formats like NumPy arrays (.fetchnumpy()) or Arrow tables (.arrow()) are available, for most of your FP&A workflows that currently use pandas, .df() will be your primary tool.

From Pandas DataFrame to DuckDB Query

This is where the integration becomes particularly powerful. DuckDB can execute SQL queries directly on pandas DataFrames that exist in your Python script's memory. You don't need to save the DataFrame to a CSV or a database table first.

DuckDB automatically discovers DataFrames in the local environment. You simply refer to the DataFrame's variable name in the FROM clause of your SQL query.

This feature is a game-changer. It means you can perform complex pandas manipulations and then immediately use the high-performance DuckDB SQL engine to aggregate, join, or filter the resulting DataFrame without any disk I/O.

The following video from the ArjanCodes channel provides an excellent and clear demonstration of this concept.

Why would you do this? Often, SQL is more expressive and significantly faster for set-based operations like aggregations and joins compared to the equivalent pandas code.

This image provides a compelling side-by-side comparison of a GROUP BY operation.

As you can see, the SQL query run by DuckDB is not only more readable for those comfortable with SQL, but it's also over 6 times faster. This performance advantage becomes even more significant with larger datasets.

The Bidirectional Workflow: A Practical Example

The true power of this integration lies in combining these two directions into a flexible workflow. You can seamlessly switch between pandas and DuckDB, using each for the tasks it excels at.

Let's imagine a typical FP&A task: you receive a raw sales report, need to apply some custom commission logic, and then generate a summary report.

1. Step 1: Initial processing with DuckDB. We start with a pandas DataFrame (perhaps read from a CSV or an API). We'll use DuckDB to perform initial filtering and simple transformations because it's fast.
2. Step 2: Complex logic in pandas. We'll convert the intermediate result to a pandas DataFrame to apply a complex, row-by-row commission calculation that's easier to write in Python.
3. Step 3: Final aggregation with DuckDB. We'll use DuckDB again to query our modified DataFrame to produce the final aggregated summary.

import pandas as pd
import duckdb




# --- Let's create our raw sales data ---
sales_data = {
    'Region': ['NA', 'EMEA', 'NA', 'APAC', 'EMEA', 'NA'],
    'SalespersonID': [101, 201, 102, 301, 201, 101],
    'ProductType': ['Software', 'Hardware', 'Software', 'Services', 'Software', 'Services'],
    'SaleAmount': [5000, 15000, 7500, 12000, 8000, 9500]
}
sales_df = pd.DataFrame(sales_data)




# Connect to an in-memory DuckDB database
con = duckdb.connect()




# STEP 1: Use DuckDB SQL for initial filtering.
# Let's filter for sales > $7,000 and create a 'BaseCommission' column.
intermediate_rel = con.execute("""
    SELECT 
        *,
        SaleAmount * 0.10 AS BaseCommission
    FROM sales_df
    WHERE SaleAmount > 7000
""")




# STEP 2: Convert to pandas for complex, programmatic logic.
intermediate_df = intermediate_rel.df()




# Define a function for a more complex commission rule.
# e.g., Software sales in North America get an extra 2% kicker.
def calculate_commission_kicker(row):
    if row['ProductType'] == 'Software' and row['Region'] == 'NA':
        return row['SaleAmount'] * 0.02
    return 0.0




# Apply this logic using pandas. This is hard to do in standard SQL.
intermediate_df['CommissionKicker'] = intermediate_df.apply(calculate_commission_kicker, axis=1)
intermediate_df['TotalCommission'] = intermediate_df['BaseCommission'] + intermediate_df['CommissionKicker']

print("--- DataFrame after pandas transformation ---")
print(intermediate_df)
print("\n")





# STEP 3: Use DuckDB SQL again for final aggregation on the MODIFIED DataFrame.
# The variable `intermediate_df` now holds the new columns.
final_summary_df = con.execute("""
    SELECT 
        Region,
        COUNT(SalespersonID) AS NumberOfSales,
        SUM(SaleAmount) AS TotalSales,
        SUM(TotalCommission) AS TotalCommissionsPaid
    FROM intermediate_df
    GROUP BY Region
    ORDER BY Region
""").df()

print("--- Final Summary Report ---")
print(final_summary_df)

This example perfectly illustrates the bidirectional workflow. We used SQL for the initial fast filtering, leveraged pandas' .apply method for custom row-wise logic, and then returned to SQL for the final high-performance aggregation. This pattern is incredibly effective for building sophisticated and performant financial models and reports.

The "DuckDB Python Integration" guide provides another great example of this workflow.

Conclusion

You have now learned how to create a powerful, symbiotic relationship between DuckDB and pandas. This ability to move data seamlessly between a high-speed SQL engine and a versatile data manipulation library is a crucial skill for automating your financial analysis tasks.

Key Takeaways:

• You can convert the result of any DuckDB query into a pandas DataFrame using the .df() method on the result relation.
• DuckDB can query in-memory pandas DataFrames directly by referencing the DataFrame's variable name in a SQL query. This is fast and avoids writing temporary files.
• The most effective workflows often combine both tools: use DuckDB's SQL for high-performance filtering, joins, and aggregations, and use pandas for complex, row-level programmatic logic that is difficult or inefficient to express in SQL.

In our next lesson, we will address persistence. Having mastered reading and transforming data, you'll learn how to save your results into a local DuckDB database file. This will allow you to store and efficiently re-query your processed data without having to rerun the entire consolidation and transformation pipeline every time.