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

staticdev

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?

staticdev

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)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

How to format a float with a comma as decimal separator in an f-string?

how to handle decimal separator in float using Pandas?

Format a decimal with comma as decimal separator and without thousands separator

Use pandas style using comma as decimal separator

How to remove decimal separator from float in python dataframe

Format a number to always have a sign and decimal separator

How to write a csv with a comma as the decimal separator?

Pyspark, how to write a df with comma as a decimal separator

How to read pandas CSV file with comma separator and comma thousand separator

decimal separator in woocommerce to be a comma

Import csv which have numerics with comma as the decimal separator

Importing xlsx data to R when numbers have a comma as decimal separator

How to make string format float with fixed precision and locale type decimal separator?

Display 2 decimal places, and use comma as separator in pandas?

How to format decimal with leading zeros and without a separator?

Is there any option to store a number as text with a specific format (comma as thousands separator and period as decimal separator) in Snowlake?

How to replace decimal separator dot by decimal separator comma in formattable() package R?

Format a decimal to remove trailing zeros and have a thousandths separator

How to accept both dot and comma as a decimal separator with WTForms?

How to change the decimal separator of DecimalFormat from comma to dot/point?

How to change decimal separator from comma to fullstop in redshift copy command

How to convert floating point decimal separator from dot to comma in Javascript

How to get strings parsed to floats with comma as decimal separator

In PowerPoint, how do you replace a decimal separator with a comma

AngularJS: Change decimal separator to comma

Replace point decimal separator with comma

Python, How long decimal point can a float have?

How to deserialize a float value with a localized decimal separator with Jackson

How to convert float to NSString with thousand separator and 2 decimal points?