How to add thousand separator to numbers in pandas

Question

Assuming that I have a pandas dataframe and I want to add thousand separators to all the numbers (integer and float), what is an easy and quick way to do it?

Answer 1

When formatting a number with , you can just use '{:,}'.format:

n = 10000
print '{:,}'.format(n)
n = 1000.1
print '{:,}'.format(n)

In pandas, you can use the formatters parameter to to_html as discussed here.

num_format = lambda x: '{:,}'.format(x)
def build_formatters(df, format):
    return {
        column:format 
        for column, dtype in df.dtypes.items()
        if dtype in [ np.dtype('int64'), np.dtype('float64') ] 
    }
formatters = build_formatters(data_frame, num_format)
data_frame.to_html(formatters=formatters)

Adding the thousands separator has actually been discussed quite a bit on stackoverflow. You can read here or here.

Answer 2

The formatters parameter in to_html will take a dictionary.

Click the example link for reference

Answer 3

Steps

• use df.applymap() to apply a function to every cell in your dataframe
• check if cell value is of type int or float
• format numbers using f'{x:,d}' for integers and f'{x:,f}' for floats

Here is a simple example for integers only:

df = df.applymap(lambda x: f'{x:,d}' if isinstance(x, int) else x)

Answer 4

Assuming you just want to display (or render to html) the floats/integers with a thousands separator you can use styling which was added in version 0.17.1:

import pandas as pd
df = pd.DataFrame({'int': [1200, 320], 'flt': [5300.57, 12000000.23]})

df.style.format('{:,}')

To render this output to html you use the render method on the Styler.

Answer 5

Use Series.map or Series.apply with this solutions:

df['col'] = df['col'].map('{:,}'.format)
df['col'] = df['col'].map(lambda x: f'{x:,}')

---

df['col'] = df['col'].apply('{:,}'.format)
df['col'] = df['col'].apply(lambda x: f'{x:,}')

Answer 6

If you want "." as thousand separator and "," as decimal separator this will works:

Data = pd.read_Excel(path)

Data[my_numbers] = Data[my_numbers].map('{:,.2f}'.format).str.replace(",", "~").str.replace(".", ",").str.replace("~", ".")

If you want three decimals instead of two you change "2f" --> "3f"

Data[my_numbers] = Data[my_numbers].map('{:,.3f}'.format).str.replace(",", "~").str.replace(".", ",").str.replace("~", ".")