Join and Pivot Excel Data with Polars

Polars is a fast DataFrame library with a strict, expression-based API, and it reads Excel files through the Rust-based fastexcel engine. Once the data is loaded, the two jobs that dominate report scripts — joining tables and reshaping them into a pivot — look a little different from pandas: there is no index, joins name their keys explicitly and can validate them, and pivot takes on, index and values arguments. This page builds a small sales report from a workbook with three sheets: it joins the order lines to products and regions, checks for codes that do not match, aggregates, pivots quarters into columns, and writes the result back to Excel with formatting. It belongs to Reading Excel with Polars and Arrow.

Prerequisites

pip install "polars[excel]" xlsxwriter

The excel extra installs fastexcel for reading; XlsxWriter is used by write_excel. The examples use Polars 1.x. sales_book.xlsx has an Orders sheet (order lines with store and product codes), a Products sheet with prices and a Stores sheet with regions. One order references store S9, which is not in the store list.

Step 1 — Read the sheets

import polars as pl

orders = pl.read_excel("sales_book.xlsx", sheet_name="Orders")
products = pl.read_excel("sales_book.xlsx", sheet_name="Products")
stores = pl.read_excel("sales_book.xlsx", sheet_name="Stores")
print(orders.schema)
print(orders.head(3))

Polars infers a schema per column — OrderDate arrives as a date (the cells hold midnight times), Qty as an integer, codes as strings — and, unlike pandas, does not invent an index. Reading every sheet at once with sheet_id=0 returns a dictionary of frames, the counterpart of pandas' sheet_name=None; reading is covered in Read an Excel File with Polars read_excel.

Step 2 — Join with key validation

lines = (orders
         .join(products, on="SKU", how="left", validate="m:1")
         .join(stores, on="StoreID", how="left", validate="m:1")
         .with_columns(Revenue=pl.col("Qty") * pl.col("Price")))
print(lines.select("OrderDate", "StoreID", "Region", "Product", "Qty", "Revenue"))

how="left" keeps every order line, and validate="m:1" asserts that each SKU and each store ID appears only once in the lookup tables; if a duplicate product row crept into the Products sheet, Polars would raise an error instead of silently duplicating order lines. The S9 order comes through with an empty Region. with_columns adds the revenue as an expression — Polars' way of saying "compute this column from those", evaluated efficiently across the whole frame.

Step 3 — Find unmatched keys with an anti join

A left join hides unmatched rows as nulls; an anti join lists them directly:

unknown_stores = orders.join(stores, on="StoreID", how="anti")
unknown_products = orders.join(products, on="SKU", how="anti")
print("orders with unknown stores:\n", unknown_stores)
print("orders with unknown products:", unknown_products.height)

how="anti" returns the rows of the left frame whose key has no match on the right — here the single S9 order. Checking anti joins before reporting is cheap and catches master-data gaps that would otherwise show up as a mysterious "null" region in a pivot. The pandas technique is shown in Find Rows in One Excel File Missing from Another.

Step 4 — Group and aggregate

by_region = (lines
             .filter(pl.col("Region").is_not_null())
             .group_by("Region")
             .agg(Orders=pl.len(), Units=pl.col("Qty").sum(), Revenue=pl.col("Revenue").sum())
             .sort("Revenue", descending=True))
print(by_region)

group_by takes the key, agg takes named expressions, and nothing happens implicitly: unmatched rows are filtered out explicitly rather than forming a null group. Polars does not sort groups by default — and does not even keep them in first-seen order unless you pass maintain_order=True — so sort states the order the report wants. The North region, with six order lines worth 892.50, comes first; South follows with three lines and 454.00. pl.len() counts rows per group and returns an unsigned integer, which writes to Excel like any other number.

Step 5 — Pivot quarters into columns

quarterly = (lines
             .filter(pl.col("Region").is_not_null())
             .with_columns(Quarter=pl.lit("Q") + pl.col("OrderDate").dt.quarter().cast(pl.String))
             .pivot(on="Quarter", index="Region", values="Revenue", aggregate_function="sum")
             .fill_null(0)
             .select("Region", "Q1", "Q2", "Q3"))
print(quarterly)

pivot turns the distinct values of on into columns, one row per index value, aggregating values with aggregate_function. Column order follows the order in which values first appear, so the final select fixes Q1–Q3 in sequence, and fill_null(0) turns region/quarter combinations without sales into zeros. pandas users will recognise this as pivot_table(index="Region", columns="Quarter", values="Revenue", aggfunc="sum", fill_value=0).

Step 6 — The same pipeline, lazily

For larger inputs, describe the whole pipeline first and let Polars optimise it. read_excel itself is eager, but the frames can be turned into lazy frames:

plan = (orders.lazy()
        .join(products.lazy(), on="SKU", how="left")
        .join(stores.lazy(), on="StoreID", how="left")
        .filter(pl.col("Region").is_not_null())
        .group_by("Region", "Product")
        .agg(Revenue=(pl.col("Qty") * pl.col("Price")).sum())
        .sort("Region", "Revenue", descending=[False, True]))
print(plan.explain()[:300])
result = plan.collect()
print(result)

explain() shows the optimised plan, and it is instructive here: Polars noticed that a left join followed by a filter on Region being not null is the same as an inner join, rewrote it as one, and carries only the four columns the aggregation needs through the joins. collect() runs it. For a few thousand rows the gain is negligible; for millions it is substantial, and the code stays the same. If the source data is large and refreshed often, converting it to Parquet and starting with pl.scan_parquet makes the whole pipeline lazy, as in Convert Excel Files to Parquet with Python.

Step 7 — Unpivot back to long form

Wide tables suit reading; long tables suit charts and further joins. unpivot reverses the pivot:

long = quarterly.unpivot(index="Region", variable_name="Quarter", value_name="Revenue").sort("Region", "Quarter")
print(long)

Each region/quarter pair becomes a row again, ready for a chart that plots one line per region, or for joining to a targets table keyed on region and quarter. In pandas the same step is melt(id_vars="Region", var_name="Quarter", value_name="Revenue"), covered in Unpivot a Wide Excel Sheet with pandas melt.

Step 8 — Write a formatted workbook

import xlsxwriter

with xlsxwriter.Workbook("regional_report.xlsx") as wb:
    by_region.write_excel(wb, worksheet="By region", autofit=True,
                          column_formats={"Revenue": "#,##0.00"}, table_style="Table Style Light 9")
    quarterly.write_excel(wb, worksheet="By quarter", autofit=True,
                          column_formats={"Q1": "#,##0", "Q2": "#,##0", "Q3": "#,##0"})
    unknown_stores.write_excel(wb, worksheet="Unknown stores", autofit=True)
print("sheets:", pl.read_excel("regional_report.xlsx", sheet_id=0).keys())

write_excel writes each frame as an Excel table with a style, number formats per column and fitted widths; passing an open XlsxWriter workbook puts several frames into one file. Including the unknown-store sheet keeps the data-quality issue visible to the people who can fix it. More options are in Write a Polars DataFrame to Excel with Formatting.

Performance notes

Reading is usually the slowest step, and fastexcel is among the fastest Excel readers available to Python; like pandas' calamine engine, it is built on the Rust calamine crate. After that, Polars' joins, group-bys and pivots run in parallel across CPU cores, which shows on tables of hundreds of thousands of rows and more. For workbooks of a few thousand rows like this one, any tool is instant, and the reasons to choose Polars are its explicitness — no index, validated joins, plans you can inspect — rather than speed. If the rest of a project uses pandas, df.to_pandas() and pl.from_pandas(df) move data across cheaply.

Common pitfalls

• Expecting an index. Polars has none; join and group on named columns.
• Silent row duplication. Use validate="m:1" on lookups so duplicate keys raise.
• Null groups. Unmatched rows form a null group in group_by; filter or label them explicitly.
• Pivot column order. Columns appear in first-seen order; select them in the order you need.
• Older Polars examples. pivot used to take columns=; in current Polars the argument is on=.
• Pivoting a lazy frame. A lazy plan must know its output columns in advance, so LazyFrame.pivot (in recent Polars) requires on_columns=["Q1", "Q2", "Q3"]; on a DataFrame, pivot discovers them from the data. Listing them explicitly also fixes their order.
• Integer and float arithmetic. Multiplying an integer quantity by a float price gives a float; cast explicitly when the output must stay integer.

Conclusion

Read each sheet with pl.read_excel, join lookups with how="left" and validate="m:1", list unmatched keys with how="anti", aggregate with group_by().agg(), and reshape with pivot(on=..., index=..., values=..., aggregate_function=...), fixing the column order with select. Switch to lazy frames when the data grows, and write the results with write_excel, including any data-quality sheets so problems stay visible.

Frequently asked questions

How do I join two Excel sheets with Polars? Read each sheet with pl.read_excel(path, sheet_name=...), then call left.join(right, on="key", how="left"). Add validate="m:1" to make sure each key appears only once in the lookup table.

How do I create a pivot table in Polars? Use df.pivot(on="Quarter", index="Region", values="Revenue", aggregate_function="sum"), then fill_null(0) for combinations without data and select the columns in the order you want.

How do I find rows that do not match in a Polars join? Use an anti join: left.join(right, on="key", how="anti") returns the rows of left whose key is not present in right.

Can Polars write Excel files with formatting? Yes. DataFrame.write_excel writes each frame as an Excel table and accepts number formats per column, a table style, autofit and conditional formats. It uses XlsxWriter under the hood.

Related

• Up one level: Reading Excel with Polars and Arrow — fast Excel reads and Arrow-based tools.
• Clean and Aggregate Excel Data with Polars — cleaning steps before the join.
• pandas vs Polars for Excel Workflows — when to choose which.
• Create a Pivot Table from Excel with pandas — the pandas version.