Python in Excel Review: The Good, The Bad, & The Ugly!

okay we need to have a talk last week Microsoft announced python for Excel it is one of the most exciting things to happen to excel since we look up so in this video let me share my first impressions of python in Excel how it really works what is good about it and what is bad about it let's go to use python in Excel you need Excel 3 65 and if python is available for you when you go to formulary bun you're going to find it right here I will talk a little bit more about how to activate this preview feature at the end of the video but for now let's go on a test drive here is some data for the awesome chocolates company we have got product date and amount let's load this data into Python and then we can use some of the Python features to analyze this data to do this you can go to a blank cell and press press control alt shift p to activate python mode another way to do this is you can open bracket and type py Open Bracket to enter the python mode once you are in the python mode you can expand the formula bar and type the code because usually we write multiple lines of python code you need to press control enter when you are done to run the python command we are going to get this data into python you just select the data and python will write the necessary formula for you here it says Excel B4 to d22 so it's grabbing the range of data B4 through d22 I'm going to select the headers as well and you notice that automatically headers is true option is enabled and at this point if you either hit control enter or this tick mark here you would create your very first python code the result of this python code is a data frame a data frame is like like an Excel table it will have rows and columns each column having one kind of data and each row explaining one instance of the data the output of python code comes back to us in a python object you can see that here it is showing that as a python object you'll have this kind of a special symbol here but you can actually see the underlying result by clicking on that and changing pack to excel value you can do it from the formula bar here or you can right click cck on this and choose python output Excel value as an option when you do that you're going to get back the same data that you gave that's because in this python code we just loaded the Excel data now let's use this data to build some analysis first up I'm going to create a variable called chalk and load this data frame into that next let's ask python to describe the chocolate data for us so we're going to use use chalk. describe again control enter and python is going to tell us the description of the data in our data there are three columns but only amount is a number column so amount column is being described here we have 19 values the mean is 608 and standard deviation is 3,952 if you want to describe all the columns instead of just the numeric column like an amount you can use the include keyword include all and this is how that output would look like you might think how would I know what functionalities are available like describe or something else this is where we need to peel back and understand the libraries that are being used by python to do this work the main library here is the pandas Library ah but not this kind of Panda it is a data analysis library that is part of Python and it offers many functionalities for analyzing or understanding the data for example I'm going to go here and in this cell I would like to see the first five rows of our chocolate data frame so we'll enter the python mode Control Alt shift p and type chalk. head initially we're going to get the result as a data frame but you can change the output to excel value and we can see the first five of the data here let's understand python in Excel a little bit more here I've got six columns and th000 rows of awesome chocolate sales data let's load up this data into python we can use Control Alt shift p to enter python mode and construct our own data frame I'm going to call this as DF is equal to and then grab all of our data so we have constructed a data frame which is now sitting in the cell K4 but also in a variable called DF as part of our python code once you have constructed that variable you can refer to that elsewhere in your workbook when you are writing other python code so in this cell I would like to calculate a simple pivot table that shows me by each country what is the total amount for this we can use the group by function of pandas Group by and then refer to the column of the data that you want to group by in this case we would like to group by on the country column and then what kind of an operation we want to do after grouping so we want to do a sum operation and that's going to return a data frame let's see the result as Excel value so this is how it looks it tells me for each country what is the total amount and what is the total boxes shipped let's double check this so here I have got for Australia 1.13 million we can double check this with a sum ifs function Su ifs amount column country column is Australia and you'll get the same answer as what python reported now let's understand what happens when one of your data values changes so for example here in UK I have got the amount as 5,320 what if we change this to 15,32 so that means the total on the UK needs to go up by 10,000 as soon as you hit enter Excel will enter the rec calculation mode and this value gets recalculated but all of these calculations whatever you're seeing on the screen do not happen in your computer every time you change something Excel will call a cloud service that executes your python code that is why in order to use python in Excel you need internet connection so I'm going to turn off my internet connection for a minute and then we can see what happens let's change this back to 5320 as soon as you hit enter we are going to get a lot of errors on the screen this is because our python code could not run as there is no internet connection once you're back online when you make any other changes the python calculations will rerun again python offers a powerful data visualization Library called cburn and using that you can create some impossible graphs within Excel so for example looking at this data I want to understand how the spread of amount versus boxes is looking like we can create a scatter plot to visualize that we will enter the python mode and here we are going to import the cbone library as SN and use the code sn. scatterplot data is DF that is the name of the data frame that we constructed earlier xaxis has the amount and y axis has the boxes shipped when you hit control enter we should get the chart as the chart returns an image it will initially show up as an image in the cell but again you can right click on the cell and then go to python output show it as Excel value which will generate a tiny little scatter plot inside a cell remember this cell has an image so if I select a bunch of cells and then merge them I can see the scatter plot clearly here alternatively we can leave the image as it is here make a new worksheet call it as output and in this sheet create a cell for our output so maybe make a big cell and then say this is equal to and point to the cell that has the image that's going to show the scatter plot nicely in the output worksheet as you probably know python is one of the most popular languages when it comes to data analysis data science machine learning or general purpose programming itself that's why I am super excited to have python as part of excel right now that said I am kind of underwhelmed by by the implementation of python in Excel mainly because the number one thing with python in Excel is whatever code that you are writing in Python it will actually go and run in Microsoft cloud and then the results come back to your computer this not only makes the whole process a little bit more cumbersome even with high speed internet it will take a fraction of second for you to write the code control enter on it and then get the results back now imagine if you are trying to build a more complex piece of code where you are trying to build a machine learning model or something else then this delay and lag and the ability to not immediately see errors in your code is going to stop you in your tracks on top of it the whole fact that whatever data that you are sending it doesn't really sit in your computer now it must travel across the internet to Microsoft cloud run the code there and then come back with the results this kind of opens a can of warms of data security integrity and whatnot so that's what that's one of the first things that I kind of felt underwhelmed with the implementation of python the second problem I have with excel's implementation of python is it's not really true python all we could do is take the Excel data pass it to Python and then do some operations there whether it is a data manipulations or data sciency stuff or creating some visuals most of them we can kind of already do in Excel One Way or Another whereas the true power of python really lies in its ability to develop some code automate systems or build Solutions and we are not able to readily do that with this current implementation of python but then again my fingers are crossed I am hopeful that in future we get to see more of this and we are able to actually unleash the full potential of python within Excel and my third problem is licensing of python within Excel because the python code that you are writing is going to run in Microsoft cloud the licensing requirements for this are kind of special what might Microsoft States at this point is you would require a special license to use Python once it becomes generally available it is not really clear what this special license is going to be whether it would be part of your existing office subscription or you would need to pay something extra per month or per use or per User it's not clear whatever may be the case this licensing actually makes adaptation of python even more complicated and my last problem with python in Excel is as it is Excel already has five languages and now we have python don't believe me we have got Excel formulas we have got VBA the original coding language of Excel and then we have got office scripts the new automation coding language of Excel and then you have got powerquery slm for your data Transformations and then we have Dax or power pivot measures to do calculations inside pivot tables so already five languages in Excel and now python joins the club so it feels like a big overwhelming exercise to learn Excel as it is and now that one more things joins the club it's going to make it even more complicated this is both exciting for me as a person who appreciates learning new things but also kind of concerning because one more thing means one more thing to learn remember and adopt into my Solutions so what's next I suggest trying it out if you have got Excel 365 you can enable the beta or The Insider program and give Excel for python a try it may not be enabled immediately but eventually all the people in the beta program are going to get access to the python in Excel the next thing is if you are serious about python then I suggest learning it properly the implementation of excel python is not 100% so maybe install something like Visual Studio code or anaconda or another type of python ID and use it to learn how to code with python properly I've got a YouTube video on how to build code with python that takes you through all the steps and showcases some simple projects that you can right away do with python so check out that video and maybe learn a little bit of python lastly to help you with all of this process I have prepared a Excel for python resource pack it's a free pdf guide that gives you all the links and information code samples data sets and other tips and tricks check out the link in the video description below to download that whatever way you use python or whatever Way You Learn Python I wish wish you all the very best I'll catch you again in some other video [Music] bye

Channel: Chandoo

Views: 96,034

Keywords: chandoo, chandoo.org, Excel, spreadsheets, python in excel, python for excel demo, introduction to python in excel, how to use python with excel, dataframes, python excel, python for beginners, python excel automation, demo of Python in Excel, xl python

Id: whzmtv9qfIQ

Length: 14min 42sec (882 seconds)

Published: Tue Sep 05 2023