Secondary axes issues for two scatterplots, openpyxl

Question

I am trying to create charts with python and openpyxl. These charts have a three series each, two series plotted on the primary axis and the other series plotted on the secondary axis.

Here is my test data: https://docs.google.com/spreadsheets/d/12RlWx8g-7S8cwtG8swz9n5I2rwaMMh5q/edit?usp=sharing&ouid=104041081140482661384&rtpof=true&sd=true

Here is my code so far:

# openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import *

# other
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# define input and output files
file_input = 'C:/LocalData/VirtualEnvironment_terraphase/Learning_TechwithTim_OpenPyXL/test_data1.xlsx'
file_output = 'C:/LocalData/VirtualEnvironment_terraphase/Learning_TechwithTim_OpenPyXL/result3.xlsx'

# create workbook
wb = Workbook()

# create main data frame
df_main = pd.read_excel(file_input)

# determine number of columns in dataframe, this will be used later
df_main_range_columns = len(df_main.columns) + 1

d={}

for index, LocCode in enumerate(df_main['LocCode'].unique()):
    d[LocCode] = df_main[df_main['LocCode'] == LocCode]
    
    df_local_range_rows = len(d[LocCode]) + 2
    
    # create charts
    chart_scatter_analytical = ScatterChart()
    chart_scatter_gwe = ScatterChart()
    chart_scatter_combined = ScatterChart()
    
    # create sheets
    wb.create_sheet(LocCode)
    ws = wb[LocCode]
    
    for dataframes in dataframe_to_rows(d[LocCode], index=True, header=True):
        ws.append(dataframes)
    
    for index, Analyte in enumerate(d[LocCode]['Analyte'].unique()):
        # get column indices
        for cell_value in ws.iter_cols(min_row=1, max_row=1, min_col=1, max_col=df_main_range_columns):
            for cell in cell_value:
                if cell.value == 'Date':
                    col_date = cell.column
                elif cell.value == 'Concentration':
                    col_conc = cell.column
                elif cell.value == 'Analyte':
                    col_analyte = cell.column
                    
        # get row indices for analyte
        row_analyte_list = []
        
        for cell_value in ws.iter_rows(min_row=3, max_row=df_local_range_rows, min_col=col_analyte, max_col=col_analyte):            
            for cell in cell_value:
                if cell.value == Analyte:
                    row_analyte_list.append(cell.row)
        
        row_min = min(row_analyte_list)
        row_max = max(row_analyte_list)      
                        
        data_x = Reference(ws, 
                           min_col=col_date, 
                           max_col=col_date, 
                           min_row=row_min, 
                           max_row=row_max)
        
        data_y = Reference(ws, 
                           min_col=col_conc, 
                           max_col=col_conc,
                           min_row=row_min, 
                           max_row=row_max)

        # create series
        series_analyte = Series(values = data_y, xvalues=data_x, title=Analyte)
        
        # add series to charts
        # add analytical results to one chart, GWE to another
        if Analyte == 'GWE':
            chart_scatter_gwe.series.append(series_analyte)
        else:
            chart_scatter_analytical.series.append(series_analyte)
            
    chart_scatter_analytical.y_axis.crosses = 'autoZero'
    
    # set y-axis on gwe chart to secondary position
    chart_scatter_gwe.y_axis.crosses = 'max'
    
    # combine charts
    chart_scatter_gwe += chart_scatter_analytical
    chart_scatter_combined += chart_scatter_analytical    
    chart_scatter_combined += chart_scatter_gwe
        
    ws.add_chart(chart_scatter_analytical, "H1")
    ws.add_chart(chart_scatter_gwe, "H18")
    ws.add_chart(chart_scatter_combined, "R1")
   
wb.save(file_output)

This is the result I'm getting:

So, I'm interested in the chart_scatter_gwe but you can see that everything is plotted on the secondary axis. Seems like the chart_scatter_analytical.y_axis.crosses = 'autoZero' is not being recognized?

One thing I tried was switching the chart type of chart_scatter_gwe to a line chart. This actually got two separate axes on the chart, so maybe this method for two scatter charts needs to be different?

Any ideas/guidance would be greatly appreciated. Thanks,

Answer 1

Ok so I was able to get this working by adding the line chart_scatter_analytical.y_axis.axId = 200 above chart_scatter_gwe.y_axis.crosses = "max"

Not 100% sure why this worked, I saw the examples in the documentation had this line so I thought I'd try it. I'll keep looking through the docs/source code to understand the axId better.