Formatting Floats in Python Using Comma as Decimal Separator and Dot as Thousands Separator.

Formatting Floats in Python – Using Comma as Decimal Separator and Dot as Thousands Separator (5,000.32 -> 5.000,32)

by

In Python, the default formatting of floating-point numbers uses a dot (.) as the decimal separator and a comma (,) as the thousands separator. However, in some countries and regions, the opposite is true – a comma is used as the decimal separator and a dot is used as the thousands separator. In this post, we will look at how to format floats in Python using a comma as the decimal separator and a dot as the thousands separator.

Setting the Locale

The first step in formatting floats in this way is to set the locale. The locale is a set of parameters that defines the formatting conventions for numbers, dates, and other data in a specific region or language. In Python, we can use the locale module to set the locale. In this example, we will set the locale to German (de_DE) as it uses comma as decimal separator and dot as thousands separator.

import locale
locale.setlocale(locale.LC_ALL, 'de_DE')

Modifying the Float Separator

Once the locale is set, we can use the locale.format_string() function to format floating-point numbers according to the conventions of the current locale. The format_string function takes three arguments: a format string, a value, and an optional argument grouping that tells whether to use the thousands separator.

def modify_float_separator(value):
    # Check if the value is a float
    if isinstance(value, float):
        # Set the locale to German
        locale.setlocale(locale.LC_ALL, 'de_DE')
        # Format the float using the German locale
        formatted_value = locale.format_string("%.2f", value, grouping=True)
        return formatted_value

Reading the Excel File

Next, we will read an excel file into a DataFrame using pandas library.

Pandas is a powerful library in Python for data manipulation and analysis. To read an Excel file using Pandas, you can use the read_excel() function. This function takes in the file path and sheet name as its main parameters and returns a DataFrame object, which is a 2-dimensional table-like data structure.

Here is an example of how to read an Excel file named “data.xlsx” in the same directory as the script and read the sheet named “Sheet1”:

import pandas as pd
data = pd.read_excel("data.xlsx", sheet_name="Sheet1")

Example

import pandas as pd
import locale

def modify_float_separator(row_num, col_num):
    value = df.iloc[row_num, col_num]
    # Check if the value is a float
    if isinstance(value, float):
        # Set the locale to German
        locale.setlocale(locale.LC_ALL, 'de_DE')
        # Format the float using the German locale
        formatted_value = locale.format_string("%.2f", value, grouping=True)
        return formatted_value


# Read the Excel file into a DataFrame
df = pd.read_excel('example.xlsx')


print(str(df.iloc[1,0])) # 5.32
print(modify_float_separator(1,0)) # 5,32

This script imports the pandas and locale libraries, then defines a function called “modify_float_separator” that takes in a row number and column number as arguments. The function uses the iloc property of the DataFrame to access the value at the specified row and column. It then checks if the value is a float, and if it is, it sets the locale to German using the setlocale method from the locale library, and formats the float using the German locale, returning the formatted value. The script then reads an Excel file called “example.xlsx” into a DataFrame and prints the value of the first cell and the value returned by the modify_float_separator function for the same cell, to demonstrate the changes in formatting.

Conclusion

In this post, we have seen how to format floats in Python using a comma as the decimal separator and a dot as the thousands separator. This can be useful when working with data from different regions or languages that use different formatting conventions. By setting the locale and using the locale.format_string() function, we can easily format floats according to the conventions of a specific region or language.

Leave a Comment