Pandas Formatting with Currency sign, custom thousand separator and custom decimal separator

user_8952565 0
Viewed 47

My data contain a bunch of floats, which I'd like to format as follows:

  1. Thousand separator is a dot
  2. Decimal separator is comma
  3. Always show two decimals
  4. add Euro sign

This is what I got so far:

import pandas as pd

df = pd.DataFrame({'x': [1.50, 
                    2.3456,
                    10000.22,
                    100.3,
                    6800]})

df['x'].map('{:.2f} €'.format)

This yields

0        1.50 €
1        2.35 €
2    10000.22 €
3      100.30 €
4     6800.00 €
Name: x, dtype: object

Desired outcome is however:

0        1,50 €
1        2,35 €
2   10.000,22 €
3      100,30 €
4    6.800,00 €
Name: x, dtype: object

How can this be done?

pandas format
1 Answers
#                      _ HERE
In [113]: df["x"].map("{:_.2f} €".format).str.translate(str.maketrans("_.", ".,"))
Out[113]:
0         1,50 €
1         2,35 €
2    10.000,22 €
3       100,30 €
4     6.800,00 €
Name: x, dtype: object

python's formatter accepts a thousand separator but it could be either , or _. Now, you eventually want . but can't directly do that. And you want , as the decimal separator, so that's dangerous too. So we use _ as the separator there, only to be replaced later with .. Other replacement we do is . to , in the decimal part

user_93321870
Related