Locked lesson.

Upgrade

About this lesson

Use these steps to add a Bubble Chart to your spreadsheet.

Exercise files

Download this lesson’s related exercise files.

36 - Bubble Chart.docx
60.4 KB 36 - Bubble Chart SOLUTION.docx
57.1 KB

Quick reference

Bubble Chart

In this video we'll look at Bubble Charts.

When to use

Use these whenever you need a Bubble Chart in your spreadsheet.

Instructions

# Import the charts
from openpyxl.chart import Reference, Series, BubbleChart

# Create a Chart instance
chart = BubbleChart()

# Load data differently than other charts
xvalues = Reference(ws, min_col=1, min_row=2, max_row=5)
yvalues = Reference(ws, min_col=2, min_row=2, max_row=5)
size = Reference(ws, min_col=3, min_row=2, max_row=5)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2020")
chart.series.append(series)

# Add second set of data
xvalues = Reference(ws, min_col=1, min_row=7, max_row=10)
yvalues = Reference(ws, min_col=2, min_row=7, max_row=10)
size = Reference(ws, min_col=3, min_row=7, max_row=10)
series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2021")
chart.series.append(series)

Hints & tips

  • from openpyxl.chart import Reference, Series, BubbleChart
  • chart = BubbleChart()
  • Bubble Charts load data differently than other charts
Login to download
  • 00:04 Okay, in this video I want to look at bubble charts.
  • 00:07 So I've created a file called bubble.py.
  • 00:09 And we're going to load this bubble worksheet and
  • 00:12 we're going to save it as always as bubble2 down here.
  • 00:16 And before we get into this code,
  • 00:17 let's take a look at this worksheet really quickly.
  • 00:20 And you can see, I've just got some dummy data here, products, sales and
  • 00:23 market share.
  • 00:24 And so this is products, we're just using numbers instead of names.
  • 00:28 This is how many sales we had of each one.
  • 00:30 And this is the market share of that product.
  • 00:33 So we've got two sort of sets of data, and I kind of separated them just so
  • 00:38 visually, it's easier to keep track of these.
  • 00:41 Let's call these sales from, I don't know, 2020, and
  • 00:44 let's call these sales from 2021.
  • 00:46 So just some basic dummy data and that's what we're going to deal with.
  • 00:50 So, I'll head back over to our file and we're importing BubbleChart.
  • 00:54 We are also importing Series and we're importing Reference.
  • 00:58 So we can start by loading our spreadsheet as always,
  • 01:01 going to set the chart as BubbleChart.
  • 01:03 And then this is where it gets a little different than all the other charts we've
  • 01:07 done in the past.
  • 01:08 If you take a look at some of the other charts we've done like the donut charts we
  • 01:11 just looked at, we have these labels and we have these data.
  • 01:14 And then we add the data and the labels.
  • 01:17 And most of our charts and graphs have pretty much followed this
  • 01:21 same layout where we reference our data and our labels.
  • 01:25 And we set the columns like this.
  • 01:27 Well with the bubble chart, we still set the columns and rows,
  • 01:31 we just do it a little differently.
  • 01:33 Instead of labels and data, we're calling x values and y values and size.
  • 01:38 And the reason why this is size is because we're dealing with market share and
  • 01:42 the size of our bubbles will equal the size of that market share.
  • 01:45 So same deal, we're going to set our columns using the Reference tag here.
  • 01:52 And then our worksheet, and let me pull up our data and
  • 01:56 kind of bring it in like this.
  • 01:58 So you can see for our x values we've got, we started minimum column 1.
  • 02:02 That's right here, right?
  • 02:04 And we go minimum row 2 through 5, so that's row 2 through 5, so
  • 02:09 that's going to pull all of this data, right?
  • 02:13 Those are the x values.
  • 02:14 For the y values, we're going to start at column 2 and we're going to go
  • 02:19 row 2 through row 5, and so we're just going to call this stuff here, right?
  • 02:26 So the main difference is in the x values, we've got this and
  • 02:29 the y values we don't, right?
  • 02:31 And then finally the size, we just go straight into column 3,
  • 02:34 that's our market share.
  • 02:36 So min column 3, min row 2 through 5.
  • 02:39 That's right here, 2 through 5, right?
  • 02:42 So sort of the same thing, we're just kind of calling them different things,
  • 02:46 x values and y values versus labels and data basically, right?
  • 02:50 So, okay, once we do that, we have to put all of that stuff together.
  • 02:54 So we call series = Series, and that's this guy right up here,
  • 02:59 and we set our values to yvalues, that's this.
  • 03:03 We set our xvalues to xvalues, which is this.
  • 03:07 Set our zvalues to the size, which is obviously this, and
  • 03:10 then let's give this thing a title of 2020.
  • 03:13 Now this is not going to be the title of the chart,
  • 03:15 this is more like the legend title for this set of data, right?
  • 03:19 So okay, we can then come down here and do the exact same thing for
  • 03:23 the other chunk of data that we've got.
  • 03:26 So if we pull this up, that was all this stuff.
  • 03:29 We do it again for this second chunk right here, right?
  • 03:34 And again, we call xvalues, yvalues, size and
  • 03:36 we just set the different columns in those exact same way we did up here, just for
  • 03:41 that second chunk of data and again, we put it all together.
  • 03:44 So after we've sort of defined both of our chunks here,
  • 03:50 this chunk and this chunk, right,
  • 03:53 we also need to chart.series.append this thing.
  • 03:58 And we do that for each of these, so right here and right here.
  • 04:02 So that's really all there is to it.
  • 04:04 Now we can set a chart style like we've done many times before.
  • 04:07 And again, this is just a number, you can pick any number you want.
  • 04:11 I will go with 18.
  • 04:12 And then finally we just add the chart in the same way we've always added
  • 04:15 our charts.
  • 04:16 Let's stick this in E1. If we look at our data again really
  • 04:19 quickly, that's going to be right here.
  • 04:23 It's going to be right here, right here at the top.
  • 04:25 So we give some space in the D column and then our chart will go right there.
  • 04:29 So, okay, so looking through this, it looks good.
  • 04:34 Up here, we also need to load our workbook, right?
  • 04:38 Left that off.
  • 04:38 So just like all the other things we've done throughout this course,
  • 04:41 we need to load our workbook.
  • 04:42 So okay, let's go ahead and save this.
  • 04:44 And now let's run this.
  • 04:45 So let's go python bubble.py, and now we can head over here and open bubble2.
  • 04:52 And when we do, we see this cool bubble chart.
  • 04:55 And we can move it around a little bit.
  • 04:57 And so the blue is this first set of data.
  • 05:00 The red is the second set of data.
  • 05:03 And sort of the relative size of each bubble, that's your market share.
  • 05:08 And you can look through here, so this one right here, so this is we're over 20.
  • 05:12 So that would be this guy right here, we're up 60.
  • 05:17 Sure enough 60, and 33% is the relative market share, not the biggest one.
  • 05:24 That would be this one right here, which would be this guy.
  • 05:28 So if we look here over, 22 yeah, that's about right, up 32.
  • 05:31 That's about right, and very cool.
  • 05:35 So that's the bubble chart.
  • 05:37 And that's all for this video.

Lesson notes are only available for subscribers.

Gift this course