Plotting high-low range chart in python pptx with average/median marker

Question

I have a specific problem, I'm trying to plot a horizontal range chart showing the low to high range while marking the average with a marker say diamond, so far I have tried different methods, the best that I have got is below output with the following code. Was wondering if this is something possible using python-pptx?

Code as following, please note I'm using a sample data in the code:

# Import relevant packages
import pandas as pd
import numpy as np
from pptx import Presentation
from pptx.dml.color import RGBColor
from pptx.chart.data import CategoryChartData, ChartData
from pptx.enum.chart import (XL_CHART_TYPE, XL_LEGEND_POSITION, XL_DATA_LABEL_POSITION, XL_TICK_LABEL_POSITION, XL_TICK_MARK, XL_LABEL_POSITION,XL_TICK_MARK)

# Set up data to be used in the chart
lows = np.array([12.1, 12.6, 9.9, 9.4, 13.6]) # all the 1 year lows
highs = np.array([20.6, 21.7, 15.1, 16.1, 24.0]) # all the 1 year highs
avgs = [16.5, 17.3, 13.4, 12.3, 19.0]
names2 = ['A', 'B', 'C', 'D', 'E']

high_low_range = (highs - lows).round(1).tolist()
# Add chart data
chart_data3 = ChartData()
chart_data3.categories = names2
chart_data3.add_series("1Y Low", lows)
chart_data3.add_series("1Y High", high_low_range)

# Add chart to slide
x, y, cx, cy = Inches(6.3), Inches(1.), Inches(4.8), Inches(3.5)
chart = slide.shapes.add_chart(XL_CHART_TYPE.BAR_STACKED, x, y, cx, cy, chart_data3).chart

# The idea is to plot 2 bar charts, one for low and one for high, and simply make the first bar chart invisible
# Set the chart type to a horizontal range chart
chart.has_legend = False

series1 = chart.series[0]  # that's the first bar chart up to low value
for point in series1.points:
    # plot = chart.plots[0]
    fill = point.format.fill
    fill.background()  # do a background fill

series2 = chart.series[1]  # that's the range bar chart
for point in series2.points:
    fill = point.format.fill
    fill.solid()  # fill the colour with light blue
    fill.fore_color.rgb = RGBColor(135, 175, 191)

# Remove all the gridlines, we don't need them
value_axis = chart.value_axis
value_axis.visible = True

# Set Category Axis
category_axis = chart.category_axis
category_axis.visible = True
category_axis.format.line.fill.background()

I would like to create a chart like this, will someone be able to point some pointers or offer guidance on this?

Answer 1

This desired outcome is achievable, but a different approach must be taken, and it might not look quite as good as you are hoping. Such are the limits of using pythonpptx for data visualization. I recommend using Matplotlib next time or R-lang.

---

Four different ranges must be defined. lows, avgs_minus_x, avgs_plus_x, and highs.

You already have lows, so let's define the others. The value x is going to be half the width of the average indicator. (Edit: For the sake of simplicity, I chose an arbitrary value of .2. You can edit this value based on your preference.)

avgs_minus_x = ((avgs - lows) - .2).round(1).tolist() #Here x is .2.
avgs_plus_x = ((highs - (avgs + .2).round(1).tolist() #x also .2 here.
avg_range = [.4 for x in lows]                        #.2 + .2 = .4

We also have to add them to the chart_data3 series. Note the order, important!

chart_data3.add_series("1Y avg_to_low", avgs_minus_x)
chart_data3.add_series("1Y avg_range", avg_range)
chart_data3.add_series("1Y avg_to_high", avgs_plus_x)

Finally, we need to systematically add these series to the chart.

for point in chart.series[0].points:
    fill = point.format.fill
    fill.background()

for point in chart.series[1].points:
    fill = point.format.fill
    fill.solid()
    fill.fore_color.rgb = RGBColor(135, 175, 191) #light blue

for point in chart.series[2].points:
    fill = point.format.fill
    fill.solid()
    fill.fore_color.rgb = RGBColor(225, 0, 0) #red

for point in chart.series[3].points:
    fill = point.format.fill
    fill.solid()
    fill.fore_color.rgb = RGBColor(135, 175, 191) #light blue

The result:

---

This outcome is essentially an illusion since it gives the appearance of the red line being superimposed over the blue bar, but this is not reality. In practice, we are adding a blue line up to the average, changing the color to red, adding a very small value to look like a line, changing the color back to blue, and then filling up to the high value. The two blue sections are not connected in any way in the code, it just looks like they are.

---

I hope this is what you were looking for!