Convert columns with large numbers and float64 as int type to suppress scientific formatting

Question

I tried many SO answers but nothing tried so far worked.I have a column in a dfwhere there is a large value in a column like 89898989898989898 Whatever I do this is not being displayed as a number. All the cols are of float64 dtype.I do not have any float values in my df After creating pivot I get a dataframe df and I tried converting to int then writing to excel, does not seem to have any difference and displays as scientific formatting(I can see the value when I click on the cell in the value bar) I could not convert to int directly as there are Nan values in the column :-

  ID CATEG   LEVEL     COLS    VALUE  COMMENTS
    1   A        2     Apple    1e+13 comment1
    1   A        3     Apple    1e+13 comment1
    1   C        1     Apple    1e+13 comment1
    1   C        2     Apple    345   comment1
    1   C        3     Apple    289   comment1
    1   B        1     Apple    712   comment1
    1   B        2     Apple    1e+13 comment1
    2   B        3     Apple    376   comment1
    2   C        None  Orange   1e+13 comment1
    2   B        None  Orange   135   comment1
    2   D        None  Orange   423   comment1
    2   A        None  Orange   866   comment1
    2            None  Orange   496   comment2

After pivot the Apple column looks like this (providing just sample values to show the scientific notation values) :-

   index    Apple
    1655    1e+13
    1656    1e+13
    1657    1e+13
    1658    NaN
    1659    NaN
    df=pd.pivot_table(dfe,index=['ID','CATEG','LEVEL'],columns=['COLS'],values=['VALUE'])
    df= df.fillna(0).astype(np.int64)
    with pd.ExcelWriter('file.xlsx',options={'nan_inf_to_errors': True}) as writer :
            df.groupby('ID').apply(lambda x: x.dropna(how='all', axis=1).to_excel(writer,sheet_name=str(x.name),na_rep=0,index=True))
    writer.save()

What should I do to get rid of scientific formatting and get it displayed as a number in excel. Also is there a way to autofit the columns in excel while writing from python to excel.Im using pd.ExcelWriter to write to excel

Answer 1

You can use set_option to avoid scientific formating

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

Also you can change column type with this command:

df['col'] = df[['col']].fillna(0)
df['col'] = df['col'].astype(int)

Answer 2

The following code generates a Dataframe with two columns. One with text and one with numbers and writes them to excel and changes the format so in order for excel to display the numbers as dot separated integers rather than in scientific notation. If you prefer a different format I am sure you can adopt the format string in the code. I hope that's what you're looking for :).

col1 = [float("nan"),*np.random.randint(10**15,size=5)]
col2 = [random.choice(["apple", "orange"]) for _ in range (6)]

with pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter') as writer:
    pd.DataFrame(data={"numbers":col1, "strings":col2})\
        .to_excel(writer)
    
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    format_ = workbook.add_format({'num_format': '#,##'})
    worksheet.set_column('B:B', None, format_)

For your specific example try:

with pd.ExcelWriter('file.xlsx',options={'nan_inf_to_errors': True}) as writer:
        df.groupby('ID').apply(lambda x: x.dropna(how='all', axis=1).to_excel(writer,sheet_name=str(x.name),na_rep=0,index=True))
    workbook  = writer.book
    worksheet = writer.sheets['Sheet1']
    format_ = workbook.add_format({'num_format': '#,##'})
    worksheet.set_column('C:C', None, format_)
    worksheet.set_column('E:E', None, format_)

Notice that I am guessing form the other thread that your columns with numbers will be C and E. You will probably have to widen the columns for the huge numbers. Excel will write ######### for it otherwise.