CODEWITHSUNDEEP
pythonplotxlsxwriter
wedjati
wedjati

Reputation: 13

Unordered and repeated values on x-axis in a chart in xlsxwriter

I am trying to plot irregular data using xlsxwriter, where the x values are unordered and not unique, for example 0, 1, 0, -1, 0. Using the chart type 'line' and adding the series with the options containing both 'categories' (for x-axis) and 'values' (y-axis) results in a chart with the x-axis labels in the original order 0, 1, 0, -1, 0, however I would like the plot to have a regular x-axis of -1, 0 and 1, with the plotted line forming a closed area. Is this possible to achieve with xlsxwriter? Simply making a plot from similar data within Excel 2016 results in the desired behaviour.

Upvotes: 1

Views: 818

Answers (2)

jmcnamara
jmcnamara

Reputation: 41644

In Excel the line chart type that you used can only plot fixed categories. What you need to use is the scatter chart type which is used for plotting X-Y data.

Something like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet()

# Create a new Chart object.
chart = workbook.add_chart({'type': 'scatter', 
                            'subtype': 'straight_with_markers'})

# Write some data to add to plot on the chart.
worksheet.write_column('A1', [0, 1, 0, -1, 0])
worksheet.write_column('B1', [1, 2, 3,  2, 1])

# Configure the charts.
chart.add_series({
    'categories': '=Sheet1!$A$1:$A$5',
    'values':     '=Sheet1!$B$1:$B$5',
})

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

workbook.close()

Output: enter image description here

Upvotes: 1

Falloutcoder
Falloutcoder

Reputation: 1011

Here you can find examples of stuff that can be done using xlswriter. Another package is openpyxl which is used for excel files manipulation and can be used for charts. Details for that are here.

Upvotes: 1

Related Questions