HOW TO Ungroup chartdata with time-dependent values in python-pptx

Question

I tried to output XYdata with Date-type category axis (datetime.datetime objects along X-axis). I need to view all data points (X,Y) that are contained in chart_data. But, unfortunatelly, they appear grouped by year in presentation. How can I change the 'gruoing option' for XY Chart?

I already have changed

chart.category_axis.category_type = XL_CATEGORY_TYPE.CATEGORY_SCALE

but it did not lead to success

There is code example:

# -*- coding: utf-8 -*-
"""
Created on Wed May 30 14:46:28 2018

"""

import datetime, time
import xlrd
import pptx
from pptx import Presentation
from pptx.util import Cm, Pt
from pptx.enum.text import MSO_ANCHOR, MSO_AUTO_SIZE,PP_ALIGN
from pptx.chart.data import ChartData, XyChartData
from pptx.enum.chart import XL_CHART_TYPE
from pptx.enum.chart import XL_TICK_MARK
from pptx.enum.chart import XL_LEGEND_POSITION
from pptx.enum.chart import XL_CATEGORY_TYPE

prs = Presentation('templae.pptx')


def FonST(obj, s, t):
    try:
        obj.text_frame.paragraphs[0].font.size = Pt(s)
        obj.text_frame.paragraphs[0].font.typeface = t
    except AttributeError:
        obj.font.size=Pt(s)
        obj.font.typeface=t


title_slide_layout = prs.slide_masters[1].slide_layouts[1]
slide = prs.slides.add_slide(title_slide_layout)
shapes = slide.shapes
title = shapes.title

class MyXySeriesData(pptx.chart.data.XySeriesData):
    """
    The data specific to a particular XY chart series. It provides access to
    the series label, the series data points, and an optional number format
    to be applied to each data point not having a specified number format.

    The sequence of data points in an XY series is significant; lines are
    plotted following the sequence of points, even if that causes a line
    segment to "travel backward" (implying a multi-valued function). The data
    points are not automatically sorted into increasing order by X value.
    """
    def add_data_vector(self, vec1, vec2):
        if len(vec1) != len(vec2):
            return -1
        else:
            for i in range(0, len(vec1)):
                self.add_data_point(vec1[i], vec2[i])

class MyXyChartData(pptx.chart.data.XyChartData):
    """
    Переопределяем класс библиотеки pptx
    """
    def add_series(self, name, number_format=None):
        """
        Return an |XySeriesData| object newly created and added at the end of
        this sequence, identified by *name* and values formatted with
        *number_format*.
        """
        series_data = MyXySeriesData(self, name, number_format)
        self.append(series_data)
        return series_data

# Open the workbook
xl_workbook = xlrd.open_workbook('bookxl.xlsx')

# Grab the first sheet by index 
#  (sheets are zero-indexed)
#
xl_sheet = xl_workbook.sheet_by_index(0)
#print ('Sheet name: %s' % xl_sheet.name)

# define chart data ---------------------


chart_data = MyXyChartData()


vec1 = [ datetime.datetime.fromordinal(int(693594+i.value )) for i in xl_sheet.col(0)]
vec2 = [ float(i.value) for i in xl_sheet.col(1)]


series_1 = chart_data.add_series('Model 1')
series_1.add_data_vector(vec1 , vec2)

# add chart to slide --------------------
x, y, cx, cy = Cm(10), Cm(3), Cm(10), Cm(8)
chart = slide.shapes.add_chart(
    XL_CHART_TYPE.XY_SCATTER_SMOOTH_NO_MARKERS, x, y, cx, cy, chart_data
).chart
chart.chart_title.text_frame.text = 'YTD COMPLETION TO PLAN'
FonST(chart.chart_title, 12, 'Times New Roman')
#chart.chart_title.text_frame.paragraphs[0].font.size = Pt(12)
#chart.chart_title.text_frame.paragraphs[0].font = Pt(12)
chart.has_legend = True
chart.legend.include_in_layout = False

FonST(chart.legend, 12, 'Times New Roman')
#chart.legend.font.size = Pt(12)
chart.legend.position = XL_LEGEND_POSITION.BOTTOM
chart.series[0].smooth = True


#chart.category_axis.tick_labels.font.size=Pt(10)
#chart.value_axis.tick_labels.font.size=Pt(10)
FonST(chart.category_axis.tick_labels, 10, 'Times New Roman')
FonST(chart.value_axis.tick_labels, 10, 'Times New Roman')
#chart.category_axis.tick_labels.number_format = '0.00'
chart.value_axis.tick_labels.number_format = '0.00'

chart.value_axis.axis_title.text_frame.text = 'Something'
FonST(chart.value_axis.axis_title, 10, 'Times New Roman')

chart.category_axis.axis_title.text_frame.text = 'Дата'
FonST(chart.category_axis.axis_title, 10, 'Times New Roman')

chart.category_axis.category_type = XL_CATEGORY_TYPE.CATEGORY_SCALE



prs.save('testout.pptx')

1st list Content of 'bookxl.xlsx' is:input data example, for convenience, input data are listed below:

    01.11.2017  6.660761604
    06.11.2017  7.012580588
    11.11.2017  6.941095486
    17.11.2017  6.85236787
    20.11.2017  6.807413035
    22.11.2017  6.777194047
    26.11.2017  6.716288151
    29.11.2017  6.670361491
    01.12.2017  6.639671179
    01.01.2018  6.13350148
    01.02.2018  6.31542197
    01.03.2018  4.600364229
    01.04.2018  4.152160143
    29.04.2018  3.442227537
    30.04.2018  3.43343609
    01.05.2018  3.425108975
    01.06.2018  3.396918009
    01.07.2018  3.369342609
    01.08.2018  3.35360536
    01.09.2018  3.351036414
    01.10.2018  3.358021731
    01.11.2018  3.371659755
    01.12.2018  3.38805915
    01.01.2019  3.406137358
    01.02.2019  3.423831347
    01.03.2019  3.438710981
    01.04.2019  3.452972517
    01.05.2019  3.464318611
    01.06.2019  3.473092026
    01.07.2019  3.478872101
    01.08.2019  3.481948962
    01.09.2019  3.482171692
    01.10.2019  3.479855714
    01.11.2019  3.475044618
    01.12.2019  3.46824709
    01.01.2020  3.459274478

Now results is: Stair-like plot

I want to view something like this: Desired result

If you can add a screenshot of what you're getting and another of how you want it to look I'll see if I can help. — scanny, May 30 '18 at 19:45
@scanny, Yes of course. I am getting now this graph:i.stack.imgur.com/aong8.png. Next figure illustrates how I want iy to look: https://i.stack.imgur.com/SCeIN.png. Thank you — EugenS, May 31 '18 at 04:18
When you open the excel worksheet "behind" the chart in PowerPoint, does the data look as you expect? You get to this by right clicking on the chart and choosing "Edit Data in Excel" or something similar, depending on the version. This will tell you whether the problem is how you're writing the data or how that data is be presented. — scanny, May 31 '18 at 04:23
@scanny, I have checked it: excel worksheet contain corret data as expected. The problem is how data is presented. — EugenS, May 31 '18 at 04:29

score 1 · Accepted Answer · answered May 31 '18 at 04:45

I believe the problem is that you are creating an X/Y chart instead of a category chart. If you feed date values as the categories in a category chart, the X-axis automatically becomes a Date axis, and exhibits the behaviors I think you are looking for.

There is some additional information in the project technical documentation here: http://python-pptx.readthedocs.io/en/latest/dev/analysis/cht-date-axis.html

Briefly, the recipe is:

Use a CategoryChartData object instead of XyChartData
Use one of the Line Chart types instead of an XY type when you add the chart (maybe LINE or LINE_MARKERS).
Use datetime.date or datetime.datetime objects as the category values (instead of the usual str labels).
Possibly change LineSeries.smooth to suit (perhaps series.smooth = True).

I think that will give you what you're looking for.

HOW TO Ungroup chartdata with time-dependent values in python-pptx

1 Answers1