Search code examples
pythonexcelopenpyxlscatter

Python openpyxl scatter plots with secondary y-axis

Does anyone have an example of Python code that creates a secondary y-axis in Excel using Scatter plots? Even if it's just something simple, it would help me tremendously. I'm plotting multiple series in Excel, using openpyxl, and would like to have some of them associated to the left axis and some associated to the right axis. I tried working from the openpyxl Aliens/Humans example but it uses a Bar chart and Line chart, and I just can't seem to get it adapted to work with a Scatter plot.

Thank you in advance! Melissa

Solution

  • The scatter chart looks much like a line chart in openpyxl because the scatter chart is just a list of points with lines in between. To remove the lines, set the line style to noFill. Also - for the scatter chart, you need an additional series of data for the X coordinates (row 1 in excel).

    Try this code:

    from openpyxl import Workbook
from openpyxl.chart import (
    LineChart,
    BarChart,
    ScatterChart,
    Reference,
    Series,
)

wb = Workbook()
ws = wb.active

rows = [
    ['Index', 1,2,3,4,5,6],  # needed as X coordinates for scatter chart
    ['Aliens', 2, 3, 4, 5, 6, 7],  # aliens Y coordinates
    ['Humans', 10, 40, 50, 20, 10, 50]  # humans Y coordinates
]

for row in rows:
    ws.append(row)


# Aliens
c1 = ScatterChart()

xvalues = Reference(ws, min_col=1, min_row=1, max_col=7)
values = Reference(ws, min_col=1, min_row=2, max_col=7)
series = Series(values, xvalues, title_from_data=True)
c1.series.append(series)

s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True  # hide lines

c1.style = 13
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.x_axis.majorGridlines = None
c1.title = 'Survey results'


# Humans
c2 = ScatterChart()

xvalues = Reference(ws, min_col=1, min_row=1, max_col=7)
values = Reference(ws, min_col=1, min_row=3, max_col=7)
series = Series(values, xvalues, title_from_data=True)
c2.series.append(series)

s2 = c2.series[0]
s2.marker.symbol = "diamond"
s2.marker.graphicalProperties.solidFill = "0000FF" # Marker filling
s2.marker.graphicalProperties.line.solidFill = "0000FF" # Marker outline
s2.graphicalProperties.line.noFill = True  # hide lines

c2.style = 13
c2.x_axis.title = 'Days'
c2.y_axis.title = 'Humans'
c2.y_axis.axId = 200
c2.y_axis.majorGridlines = None
c2.x_axis.majorGridlines = None


# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2

ws.add_chart(c1, "D4")

wb.save("secondary.xlsx")

    Output (in Excel)

    ScatterChart