How To Not Have "=" Changed To 0 From DataFrame.to_excel Using XlsxWriter

Question

I can't seem to identify why my DataFrame changes a string of "=" to 0.

import pandas as pd
df = pd.DataFrame.from_dict({'rate': ['=', '+', '-']})
writer = pd.ExcelWriter('Test.xlsx', engine='xlsxwriter', options={'strings_to_urls': False})
df.to_excel(writer, sheet_name='Test', index=False)
writer.save()

For some reason, it keeps the "-" and "+" but changes "=" to 0.

Answer 1

Pandas uses XlsxWriter's write() method which by default treats strings beginning with = as formulas.

You can change this behaviour by setting strings_to_formulas to False (in addition to the strings_to_urls that you are already using):

writer = pd.ExcelWriter('Test.xlsx', 
                        engine='xlsxwriter', 
                        options={'strings_to_urls': False, 
                                 'strings_to_formulas': False})

See the docs for more details on XlsxWriter constructor options.

Note: the Pandas options syntax changed to engine_kwargs after version 1.3.0. See the updated XlsxWriter Pandas docs for the new syntax.

Answer 2

Since pandas 1.3.0, the correct syntax is:

writer = pd.ExcelWriter(
    "Text.xlsx",
    engine="xlsxwriter",
    engine_kwargs={"options": {"strings_to_formulas": False}},
)