How to add thousand separator to numbers in pandas

28.8k views Asked by At

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?

6

There are 6 answers

1
2ps On BEST ANSWER

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.

0
Ranadip Dutta On

The formatters parameter in to_html will take a dictionary.

Click the example link for reference

0
Maksim On

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)
1
lcvriend On

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.

1
jezrael On

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:,}')
0
Pablo Vilas On

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("~", ".")