Microsoft Excel Tutorial - Drawing a 3D Scatterplot using Python in Excel
Download the workbook: Excel Python 3D Scatterplot - Episode 2620 Sample Files - MrExcel Publishing
How to plot a circle in an Excel XY Chart
Converting degrees to radians in Excel
Building X, Y, Z values for a series of circles in Excel
Using Chat-GPT for Python Code for a 3D Scatter Plot
Adapting the copied code
Marker choices for Python Charts
Table of Contents
(0:00) Problem Statement - 3D Scatterplot in Python
(0:14) Excel formulas to plot a circle in Excel
(0:45) Convert degrees to Radians in Excel
(1:15) Formulas to Create a 3D Spiral in Excel
(2:45) Finding Python code from Chat-GPT
(3:10) Adapting Python code for Excel
(4:45) Making a tiny chart larger with a reference
(5:25) Marker choices in Python
(5:35) Wrap-up
Download the workbook: Excel Python 3D Scatterplot - Episode 2620 Sample Files - MrExcel Publishing
How to plot a circle in an Excel XY Chart
Converting degrees to radians in Excel
Building X, Y, Z values for a series of circles in Excel
Using Chat-GPT for Python Code for a 3D Scatter Plot
Adapting the copied code
Marker choices for Python Charts
Table of Contents
(0:00) Problem Statement - 3D Scatterplot in Python
(0:14) Excel formulas to plot a circle in Excel
(0:45) Convert degrees to Radians in Excel
(1:15) Formulas to Create a 3D Spiral in Excel
(2:45) Finding Python code from Chat-GPT
(3:10) Adapting Python code for Excel
(4:45) Making a tiny chart larger with a reference
(5:25) Marker choices in Python
(5:35) Wrap-up
Transcript of the video:
3D scatterplot in Python. One of the earlier videos I showed a cool scatter chart.
And someone said, "Hey, the graph's already done. How do we make a graph like that?" All right, so this goes back to my very first programming class.
I think it was 11th grade in high school on a TRS-80.
And I was just in the lab trying to do something cool. And I wanted to draw a circle.
And we had just learned that you take the radius times the SIN of the degrees.
And the radius times the COSINE of the degrees. But my circle didn't work at all.
And the teacher of that class said, "Oh yeah, you have to use radians".
And I still to this day cannot tell you what the heck a radian is.
So we have degrees here from zero to 360. And use the RADIANS function to convert it.
It goes from zero to 6.28. And what is 6.28?
Well, if you use the PI function and multiply it by two, you get 6.28.
So there are 2 * PI radians in a circle.
Anyway, once I changed this graph to use the SIN of the RADIANs of the degrees. Then all of a sudden, perfect circle.
All right, so, trying to create a fake data set here.
That would be something cool. I decided just to draw a bunch of circles.
So I have the degrees here going up in three degree increments.
And I just copied way down. It looks like tons of circles here.
To make sure I get between zero and 360, I use the MOD function.
The MOD function takes this number. Actually let's do it larger.
The MOD function takes this number, 534, divided by 360.
That's one and some remainder, MOD just shows us the remainder.
And then, let's see, I had a radius over here that I started out at six.
So the radius times the COS of the RADIANs of B. The radius times the SIN of the RADIAN of B.
And then for the height, I was just going to start at three and have each circle go up a little bit.
I figured it be more interesting to have it slowly kind of spiral up.
So I'm dividing this number of degrees by 360.
And adding it to three plus the number of previous circles. For color, it goes from zero to 18.
We'll see how that evolves.
And then the radius here, I had slowly expand again. All right, so there's a data set that we can use.
I have X, Y, and Z. I'm also going to use the color.
We want to figure out how to create the Python for that.
All right, we'll go into Python with Ctrl, Alt, Shift, P.
Make the formula bar larger with Ctrl, Shift, U.
Up until this point, I've been using the documentation at matplotlib. It worked really well.
But I'm going to tell you that it didn't work when I tried to create this chart.
So I went out to ChatGPT. Asked for Python code to create a 3D-scatterplot.
From a DataFrame with columns for X, Y, and Z. And they gave me this code.
Copy the code.
Okay, we'll paste. All right.
We know that because Excel is initializing, they already import pandas and matplotlib so we can get rid of those lines.
This, though, is one that's not imported, so we have to leave that line.
Here they're creating a sample DataFrame called df. So we'll just say df is equal to.
And I'm going to point to my dataset. So the X, Y, Z, and color.
So we can get rid of that sample dataset. We'll leave the 3D scatterplot there.
They get the X, Y, and Z values and I happen to just use the same headings that were in the example. That's beautiful.
Out here though for this is C equals color and I'm going to use my DataFrame objects.
So df.Color.
And for marker. Marker.
Check this out.
There's a great place that talks about all the different possible markers. So right now they're using a lowercase o.
You see down here, which is a circle. I'm going to use something smaller.
I'm going to use a period to get a point. There's even a comma for a pixel.
Let's just try and go with a point. And then they're putting labels on the axes.
If you wanted these to be something in particular, you could.
I don't really have anything other than just I guess X, Y, and Z.
And then the scatterplot.
“3D scatterplot example in Python”, that's the title. And then plot.show.
All right, there we go. We should be able to do Ctrl, Enter.
And wait for it to render all of those. All right, there's our very, very tiny chart.
I'll press Ctrl, Shift, U to collapse the formula bar.
Right click on the chart, go to picture in cell and create reference.
It brings a nice big version of that chart out. All right, so there we go.
Creating a scatterplot.
Mostly just copying the code from ChatGPT in this case.
Usually I go straight to the code samples from matplot.lib. This worked as well.
An example of creating a three dimensional scatter chart in Python and various marker choices.
There's even all kinds of crazy ones down here.
All right, if you want to download the workbook, the link is down in the YouTube description. I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
And someone said, "Hey, the graph's already done. How do we make a graph like that?" All right, so this goes back to my very first programming class.
I think it was 11th grade in high school on a TRS-80.
And I was just in the lab trying to do something cool. And I wanted to draw a circle.
And we had just learned that you take the radius times the SIN of the degrees.
And the radius times the COSINE of the degrees. But my circle didn't work at all.
And the teacher of that class said, "Oh yeah, you have to use radians".
And I still to this day cannot tell you what the heck a radian is.
So we have degrees here from zero to 360. And use the RADIANS function to convert it.
It goes from zero to 6.28. And what is 6.28?
Well, if you use the PI function and multiply it by two, you get 6.28.
So there are 2 * PI radians in a circle.
Anyway, once I changed this graph to use the SIN of the RADIANs of the degrees. Then all of a sudden, perfect circle.
All right, so, trying to create a fake data set here.
That would be something cool. I decided just to draw a bunch of circles.
So I have the degrees here going up in three degree increments.
And I just copied way down. It looks like tons of circles here.
To make sure I get between zero and 360, I use the MOD function.
The MOD function takes this number. Actually let's do it larger.
The MOD function takes this number, 534, divided by 360.
That's one and some remainder, MOD just shows us the remainder.
And then, let's see, I had a radius over here that I started out at six.
So the radius times the COS of the RADIANs of B. The radius times the SIN of the RADIAN of B.
And then for the height, I was just going to start at three and have each circle go up a little bit.
I figured it be more interesting to have it slowly kind of spiral up.
So I'm dividing this number of degrees by 360.
And adding it to three plus the number of previous circles. For color, it goes from zero to 18.
We'll see how that evolves.
And then the radius here, I had slowly expand again. All right, so there's a data set that we can use.
I have X, Y, and Z. I'm also going to use the color.
We want to figure out how to create the Python for that.
All right, we'll go into Python with Ctrl, Alt, Shift, P.
Make the formula bar larger with Ctrl, Shift, U.
Up until this point, I've been using the documentation at matplotlib. It worked really well.
But I'm going to tell you that it didn't work when I tried to create this chart.
So I went out to ChatGPT. Asked for Python code to create a 3D-scatterplot.
From a DataFrame with columns for X, Y, and Z. And they gave me this code.
Copy the code.
Okay, we'll paste. All right.
We know that because Excel is initializing, they already import pandas and matplotlib so we can get rid of those lines.
This, though, is one that's not imported, so we have to leave that line.
Here they're creating a sample DataFrame called df. So we'll just say df is equal to.
And I'm going to point to my dataset. So the X, Y, Z, and color.
So we can get rid of that sample dataset. We'll leave the 3D scatterplot there.
They get the X, Y, and Z values and I happen to just use the same headings that were in the example. That's beautiful.
Out here though for this is C equals color and I'm going to use my DataFrame objects.
So df.Color.
And for marker. Marker.
Check this out.
There's a great place that talks about all the different possible markers. So right now they're using a lowercase o.
You see down here, which is a circle. I'm going to use something smaller.
I'm going to use a period to get a point. There's even a comma for a pixel.
Let's just try and go with a point. And then they're putting labels on the axes.
If you wanted these to be something in particular, you could.
I don't really have anything other than just I guess X, Y, and Z.
And then the scatterplot.
“3D scatterplot example in Python”, that's the title. And then plot.show.
All right, there we go. We should be able to do Ctrl, Enter.
And wait for it to render all of those. All right, there's our very, very tiny chart.
I'll press Ctrl, Shift, U to collapse the formula bar.
Right click on the chart, go to picture in cell and create reference.
It brings a nice big version of that chart out. All right, so there we go.
Creating a scatterplot.
Mostly just copying the code from ChatGPT in this case.
Usually I go straight to the code samples from matplot.lib. This worked as well.
An example of creating a three dimensional scatter chart in Python and various marker choices.
There's even all kinds of crazy ones down here.
All right, if you want to download the workbook, the link is down in the YouTube description. I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
