CODEWITHSUNDEEP
pythonpython-3.xpandas
staticdev
staticdev

Reputation: 3060

How to have float format with comma as a decimal separator in Python Pandas?

I have a csv with string values for money numbers such as "R$2,444.99" and I read need to make reports with sums, groupbys, etc for the loaded DataFrame.

For these columns, what I've tried to do is create a custom conversion function to load it as floats:

def convert_reais(value: str) -> float:
    """Transforms column value to calculable float."""
    if value == "":
        return 0.0
    return float(value.replace("R$", "").replace(".", "").replace(",", "."))

Then I am able to load the columns correctly passing a dictionary with these columns mapped with this function:

converters = {column_net: convert_reais, column_gross: convert_reais}

source_df = pd.read_csv(filename, encoding=file_encoding, sep=";",
                        skiprows=1, converters=converters)

My final problem is that I need to show sums and groupbys showing "," as the decimal separator (which by default is ".". Ex.:

print(source_df.groupby([column_sort])[column_net].sum())

I've found a way to format the results with:

pd.options.display.float_format = "{:,.2f}".format

But I didn't find a float_format that changes points to commas.

I also tried loading the CSV with:

thousands=".", decimal=","

But this makes no difference since "R$" still make it loaded as a string.

Any thoughts on my approach?

Upvotes: 4

Views: 4088

Answers (1)

staticdev
staticdev

Reputation: 3060

I found the solution for the problem.

Just follow the steps:

  1. Import built-in locale module: import locale
  2. From your shell, select desired and installed locale with your currency format from the list: locale -a
  3. Set on python your script locale: locale.setlocale(locale.LC_ALL, yourlocale) # ex. 'pt_BR.utf8'
  4. Change pandas configuration for visualizing floats: pd.set_option("float_format", locale.currency)

Upvotes: 2

Related Questions