将数据框导出到excel时,小数舍入和“%”符号消失

2024-03-28 23:43:14 发布

您现在位置:Python中文网/ 问答频道 /正文

导出带有四舍五入十进制值且附加到值的“%”符号的数据帧时,数据帧将消失。我的pandas版本是1.1.5和python 3.6.8。 我还使用xlsxwriter将输出输出格式化为excel,但这也不起作用-

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()

其他格式(如扩展宽度)工作正常。 为什么数字格式在这里不起作用

更新-

假设这是我的数据帧

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')]}])


Tags: to数据informatpandasdfnpcol