Introducing Python in Excel

My God, okay, it's happening! Everybody,  stay calm - what's the procedure everyone? So, Python for Excel is here and it's   ready to test in the Beta channel of Office  365. Excel has been getting a lot of updates,   but this... This is going to put it in a different  league. So, you might be thinking, "That's not for   me. I'm not a programmer, I'm not a geek. I just  do my daily Excel stuff. I don't need this." Wait   until you see what I'm going to show you, and  I recommend that you watch the whole thing and   take some time to digest it, because I didn't  want to give you a stroke. So, I've sprinkled   all the cool stuff throughout the video.  So, sit back and let's start from scratch. Okay, so when you open up Excel and you  have Python, you are going to find it in   the Formulas tab. There is a Python section here. You can get started by inserting your own   custom Python formula or exploring Python  samples. And then, start by taking this   tour. But we are going to get started together  right now, and I'll show you how this works. The first thing we need to do is get into Python  mode. One way we can get there is by going to   Insert Python and then inserting a Custom Python  Formula. Now, keep your eye on the formula bar and   see what happens when I select that. The box  changes into a Python box. Now, another way you can   get in Python mode is by just typing in "=PY".  This function creates a Python formula. So,   the moment I press Tab, I'm already in Python  mode. What happens now? Well, let's say as   our first example we want to give some insights  from this dataset. If you're working with data,   the first step you need to do is to send your data  to Python, because it needs to be able to see it   to work with it. The way we can do that here is  by just referencing the cell. I'm just going to   select it, use the keyboard shortcuts, Ctrl + Shift  and the down arrow key, to select the whole range.   And notice what we get in the formula bar. We  have our data range, "headers=True". That was   automatically picked up, and everything is wrapped  inside an XL function. Now, when I press Enter,   nothing happens. What Enter does is it adds  new lines to my Python script, because Python   scripts could get long. So, it makes it easier to  work with these scripts. So, for me to be able to   run this, instead of Enter, I need to press Ctrl  + Enter, and I get what is called a DataFrame.   If you know Python, you know what this is. If not,  it's basically a condensed version of your table.   It's a two-dimensional data structure that's  all sitting in a cell. Now, take a look at this.   Here we have a dropdown. What is being returned  here is a Python Object, that's our data frame.   But I can switch my view and return Excel Values.  When I do that, I get my data set back. But this   takes up a lot of space, so I'm going to switch  it to a Python object and get my DataFrame. Now you can think of this DataFrame as an Ikea  table. Right, when you buy a new table from Ikea,   it's all condensed in a single package. If  you want to get a glimpse of what's inside,   you can click on this card, and you get  to see the beginning rows of your data   set and the ending rows. This DataFrame is  a fundamental structure of a specific Python   library that's called Pandas. The Pandas  Python library is great for data analysts,   and you need to know the name. I know it sounds  weird, but you need to know it if you want to look   up other functionalities in Python's library.  But I'm going to explain more about libraries   and Pandas later. What I want to show you right  now is some of the cool things you can do here. So, for example, I'm going to go to another  cell, go to Python mode by typing in "=PY".   I'm going to reference my DataFrame  and then use the Describe method. So,   I'm just going to type in ".describe". So,  I'm in Python mode now, not Excel mode.   I'm going to open and close brackets, press  Ctrl + Enter to run this, and I get a DataFrame back. But I want to see the Excel Values.  I'm going to switch my view to Excel Values,   and I get to see some information about my data  set. So, I have the Quantity and Sales columns   here. This is the count of each column,  the mean, standard deviation, and so on. Now, if we take a look at our data set, we  have date, product, quantity, and sales. Now,   let's say I wanted to describe the Product column.  I'm just going to go and adjust my function. So,   right after I reference the DataFrame, which is  the data set here, I'm going to put in my column   "Product". The way you reference columns is  by opening up the square brackets, similar to   referencing Excel tables, and then typing in, in  quotation marks, the name of the column headers.   In this case, "Product". And I'm going to close  the square bracket, press Ctrl + Enter, and I   have some information about products. So, I have  96 products, 15 are unique. The top is "Laptop   Bag Red" because that occurs 15 times. It's a  quick way of getting insights from your data. Now, if you're planning on using the data that  you uploaded to Python and doing different types   of calculations, it helps to give it a name. This  way, you don't always have to reference the cell.   The way you do that is by going to your first  DataFrame and then just typing in any name,   as long as it doesn't have spaces in it. So,  I'm going to call it "df" for DataFrame, equals   this data set. So, when I run this right now,  nothing changes. All I've done is just give this   a name. But when I come to do other types of  calculations based on this DataFrame, I no longer   need to reference it by referencing the cell.  Instead, I can just type in "df", and that's it. Now, talking about shortcuts, another shortcut that I  like is to avoid these square brackets. So, whenever I   can avoid them, I will. And the great thing with  the Pandas library is that it transforms your   headers into attributes. So, as long as you don't  have spaces in the headers, you can actually type   them out like this. So, I can mention the DataFrame  'dot' the header 'dot' describe, and when I run this,   I get the same thing. But if they happen to have  spaces, you need to use the square brackets. Now, there are lots of functionalities in the  Pandas library. So, some simple ones that you're   familiar with in Excel is, for example, the SUM or the AVERAGE function. So, let's say I wanted to   use Python and sum the sales column. I'm going to  go to Python mode, but this time using my shortcut   keys Ctrl + Alt + Shift + P. Now, this sounds like  a complicated shortcut, but it's actually easy   to reach for on the keyboard. Then, I'm going to  mention the DataFrame. I gave it a name, it's df,    'dot', I want to sum the sales column. So, I'm going  to go with ".sales", it doesn't have any spaces. I   can reference it in this way. .sum method. Run  this, and I get the total of my sales column. So,   let's just double-check this. That's 174510. So, if I sum this, 174510. Now,   if I want to get the average, I'm going  to use mean here. And that's the average. All of this you can do with simple Excel  formulas, right? Why do we need Python? So,   let's take this a step further. Let's say I  want to get my total sales values for each   date. Notice, my dates are repeated, but  I want to aggregate them for each date,   similar to what you would do in a pivot  table. I'm going to go to Python mode,   then reference my DataFrame, which was df,  .groupby I want to group everything by the date   column. And I want to have the total values  from sales. I'm going to go with .sales, .sum,   and I'm going to press Ctrl + Enter. And  I'm going to get my series returned as a   Python object. But if I want everything as Excel  values, I'm just going to spill them, and I have   the total sales for each date. And if you don't  believe me, let's just double-check this. So,   this is for the first of June, and I have these  four numbers here. That's 9680. That's 9680. Now, what if I change my mind and I want  everything on a monthly basis? Well,   check this out. I'm going to go inside  groupby, and I'm going to use a class   called pd.Grouper. My key equals the date  column, and my frequency equals, I'm going   to put M here, and close the bracket. Run this,  and I get my sales on a monthly basis. So,   total sales for June should be 38060. Let's  just double-check this. And we have 38060. Now, okay, you might say, "Well, a pivot table  can do this." The great thing about this is that   these are formulas. So, if I change this value  to let's say 6000. Keep an eye on the June value   here. And I press Enter, all of these refresh  immediately. I'm just going to press Ctrl + Z to   go back. Something to keep in mind is that you  always get the last expression returned. So,   you could be writing a lot of code, but whatever  you end up seeing in the cell is based on the last   expression. Let me show you something even cooler.  I'm going to give this a name. I'm going to call   this "forchart," and we are going to put a chart  in a cell. So now, I've called this series "for   chart," and I want to plot this series. I'm going  to do forchart.plot. I want to put a line chart.   My x-axis is going to have my dates, which are  in the date column. My y-axis is going to have   my sales values. And the kind of chart I want  to plot is a line chart. I'm going to run this,   and I get a tiny little line chart in a  cell. You probably really can't see this,   so let me go and merge the cells, and we get  to see our monthly sales values. How cool is   that? If I wanted an area chart, I'm going to just  change this to "area," and I have an area chart.   If I wanted frequency to be on a weekly basis,  I'm going to change this to "W," and I get my   weekly sales data shown as an area chart. I prefer  line charts. I'm going to change that. So, notice   that the last expression is the one that plots  the chart for me, and that's what's returned. Now, if you're wondering how I'm coming  up with this syntax, how do I know these   classes and the methods? Well, there is  great documentation on this in the Pandas   library. You have lots of information here on  this webpage to help you get started and lots   of code to play around with. Plus, you've got  AI. There is Bing Chat and ChatGPT that can   help you when you get stuck. Just double-check  your results to make sure it's what you want. Now, before I move on and I show you some even  more cooler stuff, there is an important thing   to keep in mind, and that's the calculation logic  of Python Cells. Python Cells calculate from left   to right and top to bottom. So, notice here,  first, I've created the DataFrame, and here,   I'm referencing the DataFrame. I'm on the  right side. Here, I'm below it. But if I copy   and paste this here, I'm going to end up with  a hash value. I get diagnostics pop up here,   and it tells me "name 'df' is not defined."  That's because Python Cells calculate   from left to right and top to bottom. I  would have to bring my DataFrame here   for everything to calculate fine. So,  I'm just going to demonstrate that. I'm   going to Ctrl + X and Ctrl + V this,  and now, everything calculates fine. Now, let me show you some cool examples from  the default libraries that we have. So here,   I'm using pd.melt function to restructure this  data set. So, I have quantity and sales in two   separate columns. When I use pd.melt, I  can bring them in the same column. So,   this is similar to "unpivot" in Power Query.  And look at the formula bar here. It's very   simple to write this. I just need to mention my  non-value columns, which are date and product,   and my value columns, which are quantity and  sales. And it does the whole restructuring for me. Now, another cool one is query. Take a look  at this. I'm going to go into Python mode,   reference my data set, which is df, right? That's  my DataFrame that I uploaded to Python. And I'm   going to use query. Then, I'm going to put  this in. What this does is it takes a look   at the sales column. It grabs everything  that's above 2000 and takes a look at   my product column to see if anything contains  the word "black". So, this is similar to the   QUERY function in Google Sheets, which is also  similar to the syntax from SQL. When I run this,   by default, I get a DataFrame back.  But I'm going to show the Excel values,   and I get everything that has the  word "black" and sales is above 2000. Now, let's move on to something else. We can  create charts like this. I have small multiples   returned as a single object. Take a look at  this one. This is a bit more complicated,   but I also wanted to show you that you can write  longer scripts and get Python to do what you want.   So here, I just have dates and students from 2018  to 2022. And what I wanted to do here was this:   I want to return the last four years, and  I wanted to arrange the charts beside one   another. I also wanted to make sure that  the y-axis is showing the same values,   right? So that I can easily compare each of these  with one another. Now, I've even added labels to   this. And you can do all of that in Python here. You can set the title, set the label,   and so on. And yeah, I didn't do all of this on  my own. I didn't know I had to flatten the axis,   so I asked ChatGPT for help, and it  helped me get what I wanted right here. Now, this is my favorite part of the video. What  we're going to do is this: We have an Excel table,   and we have some feedback in each row. What  we want to do is grab any URLs that we have   inside each row. The tricky part is that  they might be in the middle of the text,   and the pattern isn't so obvious. So, sometimes  they start with www, sometimes we don't even   have that, they just end with .com. Sometimes  we have https and some might just have http.   Some might not even have a link. What we want  to do is just grab any URLs in there. So here,   you can see we're grabbing both of these  in the cell, and then we're omitting this   one because it didn't have any links. Then  we have this and this. How can we do this? The great news is you can import  any Python libraries or modules   that might not be there by default. So,  the ones we used are there by default:   the Pandas library for data analysis,  Matplotlib for data visualization.   But Python has so many different libraries  and modules. There are ones for AI, for web,   for regular expressions. So this RE that I'm  importing here works with regular expressions. All   I have to do is go to Python mode and import the  library or module, and I've returned "re loaded."   That's just because I want to see something  in the cell aside from none, which is what I   would see if I don't have this. Everything still  works, but I'm going to see none or 0 in the cell. Right, so I'm just going to press Ctrl + Z  to go back. Now, after I imported the library,   what I did is create a data frame  based on the table. And the way you   do that is similar to before, except  you just have to make sure that you   have the right referencing for the table  that you are including everything. So,   I have "#All" to make sure that I'm also including  the header. Now, when I hover over the DataFrame   here and click on the card, I can see that  the column header is a part of my DataFrame. Now, the next step is to write some Python code to  get this done. What is done here is that I've   combined the patterns using this. This is based  on the patterns that I can see in my data set.   This makes sure that anything that ends with  .com is included, that anything that has https,   http, or www is also included. Then, there  is a function here that grabs the URLs,   and this function is run on each single cell, so on  each single text that I have in my DataFrame. You can also see the syntax here. Wherever you  come across the hash sign, this means that this   is a comment. This is a good way of also testing  your code. You can comment things out to see what   is the last expression that's returned or just  add in some documentation to your code. Now,   the end here, we're just filtering out any  rows that don't have any URLs. Now, the great thing   is because it's referencing the table, it is  dynamic. So, if I go in and add another URL,   so let's say youtube.com, this is going to  be automatically added to my URL list here. Now, if you're wondering whether you can connect  Python to your Power Queries, yes, you can. You   don't need to have the data physically loaded in  your workbook. They can be there via Connection   Only, and you can create a DataFrame if you want  based on these and use Python code on them. Now,   this video is getting super long, so I'm  going to leave this topic for a future video.   As you can see, I'm super excited about this.  It just opens up a whole new dimension in Excel.   What do you think about it? Let me know  in the comments. Thank you for being here,   thank you for watching, and I'll  catch you in the next video.