How to change format of df column numbers to local currency

Question

I want to format the numbers in a df column into local currency.

For example, given the original df:

Column A	Column B
5000	5%
2000	15%

If I was in the UK, the result of the table should be:

Column A	Column B
£ 5000.00	5%
£ 2000.00	15%

and if I were using the table in the US, it would convert to this instead:

Column A	Column B
$ 5000.00	5%
$ 2000.00	15%

I have tried:

locale.currency(x, grouping=True)

but I cannot get it to apply to a df column.

Is there also a way to keep it a float to run calculation? And also format correctly if a calculation runs a negative it should display:

- CCY XXX.YY

instead of

CCY - XXXX.YY

Answer 1

Floats cannot be formatted, so you need to create a new column with string values formatted in the way you want.

To get the default locale of the machine running the code, you can call locale.setlocale with an empty string as the locale. Then just format the value with locale.currency.

df = pd.DataFrame({"Column A": [5000, -2000], "Column B": ["5%", "15%"]})

locale.setlocale(locale.LC_ALL, "")

df[f"Formatted"] = df["Column A"].apply(
    lambda value: (
        locale.currency(value) if value >= 0 else f"-{locale.currency(abs(value))}"
    )
)

   Column A Column B  Formatted
0      5000       5%   $5000.00
1     -2000      15%  -$2000.00

---

If you want to use international currency acronyms instead of symbols, set international=True:

df[f"Formatted"] = df["Column A"].apply(
    lambda value: (
        locale.currency(value, international=True)
        if value >= 0
        else f"-{locale.currency(abs(value) ,international=True)}"
    )
)

   Column A Column B     Formatted
0      5000       5%   USD 5000.00
1     -2000      15%  -USD 2000.00