Secondary y-axis with scatter plot

[severine B.]

Hello all;

I'm trying  to create a scatter plot with 2 series having different y scale. Series 1 will be on primary axis, and serie 2 on secondary axis.
 I followed the example given in https://openpyxl.readthedocs.io/en/stable/charts/secondary.html
However, with scatter graph, the 2 series have the same primary axis.

My code is below :

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

wb = Workbook()
ws = wb.active

rows = [
    ['Aliens', 2, 3, 4, 5, 6, 7],
    ['Humans', 10, 40, 50, 20, 10, 50],
    ['x', 1,3,4,5,8, 10]
]

for row in rows:
    ws.append(row)

c1 = ScatterChart()
v1 = Reference(ws, min_col=2, min_row=1, max_col=7)
x1= Reference(ws, min_col=2, min_row=3, max_col=7)
#c1.add_data(v1, titles_from_data=True, from_rows=True)
series = Series(v1, x1)
c1.series.append(series)


## Create a second chart
c2 = ScatterChart()
v2 = Reference(ws, min_col=2, min_row=2, max_col=7)
x2 = Reference(ws, min_col=2, min_row=3, max_col=7)
series2 = Series(v2, x2)
c2.series.append(series2)


## 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")

Any idea ?
Thanks for your help;

Séverine

[Charlie Clark]

Am .07.2019, 11:15 Uhr, schrieb severine B.
<simone.yvo...@gmail.com>:

Hi Séverine,

> Any idea ?

Something very similar came up recently on StackOverflow:

https://stackoverflow.com/questions/56772895/openpyxl-not-showing-second-graph

I think you have to be particularly careful about the source of data for
the x-axis as you can only share axes that are of the same type.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[severine B.]

Hi Charlie;

Thanks for your help. It turns out that the x-axis is ok, even if I  created an unnecessary additional variable x2 to plot against v2 (could have used x1 instead). The problem is that I forgot to modify axId for the 2nd chart.
By adding :

c2.y_axis.axId = 200

I got the correct display.
By the way, any detail on the meaning of this axId variable ?  I couldn't find many explanation on the help.

Below is the working code :
 

c1 = ScatterChart()
v1 = Reference(ws, min_col=2, min_row=1, max_col=7)
x1= Reference(ws, min_col=2, min_row=3, max_col=7)
#c1.add_data(v1, titles_from_data=True, from_rows=True)
series = Series(v1, x1)

series.SeriesLabel = rows[0][0]
c1.series.append(series)

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

## Create a second chart
c2 = ScatterChart()
v2 = Reference(ws, min_col=2, min_row=2, max_col=7)

series2 = Series(v2, x1)
series2.graphicalProperties
c2.series.append(series2)
c2.y_axis.axId = 200
c2.y_axis.title = "Humans"

## 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

#c1.series.append(series2)

ws.add_chart(c1, "D4")

Séverine                    

Le jeudi 4 juillet 2019 11:28:01 UTC+2, Charlie Clark a écrit :

Am .07.2019, 11:15 Uhr, schrieb severine B.  

<simone.yv...@gmail.com>:

[Charlie Clark]

Am .07.2019, 15:49 Uhr, schrieb severine B.
<simone.yvo...@gmail.com>:

>

> Hi Charlie;
>
> Thanks for your help. It turns out that the x-axis is ok, even if I
> created an unnecessary additional variable x2 to plot against v2 (could
> have used x1 instead). The problem is that I forgot to modify axId for
> the
> 2nd chart.
> By adding :
> c2.y_axis.axId = 200
> I got the correct display.
> By the way, any detail on the meaning of this axId variable ? I couldn't
> find many explanation on the help.

Glad you got it working. There is no real meaning for the axId, it just
has to be unique for each access and is referred to from several different
elements such as when axes cross. As a result openpyxl has a convention of
10, 100, 500 and 1000 as default values for different axes types, because
it makes some things easier. Normally you don't have to worry about the
details, but you do for this kind of chart.

[severine B.]

Ok, thanks Charlie for the explanation.

Le jeudi 4 juillet 2019 15:56:55 UTC+2, Charlie Clark a écrit :

Am .07.2019, 15:49 Uhr, schrieb severine B.  

<simone.yv...@gmail.com>: