13

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?

Trenton McKinney
  • 56,955
  • 33
  • 144
  • 158
DanZimmerman
  • 1,626
  • 6
  • 23
  • 45

6 Answers6

9

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.

Community
  • 1
  • 1
2ps
  • 15,099
  • 2
  • 27
  • 47
6

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.

lcvriend
  • 105
  • 1
  • 7
6

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:,}')
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
4

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)
Maksim
  • 191
  • 1
  • 10
2

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

Pablo Vilas
  • 546
  • 5
  • 13
1

The formatters parameter in to_html will take a dictionary.

Click the example link for reference

Community
  • 1
  • 1
Ranadip Dutta
  • 8,857
  • 3
  • 29
  • 45