Excel to Python: A Data Science Crash Course

1. Introduction: Do you do data science with Excel and then Python? And welcome to a Skillshare crash course where you will learn the fundamentals of Python for data science. I have over 15 years of experience in computer science and data science with a speciality in Python. I work as a lead software developer and also tooted, mentored and coached hundreds of students and program. So why take the class? Do you want to learn Python grade? This is the course for you. You do similar things in Excel, as you can see on the screen. You want to improve your data science and even Excel skills in Python is a way to go. What will you do? You will learn the fundamentals of Python and your practical data science project. What will you need? Well, this is a beginners class, so we assuming you have no experience in Python, and you don't need to install any fancy software to you just in a browser and Internet. We will cover the basics of Python like storing data, variables, functions, and we will also do some stats like finding the average and standard deviation. Mostly. We'll also be showing you how to read a data set like a CSV file and do some plotting as shown on the screen. Now this one, I'm going to be useful if you have some practical project your pliers with. And so I've created a project using Excel data is you need to process with Python. This will evaluate all that you need to know. What will you need. Again, just a browsable the Internet as shown on the screen. You will be doing all your coding in a browser. And I will show you step-by-step, what shouldn't you do an unexplained what is necessary to get going? Let's get started. 2. Project: Alright, so you will be doing a simple data science project to test whatever event in this crash course. So let's have a look at what you'll need to do. So the best way to learn is to do, alright. So this is a data science project and is relevant to your day-to-day tasks that you would do. For example, in Excel, I have provided you with Excel data that you need to now process in Python. The great thing is you can use your own debt if you want to as well. Before we get on. Let us first have a look of what you need to do. So there is a dummy data PDF and the resource folder and a tomato data CSV file you can use for your own data or it can use on data. And your project is to take an Excel dataset and process the data in Python. You'll use all the skills you learned in this course, has an extra challenge. Try and pursue that in the same way. Now what are the general outcomes? Firstly, you read in data and saw columns from data CSV into lists. Then only to work out the minimum, maximum population in the population list and count. You also need to work out the average population and sudden deviation, for example, and store those values as variables. Lastly, we wanted to create a plot of a city in population lists and add various plot features as well. For example, it looks similar to what you'll see on the screen. Right? Let's get started. 3. Lesson 01: Excel vs Python: Let us now begin with the first lesson where we will compare excel versus Python. So when you want to compare Excel with Python, we want to see how the tools differ and look at the similarities and differences. So both are used in the field of data science, data processing, statistics, data analytics, and much more, knows allowed to store large amounts of data versus the data and plot the result. Both are quite easy to use and obviously very popular. Now let's look at the differences. Hi thing is code-based, whereas XOR is software interface tool. However, you can run code in Excel, like using VBA, I think can handle large amounts of data better than Excel and is more efficient when processing the data. I think it's free, whereas Excel format, whatever you do, get free. Alternatives, I think is a widely used in various applications beyond the scope of that excellent Can knowledge. And XL is simply to use high event types of data handling, automation, scalability, Python, high-performance, Excel. This is the end of lesson one. 4. Lesson 02: Python Learning Advice & Tips: For lesson two, we're going to look a bit about lending advice and how to make use of this course was Scotia. So I'm gonna give you a bit of tips on learning, right? So the best way to learn a programming language is to code. Best way to learn is to do things right? Tend to do things with programming. You have to code. So as you're going through these lessons, is best to pause the lessons, copy the code I've done, and try to solve in the browser, as I will show you, step-by-step process. Don't worry too much about the technical details. Just make sure you can understand how it works. The details will come as time progresses and get more experience with coding. Remember, google is your friend. If you Google it, find out what maybe sudden functions of features meaning Python. It is very useful. Also important when you're learning and trying to benefit most of this course, tried to keep it relevant. Tried to have some project in mind that you want to do or something that you might want to practice. And always keep trying. That's the best way to that. And always the best way to keep in mind that you all learn and gain experience as well. Thank you. That's the end of lesson two. 5. Lesson 03: Python Coding Environment: So let's move on to lesson three. We will discuss the coding environment which will be used. So there are many ways to run Python, namely browsers, text editors, and IDEs. You can choose any option you are comfortable with. However, for the purpose of this crash course and for complete beginners to Python, we will stick to using browsers. So the easiest way to get up and running with Python is use a browser. And there are hundreds of sites that allowed you on Python on your browser. And we will be using the following link, which I showed over here is triggered at IO. Three. You can use text editors as well. For example, you can use any text editor and type code in it, append the files dot PY and then run it in your Python environment. Some popular ones are sublime, Atom, Notepad Plus, Plus Emacs and many more. Remember, you will have to have installed Python environment on your operating system. Ides. So IDE or integrated development environment is required for almost all programming languages. That involves installing Python on your computer. I've been running the code and either console or did it get software like PyCharm VS Code spider? I don't anymore. So the coding environment, again, we'll be using is in a browser. And if you click on this link, you will see the following page pop-up. You can use most browsers. However, I recommend Chrome. If you click on the link, you should see the following. Using Chrome on Windows ten, and you'll see it in the browser as well. So the first thing we're gonna do is type out the following code in the main that pi script file, as you can see below. So you'll see there's a man of pi fall here and just type out, print open brackets, double-quotes, getting salt in Python. So that's all I wanted to do for now. In your browser, you should have this when you type it out. We'll discuss different color codes. But for now, just make sure you type this out. Next step is we're going to click on this drop-down and we're going to click on Run. And then you should see on your right-hand side, at the output of this print statement, it's going to be the same thing that you had it. So again, you saw that in Python, you want to print this to the console. This is what you call the console output of your code. And you'll see getting started in Python. And this is your first script that you wrote in Python. So also, just to remind you, you will see the screen on the right showing the texts that was inside the brackets, right? So this is again the ticks you'll see in the brackets. And as you first code is run Python, which is written in the script. We will discuss what difference between bitumens script and running things in the console is a bit later. So now what you're gonna do is we're going to remove what was in multiplied by that was that print statement. And you're going to click on the drop-down arrow and click on Console. You'll see now the screen on the right startup with three arrows, right, 33 arrows over here, which represents your Python console. We can do quick calculations. The left-hand side is where you do most of your Python coding the script, and you can run it as well. So for example, we can do a quick calculations in your console here. But most of the work will be done in a script on the left-hand side. So for example, if you want to type out one plus one in your console, you'll get to unselect tool. So it works very similar to like a basic calculator. That's it. That's your heart to operate your console in Python. But again, we will be using the script, writing my Python scripts in this course. So let's just do a practical example of what we just learned. So again, we're going to print out the state and you want, which is getting started with Python. In Python. So if you make a mistake, backspace, and then that's the statement. Once we go to drop-down, click on Run, then we should get an output on the right-hand side, which is getting sudden Python. And then as your first script that you wrote, it cannot delete it and go to the console button. And then we can say, for example, one plus one. And we get the answer of two. And that's it. That's how you can run a script file and run code in the console. Thank you, That is the end of the lesson. 6. Lesson 04: An Excel Example For Python: Alright, so now we're onto the next lesson and Excel example, we can use a dummy dataset, car model information. So let us firstly, some common tasks Excel on a dataset to see where we're headed. Have a look at the data below. We have some data that has four columns of comic information. Now, we might want to do a few things with the data. For example, some stats on column D or the price column, where we want to find the minimum, the minimum, maximum count, average, standard deviation, the code used to excel. This should be quite a standard process for you to do. As we can see, using built-in functions in Excel, we can work out these steps. We can also do some common tasks like plot, the data bar graph, a line plot, which is all quite straightforward to do in Excel if you familiar with it. And now the question is how to do the exact same thing in Python? So again, just to make sure you're all familiar with Excel, I will show you the steps that you need to do to work out some stats using this data. So we're going to work at a min, max count, average, standard deviation using the built-in Excel functions. This should be quite familiar if you are used to excel. So we can work out all these different sets and metrics for the column, the price column. And you'll see the results coming out. We could also, for example, just makes it bold. And then we can also plot some data. Maybe let's take column B and column D, go to Insert and then click on a bar chart. Then we can display some of the data. We can do another chalk, maybe, maybe a line chart. We can go back to Insert. Click on Line shot. Also scatter plots with lines and get the following data. So again, this should be all quite familiar to you if you use Excel. Thank you. This is the end of the lesson. 7. Lesson 05: Python Fundamentals: Alright, so now let's move on to the next lesson, which is Python basics. But then fundamentals of Python to get you started. So the first thing we're going to look at is math operators. Take note that unlike other Python courses, we're not going to show all the different aspects to Python. We're going to show you only what you need for to do the same task because I didn't. Xl reaches a bit about Python and how it works. To go ahead and click on your browser, triggered at IO such passion Python three. And that should open up your browser and you should see the window that you saw before in the initial listen on the environment. So you can do some basic calculations with Python script that is practiced. Few examples with comments and column math operators. So as you can see, I have written a few examples how to add numbers. Subtract two numbers, multiply two numbers, divide, find the power. So we'll see this in practice but later. But if you can pause the video and practice tapping out the exact same thing here, and when you run it, you should get the following output. Just remember, a comment is anything that starts with a hash. Code becomes green, which means nothing will run. This is just a comment. Help what you're coding. Let's see this in practice. Remember anything that he started with, the hash will be a comment. So you can type in anything it won't run as part of your code. It is just for comments to help you remember what you did in your code. So the first thing wouldn't use actual numbers. Using the print statement, which is ageing numbers. You can run it. Let me get the answer 50, which is what we expect. Or we can subtract the numbers. We can also then multiply two numbers. And then you can also find a different head to two numbers. And then we can also very importantly, what cut the power of a number, for example, of three, of two, can run that and then we'll get the answer as we expect. So the next thing we're gonna look at is Python basics with variables. So just like an Excel example where we stored that isn't a sudden south, like A2 and A3. They can create the same variable names and Python script and also store the same values. For entering out those values. Again, we can see the values that are stored inside them. So when storing text, whereas the sentences we have to store the values by enclosing them in double-quotes like valleys P2 and P3. So as you can see, you can pause the video, copy this code, and type it out in the main.py file and run it. And you should get the following. And let see a practical example of this. So we're going to look at an Excel dataset. We can call a variable A1 and A2, for example, to A2 equals one, A3 equals to, for example, storing variables. And we can print those variables out. And you should get the same result. And then if you run it, we should get 12, which is stored in those variables. With the words with texts. We must enclose those variables with double-quotes. I can see that example now coming here. So for example, B23, mercury and B3 are being so busy making me print this out as well. Print this out then you can see the results that you get. Alright, so Python, formulas and functions, this is quite important part of Python. So just like in Excel, you can also create formulas and equations with variables. For example, if we went to store the values for the model, yeah, compare and see how long ago the ice was. We can do that as well. So we can also group takes some variables and one opera statement using the built-in print function for example. So below, you can pause the video and copy this code out. What we're doing is restoring a variable for c1 is equal to 2001, a year or candy, or for example, 2022. And we can find a simple formula. It's working on the difference. We can set a year minus C1, which is maybe the model here for this car. How we can put a difference. And we'll see the value is 21, so it should be 21 years. Then we can also print out texts as well. Sample of just printing out a text. So Python is cool as we did in the first example of writing a script. And then how do you print text with the variables? Or you could print out your text and then comma the variable name, which would be yeah, So we can say the current year is to enter into so you can pause the video, copy this out, make sure you get it. And they see a practical example of this as well. Now if we're storing available for C1, then for yeah, we can find the difference between the data and variables c1. So just subtracting two integers, Actually, they can print out that value. Then we can again just print out if you want to text it can. We've done this before on the first script you wrote. For example, say pattern is cool. Then if you want to say print texts with variables, we can do that as well. So for example, the county it is and just say comma the year, the variable name agile. Run it. We get the output as we expect. Right now, how do we store many, store, storing up many values? This is quite common and important feature that we use an exon if you have columns that are full of data and different rows have lots of rows that had dated. So how do you do the same thing in Python? So for example, in column C, The model year has variable C1, C2, C3, and so on. And what we can do is create a variable model yet install many values. So one type of variable you can solve, lots of values. Values is quite a list. It is done as follows. So what you do is we can call it mobile yet, equals and then square brackets. We can use, or we can store multiple variables. Previously we didn't have any brackets that's fostering single values, but with square brackets and commas in between, we can store multiple, sorry, multiple values. So if you create that variable and print it out, you'll see that you'll get the output similar to what we have in column C in Excel. So if we see a practical example of this, using the data from Excel column, we have Maria can tap up all the values that we have an excellent dataset. Then if you print it out, you should get the following. Thank you. That is the end of this lesson. 8. Lesson 06: Python Statistics: Alright, so let's move on to the next lesson, which is six statistics. Here we will cover looking at minimum, maximum count, average, and standard deviation. So we're going to dig a bit deeper and come closer to performing the same tasks that you need with Excel. The previous lesson with the last thing we did was we stored all the values of model yet in the list variable. And remember this variable, you can store a lot of values. Now, we are going to do the same thing with the price column. Then just like we have built-in functions in Excel for minimum, maximum, and count, we have the same functionality in Python. There's one difference. So to our cut the count in Python, we use the abbreviated either n or length function, as you can see below. Now if we look at the code, what we did was we stored all the price values in a list variable. You can see them over here. And then we print out that list so I can see all the values on the right-hand side. And then to work out the minimum of a price, we say men, square brackets and the price, what works at a minimum value? I cut the max here with the stored that max value in a variable called max. Printed that out so we get the next price value. But then to work out the next V is the length bottom function, and we looked at price. So let's see this in action. So first we're going to just write a comment, that puzzle what you're doing. Then we need to store all the values in a price column. So all the values like we did before, square brackets, signs that the value of all the values from column. So if we just check all the values you need, It's all just copy that actually form the Excel spreadsheet subtyping and an art store it with square brackets into the variable name called price. Then we can just print out that list of variables. We run it. We can see in the right-hand side the values being printed out. Now, we can use a built-in function men to work out the men of the price. We can also store a variable, variable for the maximum value, max square brackets for price as well. Then if you want to maybe print that out, we can, if we run it, we can see we get the maximum and minimum values printed out on the right-hand side. And then to work out the count, we use the built-in function. We say comma then in full length for price and run it. And you'll see we get the value that we expect to work out, the average and standard deviation. We will use more built-in functions in Python. So for that, we will need to use the sum function and length function to work at the standard deviation. We need to import an extra library in Python. Libraries are just ways to add extra functionality to existing Python code. And the library that we will use is called num pi. So we did this in the following way. You see below that code, we have our list of variable with all the values. I went to work on average, if we use a built-in function with some. So some of the values in price divided by the length or countable number of values. And we can easily work out the average and standard deviation we have to. This is how you import a library. So import numpy as np and then Torkel standard deviation, we'll just say np.array, STD, shortfall standard deviation and the price. And we can print, print out the value. If you want to, maybe around that value, we can, using a built-in function called round, round, round brackets, the value we want to round. And then two stands for the number of decimal places. So let's see this in action. So I could write average is just the sum over all the values and the price list and modify the ninth or the counter values. We can print that value out. We can run it and we'll get the following value, which is what we expect. Now to import the numpy library for standard deviation, we say import numpy as np. Np is as a shortened way for non-time and periodicity, the city against actual standard deviation, print it out. And we get the value that we expect. Now to work out the the value in a string, for example, with texts, we can do that as well. And it sees around built-in function is to minimize the amount of decimal places. So everyone too, so just say comma two. And I think this might actually give an error because he put in the wrong place, which is a good example that you see. It put in the wrong place. It should actually be next to press any deviation. Yeah, that's correct. Alright, so let's briefly go over the built-in functions a lot about. So firstly, we're going to look at men. So it's a built-in function, gets some minimum value in the list. Give me smacks, gets the maximum value in the list. Length constant values into list. Some sums organizing analyst average. This is a formula that we just created to get the average. So it's a division calculation to get the average value. And storing it in a variable called average. We have numpy as np. Choose any extra Python library you need to use import keyword everyone to import NumPy library. The as keyword just allows you to create an alias, a shortcut when referring to NumPy. So we don't have to type out manpower each time. We're just saying p instead of just easier way just to use it. And when we using NP, we have to first pull that library or references names or NP has an STD just Central standard deviation. Then Rolland it is a built-in function. You specify the value, you went around, decimal places. Thank you, and that's it for this lesson. 9. Lesson 07: Python Plotting: All right, so onto the next lesson, lesson seven. Here we will be looking at plotting graphs, specifically bar graphs and line graphs, as you didn't think so. So again, we will need to use another library to actually do plotting. So in this case, we'll use a library called matplotlib. And you get the same results as we've got an Excel. We have two type of the following code. So you can pause the video here. Type of the father as you see it, and see if you get the same result. See results you should get when you run the code. And I'll let see that in action. First kind of like a cone football graph. And this goes step by step through the code that we just wrote. So important that took method like we did with NumPy. We're just going to call it PLT, this. We can use it easier throughout the code. In here, we're just going to get all the data that you want to plot. So in that case, it's the data for price. I store that in a list variable and lovely as well also analysed variable. Just put it on top so it's easier to see what you're doing. Alright? So now what you need to do is also gets the comic because you went to two different plots, also put them in a list. So now to do any plotting, we have to use PLT keyword and then access the bar. He says a dot bar. And now we input the two lists we wanted to plot, comic and price. And here we can specify the color. Here the kind of can be blue. Plot that very simply, we get a very basic plot of what you're expecting and essentially not showing him because something is missing. What is missing is another function called plt.show. If I run that and show the basic plots. There we go. So there's the basic plot. Now let's add some features. May be, we can slant the labels as well, change the font size. Font size five rotation, we're going to rotate it by 30 degrees. Let's also maybe change some other features of the graph. Maybe the y label, I can call it something, comment maybe, or whatever you want to call it, and change it to change the font size. Here, I mean, we can display, this is just modifying, customizing Javier what it should be. Then label was and that's what correctly. You can also change the x level as well. So that should be actually the other way around comic and price. Change it later. And then the title and the title we want really, so this is just customizing the graph, right? So this is how to make it functional. It should be not pressing, yes, but if she comic and price. So we can change that. We can change that later. But that's how you basically modify and plot a bar graph. So let's just do a quick recap of what we learned. Again, if you want to import any library, we have to do is import keyword. Here we want to import this library called matplotlib pyplot, as we can just make a shortcut for PLT when we use it in the code. If she wants you to plot the bar graph, we just say adopt bar, PLT bar. And then the two lists you want, the color fealty that specifies the x axis labels, um, where we can change the font size of rotation. Plt xlabel specifies the level properties, one size, color, somebody with y label, and then title. It gives us the option of, you know, the label name and then PLT to show as required for displaying graphs. So these are different options. Pyplot peyote, peyote extracts, PLT xlabel, ylabel, title and POB been shown. These are the built-in functions as part of the matplotlib library. Now how to do a line plot? So again, you can pause it, pause the video, remove the code that you had before and our code or type out what we have here. So it's very similar to what we've done before. Just slightly different. Here we're doing a line plot. So if you write out this code and you run it, you should get this plot. So let's go into detail step-by-step with each line of code does. So this is a previous code, so let's, we can remove the bar plot and this replace it with plot subplot. We'll change it to a line plot. Here we want to drip line plot of model yeah. And price. Then maybe want to change the color. And he has a different way of doing it. Be sensible. Blue, OH, is little circles and the dashed lines, right? So it's a different way of modifying the type of pottery, getting fourth line plots, it's ticks. We can modify that as well. You're going to change that shortly. Then we're going to make my level is obviously price what it should have been before. Then the next level should be, yes, there's a two sets we are plotting. And then we still need to add some more features. So what we're gonna do now is to a title. We want to add the average for the, for the price data set list and also standard deviation. I remember to do that. We import the NumPy library, then we get the standard deviation. Having nice feature is we can actually put those values inside the title. To do that, we use a special character in the print for title. And now we can use curly brackets. And inside of curly bracket you put the actual variable. So Python will know that inside the curly brackets is a variable that you need to use. We can also add a legend using plt.plot legend. And you can also add a grid feature as well Around that should get the following. We have the data in the title and there's your line click. So just to recap, the few new things that you did was the grid and the legend title. Crash vertical lines is the grid legend. I just add a legend title. This is a new feature, is an F character with curly brackets, four variables. Thank you, and that is the end of this lesson. 10. Lesson 08: Python Reading Data: All right, so onto the last lesson, which is this an ED 3D in data using a library called pandas. Using, we're using that to read in data. Alright, so many times when treating data, type it all the values and I could have done in the previous lessons. So for example, if you have a CSV file or a text file, I can import it quite easily to exhale and then start working with it immediately. It can do the same with Python using another library called pandas. So in our browser, using our eyes environment, we can upload Komodo data we have been working with. Here's icon to upload a text file or a CSV file. And this is a CSV file that we're working with. It There's also in the Projects folder. So now it's uploaded it so that I can ever have in your mind. We can now import it and deselect. So this is a very handy feature of pandas. Python is this library called pandas. We can read in data, and it also displays it almost like in a table format. As you can see. Three lines of code is all you need to read in the CSV file and display it almost like in a table format. So pause what do type of the code and see you get the same result. We can also get some other steps from this data in just one command is in described function. So if we say data that describe, we get count, mean, standard deviation, Min, max, and percentiles for each of the columns, which is lot easier than what you've done before we were hit in manly workout. Each of these columns. So let's see this in action. This is the CSV file already uploaded. So to click on that link, also to upload that file. So now we can go through the code step by step, what we did to read in the file and display those results. That's the first we import a library called pandas. And this shortcut as pd. We use p dot read CSV. To read the CSV file. I will just put the name of the CSV file. Now this should be in the same location as your Python file. Then we can print the data. Then you see that printed out almost in a table form. The head function just prints out the first five values. And then to get the snacks, we can use describe. Run that. I get the stats for that, which is very handy feature instead of having to work out this manually. So what did we learn here, or new features or the pandas library, which allows us to read in data and do some stats on the data pretty easily. To do that we needed the period of read csv. Then to take the first five values. Just said I'd head. And to get it, get some stats on the data set not described in very handy library when we analyze data. Great, So that is the end of this lesson and the end of the course. I hope you learned something and enjoyed it. 11. Closing: Alright, so this is the closing for the Skillshare gloss. So we're going to do a quick recap of what you've done. Alright, so we've done quite a lot of aspects regarding what you can do in Excel, then how to apply that in Python, right? So what do we invent is a common tossing economic quite easily than in Python. And hopefully in Python you would have seen that there's lot more functionality and a lot more options. And hopefully you can apply to your own code on projects or even research. Most importantly, I tried to focus on presenting the lessons in terms of learning. By doing. So, copy the code out for yourself, make sure it works, and then try to figure it out. I tried to explain. I'm but then afterwards, what each thing watch, each line of code does. But the best way to learn is by doing. And that is a good way to help you improve your experience in coding in general. But I hope you learned something. And if there's any feedback, please let me know. If you enjoyed it and able to apply to your own projects and research. Now totally up to you. You try the project assignment and see how you understood the lessons. And if you can apply to your own code as well. So happy coding. And all the rest.