Clean and Aggregate Excel Data with Polars Expressions

A sales export from a regional team arrives with the usual problems: " north", "NORTH " and "north" for the same region, "tea" and "Tea" for the same product, revenue typed as "1,200", "€ 300", "1 050" and "n/a". Polars can fix all of it in a handful of expressions, and aggregate the result much faster than a pandas loop on large sheets. Expressions also compose: the cleaning rules are values you can store, reuse and apply to every export. This guide, part of Reading Excel with Polars and Arrow, goes from the raw sheet to a summary workbook.

Prerequisites

pip install polars fastexcel xlsxwriter pandas openpyxl

The sample export:

import datetime as dt

import pandas as pd
import polars as pl

pd.DataFrame({
    "Region": [" north", "South", "NORTH ", "east", "South", "north"],
    "Product": ["Tea", "tea", "Coffee", "Tea", "Coffee", "Coffee"],
    "Revenue": ["1,200", "950", "€ 300", "n/a", "1 050", "700"],
    "Units": [12, 10, 3, None, 11, 7],
    "Date": [dt.date(2026, 1, 5), dt.date(2026, 1, 9), dt.date(2026, 2, 2),
             dt.date(2026, 2, 3), dt.date(2026, 2, 20), dt.date(2026, 3, 1)],
}).to_excel("sales.xlsx", index=False)

raw = pl.read_excel("sales.xlsx", schema_overrides={"Revenue": pl.String})
print(raw.schema)

Reading Revenue as a string is deliberate. Left to inference, a column of mixed text and numbers could come back with some values already lost; as text, every value arrives exactly as typed and the cleaning rules decide what it means.

Step 1 — Write the cleaning rules as expressions

Each rule is an expression: a description of a computation on a column, not yet applied to any data. Keeping them in a dictionary makes them reusable and easy to review:

AMOUNT = pl.col("Revenue").str.replace_all(r"[^\d.\-]", "").cast(pl.Float64, strict=False)

RULES = {
    "region": pl.col("Region").str.strip_chars().str.to_titlecase(),
    "product": pl.col("Product").str.strip_chars().str.to_titlecase(),
    "revenue": AMOUNT,
    "units": pl.col("Units").cast(pl.Int64),
    "month": pl.col("Date").dt.truncate("1mo"),
}
clean = raw.select(**RULES)
print(clean)

strip_chars and to_titlecase turn all three spellings of north into "North". The amount rule deletes every character that is not a digit, a point or a minus sign — thousands separators, currency symbols, spaces — and casts the rest. strict=False makes values that still do not parse, like "n/a", become null instead of raising. dt.truncate("1mo") maps each date to the first of its month, a clean key for monthly totals. The rule assumes a decimal point; for exports that use a decimal comma, replace the comma with a point first.

Step 2 — List the values that failed to parse

A lenient cast is only safe if you look at what it discarded. Filter the raw rows where the original had a value but the parsed amount is null:

failed = raw.filter(pl.col("Revenue").is_not_null() & AMOUNT.is_null())
print(failed.select("Region", "Revenue").rows())      # [('east', 'n/a')]
print(clean.null_count().row(0, named=True))
# {'region': 0, 'product': 0, 'revenue': 1, 'units': 1, 'month': 0}

Reusing the same AMOUNT expression guarantees the check and the cleaning agree. "n/a" is a genuine placeholder. Note what the list cannot show: a revenue typed as "1.200,00" in European format parses without complaint as 1.2, because deleting the comma leaves a valid number. Lenient parsing only catches values that fail; if exports may mix number formats, check each file's format — for example by testing for a comma followed by exactly two digits at the end — before applying the rule. Placeholder handling in general is covered in Replace Placeholder Text with NaN in pandas.

Step 3 — Add conditional columns with when/then

Polars' when/then/otherwise is the equivalent of nested IF formulas, evaluated for the whole column at once:

clean = clean.with_columns(
    size=pl.when(pl.col("revenue") >= 1000).then(pl.lit("large"))
          .when(pl.col("revenue") >= 500).then(pl.lit("medium"))
          .otherwise(pl.lit("small"))
)
print(clean.select("revenue", "size").rows()[:4])
# [(1200.0, 'large'), (950.0, 'medium'), (300.0, 'small'), (None, 'small')]

Note the last row: a null revenue falls through every condition and lands in otherwise, so the unknown order is labelled "small". Add an explicit first branch — pl.when(pl.col("revenue").is_null()).then(pl.lit("unknown")) — whenever nulls are possible.

Step 4 — Aggregate with group_by

A group-by takes any number of aggregation expressions, each named with a keyword:

summary = clean.group_by("region").agg(
    revenue=pl.col("revenue").sum(),
    units=pl.col("units").sum(),
    orders=pl.len(),
    avg_price=(pl.col("revenue").sum() / pl.col("units").sum()).round(2),
    products=pl.col("product").n_unique(),
    missing_revenue=pl.col("revenue").is_null().sum(),
).sort("revenue", descending=True)
print(summary)

avg_price divides two sums, which gives the true price per unit rather than an average of per-order prices. The East row shows why missing_revenue belongs in the summary: its only order had no parseable revenue, and Polars' sum of nothing is 0 — so East reports revenue 0.0, which is not the same as zero sales. With the null count next to it, readers can tell the difference. Per-column aggregation rules are discussed in more depth in Use Different Aggregations per Column in a pandas Pivot.

Step 5 — Shares with over and a monthly pivot

A window expression computes a group value and broadcasts it back to each row — here, each order's share of its region's revenue:

shares = clean.with_columns(
    share=(pl.col("revenue") / pl.col("revenue").sum().over("region")).round(3)
)
print(shares.filter(pl.col("region") == "North").select("product", "revenue", "share").rows())
# [('Tea', 1200.0, 0.545), ('Coffee', 300.0, 0.136), ('Coffee', 700.0, 0.318)]

monthly = clean.pivot(on="month", index="region", values="revenue",
                      aggregate_function="sum").sort("region")
print(monthly)

over is Polars' equivalent of a SQL window function or pandas' groupby().transform(), and it needs no join. The pivot puts months across the top for a report layout; the month columns are named from the dates, so rename them to "Jan 2026" and so on before writing if the audience prefers. In this version of Polars, region-month combinations with no orders show 0.0 in the pivot rather than null — another place where a count alongside the sum keeps the report honest.

Step 6 — Run the pipeline lazily

read_excel is eager, but everything after it can be lazy. Calling .lazy() lets Polars see the whole pipeline and optimise it — pushing filters down, dropping unused columns — before any work is done:

report = (
    pl.read_excel("sales.xlsx", schema_overrides={"Revenue": pl.String})
    .lazy()
    .select(**RULES)
    .filter(pl.col("units") > 5)
    .group_by("product")
    .agg(units=pl.col("units").sum(), revenue=pl.col("revenue").sum())
    .sort("product")
    .collect()
)
print(report.rows())      # [('Coffee', 18, 1750.0), ('Tea', 22, 2150.0)]

Because RULES is a dictionary of expressions, the same cleaning runs unchanged in eager and lazy code. On a sheet this small the difference is invisible; on hundreds of thousands of rows, letting the optimiser drop the unused month column and apply the filter early saves real time and memory. Grouping on the cleaned product column matters: grouping the raw column would give "Tea" and "tea" as two products.

Step 7 — Write the summary to Excel

Polars writes workbooks through XlsxWriter with write_excel, which can size columns and add a table style in the same call:

summary.write_excel("summary.xlsx", worksheet="Summary", autofit=True,
                    table_style="Table Style Medium 2")
print(pd.read_excel("summary.xlsx", sheet_name="Summary").columns.tolist())

Formatting options such as number formats per column and conditional formats are covered in Write a Polars DataFrame to Excel with Formatting.

Common pitfalls

• Casting with strict=True on dirty text. The default strict cast raises on the first bad value, with no list of the others. Cast leniently and report failures instead.
• Title case on codes. to_titlecase suits names, not codes: "UK" becomes "Uk" and "SKU-a1" becomes "Sku-A1". Use to_uppercase for codes and IDs.
• Cleaning after grouping. Normalise text before group_by, or variants of the same name become separate groups.
• Treating 0 as "no data". Sums of all-null groups are 0; count nulls alongside every total that can have gaps.

Conclusion

Polars expressions turn a cleaning specification into reusable values: trim and normalise text, strip formatting from amounts and cast leniently, then list what failed. Add conditional columns with when/then, aggregate with group_by and named expressions, compute shares with over, pivot for layout, and run the whole thing lazily for large sheets before writing the summary with write_excel. Keep null counts next to totals so an unknown never reads as zero.

Frequently asked questions

How do I convert a text column like "1,200" or "€ 300" to numbers in Polars? Remove everything except digits, the decimal point and the minus sign with str.replace_all, then cast to Float64 with strict=False. Values that still cannot be parsed become null, so list them before continuing.

Why is the sum of a group 0 when all its values are missing? Polars' sum ignores nulls, and the sum of nothing is 0. Count the nulls in the same aggregation, or use a when/then expression that returns null for groups without any values, so an unknown total is not reported as zero.

How do I calculate each row's share of its group total? Divide the column by the same column's sum computed over the group, pl.col("revenue") / pl.col("revenue").sum().over("region"). The over clause is the Polars equivalent of a SQL window function.

Should I use lazy mode for Excel data? read_excel is eager, but you can call .lazy() on the result and build the rest of the pipeline lazily. Polars then optimises the whole query, which pays off on large sheets with several filters and aggregations.

Related

• Up one level: Reading Excel with Polars and Arrow — fast Excel reading.
• Read Multiple Excel Sheets with Polars — building the input frame from many sheets.
• Strip Whitespace and Normalise Text Columns with pandas — the same text problems in pandas.
• Pandas vs Polars for Excel Workflows — choosing between them.