用Pandas数据框和XlsxWriter添加千位分隔符

Question

据我所知，Xlsxwriter可能是最适合使用千位分隔符格式化数字的包。我已经多次阅读了xlsxwriter文档，但仍然感到非常困惑，我认为其他人可能也有同样的问题，因此我在这里发表我的问题。我有一个pandas数据框DF_T_1_EQUITY_CHANGE_Summary_ADE，并且想将它们导出到Excel并使用千位分隔符格式。

Row Labels               object
Sum of EQUITY_CHANGE    float64
Sum of TRUE_PROFIT      float64
Sum of total_cost       float64
Sum of FOREX VOL        float64
Sum of BULLION VOL      float64
Oil                     float64
Sum of CFD VOL           object
Sum of BITCOIN VOL       object
Sum of DEPOSIT          float64
Sum of WITHDRAW         float64
Sum of IN/OUT           float64
dtype: object

数据框 DF_T_1_EQUITY_CHANGE_Summary_ADE 很清晰，除了第一列的 Row Labels 是对象类型，其他都是数字。 因此，我使用 xlsxwriter 将数据框写入 Excel：

import xlsxwriter 
num_fmt = workbook.add_format({'num_format': '#,###'}) #set the separator I want
writer = pd.ExcelWriter('ADE_CN.xlsx', engine='xlsxwriter')
DF_T_1_EQUITY_CHANGE_Summary_ADE.to_excel(writer, sheet_name='Sheet1')
workbook=writer.book
worksheet = writer.sheets['Sheet1']
worksheet.set_column('C:M', None, num_fmt)
writer.save()

然而，我无法获得千位分隔符。在Excel中的结果如下：

    Row Labels  Sum of EQUITY_CHANGE    Sum of TRUE_PROFIT  Sum of total_cost   Sum of FOREX VOL    Sum of BULLION VOL  Oil Sum of CFD VOL  Sum of BITCOIN VOL  Sum of DEPOSIT  Sum of WITHDRAW Sum of IN/OUT
0   ADE A BOOK USD  778.17  517.36  375.9   37.79   0.33    0   0   0   1555.95 0   1555.95
1   ADE B BOOK USD  6525.51 403.01  529.65  35.43   14.3    0   0   0   500 -2712.48    -2212.48
2   ADE A BOOK AUD  537.7   189.63  147 12.25   0   0   0   0   0   0   0
3   ADE B BOOK AUD  -22235.71   7363.14 224.18  2.69    9.16    0.2 0   0   5000    -103    4897

有人能够提供一个解决方案吗，非常感激。

- Michael

---

在你的代码中，你要么在定义workbook之前调用它，要么在调用workbook=writer.book时重新定义。无论哪种方式，你都会失去添加的数字格式。 - James

你能给我一个编码示例吗？我还是有些困惑。 - Michael

Answer 1

应该可以工作。您需要在代码稍后获取工作簿对象后移动add_format()。这是一个例子：

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [1234.56, 234.56, 5678.92]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Set a currency number format for a column.
num_format = workbook.add_format({'num_format': '#,###'})
worksheet.set_column('B:B', None, num_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

输出：

- jmcnamara