如何避免DataFrame复制到Excel文件时发生格式变更？

我使用以下Python代码将DataFrame内容导出到Excel文件：

unified_dataframe.to_excel('FinancialAnalysis_'+timestr+'_.xlsx', sheet_name='Banks', index=False)

原本DataFrame前两列为文本格式，其余列为float格式，但导出到Excel后所有列都变为文本格式。请问如何避免这种情况，或是将Excel中目标列（排除表头）从文本转为float？

补充说明 #

查看unified_dataframe.dtypes时发现类型变化问题：
初始DataFrame的类型为：

Financial KPI                                      object
Year                                               object
BAC (All numbers in thousands)                    float64
C (All numbers in thousands)                      float64
CS (Currency in CHF. All numbers in thousands)    float64
JPM (All numbers in thousands)                    float64
WFC (All numbers in thousands)                    float64
dtype: object

但执行新增平均值列并格式化数字的代码后，所有数值列类型都变为object：

unified_dataframe['Average'] = unified_dataframe[company_list_average].mean(axis=1)
for i in list_of_columns:
    unified_dataframe.loc[:, i] = unified_dataframe[i].map('{:,.0f}'.format)

修改后的dtypes：

Financial KPI                                     object
Year                                              object
BAC (All numbers in thousands)                    object
C (All numbers in thousands)                      object
CS (Currency in CHF. All numbers in thousands)    object
JPM (All numbers in thousands)                    object
WFC (All numbers in thousands)                    object
Average                                           object
dtype: object

DataFrame前几行数据示例：

Financial KPI        Year  ... WFC (All numbers in thousands)     Average
0  Total Revenue         TTM  ...                     74,981,000  91,295,750
1  Total Revenue  12/31/2021  ...                     78,492,000  90,294,250
2  Total Revenue  12/31/2020  ...                     72,340,000  88,209,250
3  Total Revenue  12/31/2019  ...                     85,063,000  91,750,250
4  Total Revenue  12/31/2018  ...                     86,408,000  90,180,000

问题根源 #

你用map('{:,.0f}'.format)把数值列转换为带千分位分隔符的字符串，导致所有数值列类型变成object，导出到Excel后自然显示为文本格式。

方法1：导出时用xlsxwriter设置格式（推荐） #

保留DataFrame的数值类型，在导出Excel时直接设置单元格的显示格式，既能保留数值特性，又能显示千分位：

1. 先安装xlsxwriter：

pip install xlsxwriter

2. 修改导出代码：

import pandas as pd

# 创建Excel写入器，指定引擎为xlsxwriter
writer = pd.ExcelWriter('FinancialAnalysis_'+timestr+'_.xlsx', engine='xlsxwriter')
unified_dataframe.to_excel(writer, sheet_name='Banks', index=False)

# 获取工作簿和工作表对象
workbook = writer.book
worksheet = writer.sheets['Banks']

# 创建带千分位的数值格式
num_format = workbook.add_format({'num_format': '#,##0'})

# 获取所有数值列的索引位置（前两列是文本，其余为数值列+Average列）
num_col_names = list_of_columns + ['Average']
num_col_indices = [unified_dataframe.columns.get_loc(col) for col in num_col_names]

# 为每个数值列设置格式和列宽
for col_idx in num_col_indices:
    worksheet.set_column(col_idx, col_idx, 22, num_format)

# 保存并关闭写入器
writer.close()

方法2：Excel手动转换格式（适合少量数据） #

如果已经导出了Excel文件，可直接在Excel中操作：

• 选中需要转换的目标列
• 按下Ctrl+1打开单元格格式窗口
• 选择「数字」→「数值」，勾选「使用千位分隔符」，点击确定即可

方法3：修复DataFrame类型后再导出 #

如果需要先修正DataFrame的类型，再导出：

# 定义转换函数：去掉字符串中的逗号，转为float
def str_to_float(s):
    return float(s.replace(',', ''))

# 对所有数值列进行类型转换
for col in list_of_columns + ['Average']:
    unified_dataframe[col] = unified_dataframe[col].apply(str_to_float)

# 此时导出Excel，数值列会保持float类型
unified_dataframe.to_excel('FinancialAnalysis_'+timestr+'_.xlsx', sheet_name='Banks', index=False)

注意：这种方法会丢失千分位显示，导出后需要在Excel中手动设置格式。

内容的提问来源于stack exchange，提问作者rcmv