英文:
Preventing overlapping labels in a pie chart Python Xlsxwriter module
问题
任务: 我正在使用xlsxwriter将数据框/表格写入Excel文件,并添加有见地的图表。为了写入Excel文件,我正在使用xlsxwriter来添加图表和其他内容。
我已经附上了一个示例输出,这是我目前得到的输出。
从百分比参考生成饼图。为了将百分比添加到Excel文件中,我设置了公式。所以我没有参考值。
我尝试了不同的配置,但没有得到令人满意的结果。
这是一个示例代码,用来获取同样的图表以及我目前面临的相同问题。数据点重叠。
import xlsxwriter
# 创建一个工作簿并添加一个工作表
workbook = xlsxwriter.Workbook('pie_chart.xlsx')
worksheet = workbook.add_worksheet()
# 向工作表添加数据
data = [84, 11, 2, 0, 2]
labels = ['A', 'B', 'C', 'D', 'E']
worksheet.write_column('A1', data)
worksheet.write_column('B1', labels)
# 创建一个饼图
pie_chart = workbook.add_chart({'type': 'pie'})
pie_chart.add_series({
'name': '饼图',
'categories': '=Sheet1!$B$1:$B$5',
'values': '=Sheet1!$A$1:$A$5',
'data_labels': {'percentage': True},
})
# 将饼图插入工作表
worksheet.insert_chart('D1', pie_chart)
# 保存工作簿
workbook.close()
当前输出:
期望输出:
没有数据点重叠的饼图。
英文:
Task: I am writing the dataframe/tables into excel file with the insightful charts. To write into excel file I am using xlsxwriter for adding charts and everything.
I have attached one sample output which currently I am getting.
Generated the pie chart from the percentages reference. For adding percentages into the excel file I have set the formulas. So I don't have any reference to the values.
I tried different configurations but I didn't get any satisfactory results.
Here, is one sample code to get the same chart with the same problem which currently I am facing.
The data points are overlapping.
import xlsxwriter
# Create a workbook and add a worksheet
workbook = xlsxwriter.Workbook('pie_chart.xlsx')
worksheet = workbook.add_worksheet()
# Add the data to the worksheet
data = [84,11,2,0,2]
labels = ['A', 'B', 'C', 'D', 'E']
worksheet.write_column('A1', data)
worksheet.write_column('B1', labels)
# Create a pie chart
pie_chart = workbook.add_chart({'type': 'pie'})
pie_chart.add_series({
'name': 'Pie Chart',
'categories': '=Sheet1!$B$1:$B$5',
'values': '=Sheet1!$A$1:$A$5',
'data_labels': {'percentage': True},
})
# Insert the pie chart into the worksheet
worksheet.insert_chart('D1', pie_chart)
# # Save the workbook
workbook.close()
Expected output:
The pie chart without data points overlapping.
答案1
得分: 3
只要明确一点,XlsxWriter 不会重叠或定位标签,而是在渲染文件格式时由 Excel 完成。
通常情况下,Excel 在渲染图表时会尽量避免标签重叠,但如果数据段非常小或距离很近,可能会发生重叠。您可以通过指定引导线和 best_fit
定位来改善一些情况。类似于以下代码:
import xlsxwriter
# 创建工作簿并添加工作表
workbook = xlsxwriter.Workbook('pie_chart.xlsx')
worksheet = workbook.add_worksheet()
# 将数据添加到工作表
data = [84, 11, 2, 0, 2]
labels = ['A', 'B', 'C', 'D', 'E']
worksheet.write_column('A1', data)
worksheet.write_column('B1', labels)
# 创建饼图
pie_chart = workbook.add_chart({'type': 'pie'})
pie_chart.add_series({
# 'name': 'Pie Chart',
'categories': '=Sheet1!$B$1:$B$5',
'values': '=Sheet1!$A$1:$A$5',
'data_labels': {'percentage': True, 'leader_lines': True, 'position': 'best_fit'},
})
# 将饼图插入工作表
worksheet.insert_chart('D1', pie_chart)
# 保存工作簿
workbook.close()
在 Excel 中,还可以手动调整数据标签的位置以进行更精细的调整,但 XlsxWriter 不支持此功能。
英文:
Just to be clear, XlsxWriter isn't overlapping or positioning the labels, Excel is doing it when it renders the file format.
In general Excel tries to avoid overlapping labels when rendering charts but it can happen if the data segments are very small or close together. You can maybe make it a bit better by specifying leader lines and best_fit
positioning. Something like this:
import xlsxwriter
# Create a workbook and add a worksheet
workbook = xlsxwriter.Workbook('pie_chart.xlsx')
worksheet = workbook.add_worksheet()
# Add the data to the worksheet
data = [84,11,2,0,2]
labels = ['A', 'B', 'C', 'D', 'E']
worksheet.write_column('A1', data)
worksheet.write_column('B1', labels)
# Create a pie chart
pie_chart = workbook.add_chart({'type': 'pie'})
pie_chart.add_series({
#'name': 'Pie Chart',
'categories': '=Sheet1!$B$1:$B$5',
'values': '=Sheet1!$A$1:$A$5',
'data_labels': {'percentage': True,
'leader_lines': True,
'position': 'best_fit'},
})
# Insert the pie chart into the worksheet
worksheet.insert_chart('D1', pie_chart)
# # Save the workbook
workbook.close()
Output:
In Excel it is also possible to manually position the data labels for finer grained adjustment but that isn't supported by XlsxWriter.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论