Convert float to currency in Pandas before writing to Excel the right way

Question

I have a dataframe I'm eventually writing to excel using the to_excel method. One of the columns is represents a monetary amount. Currently it's float, and what I'd to accomplish is to write it as currency. However, it's important that excel would read the data in the column as currency, not as string (so, for example, you could perform calculations on the column).

Whatever I try, excel reads the column as string. This is what I've tried:

df['Monetary_Field'] = df['Monetary_Field'].map(lambda x: '$%2.0f' % x)
df['Monetary_Field'] = df['Monetary_Field'].map(lambda x: '${:20,.0f}'.format(x))
df['Monetary_Field'] = df['Monetary_Field'].map('${:,.2f}'.format)

(tried these separately of course, not one after the other).

Can you please help? Thanks! :)

Answer 1

Currency is a format in Excel, the data itself is still represented as the same numerics.

If you want to control the formatting in Excel, you can do something like this, getting access to the underlying XlsxWriter object and specifying formatting. I based this on the linked blog post which has more explanation and examples. http://pbpython.com/improve-pandas-excel-output.html

In [95]: writer = pd.ExcelWriter('out.xlsx')

In [96]: df.to_excel(writer, sheet_name='report')

In [97]: wb = writer.book

In [98]: ws = writer.sheets['report']

In [99]: money_fmt = wb.add_format({'num_format': '$#,##0'})

In [100]: ws.set_column('B:B', 12, money_fmt)
Out[100]: 0

In [101]: writer.save()