How to format numbers floats with comma and dot

Question

I have the following data that I need to format:

  Ativo        high       close
 HTMX11       169.8       169.8
 KNHY11       116.0       116.0
 FAED11       272.3       272.3
 FLRP11     1579.92     1579.92
 FVPQ11       215.5       215.5

Need to convert 1579.92 to 1.579,92

not a problem, since I can do with:

import pandas as pd
import locale
locale.setlocale (locale.LC_ALL, 'pt_br.utf-8')
pd.set_option ('display.float_format', lambda x: locale.format ('%. 2f', x, grouping = True))

but when I need to convert the column to a string and add some letters, numbers are kept only with commas:

I need complete formatting.

df['close'] = '$' + df['close'].astype()
df['close']

Results in:

Fechamento
  R$ 169,8
  R$ 116,0
  R$ 272,3
R$ 1579,92
  R$ 215,5

desired output:

R$ 1.579,92

Does this answer your question? [How to print number with commas as thousands separators?](https://stackoverflow.com/questions/1823058/how-to-print-number-with-commas-as-thousands-separators) — Gabriela Melo, Feb 19 '20 at 02:51

score 1 · Accepted Answer · answered Feb 19 '20 at 05:46

As you've noticed, the display option only affects the display. So you need to do an explicit conversion, possibly using locale.format(), if you want to actually convert the column to a string.

The locale methods are also somewhat limited in what they can do, so I'd recommend using the Babel module for internationalization and localization. Babel has a richer API and it actually ships localization data you can use (so you don't need to depend on it being available in your O.S.) It also includes data about currencies, so it can do that conversion for you as well.

You can install Babel with:

pip install Babel

And then you can convert your columns to use Brazilian Real currency with:

from babel.numbers import format_currency

df['close'] = df['close'].apply(
    lambda v: format_currency(v, 'BRL', locale='pt_BR'),
)

Or, to convert both "high" and "close" together:

df[['high', 'close']] = df[['high', 'close']].applymap(
    lambda v: format_currency(v, 'BRL', locale='pt_BR'),
)

If you're generating HTML from the DataFrame (for example, in a Jupyter notebook), you can use the Styling API to apply the format only when rendering the DataFrame, keeping the underlying data as floats and not strings:

df.style.format(
    lambda v: format_currency(v, 'BRL', locale='pt_BR'),
    subset=['high', 'close'],
)

score 0 · Answer 2 · answered Feb 19 '20 at 06:52

import pandas as pd
from babel.numbers import format_currency
df = pd.DataFrame({"close":[169.8,116.0,272.3,1579.92,215.5]})
df['close'].apply(lambda x :format_currency(x,'BRL', locale='pt_BR'))

output:

0      R$ 169,80
1      R$ 116,00
2      R$ 272,30
3    R$ 1.579,92
4      R$ 215,50

How to format numbers floats with comma and dot

2 Answers2