New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Feature request: Support different chart types on an individual series #72

Closed
bsphil opened this Issue Dec 3, 2013 · 15 comments

Comments

7 participants
@bsphil

bsphil commented Dec 3, 2013

Add in the ability to set an individual series in a chart to use a different chart type. For example, a line graph series on top of a bar graph series:

image

Great job on the library so far, it's been very helpful. Thanks!

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 3, 2013

Owner

That is definitely on the TODO list.

Owner

jmcnamara commented Dec 3, 2013

That is definitely on the TODO list.

@bsphil

This comment has been minimized.

Show comment
Hide comment
@bsphil

bsphil Dec 4, 2013

That's awesome. Thanks again! I'd donate to this cause if I could, it's definitely worth at least a $10 to my uses.

bsphil commented Dec 4, 2013

That's awesome. Thanks again! I'd donate to this cause if I could, it's definitely worth at least a $10 to my uses.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Dec 4, 2013

Owner

Note that this feature is non-trivial. It requires a significant amount of additional code, refactoring and test cases.

So, it will take a while.

Owner

jmcnamara commented Dec 4, 2013

Note that this feature is non-trivial. It requires a significant amount of additional code, refactoring and test cases.

So, it will take a while.

@ghost ghost assigned jmcnamara Dec 4, 2013

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Nov 11, 2014

Hi John,

Any chance of getting this anytime soon... I do a lot of charts like below that require manual edit and prevents complete automation of reports....

Best,
George Lovas

image

georgelovas commented Nov 11, 2014

Hi John,

Any chance of getting this anytime soon... I do a lot of charts like below that require manual edit and prevents complete automation of reports....

Best,
George Lovas

image

@gglanzani

This comment has been minimized.

Show comment
Hide comment
@gglanzani

gglanzani Nov 12, 2014

+1 for this one. @jmcnamara Can you share if and what kind of help would you need for this one?

gglanzani commented Nov 12, 2014

+1 for this one. @jmcnamara Can you share if and what kind of help would you need for this one?

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Nov 12, 2014

Owner

@gglanzani

Can you share if and what kind of help would you need for this one

In general I don't need help, I just need time.

This feature is tricky because it is implemented in Excel as a mixin of two chart types with one embedded in the other. It would require at least 20-30 comparison test cases to verify that it is working correctly. So most of the work would go into upfront design and testing.

Also, there isn't a single solution for all chart types and it exposes a lot of Excel features that behave slightly differently if they are referring to the primary or secondary chart/axes. So this would also generate a lot of additional maintenance work.

Personally I am in favour of this feature since it is, from my point of view, one of the last missing pieces. I may try to tackle this in January but I can't promise anything.

John

Owner

jmcnamara commented Nov 12, 2014

@gglanzani

Can you share if and what kind of help would you need for this one

In general I don't need help, I just need time.

This feature is tricky because it is implemented in Excel as a mixin of two chart types with one embedded in the other. It would require at least 20-30 comparison test cases to verify that it is working correctly. So most of the work would go into upfront design and testing.

Also, there isn't a single solution for all chart types and it exposes a lot of Excel features that behave slightly differently if they are referring to the primary or secondary chart/axes. So this would also generate a lot of additional maintenance work.

Personally I am in favour of this feature since it is, from my point of view, one of the last missing pieces. I may try to tackle this in January but I can't promise anything.

John

@gglanzani

This comment has been minimized.

Show comment
Hide comment
@gglanzani

gglanzani Nov 12, 2014

@jmcnamara

Thanks, that's clear. It feels indeed as the last missing feature.

Looking forward to it. If you need people to test the feature while in development, feel free to ping me (or reply to this thread).

gglanzani commented Nov 12, 2014

@jmcnamara

Thanks, that's clear. It feels indeed as the last missing feature.

Looking forward to it. If you need people to test the feature while in development, feel free to ping me (or reply to this thread).

@JrSchild

This comment has been minimized.

Show comment
Hide comment
@JrSchild

JrSchild commented Dec 1, 2014

+1

@slara

This comment has been minimized.

Show comment
Hide comment
@slara

slara commented Dec 27, 2014

+1

@NotSqrt

This comment has been minimized.

Show comment
Hide comment
@NotSqrt

NotSqrt commented Feb 6, 2015

👍

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 16, 2015

Owner

Hi,

It took a while to get around to working on support for combined charts in XlsxWriter so I'm happy to report some good progress in time for St. Patrick's day.

There is a beta branch (combined_chart) with support for combined charts. You can now create 2 charts and add one to the other to combine them like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('combined_chart1.xlsx')

worksheet = workbook.add_worksheet()

chart1 = workbook.add_chart({'type': 'column'})
chart2 = workbook.add_chart({'type': 'line'})

# Some data to write to the chart.
data = [
    [2, 7, 3, 6, 2],
    [20, 25, 10, 10, 20],
]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])

# The primary chart.
chart1.add_series({'values': '=Sheet1!$A$1:$A$5'})

# The secondary chart.
chart2.add_series({'values': '=Sheet1!$B$1:$B$5'})

# Add the secondary chart to the primary chart.
chart1.combine(chart2)

# Insert it in the worksheet.
worksheet.insert_chart('E9', chart1)

workbook.close()

screenshot

Or place the secondary chart on a secondary axis, using the standard syntax. The only difference is this line:

chart2.add_series({'values': '=Sheet1!$B$1:$B$5', 'y2_axis': True})

Code:

import xlsxwriter

workbook = xlsxwriter.Workbook('combined_chart2.xlsx')

worksheet = workbook.add_worksheet()

chart1 = workbook.add_chart({'type': 'column'})
chart2 = workbook.add_chart({'type': 'line'})

# Some data to write to the chart.
data = [
    [2, 7, 3, 6, 2],
    [20, 25, 10, 10, 20],
]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])

# The primary chart.
chart1.add_series({'values': '=Sheet1!$A$1:$A$5'})

# The secondary chart.
chart2.add_series({'values': '=Sheet1!$B$1:$B$5', 'y2_axis': True})

# Add the secondary chart to the primary chart.
chart1.combine(chart2)

# Insert it in the worksheet.
worksheet.insert_chart('E9', chart1)

workbook.close()

screenshot

Both charts can be configured separately before or after they are combined.

The new chart.combine() method is the only addition. Everything else is the standard chart interface.

Terms and Conditions Apply

This is beta code. It needs a lot more test cases and it is quite possible to break things. Also, scatter charts aren't support yet, but will be shortly.

If you'd like to test it out then please do and let me know if you encounter any issues.

John

Owner

jmcnamara commented Mar 16, 2015

Hi,

It took a while to get around to working on support for combined charts in XlsxWriter so I'm happy to report some good progress in time for St. Patrick's day.

There is a beta branch (combined_chart) with support for combined charts. You can now create 2 charts and add one to the other to combine them like this:

import xlsxwriter

workbook = xlsxwriter.Workbook('combined_chart1.xlsx')

worksheet = workbook.add_worksheet()

chart1 = workbook.add_chart({'type': 'column'})
chart2 = workbook.add_chart({'type': 'line'})

# Some data to write to the chart.
data = [
    [2, 7, 3, 6, 2],
    [20, 25, 10, 10, 20],
]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])

# The primary chart.
chart1.add_series({'values': '=Sheet1!$A$1:$A$5'})

# The secondary chart.
chart2.add_series({'values': '=Sheet1!$B$1:$B$5'})

# Add the secondary chart to the primary chart.
chart1.combine(chart2)

# Insert it in the worksheet.
worksheet.insert_chart('E9', chart1)

workbook.close()

screenshot

Or place the secondary chart on a secondary axis, using the standard syntax. The only difference is this line:

chart2.add_series({'values': '=Sheet1!$B$1:$B$5', 'y2_axis': True})

Code:

import xlsxwriter

workbook = xlsxwriter.Workbook('combined_chart2.xlsx')

worksheet = workbook.add_worksheet()

chart1 = workbook.add_chart({'type': 'column'})
chart2 = workbook.add_chart({'type': 'line'})

# Some data to write to the chart.
data = [
    [2, 7, 3, 6, 2],
    [20, 25, 10, 10, 20],
]

worksheet.write_column('A1', data[0])
worksheet.write_column('B1', data[1])

# The primary chart.
chart1.add_series({'values': '=Sheet1!$A$1:$A$5'})

# The secondary chart.
chart2.add_series({'values': '=Sheet1!$B$1:$B$5', 'y2_axis': True})

# Add the secondary chart to the primary chart.
chart1.combine(chart2)

# Insert it in the worksheet.
worksheet.insert_chart('E9', chart1)

workbook.close()

screenshot

Both charts can be configured separately before or after they are combined.

The new chart.combine() method is the only addition. Everything else is the standard chart interface.

Terms and Conditions Apply

This is beta code. It needs a lot more test cases and it is quite possible to break things. Also, scatter charts aren't support yet, but will be shortly.

If you'd like to test it out then please do and let me know if you encounter any issues.

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 17, 2015

Owner

Here is a slightly more realistic example of a Pareto chart:

#######################################################################
#
# An example of creating of a Pareto chart with Python and XlsxWriter.
#
# Copyright 2013-2015, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter

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

# Formats used in the workbook.
bold = workbook.add_format({'bold': 1})
percent_format = workbook.add_format({'num_format': '0.0%'})

# Widen the columns for visibility.
worksheet.set_column('A:A', 15)
worksheet.set_column('B:C', 10)

# Add the worksheet data that the charts will refer to.
headings = ['Reason', 'Number', 'Percentage']

reasons = [
    'Traffic', 'Child care', 'Public Transport', 'Weather',
    'Overslept', 'Emergency',
]

numbers  = [60,   40,    20,  15,  10,    5]
percents = [0.44, 0.667, 0.8, 0.9, 0.967, 1]

worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', reasons)
worksheet.write_column('B2', numbers)
worksheet.write_column('C2', percents, percent_format)


# Create a new column chart. This will be the primary chart.
column_chart = workbook.add_chart({'type': 'column'})

# Add a series.
column_chart.add_series({
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!B2:B7',
})

# Add a chart title.
column_chart.set_title({'name': 'Reasons for lateness'})

# Turn off the chart legend.
column_chart.set_legend({'position': 'none'})

# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
column_chart.set_y_axis({
    'name': 'Respondents (number)',
    'min': 0,
    'max': 120
})
column_chart.set_y2_axis({'max': 1})

# Create a new line chart. This will be the secondary chart.
line_chart = workbook.add_chart({'type': 'line', 'embedded': 1})

# Add a series, on the secondary axis.
line_chart.add_series({
    'categories': '=Sheet1!A2:A7',
    'values': '=Sheet1!C2:C7',
    'marker': {'type': 'automatic'},
    'y2_axis': 1,
})

# Combine the charts.
column_chart.combine(line_chart)

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

workbook.close()

screenshot

Owner

jmcnamara commented Mar 17, 2015

Here is a slightly more realistic example of a Pareto chart:

#######################################################################
#
# An example of creating of a Pareto chart with Python and XlsxWriter.
#
# Copyright 2013-2015, John McNamara, jmcnamara@cpan.org
#
import xlsxwriter

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

# Formats used in the workbook.
bold = workbook.add_format({'bold': 1})
percent_format = workbook.add_format({'num_format': '0.0%'})

# Widen the columns for visibility.
worksheet.set_column('A:A', 15)
worksheet.set_column('B:C', 10)

# Add the worksheet data that the charts will refer to.
headings = ['Reason', 'Number', 'Percentage']

reasons = [
    'Traffic', 'Child care', 'Public Transport', 'Weather',
    'Overslept', 'Emergency',
]

numbers  = [60,   40,    20,  15,  10,    5]
percents = [0.44, 0.667, 0.8, 0.9, 0.967, 1]

worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', reasons)
worksheet.write_column('B2', numbers)
worksheet.write_column('C2', percents, percent_format)


# Create a new column chart. This will be the primary chart.
column_chart = workbook.add_chart({'type': 'column'})

# Add a series.
column_chart.add_series({
    'categories': '=Sheet1!A2:A7',
    'values':     '=Sheet1!B2:B7',
})

# Add a chart title.
column_chart.set_title({'name': 'Reasons for lateness'})

# Turn off the chart legend.
column_chart.set_legend({'position': 'none'})

# Set the title and scale of the Y axes. Note, the secondary axis is set from
# the primary chart.
column_chart.set_y_axis({
    'name': 'Respondents (number)',
    'min': 0,
    'max': 120
})
column_chart.set_y2_axis({'max': 1})

# Create a new line chart. This will be the secondary chart.
line_chart = workbook.add_chart({'type': 'line', 'embedded': 1})

# Add a series, on the secondary axis.
line_chart.add_series({
    'categories': '=Sheet1!A2:A7',
    'values': '=Sheet1!C2:C7',
    'marker': {'type': 'automatic'},
    'y2_axis': 1,
})

# Combine the charts.
column_chart.combine(line_chart)

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

workbook.close()

screenshot

@slara

This comment has been minimized.

Show comment
Hide comment
@slara

slara Mar 17, 2015

Wow! this is really awesome @jmcnamara!

slara commented Mar 17, 2015

Wow! this is really awesome @jmcnamara!

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Mar 18, 2015

Owner

I've pushed version 0.6.8 of XlsxWriter to PyPI with support for combined charts.

See the new Combined Charts section of the docs for details.

I am going to close this feature request. If there are any functional issues you can open a new bug report issue.

Regards,

John

Owner

jmcnamara commented Mar 18, 2015

I've pushed version 0.6.8 of XlsxWriter to PyPI with support for combined charts.

See the new Combined Charts section of the docs for details.

I am going to close this feature request. If there are any functional issues you can open a new bug report issue.

Regards,

John

@jmcnamara jmcnamara closed this Mar 18, 2015

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Mar 18, 2015

Hi John,

Thanks so much for implementing this so quickly... Will implement as soon
as possible.

Hope you had a good St Patricks Day...

Best,
George

georgelovas commented Mar 18, 2015

Hi John,

Thanks so much for implementing this so quickly... Will implement as soon
as possible.

Hope you had a good St Patricks Day...

Best,
George

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment