Python Excel AutomationPractical guides for developers
Comparison
Getting Started With Python Excel AutomationDeep dive

pandas vs DuckDB for Excel Data

When to query Excel data with DuckDB's SQL instead of pandas: reading .xlsx files with read_xlsx, speed on a large workbook, SQL against DataFrames already in memory, joins and window functions, the types DuckDB infers from Excel, a glob that silently reads one file, and writing results back to Excel.
Choosing a librarypandas

DuckDB is an in-process SQL database: pip install duckdb, no server, and it can query files and pandas DataFrames directly. Recent versions read and write .xlsx files through an extension that loads automatically. For people who know SQL, or whose Excel workflows are really database queries in disguise — filter, join, group, rank — it is an attractive alternative to pandas. This page compares the two on the same Excel data: reading, types, aggregation, joins, multiple files and writing back. It is part of Choosing a Python Excel Library.

Prerequisites

Bash
pip install pandas openpyxl python-calamine duckdb xlsxwriter

The test data is an order export of 100,000 rows in orders.xlsx (order ID, date, region, SKU, quantity, price), a small products.xlsx mapping SKUs to categories, and three monthly files in monthly/. The DuckDB version used here is 1.5; the Excel extension is installed on first use, which needs network access once.

Two ways to work with the same sheet Two panels. pandas reads the sheet into a DataFrame and works with Python methods; it has every Excel engine and the widest ecosystem. DuckDB reads the sheet as a table and works with SQL; it is fast at reading and aggregating and can also query DataFrames in memory. pandas DataFrame methods openpyxl / calamine engines reads .xls, .xlsb, .ods too the ecosystem default DuckDB SQL read_xlsx / COPY TO .xlsx only fast scans and joins

Reading the workbook

Both read the sheet into a table. Time them on the 100,000-row file; pandas with its default openpyxl engine and with the Rust-based calamine engine, DuckDB with read_xlsx:

Python
import time
import duckdb
import pandas as pd

def timed(label, fn):
    t = time.perf_counter()
    out = fn()
    print(f"{label:22} {time.perf_counter() - t:5.2f} s")
    return out

a = timed("pandas + openpyxl", lambda: pd.read_excel("orders.xlsx"))
b = timed("pandas + calamine", lambda: pd.read_excel("orders.xlsx", engine="calamine"))
con = duckdb.connect()
c = timed("duckdb read_xlsx", lambda: con.sql("SELECT * FROM read_xlsx('orders.xlsx')").df())

On this run openpyxl took about 2.5 seconds, calamine about 0.5 and DuckDB about 0.2; absolute times depend on the machine, the ratios hold up. openpyxl is the slowest because it builds a Python object for every cell; calamine and DuckDB parse in compiled code and are several times faster. If read speed is the only problem, engine="calamine" is the smallest change: the rest of the pandas code stays the same. Benchmark Python Excel Read and Write Speed has more measurements.

Types: what each one infers

Speed is not the only difference. Compare the column types:

Python
print(pd.DataFrame({"pandas": a.dtypes.astype(str), "duckdb": c.dtypes.astype(str)}))
print(con.sql("DESCRIBE SELECT * FROM read_xlsx('orders.xlsx')").df()[["column_name", "column_type"]].to_string(index=False))

Excel stores every number as a floating-point value; pandas notices that a column holds only whole numbers and returns int64, while DuckDB keeps DOUBLE. For quantities and IDs, cast in the query, and for columns with mixed contents, all_varchar=true reads everything as text so you can convert it deliberately:

Python
typed = con.sql("""
    SELECT CAST(OrderID AS INTEGER) AS OrderID, Date, Region, SKU,
           CAST(Qty AS INTEGER) AS Qty, Price
    FROM read_xlsx('orders.xlsx')
""").df()
print(typed.dtypes.astype(str).to_dict())

Reading part of a sheet

Report exports often have a title block above the table or notes beside it. pandas skips them with skiprows, usecols and nrows; read_xlsx takes a sheet name and an A1-style range:

Python
part_pd = pd.read_excel("orders.xlsx", sheet_name="Sheet1", usecols="A:C", nrows=5)
part_dd = con.sql("SELECT * FROM read_xlsx('orders.xlsx', sheet = 'Sheet1', range = 'A1:C6')").df()
print(part_pd.shape, part_dd.shape)
print(part_dd.to_string(index=False))

The range includes the header row, so five data rows need A1:C6. Both tools then treat the first row of the range as the header, and DuckDB again returns the order IDs as 1.0, 2.0, the DOUBLE type from the previous section.

Aggregating

A revenue summary by region and month. In pandas it is a method chain; in DuckDB, a GROUP BY:

Python
orders = b
p = (orders.assign(Revenue=orders["Qty"] * orders["Price"], Month=orders["Date"].dt.strftime("%Y-%m"))
           .groupby(["Region", "Month"], as_index=False)["Revenue"].sum()
           .sort_values(["Region", "Month"]))

d = con.sql("""
    SELECT Region, strftime(Date, '%Y-%m') AS Month, sum(Qty * Price) AS Revenue
    FROM orders
    GROUP BY ALL
    ORDER BY Region, Month
""").df()
print(p.head(3).round(2).to_string(index=False))
print("same result:", (p["Revenue"].round(2).to_numpy() == d["Revenue"].round(2).to_numpy()).all())

Both produce the same numbers, to the cent; the choice between them is about how the code reads. The DuckDB query says FROM orders: that is the pandas DataFrame named orders in the current Python scope. DuckDB reads DataFrames in place, without copying them into a database first, so SQL and pandas can be mixed freely in one script: read with pandas, query with SQL, continue in pandas.

Joins and window functions

This is where SQL tends to read more clearly. Revenue per category, and within each region the three best-selling SKUs, joining the product list from a second workbook:

Python
top = con.sql("""
    WITH sales AS (
        SELECT o.Region, o.SKU, p.Category, sum(o.Qty * o.Price) AS Revenue
        FROM orders o
        JOIN read_xlsx('products.xlsx') p USING (SKU)
        GROUP BY ALL
    )
    SELECT * FROM sales
    QUALIFY row_number() OVER (PARTITION BY Region ORDER BY Revenue DESC) <= 3
    ORDER BY Region, Revenue DESC
""").df()
print(top.head(6).round(2).to_string(index=False))

The pandas equivalent is merge, groupby().sum(), sort_values and groupby().head(3): fine, but four steps whose order matters. Get the Top N Rows per Group in pandas shows it.

The same operations in each tool A reference table. Reading a sheet is read_excel in pandas and read_xlsx in DuckDB. Group and sum is groupby and sum versus GROUP BY. A lookup join is merge versus JOIN. Top N per group is sort and groupby head versus QUALIFY row_number. Writing is to_excel versus COPY TO with FORMAT xlsx. Task pandas DuckDB Read a sheet read_excel read_xlsx Group and sum groupby().sum() GROUP BY Lookup join merge JOIN … USING Top N per group sort + groupby().head() QUALIFY row_number() Write .xlsx to_excel COPY … (FORMAT xlsx)

Several files: a trap

With CSV and Parquet, DuckDB reads a whole folder with a glob such as 'monthly/*.csv'. With read_xlsx in the version tested here, a glob does not fail — it quietly reads a single file:

Python
import glob

files = sorted(glob.glob("monthly/*.xlsx"))
per_file = {f: con.sql(f"SELECT count(*) FROM read_xlsx('{f}')").fetchone()[0] for f in files}
print(per_file, "total", sum(per_file.values()))
print("glob:", con.sql("SELECT count(*) FROM read_xlsx('monthly/*.xlsx')").fetchone()[0])

The three files hold 33,395 rows between them; the glob returned 11,414, exactly the row count of the March file, with no warning. A monthly report built on that query would quietly show one month as the whole quarter. Build the union explicitly instead, adding the file name so every row keeps its source:

Python
union = " UNION ALL ".join(f"SELECT *, '{f}' AS SourceFile FROM read_xlsx('{f}')" for f in files)
combined = con.sql(union).df()
print(len(combined), combined["SourceFile"].nunique())

In pandas the same is pd.concat over a loop of read_excel calls, as in Combine Multiple Excel Files into One in Python. Whichever tool you use, check the row count against the sum of the files.

Writing back to Excel

DuckDB writes .xlsx with COPY ... TO. It produces a plain sheet: no formats, widths or charts. For a formatted report, hand the result to pandas and xlsxwriter:

Python
con.sql("COPY (SELECT * FROM top) TO 'top_skus_duckdb.xlsx' (FORMAT xlsx, HEADER true)")
print(pd.read_excel("top_skus_duckdb.xlsx").head(2).to_string(index=False))

with pd.ExcelWriter("top_skus_formatted.xlsx", engine="xlsxwriter") as xw:
    top.to_excel(xw, sheet_name="Top SKUs", index=False)
    xw.sheets["Top SKUs"].set_column("D:D", 12, xw.book.add_format({"num_format": "#,##0.00"}))
What the glob returned A table of the three monthly files with their row counts, 11,553 for January, 10,428 for February and 11,414 for March, totalling 33,395. The glob query returned 11,414 rows, the March file only; the explicit union returned all 33,395. Source Rows January file 11,553 February file 10,428 March file 11,414 Sum of files 33,395 Glob query 11,414 UNION ALL 33,395

Which to use

  • Use pandas when the workbook is one of many inputs to a Python pipeline, when you need .xls, .xlsb or .ods, when the data needs row-by-row cleaning or custom Python logic, or when the team does not write SQL. Switch the engine to calamine if reading is slow.
  • Use DuckDB when the work is naturally SQL — joins across several sheets, window functions, large group-bys — when the same query also has to run on CSV or Parquet files, or when the analyst already thinks in SQL.
  • Use both, which is common: read and clean with pandas, run the heavy joins and aggregations in DuckDB against the DataFrames, and write the formatted report with pandas and xlsxwriter.

Common pitfalls

  • DOUBLE where you expect integers. DuckDB keeps Excel's numeric type; cast IDs and counts.
  • Globs with read_xlsx. In the version tested, a glob reads a single file silently; union the files yourself and check row counts.
  • Header detection. read_xlsx guesses whether the first row is a header; pass header=true or false when the sheet is ambiguous.
  • Blank rows. Reading stops or yields nulls at empty rows depending on stop_at_empty; check the row count.
  • Formatting on output. COPY TO writes values only; format with xlsxwriter or openpyxl.

Conclusion

DuckDB reads .xlsx quickly with read_xlsx, queries pandas DataFrames in place, and expresses joins, rankings and group-bys concisely in SQL; pandas has every Excel engine and format, integer inference, and the rest of the Python ecosystem, and with the calamine engine reads nearly as fast. Cast DuckDB's DOUBLE columns where needed, never trust a glob with read_xlsx without checking counts, and use pandas and xlsxwriter for formatted output. Most real workflows benefit from using both.

Frequently asked questions

Can DuckDB read Excel files? Yes. Since version 1.2 the excel extension provides read_xlsx, and COPY TO with FORMAT xlsx writes files. It supports .xlsx only, not .xls or .xlsb.

Is DuckDB faster than pandas for Excel files? Reading with DuckDB is much faster than pandas with openpyxl, and similar to pandas with the calamine engine. For large joins and aggregations after reading, DuckDB is usually faster.

Can I run SQL on a pandas DataFrame? Yes. duckdb.sql or a connection's sql method can refer to a DataFrame variable by name in the FROM clause, and .df() returns the result as a DataFrame.

Why does DuckDB read my integer column as DOUBLE? Excel stores all numbers as floating point, and read_xlsx keeps that type. Cast the column in the query, for example CAST(Qty AS INTEGER).

More on choosing a library

More on choosing a library →