CODEWITHSUNDEEP
python-3.xpandasdataframe
Maher Kebaier
Maher Kebaier

Reputation: 113

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

Is there any way to replace the dot in a float with a comma and keep a precision of 2 decimal places?

Example 1 : 105 ---> 105,00

Example 2 : 99.2 ---> 99,20

I used a lambda function df['abc']= df['abc'].apply(lambda x: f"{x:.2f}".replace('.', ',')). But then I have an invalid format in Excel.

enter image description here

I'm updating a specific sheet on excel, so I'm using : wb = load_workbook(filename) ws = wb["FULL"] for row in dataframe_to_rows(df, index=False, header=True): ws.append(row)

Upvotes: 1

Views: 2539

Answers (4)

Maher Kebaier
Maher Kebaier

Reputation: 113

well i found an other way to specify the float format directly in Excel using this code :

    for col_cell in ws['S':'CP'] :
        for i in col_cell :
            i.number_format = '0.00'

Upvotes: 0

Georgina Skibinski
Georgina Skibinski

Reputation: 13387

I think you're mistaking something in here. In excel you can determine the print format i.e. the format in which numbers are printed (this icon with +/-0). But it's not a format of cell's value i.e. cell either way is numeric. Now your approach tackles only cell value and not its formatting. In your question you save it as string, so it's read as string from Excel.

Having this said - don't format the value, upgrade your pandas (if you haven't done so already) and try something along these lines: https://stackoverflow.com/a/51072652/11610186

To elaborate, try replacing your for loop with:

i = 1
for row in dataframe_to_rows(df, index=False, header=True):
    ws.append(row)
    # replace D with letter referring to number of column you want to see formatted:
    ws[f'D{i}'].number_format = '#,##0.00'
    i += 1

Upvotes: 0

HHYurdagul
HHYurdagul

Reputation: 145

s = pd.Series([105, 99.22]).apply(lambda x: f"{x:.2f}".replace('.', ',')

First .apply takes a function inside and

f string: f"{x:.2f} turns float into 2 decimal point string with '.'.

After that .replace('.', ',') just replaces '.' with ','.

You can change the pd.Series([105, 99.22]) to match it with your dataframe.

Upvotes: 0

BENY
BENY

Reputation: 323266

Let us try

out = (s//1).astype(int).astype(str)+','+(s%1*100).astype(int).astype(str).str.zfill(2)
0    105,00
1     99,20
dtype: object

Input data

s=pd.Series([105,99.2])

Upvotes: 1

Related Questions