使用 Pandas 在 Excel 中写入百分比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18115741/
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
Writing Percentages in Excel Using Pandas
提问by rhaskett
When writing to csv's before using Pandas, I would often use the following format for percentages:
在使用 Pandas 之前写入 csv 时，我经常使用以下百分比格式：
'%0.2f%%' % (x * 100)
This would be processed by Excel correctly when loading the csv.
这将在加载 csv 时由 Excel 正确处理。
Now, I'm trying to use Pandas' to_excel function and using
现在，我正在尝试使用 Pandas 的 to_excel 函数并使用
(simulated * 100.).to_excel(writer, 'Simulated', float_format='%0.2f%%')
and getting a "ValueError: invalid literal for float(): 0.0126%". Without the '%%' it writes fine but is not formatted as percent.
并获得“ValueError：float() 的无效文字：0.0126%”。没有 '%%' 它写得很好，但没有格式化为百分比。
Is there a way to write percentages in Pandas' to_excel?
有没有办法在 Pandas 的 to_excel 中写百分比？
This question is all pretty old at this point. For better solutions check out xlsxwriter working with pandas.
在这一点上，这个问题已经很老了。要获得更好的解决方案，请查看xlsxwriter 使用 pandas。
采纳答案by Saullo G. P. Castro
You can do the following workaround in order to accomplish this:
您可以执行以下解决方法来完成此操作：
df *= 100
df = pandas.DataFrame(df, dtype=str)
df += '%'
ew = pandas.ExcelWriter('test.xlsx')
df.to_excel(ew)
ew.save()
回答by Mike Demenok
This is the solution I arrived at using pandas with OpenPyXL v2.2, and ensuring cells contain numbers at the end, and not strings. Keep values as floats, apply format at the end cell by cell (warning: not efficient):
这是我在 OpenPyXL v2.2 中使用 Pandas 并确保单元格末尾包含数字而不是字符串的解决方案。将值保留为浮点数，逐个单元格在末尾单元格应用格式（警告：效率不高）：
xlsx = pd.ExcelWriter(output_path)
df.to_excel(xlsx, "Sheet 1")
sheet = xlsx.book.worksheets[0]
for col in sheet.columns[1:sheet.max_column]:
for cell in col[1:sheet.max_row]:
cell.number_format = '0.00%'
cell.value /= 100 #if your data is already in percentages, has to be fractions
xlsx.save()
See OpenPyXL documentationfor more number formats.
有关更多数字格式，请参阅OpenPyXL 文档。
Interestingly enough, the docos suggest that OpenPyXL is smart enough to guess percentages from string formatted as "1.23%", but this doesn't happen for me. I found code in Pandas' _Openpyxl1Writer that uses "set_value_explicit" on strings, but nothing of the like for other versions. Worth further investigation if somebody wants to get to the bottom of this.
有趣的是，文档表明 OpenPyXL 足够聪明，可以从格式为“1.23%”的字符串中猜测百分比，但对我来说这不会发生。我在 Pandas 的 _Openpyxl1Writer 中找到了在字符串上使用“set_value_explicit”的代码，但其他版本没有类似的代码。如果有人想追查到底，值得进一步调查。
