pandaspandas.excelwriter

ExcelWriter format number to currency by column name


I'm converting the float columns to currency data type with the following:

df = pd.DataFrame({'col0': [71513.0, 200000.0, None], 
                    'col1': [True, False, False], 
                    'col2': [100.0, 200.0, 0.0]})
df[['col0', 'col2']] = df[['col0', 'col2']].astype(float).astype("Int32").applymap(\
                            lambda x: "${:,.0f}".format(x) if isinstance(x, int) else x)

I am outputting the table with the following:

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, index= False)
workbook  = writer.book
ws = writer.sheets['Sheet1']
writer.close()
writer.save()

However, when I output the datable with the following, the currency is stored as text:

enter image description here

How would I format the excel sheet itself (instead of the pandas column) based on the column name so that the value is a number, but the formatting is currency?

enter image description here


Solution

  • This is how it worked for me

    Removed the column formatting within df

    df = pd.DataFrame({'col0': [71513.0, 200000.0, None], 
                        'col1': [True, False, False], 
                        'col2': [100.0, 200.0, 0.0]})
    

    Removed index parameter from to_excel, Defined format for the columns, and assign it to columns 1, and 3

    writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
    df.to_excel(writer)  # index= False)
    
    workbook  = writer.book
    ws = writer.sheets['Sheet1']
    
    format1 = workbook.add_format({'num_format': '$#,##0.00'})
    
    ws.set_column(1, 1, 18, format1)
    ws.set_column(3, 3, 18, format1)
    writer.save()
    writer.close()
    

    reference to documentation: https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

    enter image description here