pandas to_excel() method forces scientific notation on int64 columns when viewed in Excel

1
votes

I'm using this code to save a dataframe to excel: 

    writer = pd.ExcelWriter(self.file_name, engine='openpyxl')
    writer.book = workbook
    writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets)
    df.to_excel(writer, sheet_name=sheet_name, columns=columns, header=True, index=False, startrow=11, startcol=0)
    writer.save()

unfortunately any column with a large int64 dtype comes out looking like this:

enter image description here

Within Pandas I am suppressing scientific notation by doing the following:

pd.set_option('display.float_format', lambda x: '%.f' % x) 
pd.set_option('display.precision', 0)

So when I print the dataframe the int64 columns look like this:

enter image description here

I have tried converting the column to str type like so: 

    def convert_int_cols_to_str(df):
        dtypes = df.dtypes
        for col, dtype in dtypes.iteritems():
            if dtype == 'int64':
                df[col] = df[col].astype(str)

After this applying this the dtypes for these columns show as object instead of int64 but still display in scientific notation within Excel. Any ideas on how to avoid this would be greatly appreciated!

Here are the versions I am using of the relevant packages:

openpyxl==2.5.9
pandas==0.20.3
1
pythonopenpyxl

1 Answers

0
votes

Wow sorry everyone. Turns out that I was applying the dtype conversion at the wrong point in my script. The method I described above actually works perfectly to suppress scientific notation for int64 columns within Excel. I'll leave the question up in case it helps someone else down the road.