xlsxwriter combining chart types

Question

I have an excel chart that has the following scheme

    A    B          C           D

   ID   Reading 1   Reading 2   Avg Reading 1 
   0    4.2         6.7          3.98
   1    4.4         8.8 
   2    4.5         9   
   3    5.6         4   
   4    1.2         4.5 

I'm able to draw a histogram with Reading 1 and Reading 2.

chart_1 = workbook.add_chart({'type': 'column'})
chart_1.add_series({
        'name':       '=Sheet1!$B$1',
        'categories': '=Sheet1!$A$2:$A$4,
        'values':     '=Sheet1!$B$2:$B$4,
    })

I want to overlay a line representing Avg Reading 1 across the histogram. How do I do this using python and xlsxwriter ?

Answer 1

Excel and XlsxWriter support Trendlines in charts. However, there is only a moving average option and not a fixed average option (which make sense given that it is meant to be for trends).

The usual way that you would do something like this in Excel is to add a line chart with the average on top of the histogram.

Here is a small working example based on your data:

from xlsxwriter.workbook import Workbook

workbook  = Workbook('chart_combined.xlsx')
worksheet = workbook.add_worksheet()

# Add a format for the headings.
bold = workbook.add_format({'bold': True})

# Make the columns wider.
worksheet.set_column('B:C', 12)

# Add the worksheet data that the charts will refer to.
headings = ['ID', ' Reading 1', ' Avg Reading 1']
data = [
    [0,    1,    2,    3,    4],
    [4.2,  4.4,  4.5,  5.6,  1.2],
    [3.98, 3.98, 3.98, 3.98, 3.98],
]
worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

# Create a combined column and line chart that share the same X and Y axes.

# First create a column chart. This will use this as the primary chart.
column_chart = workbook.add_chart({'type': 'column'})

# Configure the data series for the primary chart.
column_chart.add_series({
    'name':       '=Sheet1!$B$1',
    'categories': '=Sheet1!$A$2:$A$6',
    'values':     '=Sheet1!$B$2:$B$6',
})

# Create a line chart. This will use this as the secondary chart.
line_chart = workbook.add_chart({'type': 'line'})

# Configure the data series for the secondary chart.
line_chart.add_series({
    'name':       '=Sheet1!$C$1',
    'categories': '=Sheet1!$A$2:$A$6',
    'values':     '=Sheet1!$C$2:$C$6',
})

# Combine the charts.
column_chart.combine(line_chart)

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

workbook.close()

Output:

Note, this adds an average point for each data point in the first series. It would be possible to just do this with two points if you prefer.

Also, rather that calculate the averages yourself you could let Excel do it using a formula. For example with this change to the above program:

# Add the worksheet data that the charts will refer to.
headings = ['ID', ' Reading 1', ' Avg Reading 1']
data = [
    [0,    1,    2,    3,    4],
    [4.2,  4.4,  4.5,  5.6,  1.2],
    ['=AVERAGE($B$2:$B$6)'] * 5,
]

See also the section of the docs on combined charts in XlsxWriter.