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

Question

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.

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)

score 1 · Answer 1 · answered Dec 25 '20 at 20:17

1

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])

answered Dec 25 '20 at 20:17

BENY

317,841
20
164
234

Output type have to be float, cause it sent to excel sheet where it used by formulas – Maher Kebaier Dec 25 '20 at 20:31
@MaherKebaier I think Excel will understand `,` just fine if you indicate it as decimal separator. Otherwise, I don't think you can keep the float dtype. – Quang Hoang Dec 25 '20 at 20:40
for some reasons, Excel remove the extra zeros (105 instead of 105,00) and (99,2 instead of 99,20) – Maher Kebaier Dec 25 '20 at 20:45

score 0 · Answer 2 · answered Dec 25 '20 at 20:39

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.

Grzegorz Skibinski · Answer 3 · 2020-12-26T11:32:38.437

0

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

edited Dec 26 '20 at 11:32

answered Dec 25 '20 at 21:39

Grzegorz Skibinski

12,624
2
11
34

The excel is begin generated daily by the python script. Users dont want to change the format of data manually for over 90 columns – Maher Kebaier Dec 26 '20 at 08:17
Also, i'am updated a specific sheet on on excel, so i'am using : ` wb = load_workbook(filename) ws = wb["FULL"] for row in dataframe_to_rows(df, index=False, header=True): ws.append(row)` – Maher Kebaier Dec 26 '20 at 08:19
Can you share the piece of code, where you save the result? – Grzegorz Skibinski Dec 26 '20 at 09:49
NB the best would be, if you can add both pieces to your question i.e. how you read, and how you write back - they both are relevant. I think most people will assume you work on `csv` otherwise... – Grzegorz Skibinski Dec 26 '20 at 09:50
@MaherKebaier try now – Grzegorz Skibinski Dec 26 '20 at 15:45

score 0 · Accepted Answer · answered Dec 27 '20 at 20:50

0

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'

answered Dec 27 '20 at 20:50

Maher Kebaier

93
14

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

4 Answers4