python - xlsxwriter combining chart types -

i have excel chart has following scheme

       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 draw histogram reading 1 , 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 overlay line representing avg reading 1 across histogram. how do using python , xlsxwriter ?

excel , xlsxwriter support trendlines in charts. however, there moving average option , not fixed average option (which make sense given meant trends).

the usual way in excel add line chart average on top of histogram.

here small working example based on data:

from xlsxwriter.workbook import workbook  workbook  = workbook('chart_combined.xlsx') worksheet = workbook.add_worksheet()  # add format headings. bold = workbook.add_format({'bold': true})  # make columns wider. worksheet.set_column('b:c', 12)  # add worksheet data charts 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 combined column , line chart share same x , y axes.  # first create column chart. use primary chart. column_chart = workbook.add_chart({'type': 'column'})  # configure data series primary chart. column_chart.add_series({     'name':       '=sheet1!$b$1',     'categories': '=sheet1!$a$2:$a$6',     'values':     '=sheet1!$b$2:$b$6', })  # create line chart. use secondary chart. line_chart = workbook.add_chart({'type': 'line'})  # configure data series secondary chart. line_chart.add_series({     'name':       '=sheet1!$c$1',     'categories': '=sheet1!$a$2:$a$6',     'values':     '=sheet1!$c$2:$c$6', })  # combine charts. column_chart.combine(line_chart)  # insert chart worksheet worksheet.insert_chart('e2', column_chart)  workbook.close() 

output:

note, adds average point each data point in first series. possible 2 points if prefer.

also, rather calculate averages let excel using formula. example change above program:

# add worksheet data charts 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 section of docs on combined charts in xlsxwriter.