How do you replace data for an existing line chart using python-pptx?

Question

I have a prebuilt and populated powerpoint presentation where I am modifying the data for charts and tables. I would like to retain all formatting (and much of the text), but replace the data in a line chart within a slide.

I have a function that will replace the data using a pandas data frame that works with bar charts.

def replaceCategoryChart(df, chart, skipLastCol=0):
    """
    Replaces Category chartdata for a simple series chart. e.g. Nonfarm Employment

    Parameters:
    df: dataframe containing new data. column 0 is the categories
    chart: the powerpoint shape chart.
    skipLast: 0=don't skip last column, 1+ will skip that many columns from the end

    Returns: replaced chart(?)
    """
    cols1= list(df)
    #print(cols1)
    #create chart data object
    chart_data = CategoryChartData()
    #create categories
    chart_data.categories=df[cols1[0]]
    # Loop over all series
    for col in cols1[1:-skipLastCol]:
        chart_data.add_series(col, df[col])
    #replace chart data
    chart.replace_data(chart_data)

...
S0_L=  pd.read_excel(EXCEL_BOOK, sheet_name="S0_L", usecols="A:F")
S0_L_chart = prs.slides[0].shapes[3].chart
print(S0_L)
replaceCategoryChart(S0_L, S0_L_chart)
...

The python file runs successfully, however, when I open the powerpoint file I get the error

Powerpoint found a problem with content in Name.pptx. 

Powerpoint can attempt to repair the presentation.

If you trust the source of this presentation, click Repair.

After clicking repair, the slide I attempted to modify is replaced by a blank layout.

Because this function works for bar charts, I think there is a mistake in the way I am understanding how to use replace_data() for a line chart.

Thank you for your help!

score 1 · Answer 1 · answered Aug 08 '19 at 03:36

1

If your "line chart" is an "XY Scatter" chart, you'll need a different chart-data object, the XyChartData object and then to populate its XySeries objects: https://python-pptx.readthedocs.io/en/latest/api/chart-data.html#pptx.chart.data.XyChartData

I would recommend starting by getting it working using literal values, e.g. "South" and 1.05, and then proceed to supply the values from Pandas dataframes. That way you're sure the python-pptx part of your code is properly structured and you'll know where to go looking for any problems that arise.

answered Aug 08 '19 at 03:36

scanny

26,423
5
54
80

Thank you, Scanny! My chart has a horizontal (category) axis and a vertical (Value) axis which makes it a line chart, correct? https://python-pptx.readthedocs.io/en/latest/user/charts.html#line-chart If so, does replace_data() work on it? – user2529589 Aug 08 '19 at 03:52
1

Okay, yes, then `CategoryChartData` is the appropriate one to use here. Try doing it with literals and see if you get the same repair error. If you can isolate the offending data it may help to make the library more robust; we generally like to close up any ways a user can produce a repair error. – scanny Aug 08 '19 at 16:58
Thank you, again, Scanny! This isolation method was extremely helpful. replace_data() does work for category line charts, in the same way as it would for category bar charts. I created a repair error by incorrectly looping through columns (I think I didn't add any series data, which caused the powerpoint to explode). – user2529589 Aug 08 '19 at 19:36

score 0 · Accepted Answer · answered Aug 08 '19 at 19:41

As scanny mentioned, replace_data() does work for category line charts.

The repair error was (probably) caused by incorrectly adding series data, (there was a bad loop, corrected below).

    # Loop over all series
    for col in cols1[1:len(cols1)-skipLastCol]:
        print('Type of column is ' + str(type(col)))
        chart_data.add_series(col, df[col])

How do you replace data for an existing line chart using python-pptx?

2 Answers2