Excel to Python using Jupyter: Part 1

1. Introduction: Welcome to the introduction to Excel to Python using Jupiter, part one. My name is Benminbah and I've been presenting this course for Skillshare. Now in this course, we will be introducing you to the Python programming language. We'll assume you are new to Python, but familiar with Exl spreadsheets. We'll be using Jupiter Lab as a Python coding environment, and just note that this is part one of the course. There are many more to follow. We will show you the Jupiter lab environment and how to write Python code. This is typically the environment that we'll be using Jupiter lab in your browser. You can use this on a Windows machine, a Mac machine, and even on your phone. We'll show you some of the basics of Python, for example, printing Hello World and various print statements in a Jupiter lab environment. Also show you a small Excel example that we can work with. Here's an example of that where we have a few columns, car make model year, price, and we're going to show you how to use and plot some of the data here. We'll show you how to store data and do statistics on the data as well, for example, working on the average of columns, and we'll show you how to plot data as well. For example, the plotter of car prices versus CarMax. We'll show you also how to read in data files like CSV files using Panda's library. And that's it. We will be covering a few more features in the course. But for now, let's get started. 2. Project: Now, for the project, we're going to do a data science project using Python based on the skills that you'll learn in this course. So you're expected to download the dummy data CSV file from the resource section of this course, and you need to process the data and plot the data as well. This is the data that you're going to be using city and population columns that has various city names and the various populations that you can use. Now, you is required to store three columns from the CSV file into lists, namely ID, city and population. You're required to get the minimum, maximum count of the population list and average as in deviation as well. You're going to need to create a bar plot of the city and population, add labels to the X axis Y axis for your plot, add a title, and finally, save the plot and upload it. You see this is what you need to be doing for the project and ultimately what you're going to be learning. Let's get started. 3. Lesson 01 Getting Started: Great. So let's get started with using Python. So to use Python, you need an environment, and the environment we're going to be using to do our Python coding is called Jupiter Lab. So go to this link, Bh jupiLt dot gia dot IO slash DemoLAbSlash Index at HML. The link is also in this description. You can click on this link and you should see the following. This is an environment that you're going to be using. Again, you can use it in a Windows machine, a Mac machine, even on your phone, and this is where we'll start off with. The next thing to do is to go to the notebook section and select Python Brackets Pio dt option. Then you should see the following a cell that appears in your browser, and this is where you're going to be input text and ultimately code in Python. Now, our first example is something quite simple that you'll learn in every programming language, which is hello world. So in the cell, just type print brackets in double codes, hello world, and that's it. Go to your next line, press Enter and say, print, welcome to Python. These are your first two lines of Python code that you'll be going to be inputting into this cell. Now to execute the text or to run it in Python, you can press a triangle button in the toolbar on top or press Shift Enter. Once you do that, you'll see the following. You get the output of these two lines of code, which is H world and welcome to Python. Well, then, if you've just run your first two lines of Python code, this is excellent. All you did was use Python command called Print display some text to the screen. We use a print command all the time, and it's a function that is quite useful for debugging or printing text to the screen. After that, you'll see a new Python cells actually automatically created for you to enter your next line of Python code. You can also enter a new Python cell by clicking on the plus symbol in the toolbar as well. Well done, if you've gotten this far, let us move on to the next lesson. 4. Lesson 02 Python Basics: Great. Let's move on to Python basics. Let's learn some basic mas skills using Python. You can use Python as a simple or even a complex calculator. Let's input the following. One plus one, shift enter, you'll get two. Five minus two, Shift Enter, you get three. Ten times three, so for multiplication, we use the star symbol, you'll get 30. Ten star star three, this is for the power out. I get the power ten to power three, you get 1,000. 100/10 for division gives you 10.0. So in Python, you can also store variables and create formless ASL, which is very useful. So if you want to store the number five, for example, in a variable called X, you just say X equals five. You can print that value by saying print brackets X. You can also store the variable ten into Y, Y equals ten. Can print print brackets Y, and you get the result. Now let's create a formula. Let's add the two numbers X plus Y that is stored in before and input that result into z variable, and we print Z, we'll get 15. So we can also add comments with hash to help us understand what you're doing in the code as well. So this is quite useful. So in the next example, we're going to just say, add a hash in the front of a line, and this will allow Pythoners to ignore what you're typing. And this allows you as a user to read what you're trying to do. So let's store the number 5.1 in the variable coded percentage. That's what you're doing. Creating variable code percentage equals 5.1, print it out, and you get 5.1. We can also store text like letters, words and phrases as variables as well. Just make sure you always put the text around double quotes input the following. We're going to store John near a variable chord first name. We're going to say first name equals double quotes, John, D as a surname, surname, equals double quods do, and we can print the first name and print the surname as you can see below. John in the first line, do in the next line. Now to put the first name and surname next to each other, you can actually just use the plus symbol in the front command. So first name plus surname, you get John Doe. But you see, there's no space between it, so let's try and add one. I can do that by just saying plus double quotes and add a empty space plus surname and you get John Doe. Well done on getting so far, let's move on to the next lesson. 5. Lesson 03 An Excel Example: Great, let's start Lesson three Excel example. Let us say we have an Excel dataset like the following. We have car make, model year and pricing on columns and a column full of IDs. And this just tells us the year of the various makes and the various prices. So let's say we want to do some basic statistics from the price column, we can maybe get the minimum, maximum, the count, the average, and sand deviation quite easily in an Excel spreadsheet. Now, we can also do a plot as well of the various makes and the various prices. Now, how do you actually do that in Python? That is the important question. Well, let us start by storing all the data. Each cell can be represented as a variable, for example, the carmx. For example, we can say B two equism equals mercury, B three equals misshy, B four equals infinity, and we can print all those values for all those variables. However, this is quite tedious to store each cell individually. What you want is to rather have a variable that represents a column only, which we can do using a special variable type called lists using square brackets. So we can store all the car makes in a list variable. Just make sure do not use spaces in variable names. So we're going to say car and the square makes equals square brackets, and you can add the list of all the various Carme. Remember to be double quotes. And then at the very end, we can print comics and you get the following results. All the comics print to the screen. Now we sort all the CarMax, but just remember note to not use spaces and variable names, so we use car underscore Max. Now let's store the prices. In the same way, we are storing all the prices in the list type of variable, car underscore prices with square brackets, as you can see, and then reprinting those as well. While done, I'm getting this far, remember to try these examples yourself as you see them before you move on to the next lesson, well done. 6. Lesson 04 Python Statistics: All right, so let's go to the next section which is Python statistics. In the previous lesson, we were able to store the car max and car prices list variables. Now, let's get the statistics on the car prices column using Python's built in commands. Python is built in commands like min max, and length. For average me a formula. I Sandi variation, we use another Python tool called NumPy. So here, for example, we can get the minimum from the car prices this variable. So min, open brackets, car prices, give us some minimum value from that list variable. To get the maximum for the car prices, this variable, we say max, open bracket and put the car list in there to get the maximum car prices for that list. To get the count from the car prices, this variable, we just use a length function. As we can see below, we get the amount of Ross ten. To get the sum from the car prices, this variable, we use sum the car prices. This we all need for the average formally we're going to work out. To get the average from the formula, we divide sum and count for the car prices, this variable. Here we have average equals sum car prices divided by length of car prices. Print out the average and you get the average below. To get the standard deviation, we need to use a special library called NumPi to help us. Import NumPi is the first line of code you need to do. Then you store a variable or create a variable standard underscore Dev, make that equal to numpi dot STD or stand deviation, and you open brackets, car prices, and there you go. You can work with the sand deviation, and you print, then in the next line, you get the result that we are looking for. Well, then, should have seen the results we got above is the same as we got in the Excel spreadsheet. Let's move on to the next lesson. 7. Lesson 05 Python Plotting: In this lesson, we're going to focus on Python plotting. So we'll show you how to plot like we did in Excel. To plot, we need to use another Python tool or library called MatplotLib. Let's go ahead and input the following. We're going to import MTTlbtPiPlot, as PRT. Again, this is a library, you do this once and you can use all the plotting features that you need to use. Next line, we have PRT dot bar, and that allows you to create a bar plot. Then we have car max and car prices. So just to spotlight this again, we have plt dot bar. This creates the bar plot. Then we add the CarMax and car prices. These are the two input lists that we're going to plot. The next line PLT dot X label. This is just a label for the X axis. We make the CarMax. Then we have PLT Y label. This is for the car prices, the Y axis. Then we can create a title as well. So car prices versus CarMax, and we can also maybe change a font size as well as a parameter, at 14. The next line is useful to use a rotation for the X axis labels. PLT Xx Rotation equals 45 and font size equals ten. And then to plot, the actual figure, we use final line plt dot show. And then you should see the following appear. Here is the plot of the CarMax versus the car prices. While done, plotting is really useful feature for displaying your data, and there are many more ways to plot in Python, which will cover in future lessons. Let us move on to the next lesson. 8. Lesson 06 Reading in data with Pandas: Reading in data with Pandas. Now, previously, we input the data manually and sorted in as a list develop. In practice, if we have thousands of rows of data, that is not practical. We can use another Python tool called Pandas to read in a CSV file. First, you'll need to download the card that is CSV file found in the resources section of this course. Then you'll need to upload it to JPab environment. Click on the upload button on the left side Tobar. Here you can see the upload button that you can press, and here you can upload the files that you need to download from the resources section. So once uploaded, we can use the file in our code. Should see it in your file explorer on the left hand side. Here it is, you've downloaded it, and the car underscore data dot CSV is in our File Explorer. Which means now we can use it in Jup lab. What we're going to do is we're going to read in card data CSV with Pandas and display to the screen. So first thing we do we import pandas as PD. This is the initial line that tells Pandas, we're using this library through our code and gives us special functions for reading in CSV files and other things. We create a variable data equals PD dot Read CSV. This just allows us to read a CSV file and we name the actual file that we want to access, which is car under Sc data dot CSV and print the result. And as you expect, we can see the results as we've gotten before in the in the Excel spreadsheet and in other list variables that you've gotten. Now you can use a lot of built in features of Pandas, you can filter the data and get statistics automatically. So if you want to also maybe see the five rows of the data from CSV file, you can use data dot head, and you get the first five rows of the data. You can also get data info, which shows you information about the columns and rows as well. So well done for getting this far, this is the end of the course. There's so much more the Python and Pandas can do. We'll cover more about these features in the next course. We hope you learn something on your journey from Exle to Python. Thank you. 9. Closing: In this closing, we will briefly overview what we covered in this course. In this course, we focus on guiding you to get from Excel to Python using Jupiter lab. We showed you the Jupiter lab environment, write Python code. We showed you some of the basics of Python. We then worked with a small Excel dataset, we showed you how to store data and do statistics on the data itself. We also showed you how to plot and read indebted CSV files. There's much more to Python than we plan to cover. Stay tuned for next course and Excel to Python with Jupiter, part two. Happy coding.