xlsxwriter - Excel饼图的图例 - python

I have an excel file with 6 tabs (worksheets). Each worksheet is of the same structure and contains two columns - Col 1 contains brand names and Col 2 contains values corresponding to each brand. For each sheet in the the excel file, I want to make a pie chart showing % share for each brand.

我有一个带有6个选项卡(工作表)的excel文件。每个工作表具有相同的结构并包含两列 - Col 1包含品牌名称,Col 2包含与每个品牌对应的值。对于excel文件中的每个工作表,我想制作一个饼图,显示每个品牌的%份额。

The example xls file you can use to run the script on is here

您可以使用示例xls文件来运行脚本

The code i wrote is quite simple and generates the charts. The problem is that the legend for the chart takes serial number names instead of the names of the brand.

我写的代码非常简单,并生成图表。问题是图表的图例采用序列号名称而不是品牌名称。

import pandas as pd
import xlsxwriter as excel

       df = pd.read_excel("/Users/jack/Documents/python-pptx/filename", sheetname=None)


        workbook = excel.Workbook('/Users/jack/Documents/python-pptx/chart_pie.xlsx')

        for sheetname, data in df.iteritems():
            if len(data) > 0: 
                worksheet = workbook.add_worksheet(sheetname)
                chart = workbook.add_chart({'type': 'pie'})
                worksheet.write_column('A1', data['Brand'])
                worksheet.write_column('B1', data['Share_of_interactions'])
                chart.add_series({'categories': '='+sheetname+'!$A$1:$A$'+str(len(data)),
                                  'values':     '='+sheetname+'!$B$1:$B$'+str(len(data)),
                                  'name':       '='+sheetname+'!$A$1:$A$'+str(len(data))})

                ## insert chart into the worksheet
                worksheet.insert_chart('C3', chart)

                ## Close the workbook
        workbook.close()

Here is a screen shot of the chart :

这是图表的屏幕截图:

IF you notice in the chart the legend says 1, 2, 3 .. . . 7 . It actually should be saying the brand name . I've added the name parameter to chart.add_series as mentioned in the documentation of xlsxwriter - http://xlsxwriter.readthedocs.io/chart.html . Any help would be much appreciated.

如果您在图表中注意到图例中的1,2,3 .. 。 7。它实际应该是品牌名称。我已将name参数添加到chart.add_series,如xlsxwriter文档中所述 - http://xlsxwriter.readthedocs.io/chart.html。任何帮助将非常感激。

2 个解决方案

#1

The problem is that you have a space in your sheet name, like Sheet 1. You need to enclose it in single quotes:

问题是您的工作表名称中有一个空格,如工作表1.您需要将其用单引号括起来:

df = pd.read_excel("/Users/julien/Downloads/SO_Example_Df.xlsx", sheetname=None)


workbook = excel.Workbook('/Users/julien/Downloads/SO_chart_pie.xlsx')

for sheetname, data in df.items():
    if len(data) > 0: 
        worksheet = workbook.add_worksheet(sheetname)
        chart = workbook.add_chart({'type': 'pie'})
        worksheet.write_column('A1', data['Brand'])
        worksheet.write_column('B1', data['Share_of_interactions'])
        # Here, add single quotes around the sheetname
        chart.add_series({'categories': "='"+sheetname+"'!$A$1:$A$"+str(len(data)),
                          'values':     "='"+sheetname+"'!$B$1:$B$"+str(len(data)),
                          'name':       'My pie chart'})

        ## insert chart into the worksheet
        worksheet.insert_chart('C3', chart)

## Close the workbook
workbook.close()

#2

In Excel, and in XlsxWriter, the names of the data points in a Pie Chart come from the "Categories". This is different from other "2D" chart types where the names come from the series name. This is because a pie chart is a special case of a single series chart.

在Excel和XlsxWriter中,饼图中数据点的名称来自“类别”。这与名称来自系列名称的其他“2D”图表类型不同。这是因为饼图是单个系列图表的特例。

Anyway, if you point your Categories to the names you want they will be displayed. Like this:

无论如何,如果您将类别指向您想要的名称,它们将被显示。像这样:

import pandas as pd

# Some sample data to plot.
data = {'apples': 10, 'berries': 32, 'squash': 21, 'melons': 13, 'corn': 18}

# Create a Pandas dataframe from the data.
df = pd.DataFrame([data], index=['Farm'])

# Create a Pandas Excel writer using XlsxWriter as the engine.
excel_file = 'pie.xlsx'
sheet_name = 'Sheet1'

writer = pd.ExcelWriter(excel_file, engine='xlsxwriter')
df.to_excel(writer, sheet_name=sheet_name)

# Access the XlsxWriter workbook and worksheet objects from the dataframe.
workbook = writer.book
worksheet = writer.sheets[sheet_name]

# Create a chart object.
chart = workbook.add_chart({'type': 'pie'})

# Configure the chart from the dataframe data.
chart.add_series({
    'categories': ['Sheet1', 0, 1, 0, 5],
    'values':     ['Sheet1', 1, 1, 1, 5],
})

# Insert the chart into the worksheet.
worksheet.insert_chart('A4', chart)

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

Also, note the use of the list for the Categories and Values instead of range strings. This optional format is easier where dealing with variable data and it handles any sheet name quoting.

另外,请注意使用类别和值的列表而不是范围字符串。在处理可变数据时,此可选格式更容易处理任何工作表名称引用。

Output:

#1