Format pandas DataFrame numbers with thousands separators: fix Styler not in-place, faster display options, and converting to strings

Learn to format pandas DataFrame numbers with thousands separators: Styler isn't in-place. See fast thousands=',', per-column formats, and safe string conversion.

Formatting numbers with a thousands separator in pandas looks straightforward, yet a common gotcha trips people up: styling is not in-place. If you call Styler and then print your DataFrame, nothing appears to change, and any custom formatter seems to be ignored. Here’s how to make it work reliably and efficiently.

Problem overview

The goal is to display integer values with a thousands separator. A DataFrame is read from an .xlsx file, and a simple formatter is defined to add commas to integers. However, printing the DataFrame shows no formatting, and it appears as if the formatter is never called.

Reproducible example

The snippet below demonstrates the issue. It reads a sheet, defines a formatter, applies style.format, and then prints the DataFrame.

import pandas as pd
SRC_FILE = "/Volumes/Spare/Downloads/prize-june-2025.xlsx"
def comma_group(n):
    return f"{n:,d}" if isinstance(n, int) else n
frame = pd.read_excel(SRC_FILE, header=2, usecols="B,C,E:H")
print(frame.dtypes)
frame.style.format(comma_group)
print(frame.head())

The printed output shows raw integers without separators. It looks like comma_group never runs.

Why this happens

Styling in pandas is a presentational layer. The expression DataFrame.style returns a Styler object that knows how to render your DataFrame with the requested formatting, typically to HTML. It does not mutate the underlying DataFrame, and it does not affect DataFrame methods like head or print. That’s why calling style.format and then printing the original DataFrame shows unchanged values.

There’s a second subtlety when working in notebooks: the rendered Styler output is displayed only if the Styler object is the last value in the cell. Otherwise, it’s created and immediately discarded with no visible effect.

Working solution

If you want to see the formatted result in a notebook, return the Styler as the final expression in the cell:

frame.style.format(comma_group)

If you want a text representation (e.g., in a script or console) instead of HTML, convert the Styler to a string and print it:

print(frame.style.format(comma_group).to_string())

Remember that once you call style, you are no longer working with a DataFrame object. This is why style should be the last step when you intend to display output.

Faster built-in formatting

The custom callable approach works, but it’s relatively slow. When your numeric columns have homogeneous dtypes, you can use the built-in thousands parameter, which is simpler and faster:

frame.style.format(thousands=',')

If you want custom formats per column, pass a dictionary of column-to-format mappings. For numeric columns, you can construct the mapping dynamically:

frame.style.format({col: '{:,d}' for col in frame.select_dtypes('number')})

If you need to influence how floats are displayed globally, you can modify the default float display with pd.options.display.float_format.

Converting the data itself (not just the display)

If the requirement is to transform the underlying values to strings with separators and keep working with a DataFrame (now containing strings), map the function over the DataFrame and capture the result:

result = frame.map(comma_group)

This produces a new DataFrame of strings, leaving the original data intact.

Why it matters

Distinguishing between presentation and data is crucial for correctness and performance. Styling is ideal for display and export workflows but won’t change the DataFrame’s contents. Relying on in-place formatting can lead to confusion, misleading prints, and unnecessary custom logic when a built-in parameter would suffice.

Summary and guidance

Use the Styler API when you want formatted output: return the Styler as the last expression in a notebook cell or convert it to a string for console output. Prefer the thousands parameter for a concise, faster solution, and provide a format dictionary when you need per-column control. If you actually need the data stored as formatted strings, map your formatter over the DataFrame. Keeping these boundaries clear will save time and reduce surprises in your pandas workflows.