避免在饼图中重叠标签的方法:Python Xlsxwriter 模块

huangapple go评论59阅读模式
英文:

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()

当前输出:
避免在饼图中重叠标签的方法:Python Xlsxwriter 模块

期望输出:
没有数据点重叠的饼图。

英文:

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.

避免在饼图中重叠标签的方法:Python Xlsxwriter 模块

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()

Current output:
避免在饼图中重叠标签的方法:Python Xlsxwriter 模块

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:

避免在饼图中重叠标签的方法:Python Xlsxwriter 模块

In Excel it is also possible to manually position the data labels for finer grained adjustment but that isn't supported by XlsxWriter.

huangapple
  • 本文由 发表于 2023年2月10日 14:14:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75407531.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定