When exporting my pandas dataframe with decimal values rounded-off and '%' symbol appended to values is getting disappeared. My pandas version is 1.1.5 with python 3.6.8. I have also used xlsxwriter to format output export to excel, but that is also not working-
writer = ExcelWriter('abc.xlsx',engine='xlsxwriter')
df.to_excel(writer, 'sheet1')
workbook = writer.book
worksheet = writer.sheets['sheet1']
fmt1 = workbook.add_format({'num_format':'0.0%'})
worksheet,set_column('G:G',None,fmt1)
worksheet.set_column('B:B',20)
writer.save()
other formatting like expanding width is working fine. Why number format is not working here?
Updated -
Suppose this is my dataframe
import pandas as pd
import numpy as np
header = [np.array([' ','X','X','Y','Y','Z','Z']),
np.array(['A','B','C','D','E','F','G'])]
df = pd.DataFrame(columns=header)
df[' ','A'] = ['n','n','m','m','m','p']
df['X','B'] = ['q','r','s','t','u','v']
df['X','C'] = [5,6,7,8,9,4]
df['Y','D'] = [1.5,2.9,3.6,2.5,7.1,0.4]
df['Y','E'] = [0.7,3.9,3.2,1.5,4.1,2.4]
df['Z','F'] = ['ab','bc','cd','de','ef','gh']
df['Z','G'] = ['5.5','2.6','8.6','4.5','0.1','3.4']
df.set_index(([(' ','A')]),drop=True,inplace=True)
I have applied styles on this df for dashboard, in dashboard it is coming properly but when exporting to excel the decimal round off and '%' symbol is going away -
df = (df.style.format('{:.1f}%', na_rep='-',subset=[col for col in df.columns if col[0] in ('X','Y') and col[1] in ('C','D','E')])).apply(highlight_cells, axis = None)\ .set_table_styles([{'selector':'th','props':[('border','1px solid black'),('font-size','8pt')]}])