Need to create a combo chart

[Danttee]

I need a combo chart which has both line and column plot like the picture showing above Based on the official website, it looks like it's impossible on API level So I was trying to add a child node in the plotarea node my code is:

prs = Presentation()
slide = prs.slides.add_slide(prs.slide_layouts[6])
chart_data = ChartData()
chart_data.categories = ['East', 'West', 'Midwest']
chart_data.add_series('Q1 Sales', (19.2, 21.4, 16.7))
chart_data.add_series('Q2 Sales', (22.3, 28.6, 15.2))
chart_data.add_series('Q3 Sales', (20.4, 26.3, 14.2))

x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
columnc = slide.shapes.add_chart(
    XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
)

prs1 = Presentation()
slide1 = prs1.slides.add_slide(prs1.slide_layouts[6])
chart_data1 = ChartData()
chart_data1.categories = ['East', 'West', 'Midwest']
chart_data1.add_series('Q1 Sales', (5, 5, 5))

linec = slide1.shapes.add_chart(
    XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data1
)
chartb = columnc.chart
chartl = linec.chart

a=chartl.plots._plotArea
b=chartb.plots._plotArea
#print a.xml
#dir(a)
k=a.getchildren()[0]
#print b.xml
b.insert(1,k)
#print b.xml
prs.save('test.pptx')

But when I open test.pptx, it shows it needed repairing, and it can't repair, so I got a blank slide. What's wrong with my code or is there any other way to do this? Thank you! A similar question has been posted on stackoverflow. (if click doesn't work, you can copy the link and manually go there) https://stackoverflow.com/questions/47564031/add-a-ref-line-in-a-column-chart-but-presentation-is-broken

[scanny]

What does the XML you need look like and how is the XML this code generates different?

[jbq]

I also badly need combined charts. Can't find the right way to implement that in the library. Any hint @scanny ?

[scanny]

This sort of thing would be a "multi-plot" chart, so chart.plots[0] might be a column-clustered and chart.plots[1] would be line type.

I haven't given any serious thought to what the API might look like for this. If you were going to try to hack it in with a workaround function (which would probably be where I would start), I'd recommend creating a simple chart of this type and inspecting the XML PowerPoint generates for it. opc-diag is handy for that work.

[jbq]

I added a Chart.add_subchart() method and it works for me, see https://github.com/jbq/python-pptx/commit/ddffc2839e3f909b3bf8acdc8fe80ecfe63492a6

It's very hackish though, the current API does not provide means to deal with the subtleties of the MS Chart API.

Example calling code:

    chart_data = ...

    # add chart to slide --------------------
    x, y, cx, cy = Cm(4.85), Cm(6), Cm(20), Cm(11)
    graphic_frame = slide.shapes.add_chart(
        XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
    )

    chart = graphic_frame.chart

    subchart_data = ...

    # add chart to slide --------------------
    chart.add_subchart(
        XL_CHART_TYPE.XY_SCATTER,
        subchart_data,
        [
            chart._chartSpace.plotArea.xpath("c:catAx/c:axId/@val")[0],
            chart._chartSpace.plotArea.xpath("c:valAx/c:axId/@val")[0]
        ]
    )

The last two lines are meant for the second chart to reuse the axis from the first chart.

[masterofdestiny]

What would be format of subchart_data . I am passing a touple and getting error . File "C:\Python27\lib\site-packages\pptx\chart\xmlwriter.py", line 1222, in _ser_xml 'ser_idx': series.index, AttributeError: 'int' object has no attribute 'index'

[jbq]

@masterofdestiny see example at https://python-pptx.readthedocs.io/en/latest/user/charts.html

[masterofdestiny]

Hi I saw it and based on that here is my entire code,

from pptx import Presentation
from pptx.dml.color import RGBColor
from pptx.chart.data import ChartData
from pptx.enum.chart import XL_CHART_TYPE
from pptx.util import Inches

from pptx.enum.chart import  XL_LEGEND_POSITION

from pptx.enum.shapes import MSO_SHAPE_TYPE
from pptx.util import Pt
from string import digits
from datetime import datetime
from pptx.enum.chart import XL_TICK_MARK,XL_CHART_TYPE

discoverable = Presentation('testc.pptx').slides[0]

cat = ('2017-03', '2017-02', '2017-01', '2016-12', '2016-11', '2016-10', '2016-09', '2016-08', '2016-07', '2016-06', '2016-05', '2016-04', '2016-03', '2016-02')
smdt = {'OSS': (24, 28, 24, 32, 30, 30, 29, 39, 32, 31, 34, 33, 36, 34), 'GVS': (41, 45, 37, 60, 46, 46, 41, 33, 43, 40, 46, 48, 54, 48), 'ATS': (39, 97, 0, 93, 82, 97, 99, 89, 100, 99, 40, 100, 100, 0), 'POSS': (42, 41, 38, 46, 56, 34, 28, 28, 58, 58, 70, 67, 71, 83), 'TS': (72, 82, 35, 77, 62, 60, 60, 49, 72, 72, 76, 70, 82, 77), 'RS': (27, 30, 32, 33, 29, 29, 25, 25, 26, 25, 29, 33, 38, 34)}
GVS = smdt['GVS']
del smdt['GVS']
chart_data = ChartData()
chart_data.categories = cat
for k,v in smdt.items():

   chart_data.add_series(k, v)
x, y, cx, cy = Inches(1.5), Inches(2), Inches(8), Inches(4)

graphic_frame = discoverable.shapes.add_chart(
   XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
)

chart = graphic_frame.chart
#self.chart_style(chart, title,  6, self.report_format)

chart_data = ChartData()
chart_data.categories = cat
chart_data.add_series('', GVS )
x, y, cx, cy = Inches(1.5), Inches(2), Inches(9), Inches(4)
chart1 = discoverable.shapes.add_chart(
  XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
).chart

category_axis = chart1.category_axis
category_axis.has_major_gridlines = False
category_axis.minor_tick_mark = XL_TICK_MARK.OUTSIDE
category_axis.tick_labels.font.size = Pt(10)
value_axis = chart1.value_axis
value_axis.has_minor_gridlines = False
value_axis.has_major_gridlines = False
value_axis.visible = False
chart1.chart_title.text_frame.text=''
chart1.has_title = False
value_axis.tick_label_position = None
chart1.has_legend = False
value_axis.major_tick_mark = XL_TICK_MARK.NONE
subchart_data = chart1
#add chart to slide --------------------
chart.add_subchart(
  XL_CHART_TYPE.XY_SCATTER,
  subchart_data
  [
      chart._chartSpace.plotArea.xpath("c:catAx/c:axId/@val"),
      chart._chartSpace.plotArea.xpath("c:valAx/c:axId/@val")
  ]
)

Still I am getting the error. chart._chartSpace.plotArea.xpath("c:valAx/c:axId/@val") TypeError: 'Chart' object has no attribute 'getitem'

[jbq]

Can you provide a minimal test GIST as example?

I get NameError: name 'smdt' is not defined with your code

[jbq]

Can you provide a working example? I get NameError: name 'chart_data' is not defined

[masterofdestiny]

Please check the above full code I have updated. Please take any local ppt file .

[jbq]

Sorry the example doesn't work at all:

NameError: name 'Presentation' is not defined

Than after fixing:

NameError: name 'x' is not defined

Seems like you are not actually executing the code to verify your testcase...

[masterofdestiny]

I am sorry since my code was into the big file so I could not copy it however I just ran everything seperately and updated code exactly you should be able to get this running .

testc.pptx is just a PPT file having single slide without any chart. You can loccaly create a PPT instead of it

[jbq]

There are several mistakes:

1. there is a missing comma after subchart_data on line 62
2. subchart_data is of type Chart instead of ChartData

[masterofdestiny]

I did below but getting another issue TypeError: 'Chart' object is not iterable.

subchart_data = chart #add chart to slide -------------------- chart.add_subchart( XL_CHART_TYPE.XY_SCATTER, subchart_data, [ chart._chartSpace.plotArea.xpath("c:catAx/c:axId/@val"), chart._chartSpace.plotArea.xpath("c:valAx/c:axId/@val") ] )

On Fri, Jul 20, 2018 at 6:37 PM Jean-Baptiste Quenot < [email protected]> wrote:

There are several mistakes:

1. there is a missing comma after subchart_data on line 62
2. subchart_data is of type Chart instead of ChartData

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/scanny/python-pptx/issues/338#issuecomment-406595272, or mute the thread https://github.com/notifications/unsubscribe-auth/ABADDF5FMHBTEiUSJznpZrwyRbbddZS5ks5uIdX4gaJpZM4QxRi5 .

[Val]

@masterofdestiny : please type @val inside backquotes or block of code.

[mimakaev]

I'm getting the same issue here.

[jbq]

Note: the work I did with add_subchart() has a drawback: it is impossible to modify the chart data in Excel, doing so screws up the chart and cannot be undone. I have to find a way to have global chart data for all subcharts.

[tedwards999]

@jbq I have a similar requirement as mentioned here i.e. to plot a combo chart consisting of a line and bar chart. You previously mentioned that data is not editable. Have you perhaps had a chance to look into this yet? It would be a great help if this was available

[jbq]

I figured out why data is not editable: one ChartData instance is used for each chart, and that is not compatible with the way python-pptx works. The same ChartData instance has to be used for all charts.

[tedwards999]

So if I understand correctly each chart in the combo chart should have its own instance of the chart data object but Pythonpptx can only create one instance for both charts in the combo chart?

Can you think of any work around? It seems you have already done a lot of work in understanding the problem.

On Thu, Sep 6, 2018, 2:57 AM Jean-Baptiste Quenot [email protected] wrote:

I figured out why data is not editable: one ChartData instance is used for each chart, and that is not compatible with the way python-pptx works. The same ChartData instance has to be used for all charts.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/scanny/python-pptx/issues/338#issuecomment-418985922, or mute the thread https://github.com/notifications/unsubscribe-auth/Ae8nHTSfxx6HE0yYM1_jggkB3n0FSyMVks5uYMdCgaJpZM4QxRi5 .

[jbq]

No it's the opposite, we need a single ChartData object to be used for all charts. I have done this research but not implemented a solution ATM.

[arpit0515]

Hi @jbq I keep getting the below error. I have modified the python-pptx.py file inside jupyter files. AttributeError: 'CategoryWorkbookWriter' object has no attribute 'x_values_ref' I am in desperate need of creating combo charts using python-pptx right now, please help!

[jbq]

@arpit0515 without a fully working testcase I can't tell you where the error comes from

Please bear in mind that there is currently no official support for combo charts. My combo chart hack is a half-baked hack that only works if the first plot has category and values axis, and breaks if you try to modify the data in PowerPoint/Excel.

I'd love to improve the quality of my contribution, but unfortunately, combo charts require a major overhaul of a significant part of the python-pptx library related to how charts are created and how chart data is referenced. Requires @scanny's help, is it possible to provide funding for this?

[Wolfever]

Inspired by the discussion here and @jbq 's add_subchart function, I created a simple boxplot and hope you find it useful.

The general idea behind this boxplot is a stacked bar plot combined with a line-plot. The barplot are consisted of three parts: 25 percentile, 50%-25%, and 75%-50%, while the first bar is set to transparent. On the other plot, the lineplot, I hid the line and added a high-low line to it. Following is the full code I've used.

from pptx import Presentation
from pptx.chart.data import CategoryChartData
from pptx.util import Inches, Pt
from pptx.enum.chart import XL_CHART_TYPE

from pptx.oxml import parse_xml
from pptx.oxml.ns import nsdecls

from pptx.chart.xmlwriter import SeriesXmlRewriterFactory, ChartXmlWriter
from lxml import etree
from io import BytesIO
from pptx.oxml.ns import _nsmap as namespaces

data = {
    '5%': [370, 381],
    '25%':[435, 458],
    '50%-25%': [71, 89],
    '75%-50%': [68, 79],
    '95%': [673, 724]
}

# XMLs used to set the properties 
# 1. set serie fill to transparent 
xml_nf = '''<c:spPr %s><a:noFill/></c:spPr>'''% nsdecls("c", "a")
# 2. set line fill to none 
lnf_xml = '''<c:spPr %s>
    <a:ln w="25400">
        <a:noFill/>
    </a:ln>
    <a:effectLst/>
</c:spPr>''' %  nsdecls("c", "a")
# 3. Add a hihe-low line 
hi_low_xml = "<c:hiLowLines %s/>" % nsdecls("c") 

prs = Presentation()
slide = prs.slides.add_slide(prs.slide_layouts[6])
chart_data = CategoryChartData()
chart_data.categories = ['One', 'Two']
for d in ['25%', '50%-25%', '75%-50%']:
    chart_data.add_series(d, data[d])

x, y, cx, cy = Inches(2), Inches(2), Inches(6), Inches(4.5)
column_bars = slide.shapes.add_chart(
    XL_CHART_TYPE.COLUMN_STACKED, x, y, cx, cy, chart_data
)

# Hide the bar in the bottom showing 25 percentile 
ser_one_no_fill = parse_xml(xml_nf)
column_bars.chart._chartSpace.plotArea.xpath("*/c:ser")[0].insert(0, ser_one_no_fill)

# Add a line plot in chartspace/plotarea 
line_data = CategoryChartData()
line_data.categories = ['One', 'Two']
for d in ['5%', '95%']:
    line_data.add_series(d, data[d])

xml_bytes = ChartXmlWriter(XL_CHART_TYPE.LINE, line_data).xml
root_el = etree.parse(BytesIO(xml_bytes.encode('utf-8')))

val_axis = column_bars.chart._chartSpace.plotArea.xpath("c:valAx/c:axId/@val")
cat_axis = column_bars.chart._chartSpace.plotArea.xpath("c:catAx/c:axId/@val")
series_count = len(column_bars.chart._chartSpace.plotArea.xpath("*/c:ser"))

for el in root_el.xpath("/c:chartSpace/c:chart/c:plotArea/*", namespaces=namespaces):
    if el.tag.endswith("Chart"):  
        # Since the two charts share the same axies, the axId should be the same 
        existing_axis_ref = el.xpath("c:axId", namespaces=namespaces)
        for x in existing_axis_ref:
            el.remove(x)
        for x in cat_axis + val_axis:   # Order is important 
            el.append(etree.Element("{%s}axId" % namespaces['c'], val=x))

        # Need to increment series index to make PP happy
        for i, x in enumerate(el.xpath("c:ser/c:idx", namespaces=namespaces)):
            x.set("val", str(series_count + i))
        for i, x in enumerate(el.xpath("c:ser/c:order", namespaces=namespaces)):
            x.set("val", str(series_count + i))
        
        # Show a short dash at 5 and 95 percentiles 
        markers = el.xpath("c:ser/c:marker/c:symbol", namespaces=namespaces)
        for marker in markers:
            marker.set('val', 'dash')
        
        # Hide the line 
        for s in  el.xpath("c:ser", namespaces = namespaces):
            lnf = parse_xml(lnf_xml)
            s.append(lnf)
        
        # Add a high-low line 
        el.append(parse_xml(hi_low_xml))
        column_bars.chart._chartSpace.plotArea.insert(1, el)  

prs.save('test.pptx')

[TylerTCF]

@Wolfever , great workaround. How would you go about replacing data in these bars if you loaded it in from an already created PPTX? I guess you could add/replace data to the dictionary when importing the series?

[rsimbu-36]

Hi,

I tried using the python-pptx package to make a combo chart, but it's giving me this error: "AttributeError: type object 'XL_CHART_TYPE' has no attribute 'COMBO'" and added the following code. Could you try to help me in making a combo chart using Python-pptx?

Define the chart data

chart_data = [
    ('Color', color_data),
    ('Mono', mono_data),
    ('Added Devices', added_device)
]

# Define the chart position and size
left = Inches(0.5)
top = Inches(1.1)
width = Inches(8)
height = Inches(4.5)

# Add a chart to the slide
chart = slide.shapes.add_chart(
    XL_CHART_TYPE.COMBO, left, top, width, height, chart_data
).chart

# Customize the chart
chart.has_legend = True
chart.legend.position = XL_LEGEND_POSITION.RIGHT
chart.legend.include_in_layout = False
chart.category_axis.has_major_gridlines = False
chart.value_axis.has_major_gridlines = False

# Format the chart series
for series in chart.series:
    series.smooth = True

# Set the category axis labels
category_axis = chart.category_axis
category_axis.category_labels = categories

[LiHaotian1995]

COMBO.... gpt result?

[NishiChandra]

can anybody suggest a combined chart code for column + line ?

[Adrena-pixel]

= slide.shapes.add_chart( I did not get the last two lines.

chart._chartSpace.plotArea.xpath("c:catAx/c:axId/@val")[0],
chart._chartSpace.plotArea.xpath("c:valAx/c:axId/@val")[0]

What if I do not want my graphs to have the same axis? I want a duel-y with two different y-axis left and right.