CODEWITHSUNDEEP
pythonpandas
Leonardo Henriques
Leonardo Henriques

Reputation: 526

How to format numbers floats with comma and dot

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

Upvotes: 2

Views: 1544

Answers (2)

Akhilesh_IN
Akhilesh_IN

Reputation: 1317

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

Upvotes: 0

filbranden
filbranden

Reputation: 8898

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'],
)

Upvotes: 1

Related Questions