CODEWITHSUNDEEP
pythonpandasxlsxwriter
Cliff
Cliff

Reputation: 136

XLSXWriter Pandas Export Truncates or Rounds Numbers

I'm having trouble exporting a dataframe with xlsxwriter. A column with very long database key integers is truncated and shows in scientific notation. How do I setup pandas and xlsxwriter to pass the entire number through the dataframe to the xlsxwriter instead of giving me a truncated result?

The column that I'm having trouble with is newdf['Record ID'], so please help me adjust the code below so that that the entire number passes through.

I tried converting this to a string, but it still shows up in excel as a truncated number in scientific notation. I think the scientific notation is fine, but I cannot afford the truncation.

    output = io.BytesIO()
    writer = pd.ExcelWriter(output, engine='xlsxwriter')
    newdf.to_excel(writer, sheet_name='NewDF Sheet')
    writer.save()
    resp = make_response(output.getvalue())
    resp.headers["Content-Disposition"] = "attachment; filename=output.xlsx"
    resp.headers["Content-Type"] = "application/vnd.ms-excel"

The code above is running in a flask application and exports the dataframe to an xlsx file. The dataframe contains columns newdf['Record ID'] that should be exporting the entire number, but it only exports the first several digits and the ends show up as zero. This also shows up in scientific notation.

UPDATE: I solved my issue by setting the option in excel writer as options={'strings_to_numbers': False} as shown below. The column was actually already a string in pandas, but still is written by excelwriter as a number unless you set this option. I'm including the string conversion in my answer for a complete solution. 

writer = pd.ExcelWriter(output, engine='xlsxwriter', options={'strings_to_numbers': False})
newdf['Record ID'] = newdf['Record ID'].astype('str')
newdf.to_excel(writer, sheet_name='NewDF Sheet', index=False)
writer.save()

Upvotes: 4

Views: 1883

Answers (1)

Cliff
Cliff

Reputation: 136

I solved my issue by setting the option in excel writer as options={'strings_to_numbers': False} as shown below. The column was actually already a string in pandas, but still is written by excelwriter as a number unless you set this option. I'm including the string conversion in my answer for a complete solution.

writer = pd.ExcelWriter(output, engine='xlsxwriter', options={'strings_to_numbers': False})
newdf['Record ID'] = newdf['Record ID'].astype('str')
newdf.to_excel(writer, sheet_name='NewDF Sheet', index=False)
writer.save()

Upvotes: 1

Related Questions