Feature request: Add possibility to set x-axis for scatter as value axis in combined chart

[JesperLM]

Hi!
I have tried to combine a radar chart with a scatter chart to create a polar chart. When I did this I didn't manage to set the x-axis on the scatter plot to a values axis which means that I coudn't set the max and min values on the chart.

When I open the output from the script below in excel I can change the chart type to another scatter chart and a get a values x-axis. The first figure shown below is the output from python and the second is when I have changed the chart type and and made the line straight without markers.

/Jesper

#!/usr/bin/env python3
####################################################################### 
# 
# An example of creating a Polar chart using radar and scatter charts with Python and XlsxWriter. 
# 
import xlsxwriter 
import numpy as np

 
workbook = xlsxwriter.Workbook('chart_polar.xlsx') 
worksheet = workbook.add_worksheet() 
bold = workbook.add_format({'bold': 1}) 
 
 
# Add the worksheet data that the charts will refer to. 
headings = ['Degree', 'Deformation', 'x-coord','y-coord'] 
my_data=[
    [0, 15, 30, 45, 90, 105, 120, 135, 180, 270, 350, 360],
		[0, 0.1, 0.05, 0, -0.05, -0.1, -0.02, 0, 0, 0, 0.1, 0],
]

# Parameters used to set axis
max_data=max(my_data[1])
max_radar=int(max_data)+1
bas= 5*max_radar
max_axis_r = max_radar
min_axis_r =  - bas
major_axis_r = (max_axis_r-min_axis_r)/6
max_axis_s = bas+max_radar
min_axis_s = -max_axis_s


# Write data to Excel sheet
worksheet.write_row('A1', headings, bold) 
worksheet.write_column('A2', my_data[0]) 
worksheet.write_column('B2', my_data[1]) 
for i in range(0,len(my_data[1])):
    worksheet.write_formula(i+1, 2, "=(B"+str(2+i)+"+"+str(bas)+")*SIN(A"+str(2+i)+"/180*PI())")
    worksheet.write_formula(i+1, 3, "=(B"+str(2+i)+"+"+str(bas)+")*COS(A"+str(2+i)+"/180*PI())")
 
 
####################################################################### 
# 
# Create a new radar chart. 
# 
radar_chart = workbook.add_chart({'type': 'radar'}) 
 
radar_chart.add_series({ 
    'name':       ['Sheet1', 0, 8], 
    'categories': ['Sheet1', 1, 5, 26, 5], 
    'values':     ['Sheet1', 1, 4, 26, 4], 
    'line':       {'none': True},
}) 

####################################################################### 
# 
# Create a new scatter chart. 
# 
scatter_chart = workbook.add_chart({'type': 'scatter','subtype': 'straight'}) 
scatter_chart.add_series({
    'name':       '=Sheet1!$C$1',
    'categories': '=Sheet1!$C$2:$C$13',
    'values':     '=Sheet1!$D$2:$D$13',
    'y2_axis':    True,
    'x2_axis':    True,
})

radar_chart.combine(scatter_chart)

# Add a chart title and some axis labels. 
radar_chart.set_title ({'name': 'Polar Chart [mm]'}) 
radar_chart.set_y_axis({'min': min_axis_r, 'max': max_axis_r, 'major_unit': major_axis_r,}) 
scatter_chart.set_y2_axis({'min': min_axis_s, 'max': max_axis_s,'visible': False}) 
scatter_chart.set_x2_axis({'min': min_axis_s, 'max': max_axis_s,}) 

worksheet.insert_chart('F2', radar_chart, {'x_offset': 25, 'y_offset': 10}) 
 
workbook.close() 

[jmcnamara]

Could you update the second image. It looks the same as the first.

[jmcnamara]

Also, is it possible in Excel to combine a radar chart and a scatter chart to get a polar chart?

[JesperLM]

Thanks for quick response!

For me the x-axis don't match with the radar axis on the first image but it do match on the second image.
I have been able to create combined plots with radar and scatter from scratch in excel where I get the x-axis as a value axis from the start, however I have to set the axis independently but it is possible to create a polar chart this way.

/Jesper

[JesperLM]

This figure maybe shows the change between the figures better. The first is from my python script and the second is the one i have changed in excel.
I have also appended a combined a radar chart and a scatter chart done in Excel.

Done_in_Excel.zip

[jmcnamara]

Thanks for the updates. I'll look into it in more detail.

It is possible that this is a bug. I am aware of a similar issue in the Perl version of the module: jmcnamara/excel-writer-xlsx#157

I'll look into it and let you know.

[JesperLM]

I’m not really familiar with the code but I have tried to check in the ”chart.py” and ”scatter_chart.py”.

I believe the problem could be in “_write_plot_area” in chart.py. The reason I believe this is that from what I can see in the code it is here the charts are combined and the function seams to be unable to define a value x-axis. I think this could be solved by adding an elseif statement in the rows 1532 to 1535 that checks if the plot is a scatter plot and if it is it writes a values axis instead of a categories axis (I have placed this statement after the write date statement since that should be able to override the behavior I want). I am not sure this will solve the problem, since I don’t know my way around this code that much;)
This statement could probably look something like:

        if self.date_category:
            self._write_date_axis(args) 
        elif self.get(‘type’) == 'scatter':
            self._write_cat_val_axis(args)
        else:
            self._write_cat_axis(args)

Hopefully this has helped ;)

[42tker]

Are there any new for this problem? I faced it today while trying to build a Polar Chart this way. The x-axis of the scatter plot seems to be more right than it should.
EDIT: The point (0,0) of both charts does not match. So the Scatter Plot has its (0,0) in (1,0) or sth like that on the radar chart. Thats what I mean by "more right".

[jmcnamara]

@42tker There isn't any update on this from my side.

[42tker]

@jmcnamara Thank you for the answer.
But I'm not sure, if this is a xlsxwriter problem. When I create the chart via xlsxwriter and then change in the excelsheet the type from "scatter" to "scatter with lines" (it doenst matter, just something has to be changed), the x2 axis is correct afterwards. So maybe something is wrong with my x2 axis ...