How to change decimal separator from dot to coma in Pandas when column have NaN values?

Question

When I try to open my ready files in Excel, it changes my decimals number to data. I try to change dot to coma in decimals numbers, and it work. I used this code to change it:

def convert_df(df):
    return df.to_csv(sep=';',decimal=',').encode('utf-8')

Problem is that I have some NaN values in my DataFrame. I changed NaN values to '-' to make it look prettier. This function above does not change dot to coma in columns that have this '-' value.

I try this code too:

DF['Age'].replace('.',',',inplace=True)

But this solutions work in the same way as this first one.

Anyone has some solutions for this problem? Thanks for help.

Answer 1

Here is one way to do it:

import pandas as pd

df = pd.DataFrame(
    {
        "col1": [1.1, 1.2, 1.3],
        "col2": [1.1, pd.NA, 1.3],
    }
)

print(df)  # Toy dataframe
   col1  col2
0   1.1   1.1
1   1.2  <NA>
2   1.3   1.3

df.fillna("-").applymap(lambda x: str(x).replace(".", ",")).to_csv(
    path_or_buf="df.csv", sep=";", index=False
)

When you open df.csv in Excel: