Convert pandas Excel Code to Polars

You have a pandas script that reads a sales export, cleans it, summarises it and writes an Excel report. It works, but it is getting slow, or you want to standardise on Polars. Rewriting it is not hard, but it is not a find-and-replace either: Polars has no index, distinguishes null from NaN, prefers expressions to apply, and names many operations differently. This page converts a realistic pandas report script line by line, explains each translation, and finishes with a test that runs both versions and proves they produce the same figures — the step that turns a rewrite into a safe migration. It belongs to Reading Excel with Polars and Arrow.

Prerequisites

pip install pandas polars fastexcel xlsxwriter openpyxl

orders.xlsx holds 400 orders for the third quarter of 2026, with a region typed inconsistently and some blank discounts; regions.xlsx maps regions to managers. Both scripts below read these files and produce a monthly summary by region with the manager's name.

Step 1 — The pandas script being converted

This is the kind of script most teams have — readable, and full of pandas idioms:

import pandas as pd

def pandas_report(orders_path, regions_path):
    df = pd.read_excel(orders_path)
    regions = pd.read_excel(regions_path)
    df["Region"] = df["Region"].str.strip().str.title()
    df["Discount %"] = df["Discount %"].fillna(0)
    df["Revenue"] = df["Qty"] * df["Unit price"] * (1 - df["Discount %"] / 100)
    df = df[df["Qty"] > 0]
    df["Month"] = df["Date"].dt.to_period("M").astype(str)
    summary = (df.groupby(["Month", "Region"], as_index=False)
                 .agg(Orders=("Order ID", "count"), Revenue=("Revenue", "sum")))
    summary = summary.merge(regions, on="Region", how="left")
    summary["Revenue"] = summary["Revenue"].round(2)
    wide = summary.pivot(index="Region", columns="Month", values="Revenue").reset_index()
    return summary.sort_values(["Month", "Region"]).reset_index(drop=True), wide

pd_summary, pd_wide = pandas_report("orders.xlsx", "regions.xlsx")
print(pd_summary.head(4))

Assignments into df[...], boolean indexing, as_index=False, reset_index and .dt.to_period are all things that change in Polars. The function returns both a long summary and a wide month-by-region table, as the report writes both.

Step 2 — The same script in Polars

Here is the direct translation. Each pandas step has a Polars counterpart; the structure is a chain of expressions instead of a sequence of assignments:

import polars as pl

def polars_report(orders_path, regions_path):
    df = pl.read_excel(orders_path)
    regions = pl.read_excel(regions_path)
    df = (
        df.with_columns(
            pl.col("Region").str.strip_chars().str.to_titlecase(),
            pl.col("Discount %").fill_null(0),
        )
        .with_columns(
            (pl.col("Qty") * pl.col("Unit price") * (1 - pl.col("Discount %") / 100)).alias("Revenue"),
            pl.col("Date").dt.strftime("%Y-%m").alias("Month"),
        )
        .filter(pl.col("Qty") > 0)
    )
    summary = (
        df.group_by("Month", "Region")
          .agg(pl.col("Order ID").count().alias("Orders"), pl.col("Revenue").sum())
          .join(regions, on="Region", how="left")
          .with_columns(pl.col("Revenue").round(2))
          .sort("Month", "Region")
    )
    wide = summary.pivot(on="Month", index="Region", values="Revenue").sort("Region")
    return summary, wide

pl_summary, pl_wide = polars_report("orders.xlsx", "regions.xlsx")
print(pl_summary.head(4))

The two versions line up step for step. Formatting the month with dt.strftime("%Y-%m") replaces pandas' to_period("M").astype(str) — Polars has no period type, and a string sorts and pivots exactly like the pandas version did. The result has the same twelve rows of month, region, orders, revenue and manager, and the table below maps the rest of the vocabulary. Two with_columns calls are needed because Revenue uses the filled Discount %: expressions inside a single with_columns all see the input frame, so a column created there is not visible to its siblings. group_by never sets an index, so there is no as_index or reset_index. pivot takes on (the column whose values become headers) instead of columns.

Step 3 — The differences that bite

A few behaviours differ enough to cause wrong results rather than errors. Check each against your own script:

import numpy as np

print("1. no index:", type(pl_summary).__name__, "has no .index; row order comes only from sort()")
s = pl.Series("x", [1.0, None, float("nan")])
print("2. null vs NaN:", s.to_list(), "| null_count:", s.null_count(), "| is_nan:", s.is_nan().to_list())
print("3. fill_null does not touch NaN:", s.fill_null(0).to_list(), "| fill_nan:", s.fill_nan(0).to_list())
print("4. pandas mean skips both:", pd.Series([1.0, None, np.nan]).mean(), "| polars mean with NaN:", s.mean())

First, there is no index: anything in the pandas script that relies on index alignment — assigning a Series computed from another frame, loc by label — must become an explicit join or a column. Second, Polars keeps null (missing) and NaN (a floating-point value) separate; reading Excel blanks gives nulls, but a division by zero produces NaN. Third, fill_null fills only nulls, so a column with NaN from a calculation needs fill_nan too. Fourth, aggregations treat NaN as a number: a NaN in a column makes its Polars mean NaN, where pandas would skip it. If your pandas code relied on NaN being skipped, convert NaN to null first with fill_nan(None).

Reading and writing Excel: the engines differ too

The two libraries do not just have different APIs; by default they read Excel with different engines, and that changes what arrives in the frame. pandas reads .xlsx with openpyxl, Polars with calamine. Three differences matter in a migration. Dates: pandas gives datetime64 timestamps, while Polars gives a Date column when the cells hold whole days and a Datetime when they hold times. Whole numbers: pandas returns int64 only if a column has no blanks and float64 otherwise, while Polars keeps integers with nulls as integers. Mismatched cells: pandas keeps a text value in a numeric column by making the column object, while Polars' inference may turn it into a null without warning — see Fix Schema and Type Errors When Reading Excel with Polars. On the writing side, to_excel goes through openpyxl or XlsxWriter, while write_excel always uses XlsxWriter and formats the output as an Excel table by default, with autofilter buttons and banded rows. If the old report is post-processed with openpyxl or compared byte for byte, expect those differences and decide whether to keep them.

Step 4 — apply becomes an expression

pandas scripts often contain apply with a Python function. In Polars, almost all of these become native expressions, which are much faster. A typical case — a size band from the quantity:

def band(q):
    return "large" if q >= 10 else "medium" if q >= 5 else "small"

pd_orders = pd.read_excel("orders.xlsx")
pd_orders["Band"] = pd_orders["Qty"].apply(band)

pl_orders = pl.read_excel("orders.xlsx").with_columns(
    pl.when(pl.col("Qty") >= 10).then(pl.lit("large"))
      .when(pl.col("Qty") >= 5).then(pl.lit("medium"))
      .otherwise(pl.lit("small")).alias("Band")
)
print("same bands:", pd_orders["Band"].tolist() == pl_orders["Band"].to_list())

pl.when().then().otherwise() chains replace if/elif/else logic; string methods, date arithmetic and lookups have expression forms too. When a function truly cannot be expressed — calling an external library on each value, say — map_elements is the escape hatch, but it runs Python per row and gives up most of the speed, so treat it as the exception. Conditional columns are compared in depth in Excel IF Formulas as pandas Conditional Columns.

Step 5 — Prove both versions agree

The migration is finished only when the new script produces the same numbers as the old one. Convert the Polars results to pandas and compare with a tolerance:

from pandas.testing import assert_frame_equal

assert_frame_equal(pd_summary, pl_summary.to_pandas(), check_dtype=False)
pw = pd_wide.set_index("Region").sort_index()
qw = pl_wide.to_pandas().set_index("Region").sort_index()
assert_frame_equal(pw, qw[pw.columns], check_dtype=False, check_names=False)
print("summary and wide tables identical:", pd_summary.shape, pd_wide.shape)

assert_frame_equal compares values column by column with a small tolerance for floats; check_dtype=False allows harmless differences such as Polars' unsigned integer counts versus pandas' int64. Keep this test in the project while both versions exist, run it on several real input files rather than one, and remove the pandas version only when it passes on all of them. Writing the final workbook is then write_excel instead of to_excel — see Write a Polars DataFrame to Excel with Formatting.

Common pitfalls

• Using a column in the same with_columns that creates it. Split into two calls.
• Relying on index alignment. Polars has no index; join explicitly.
• NaN from calculations. fill_null ignores it; use fill_nan or convert NaN to null.
• Keeping apply. Rewrite as expressions; use map_elements only as a last resort.
• Migrating without a comparison test. Prove the outputs match on real files.

Conclusion

Converting pandas Excel code to Polars is a translation of vocabulary — with_columns for new columns, filter for boolean indexing, fill_null for fillna, group_by().agg() with expressions, join for merge, pivot(on=) and write_excel — plus four behavioural differences to check: no index, null versus NaN, fill_null leaving NaN, and NaN propagating through aggregations. Replace apply with when/then/otherwise and other expressions, and keep a test that compares both versions' output until the pandas script can safely be retired.

Frequently asked questions

How do I add a column in Polars like df"x" = … in pandas? Use with_columns with an expression and alias, for example df.with_columns((pl.col("a") * 2).alias("x")). Polars returns a new DataFrame rather than modifying it in place.

What replaces pandas apply in Polars? Native expressions such as pl.when().then().otherwise(), string and date methods. map_elements exists for functions that cannot be expressed but is much slower.

Why does my Polars mean return NaN when pandas did not? Polars treats NaN as a number, so one NaN makes the mean NaN, while pandas skips it. Convert NaN to null with fill_nan(None) first.

How do I check a Polars rewrite gives the same results? Run both versions on the same input, convert the Polars result with to_pandas, and compare them with assert_frame_equal from the pandas testing module.

Related

• Up one level: Reading Excel with Polars and Arrow — fast Excel reading with Polars.
• pandas vs Polars for Excel Workflows — whether to switch at all.
• Use Polars Window Functions on Excel Data — the Polars form of groupby transform.
• Compare Excel Output to a Golden File in Tests — testing the written workbook too.