从 Pandas 写入 Excel 时设置默认数字格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51068361/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Setting default number format when writing to Excel from Pandas
提问by ChrisG
I'm looking to set the default number format when writing to Excel from a Pandas dataframe. Is this possible?
我希望在从 Pandas 数据帧写入 Excel 时设置默认数字格式。这可能吗？
I can set the default date/datetime_format with the following, but couldn't find a way to set the default number format.
我可以使用以下内容设置默认日期/日期时间格式，但找不到设置默认数字格式的方法。
writer = pd.ExcelWriter(f'{file_variable}.xlsx', engine='xlsxwriter',datetime_format='MM/DD/YYYY')
Otherwise, I assume I'm going to have to assign worksheets to variables and loop through the rows for the specified columns to set the number format.
否则，我假设我将不得不将工作表分配给变量并遍历指定列的行以设置数字格式。
采纳答案by Dickster
I got this format the floats to 1 decimal place.
我得到了这种格式的浮点数到小数点后一位。
data = {'A Prime': {0: 3.26, 1: 3.24, 2: 3.22, 3: 3.2, 4: 3.18, 5: 3.16,
6: 3.14, 7: 1.52, 8: 1.5, 9: 1.48, 10: 1.46, 11: 1.44, 12: 1.42},
'B': {0: 0.16608, 1: 0.16575, 2: 0.1654, 3: 0.16505999999999998, 4: 0.1647, 5: 0.16434, 6: 0.16398, 7: 0.10759, 8: 0.10687, 9: 0.10614000000000001,
10: 0.10540999999999999, 11: 0.10469, 12: 0.10396}, 'Proto Name': {0: 'Alpha',
1: 'Alpha', 2: 'Alpha', 3: 'Alpha', 4: 'Alpha', 5: 'Alpha', 6: 'Alpha', 7: 'Bravo', 8: 'Bravo', 9: 'Bravo', 10: 'Bravo', 11: 'Bravo', 12: 'Bravo'}}
import pandas as pd
df = pd.DataFrame(data)
A Prime B Proto Name
0 3.26 0.16608 Alpha
1 3.24 0.16575 Alpha
2 3.22 0.16540 Alpha
3 3.20 0.16506 Alpha
4 3.18 0.16470 Alpha
5 3.16 0.16434 Alpha
6 3.14 0.16398 Alpha
7 1.52 0.10759 Bravo
8 1.50 0.10687 Bravo
9 1.48 0.10614 Bravo
10 1.46 0.10541 Bravo
11 1.44 0.10469 Bravo
12 1.42 0.10396 Bravo
writer = pd.ExcelWriter(r'c:\temp\output.xlsx')
# This method will truncate the data past the first decimal point
df.to_excel(writer,'Sheet1',float_format = "%0.1f")
writer.save()
but that alas is not perhaps all cases - no joy with say larger numbers and thousands separator
但这可能不是所有情况 - 说更大的数字和千位分隔符并不快乐
df.to_excel(writer,'Sheet1',float_format = ":,")
However the following seems to work
但是以下似乎有效
data = {'A Prime': {0: 326000, 1: 3240000}}
df = pd.DataFrame(data)
A Prime
0 326000
1 3240000
writer = pd.ExcelWriter(r'c:\temp\output.xlsx')
df.to_excel(writer,'Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
format1 = workbook.add_format({'num_format': '#,##0.00'})
worksheet.set_column('B', 18, format1)
#Alternatively, you could specify a range of columns with 'B:D' and 18 sets the column width
writer.save()
All taken from here: http://xlsxwriter.readthedocs.io/working_with_pandas.html
全部取自这里：http: //xlsxwriter.readthedocs.io/working_with_pandas.html
回答by Charlie Clark
For what it's worth and because the question was also tagged for openpyxl, you can also also edit the default style of a whole workbook in openpyxl. This could make sense for the number format but can have unexpected consquences if things like the font size is changed, because other GUI elements are affected. The following should work, if used with caution.
对于它的价值，并且因为该问题也被标记为 openpyxl，您还可以在 openpyxl 中编辑整个工作簿的默认样式。这对于数字格式可能有意义，但如果更改字体大小等内容可能会产生意想不到的后果，因为其他 GUI 元素会受到影响。如果谨慎使用，以下应该有效。
wb._named_styles['Normal'].number_format = '#,##0.00'
