Clean Wide Excel Sheets with Polars Column Selectors

Wide Excel sheets with a column per month, region or measure are tedious to clean column by column. Polars column selectors pick columns by type, name pattern or position and combine with set operations, so one expression can trim every text column, fill every numeric gap, total every monthly column, rename a whole block and unpivot it — and keep working when next month adds a column.

Polars & Arrow Data cleaning

Finance and operations sheets are often wide: a product per row and a column for every month, often twice over — "Sales Jan" to "Sales Dec", then "Returns Jan" to "Returns Dec" — plus a handful of descriptive columns. Cleaning such a sheet in code by listing columns by name is long, fragile and breaks the month a new column appears. Polars has a better tool: column selectors, in the polars.selectors module. A selector describes a set of columns — all numeric ones, all whose names start with "Sales ", all matching a regular expression — and selectors combine like sets. One expression then applies to every column in the set. This page cleans a wide sales sheet with selectors: trimming text, filling numeric gaps, computing totals across a block, renaming, and unpivoting into a long table. It belongs to Reading Excel with Polars and Arrow.

Prerequisites

Bash

pip install polars fastexcel

wide_sales.xlsx has six products and 22 columns: SKU, product name and category (with stray spaces and inconsistent capitals), nine monthly sales columns, nine monthly returns columns with a few blanks, and a notes column. The examples were run with Polars 1.44.

Step 1 — Read the sheet and see the selectors at work

Import the selectors module as cs — the convention in Polars' own documentation — and ask which columns a few selectors match:

Python

import polars as pl
import polars.selectors as cs

df = pl.read_excel("wide_sales.xlsx")
print(df.shape)
for name, sel in {
    "numeric": cs.numeric(),
    "string": cs.string(),
    "sales block": cs.starts_with("Sales "),
    "Q2 months": cs.matches(r" (Apr|May|Jun)$"),
}.items():
    print(f"{name:12}", cs.expand_selector(df, sel))

cs.expand_selector shows exactly which columns a selector resolves to — useful while writing one. cs.numeric() picks every numeric column, cs.string() every text column, cs.starts_with("Sales ") the nine sales months, and cs.matches() takes a regular expression, here selecting April to June across both blocks. Selectors are evaluated against the frame's actual columns at run time, so a "Sales Oct" column added next month is picked up automatically by cs.starts_with("Sales ") without any code change.

Step 2 — Clean every text column, and fill every numeric gap

Selectors go anywhere a column expression goes, so a single with_columns call can clean whole groups of columns:

Python

clean = df.with_columns(
    (cs.string() - cs.by_name("Category")).str.strip_chars(),   # trim the other text columns
    pl.col("Category").str.strip_chars().str.to_titlecase(),
    cs.numeric().fill_null(0),                         # blank month = no sales / returns
)
print(clean.select("Product", "Category").to_dicts()[:4])
print("nulls left:", clean.null_count().sum_horizontal().item())

(cs.string() - cs.by_name("Category")).str.strip_chars() trims spaces from every text column except Category, which gets its own expression that also fixes the capitalisation, so "tools" and "garden " become "Tools" and "Garden". cs.numeric().fill_null(0) fills the three blank monthly cells. The four nulls that remain are in Notes: calamine reads empty text cells as null, not as empty strings, which is usually what you want for a notes column. Filling with zero is a decision about the data — here a blank month means nothing was sold or returned; if a blank means "not reported", leave it null instead (see How Missing Values Change Sums, Averages and Counts). Why subtract Category from the string selector instead of letting the specific expression win? Because Polars does not let one with_columns call produce the same column twice: with plain cs.string() and a Category expression in the same call, it raises ComputeError: the name 'Category' … is duplicate. That strictness is helpful — it means two rules can never silently compete for a column — and set subtraction is the idiomatic way to say "all text columns except this one". The alternative is two chained with_columns calls, where the second one overwrites the first.

Step 3 — Totals across a block with set operations

Row totals across a block of columns are where selectors save the most typing. pl.sum_horizontal adds across columns, and a selector supplies the columns:

Python

SALES_M = cs.matches(r"^Sales [A-Z][a-z]{2}$")          # Sales Jan … Sales Sep, nothing else
RETURNS_M = cs.matches(r"^Returns [A-Z][a-z]{2}$")
totals = clean.with_columns(
    pl.sum_horizontal(SALES_M).alias("Sales YTD"),
    pl.sum_horizontal(RETURNS_M).alias("Returns YTD"),
    pl.mean_horizontal(SALES_M & cs.matches(r"(Jul|Aug|Sep)$")).round(1).alias("Sales Q3 avg"),
).with_columns(
    (pl.col("Returns YTD") / pl.col("Sales YTD") * 100).round(1).alias("Return rate %"),
)
print(totals.select("SKU", "Sales YTD", "Returns YTD", "Sales Q3 avg", "Return rate %"))

Selectors combine with set operators: & (columns in both), | (either), - (in the first but not the second) and ~ (everything except). SALES_M & cs.matches(r"(Jul|Aug|Sep)$") means "sales columns for the third quarter", without listing them. Note the precise monthly selectors defined first: once a "Sales YTD" column exists, cs.starts_with("Sales ") matches it too, so a later total over starts_with("Sales ") would count the year-to-date figure on top of the months — and a select that combines starts_with("Sales ") with ends_with("YTD") fails with Polars' DuplicateError, because both pick "Sales YTD". A pattern that describes exactly the monthly columns ("Sales", a space, a three-letter month) avoids both. Because the totals use selectors, they stay right when months are added; with hand-written column lists they would silently omit the new month.

Step 4 — Rename a whole block

Renaming many columns by rule is another job for selectors. name.map applies a function to the names of the selected columns:

Python

renamed = totals.select(
    cs.by_name("SKU", "Product", "Category"),
    SALES_M.name.map(lambda n: n.replace("Sales ", "") + " sales"),
    RETURNS_M.name.map(lambda n: n.replace("Returns ", "") + " returns"),
    cs.ends_with("YTD", "avg", "%"),
)
print(renamed.columns[:6], "…", renamed.columns[-4:])
print("dropped:", sorted(set(totals.columns) - set(totals.select(cs.all() - cs.by_name("Notes")).columns)))

The select keeps the descriptive columns, renames both monthly blocks — "Sales Jan" becomes "Jan sales" — and keeps the totals, in the order listed. The Notes column is not selected, so it is dropped; cs.all() - cs.by_name("Notes") shows the same set expressed as "everything except Notes". Selecting columns this way is also a convenient final step before writing: it fixes the output layout explicitly without spelling out every name.

Step 5 — Unpivot a block into a long table

Wide sheets are for people; long tables are for analysis and pivots. Unpivot both monthly blocks, then split the old column names into month and measure:

Python

long = (
    clean.unpivot(index=["SKU", "Product", "Category"], on=SALES_M | RETURNS_M,
                  variable_name="Column", value_name="Units")
    .with_columns(pl.col("Column").str.split_exact(" ", 1).struct.rename_fields(["Measure", "Month"]))
    .unnest("Column")
    .pivot(on="Measure", index=["SKU", "Product", "Category", "Month"], values="Units")
)
print(long.shape, long.columns)
print(long.filter(pl.col("SKU") == "DRL-01").head(3))

unpivot turns the 18 monthly columns into rows — selected with one selector — and str.split_exact splits "Sales Jan" into a measure and a month. Pivoting on the measure gives one row per product and month with Sales and Returns side by side: 6 products × 9 months = 54 rows. This long shape feeds straight into group-bys, charts and Excel pivot tables. The pandas equivalent is described in Unpivot a Wide Excel Sheet with pandas melt.

Writing the cleaned sheet back to Excel

Selectors also help on the way out. write_excel takes column names (or tuples of names) for its formatting options, and cs.expand_selector turns any selector into exactly that, so the formats follow the same rules as the cleaning. Passing a combined selector such as SALES_M | cs.by_name("Sales YTD") straight to column_totals fails in Polars 1.44 with "the truth value of an Expr is ambiguous" — expanding it to names first avoids that:

Python

out = totals.select(cs.by_name("SKU", "Product", "Category"), SALES_M, cs.ends_with("YTD", "%"))
names = lambda sel: cs.expand_selector(out, sel)          # selector → tuple of column names
out.write_excel(
    "sales_clean.xlsx", worksheet="Sales",
    column_formats={names(SALES_M | cs.ends_with("YTD")): "#,##0", names(cs.ends_with("%")): "0.0"},
    column_totals=list(names(SALES_M | cs.by_name("Sales YTD"))),
    autofit=True, freeze_panes="B2",
)
print("written:", out.shape, "| total row under", len(cs.expand_selector(out, SALES_M | cs.by_name("Sales YTD"))), "columns")

column_formats maps each group of names to an Excel number format, so every monthly column and the year-to-date totals get thousands separators and the return rate one decimal place; column_totals adds the table's total row under the nine monthly sales columns and "Sales YTD", written as SUBTOTAL(109, [Sales Jan]) formulas so they respect any filter a reader applies. When a new month appears, its column is formatted and totalled with no change to this code. More write_excel options are covered in Write a Polars DataFrame to Excel with Formatting.

Common pitfalls

Two expressions for one column in a single with_columns. Polars raises a duplicate-name error; subtract the column from the broad selector or chain two calls.
Prefix selectors that also catch derived columns. starts_with("Sales ") matches "Sales YTD" once it exists; use an exact pattern for the monthly block.
Filling every numeric blank with zero. Only when a blank really means zero.
Hand-listed columns in totals. They miss new months; use a selector.
Guessing what a selector picks. Check with cs.expand_selector first.

Conclusion

Polars column selectors describe sets of columns by type (cs.numeric(), cs.string()), name (starts_with, ends_with, by_name) or pattern (matches), and combine with &, |, - and ~. On wide Excel sheets they turn per-column cleaning into a few expressions: trim all text, fill all numeric gaps, total a whole block with sum_horizontal, rename blocks with name.map, and unpivot with on= a selector — and because selectors are evaluated against the real columns, the code keeps working when next month's column appears.

Frequently asked questions

What are Polars column selectors? Functions in polars.selectors, usually imported as cs, that describe a set of columns by data type, name or pattern. They can be used wherever a column expression is accepted.

How do I sum several Excel columns per row in Polars? Use pl.sum_horizontal with a selector, for example a starts_with selector for the sales columns, which adds the selected columns row by row.

How do I select all columns except some in Polars? Subtract selectors, for example cs.all() - cs.by_name("Notes"), or use ~ to negate a selector.

How do I check which columns a selector matches? Call cs.expand_selector(df, selector), which returns the names of the matching columns in that DataFrame.

Up one level: Reading Excel with Polars and Arrow — fast Excel reading with Polars.
Clean and Aggregate Excel Data with Polars — expressions and group-bys.
Join and Pivot Excel Data with Polars — reshaping after cleaning.
Standardise Excel Column Names with pandas — renaming rules in pandas.

More on polars & arrow