xlsxwriter/python - Creating a chart with Python by indexing Series values

Question

I've written a python script that generates several data frames, then sums them together into one final dataframe. The final dataframe, df is then written into an Excel sheet (along with many other sheets that contain the dataframes written before) in an Excel Workbook. The final data frame is a value given to several names per month, something like this:

df = 
                 Apr 18  May 18  Jun 18
    Character                                                        
    Sonic          75.0    23.0    23.0
    Shadow        100.0    38.0    38.0
    Amy           100.0    40.0    40.0
    Tails         100.0    40.0    40.0
    Knuckles       91.0    36.0    37.0


# where {$A1: 'Character', $B$1: 'Apr 18', ...} and so on...

I'm also pretty new to how xlsxwriter works too. I think my issue is that I've had trouble properly assigning what I assume the values argument for my series to a chart.

Basic code that I've utilized to sort of plot April/test out as the basis for a possible for loop is here:

workbook = writer.book
worksheet = writer.sheets['FINAL VALUES']

chart = workbook.add_chart({'type': 'line'})
chart.add_series({
    'name':       '=FINAL VALUES!$B$1',
    'categories': '=FINAL VALUES!$A$2:$A$5',
    'values':     '=FINAL VALUES!$B$2:$B$5',})

chart.set_title ({'name': 'Line Plot Test'})
chart.set_x_axis({'name': 'Month'})
chart.set_y_axis({'name': 'Value'})

worksheet.insert_chart('D2', chart)

How can I index for my series, into a previously created excel sheet's cells with a for loop to create a line plot of 5 lines with 3 points each?

I would like something like this, I'm only showing Sonic and Shadow in this example (excuse my horrendous Paint Skills).

score 2 · Accepted Answer · answered Jul 25 '18 at 21:47

You mentioned the following in your post:

into a previously created excel sheet's cells

The xlsxwriter docs state: "XlsxWriter is a Python module for writing files in the Excel 2007+ XLSX file format. ..... It cannot read or modify existing Excel XLSX files." (Link Here). So you cannot modify an existing .xlsx file using xlsxwriter.

If you're okay with creating an original .xlsx file, then with xlsxwriter it's relatively straightforward to create the type of Chart you are looking for. I've provided a fully reproducible example of this below.

import pandas as pd

df = pd.DataFrame({'Character': ['Sonic','Shadow','Amy','Tails','Knuckles'],
                  'Apr 18': [75,90,80,75,60],
                  'May 18': [23,30,60,50,37],
                  'Jun 18': [23,20,26,40,35]})

df = df[['Character','Apr 18','May 18','Jun 18']]

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

df.to_excel(writer, sheet_name='FINAL VALUES', index=False)

workbook = writer.book
worksheet = writer.sheets['FINAL VALUES']

chart = workbook.add_chart({'type': 'line'})

for i in range(2, len(df) + 2):
    chart.add_series({
    'categories': "='FINAL VALUES'!$B$1:$D$1",
    'name': "='FINAL VALUES'!$A$%d" % (i),
    'values': "='FINAL VALUES'!$B$%d:$D$%d" % (i, i),
    'line': {
    'width': 1.75,
    },
    'marker': {'type': 'circle'},
    'data_labels': {'value': True},
    })

chart.set_title ({'name': 'Line Plot Test'})
chart.set_x_axis({'name': 'Month'})
chart.set_y_axis({'name': 'Value'})

worksheet.insert_chart('D7', chart)

writer.save()

Expected Output:

Good answer. One improvement you could add is to use the list syntax for [add_series()](https://xlsxwriter.readthedocs.io/chart.html#chart-add-series) instead of the `$A1:$A5` style syntax. It is intended for this purpose: where the ranges are set programmatically. — jmcnamara, Jul 26 '18 at 08:16
Thank you both for your answers! I was able to use patrickjlong1 's suggestion for string encoding to loop any excel output the user creates with my program. Also, an output a user creates with my program may have more than 3 columns of data, so I used @jmcnamara 's suggestion -- I had to create an [OrderedDict](https://stackoverflow.com/questions/19030179/how-to-access-the-first-and-the-last-elements-in-a-dictionary-python) then convert it into a list with tuples, so I can tell the series to go to the last column). Thanks a lot! — florence-y, Jul 26 '18 at 15:22

xlsxwriter/python - Creating a chart with Python by indexing Series values

1 Answers1