Boost your Data Science skills with the new Python in Excel

Speaker 1 [00:05]

Thank you so very much. Can you hear me well? Good, even in the back. Brilliant. As I was saying in the beginning, if you want to, before we get started, I've updated the description of the session on pre-talks. So if you go there, you'll find some links of materials you can get to end the slides if you want to work along during the coding that we're doing today. Okay, before we start, well, a few things about myself, or as I call normally the slide, which it has not changed it. Is it getting there? I changed my slide, but I don't see it there, which is weird. Let's see. Well, it's going to be a short summary of myself in logos anyway, so lots of logos in that slide. Shall I do it again? OK, let's try. All right, let's see if, OK, now it works. My background is in computer science. I've been a scientist and researcher for many years. I've actually a PhD in machine learning, but that's another story. Been working in academia for many years. I do now work as developer advocate, community advocate. currently working for OpenMind as an education lead and community advocate. I would be more than happy to talk about what we do in OpenMind. We work with private AI, and so like data science and machine learning on private data. I'm also a fellow of the Software Sustainability Institute, which is an institute in the UK working on research software and applying software engineering to research software, and also Python Geek, as probably most of you in this room, organizing many conferences, PyData, PyCon Italy. We'll have PyCon Italy this year, third week of May, right after PyCon US in Florence, in case PyCon.it is the website, if you want to check out. That's also me, of course. I'm Casual Nerd, and you probably guessed from all the shenanigans we did yesterday. And we will do it right after this workshop, anyway. Right, so before getting started, how many of you have seen already this new feature of Python into Excel in action? OK, and you've also worked a little bit with it. So more or less? No, really? OK. I just wanted to say, I guess this is the right room for most of you. So I just wanted to take a couple of minutes to explain the difference between what I think is the difference between Python for Excel and Python in Excel. Python for Excel is essentially what the relationship between Python and Excel has been so far, because we have lots of projects that you can use, libraries actually, or xlxwriter, xlwings, xlerd, and many others, pyxl. You can find many of these libraries available open source, and the very purpose of this, there's actually a website called xlpython.org that you can check out. And the very essence of these libraries is to use Excel as a data format. So you can process and even generate with the XLX Writer. You can generate data into Excel format. So there are some plugins that you can use. And if you're using an Anaconda distribution, for example, you have plugins that you can install into Python to integrate with Excel. So the main takeaway for Python and Excel is that the community, and actually the open source community, has developed the many tools to deal with the Excel data. We have Excel, ExcelX, ExcelSX. This tool assumes Python as the glue language to interact with the data in Excel. And the standard workflow is like you work Python outside Microsoft Excel, such as you have third-party applications developed for data reporting, which sounds a little bit ridiculous because Excel is indeed a data reporting application. And Excel is considered only as a tabular data format. Whereas when we're now talking in this tutorial about Python in Excel, the difference is that we can run Excel directly into Excel workbooks. So workbooks can now integrate a new Python cell into the formulas where you can actually write Python code. This is a project released not even a year ago by Microsoft. And it's still in beta version, so expect things to change. But at the same time, given that it's still in beta, you can use it for free. I have no idea what will happen afterwards. But at the moment, and this is also for those of you not running on Windows right now, this functionality is available at the moment only on Windows for Windows user running Microsoft Excel. And so for all the users like myself not running on Windows, you have to resort to other options like running virtual machines, running Windows in the very end. So that's what we have to do. There's no other way you can use this to work with Excel. So, for example, Microsoft Excel exists for macOS, but it doesn't support the Python feature. I just want to reference you to this website, anaconda.com. Actually, before moving to OpenMind, I used to be working with Anaconda, and there you will find a few blog posts I've written, and I'm going to present you the result of those into this workshop. There's also a LinkedIn group, in case you are on LinkedIn. It's called Python in Excel. And you'll find, actually, a very active group in which you can sort out any question you may have about the feature. All right. Let's try it out. So there should be a link, if you want to, on the description on a sample notebook, a financial sample data, which is available from Microsoft. And if you follow the instructions already on installation, you could try it out with me. So I just want to switch now to that in order to understand more or less how does it look like. So this is the financial data. Could you see it? Probably not. It's going to be a bit complicated for you sitting in the back. because the Zoom capability I have on Excel are very limited. So if you have any problem, please shout out. It's very fine. I'll try to do it. Yes, please. So you mentioned where you can find the material. Okay, sure. It's on the pre-talks description of the talk, of the tutorial. On this page? No, if you go on the schedule on the website. In this session, pre-talks has everything there, along with installation. Installation should be pretty fast, as in you fast as in how fast is your downloading speed. The thing is, you have to just join Microsoft Insiders, and you have to switch to the better channels to activate it. The result is that you will update Microsoft Excel and all of a sudden when you restart, sometimes even rebooting because it's when they're in the very end, you'll have a new Python section in the formula here. So this is how it looks like. So in the formulas tab, you have a new Python thingy. I'm going to explain a little bit the meanings of all this in the presentation. But the final result is this. So if you want to understand whether Python is enabled into Excel or not, that's what you have to look for. You go to formulas and you have to check that. There's also another thing I was mentioning in the description if you want to. There's an add-in, which is very handy. It's not required, but very handy. That is called Excel Labs. It's from Microsoft. Excel Labs, as far as I understand, contains access to features, new features that are available in Excel. Within these features, there's a new one called Python Editor, which means that whenever you have a Python cell, let me grab a new worksheet here. So before talking into details of everything and how everything works, because that's paramount important, because before doing anything, I just want to show you how that works. So in order to start writing Python into your, probably if I do, if I switch away from the come on Parallels. I'm running, oh, there you go. I'm running on Parallels, by the way. So if you're a Mac user, that is very recommended. The very first thing you would like to do to start writing Python is you add it to the cell and just type equal py. Sorry, py. You'll see it creates Python formula. Press tab. And then you'll see that the formula area here has a new green band. This is the reference to tell you that now you have a Python cell. And here you can start writing Python code. Okay? What I was saying before is the new PyFormulas has syntax highlighting features. That was something that wasn't enabled immediately. They did it finally. So, for example, if I import OS, I have something that I can see colored with syntax highlighting. And also there's a tab completion thing. The thing is that it's still a cell. If you write longer code, it could be clunky. and the syntax highlighting has some bugs in it, so it doesn't always work. So what I was recommending is to install Excel Labs extension, which has a new Python editor, which essentially gives you notebook experience working with Python and Excel. So if I select a cell and I do go here, essentially I'm changing the formula directly. And also with auto-completion and everything, As soon as I save it, this will be automatically run. You're already viewing a preview of what's going on here. So something weird is appearing in the cell. I'm going to explain everything I promise. OK, let me go back to the presentation. In the meantime, do you have any burning questions you want to ask? Please, go ahead. Can you switch environments? That's a fantastic question. So the question for the audience online and for the video feedback is, can we switch environments? The short answer is no, absolutely not. And actually, that is a very good question. And essentially, I have a collection of slides of things. I haven't told you anything how this thing works. So it's a very fair question. And I'm sure you'll get your answer immediately as soon as I get into it. But the short answer for that is no, you can't do anything. But OK, let me go back to that in a second. Any other question in the meantime? Yes, please. Is there a ? Is there any to? ? Integration with what exactly? Sorry, I missed that. . Oh, Git integration. Sorry, I missed the word. So the question was, if there's any Git integration. The answer is no. It's going to be disappointing. Sorry, are there any other convenient ways of getting the code into that little cell? ALAN PARSONS- Convenient ways. So the question was, if there's any convenient way to write Python into the formula cell. is the question. The answer is yes, but depending on what you mean by convenient, no kidding, is normally the takeaway I have is, from experience, try to keep your Python code very short. So don't write too much stuff into Python cells, because you have to think of it similarly to as you were using Jupyter Notebooks. And please keep this in the back of your head, because this will make sense in a second. There was another question. Yes, please. So it looks like there is no plan to make the editor of Visual Basic available for Python, like to have a third place where you can put all your Python code getting into it. So like storing libraries of function and these things? Yeah, the code, in the same way that the Visual Basic for application is done right now, functions. Of course, I cannot speak on behalf of Microsoft, because I have no idea what they're doing. And I didn't have any idea even when I was working on Anaconda. And I'll tell you in a bit what's the relationship between the two. However, as far as it is right now, it's definitely not looking like what you're describing. It's something else. It's like you write a Python code, and that's it. But the Python code lives within the workbook, that's for sure. But you can't reuse previous libraries, At least not for now. Things may change. And things may drastically change. And things could look really different in, I don't know, six months' time, even less. I have no idea. And when this will happen, it could come in different options. Like, you can have features that can only be available to paid customers, for example. But I have no idea. I don't know what's the plan there. I can only tell you about what it is right now and what's the capability. Yes? If you say that the source code is actually living in the Excel document, is it executed? OK, I'll get to that. Brilliant. Because that's exactly the right question. So you are the perfect audience. So you're all asking the right questions. And what I'm going to tell you in a second is, I know I'm creating this hype, but there's No big hype, really. It's like all those questions will be answered. And in addition, I will try to spare you from some misery from experience of me starting to do things and trying out as well. But all of those are very, very fair questions. If you don't get the questions online, the question was where the code is executed. OK, so first off, that's the UI we've seen. There's an initialization button. That initialization button, and this is the answer to the very first question we got. What's the environment we have? So the Python and Excel feature has been developed in partnership between Microsoft and Anaconda. Anaconda is providing the environment, is what Anaconda is contributing to this feature. Whenever you start your workbook, Sorry if I call workbook notebooks because I'm used to work with notebooks. So I always switch the terms. But workbooks, when you initialize, when you open a workbook, you will initialize a new environment. And you can always reinitialize it by clicking on the initialization button. And what happens is that you can see you have immediately in your namespace, this is a design feature, not a feature I particularly like, but in your namespace, you always have access to numpy as np, pandas as pd, matplotlib as plt, and so on and so forth, all of them. Plus, there's also a new import Excel module there, and this will make sense in a second, for a new special function developed specifically to work with Python and Excel. But in your namespace, you'll have all these variables available. So if you start writing into Python and Excel, for example, I don't know, like here, I'm going to write something like, I don't know, np.something. And as you can see, autocompletion doesn't really react very well in the editor. Let's try to do this here. Import np, sorry, np.something. can we do like random computer is broken can we blame Microsoft for that yes let's do it yeah no I guess it's it's my internet connection probably not getting along yes all right np.abs okay np is already available in namespace I don't have to import an and pi as an np, it's already there, so I can do np.abs, and then it's probably doing something, so this right, I can run this and see what happens, okay we're already seeing how errors are going to be shown here, anyway we'll get back to that all right so we talked about environment there's a link in the slide open source libraries in python and excel those are the only libraries available in python and excel you you you can't install any additional libraries any additional package which means you you can't use any local python code okay you can't install new libraries the environment is provided by anaconda and it's essentially the 90% of what you have in anaconda distribution, which means a lot of libraries, a long list of libraries. Certainly, the basic data stacks you use in data science jobs, that's going to be there. And so this would be the case for pandas, numpy, malpron-leap, seaborne, scikit-learn, just to mention the most popular one. But that links lists the available libraries in Excel. So we can create a new Python in Excel by doing equal pi. And what we can do within the cell is to write custom logic with Python. Thank you. As I was mentioning, there's Excel Labs, which is this add-on, which I strongly you working with because it helps you a lot with the editing. Yeah, so I've shown you this already in the live demo. All right. Something you can do is you can reset the runtime. And this essentially gives me the possibility to tell you how that works. Python and Excel doesn't require you to have Python installed in your computer at all. Everything runs in Microsoft Azure Cloud. So you only need internet, and this is like pros and cons. You can't work with it offline. You need internet connection, okay? The code runs into the cloud, where the whole environment is already set up, and everything runs within a sandbox. Please keep this in the back of your head, because this will make sense in a second of something I'm about to tell you. You can reset the runtime, you can reset the execution, because sometimes you can get weird things like this, like a dash Python exclamation mark. Normally, you have additional information to that, but generally it could be a runtime error, and so you just reset and restart everything. Reset essentially is very similar to close the kernel, shut down the kernel in Jupyter Notebook and restart all the cells again. Right. The execution model. This is very, very important. This is probably the most important slide of everything, of all the deck, just to understand how the thing works. Are you all familiar with Jupyter Notebooks? Anyone not having an idea what a Jupyter Notebook is? Okay, so a Jupyter Notebook works like the top to bottom, right? So the execution order in a Jupyter Notebook is one directional. You have top-to-bottom cells, okay? The execution model you have in Excel, with Python in Excel, is like a Jupyter notebook, but three-dimensional. It's just insane, but it is. So, first off, it works because we have three dimensions. We have the worksheets. We have, within the single worksheet, we have rows and columns. So, the code can actually appear everywhere, sorry, anywhere, into any of these three dimensions. And so they have to be executed in order. So the order counts. First off, it goes left to right on a sheet axis. So the first worksheet is going to be executed first, in this case, data set, then step one, data partition, and so on and so forth. All the Python cell within the worksheet. Within a single worksheet, it goes left to right, top to bottom. Is that clear? OK. Why this is important and why this matters. This is important because if a single Python cell is counting on or is referencing a previous execution from a previous Python cell, that might not work if the position of that cell is in the wrong order. So for example, imagine that you're defining a function in cell B1, okay? And then you're using the function in cell A2. Is that working according to you or not? Who says yes? Who says no? It works because it goes left to right, top to bottom, okay? So B1, a2. If I do instead a2 b1, so I define a function in a2 and I call this function in b1, is this going to work? No. Because b1 is calling a function that hasn't been defined yet. Because it will be defined afterwards. It goes left to right, top to bottom. And even worse, if you're essentially referencing some code in a worksheet that has been defined in a worksheet that comes afterwards. So you have to keep in mind. And there's always this idea of a global namespace. So similarly, you had multiple notebooks running at the same time, and they can interact with them. You can call code from one notebook to another notebook within the same big namespace. If you think of notebooks that way, it's completely, I was just going to say a different word. It's insane, let's say insane. If you're looking in Excel, it does make some sense, because it's within the same environment. But still, it's a bit counterintuitive sometimes, so you have to keep this in mind. All right, that being said, things you can't do in Python and Excel. You can't do anything to call, import, invoke, install anything from your local computer, which means everything runs within a sandbox on Azure. The sandbox means that nothing in your local computer can be accessed. And also means that anything, if your code has some requirements of downloading stuff from the internet, it's not going to work because you don't have control within your sandbox. The sandbox is just there to running some code. If you need to download anything like request.get, pd.readcsv from a local file or a remote URL, it's not going to work because it's sandboxed, so no internet connection within the execution environment. And also, at the moment, there is a hard limit of 100 megabytes on the size of computation, data, and code that you can execute per single cell. It's a hard limit, but it's not really a limit. 100 megabyte per single cell data plus code, it's really a lot. So you can definitely use it. So that is per requirement, and I have to tell you. But it's very, very difficult that you reach that limit. And if you do, you essentially are using, I don't know, you're stretching the current available computational capabilities of Excel into that. So there's probably, at some point, there's much that Python and Excel can do at the moment, like running heavy computation. This is not going to happen at the moment. All right, second thing, what is it you can do in Python and Excel, but it doesn't work the way you would expect? First thing you would try is, OK, first thing to try, print a low world. OK, this is interesting. When you do print, what you get in return in the cell value is a none. And this is because the print function doesn't work like, there's no concept of standard output or file in Excel. So what they decided to do is to have this diagnostic panel, which is where also errors are going to appear during the execution. And you essentially see the message there. So you'll probably see a none into the cell. If you really want to see something there, what you can do is to do something like that. So you can evaluate the code into the cell. Similarly to something you would do into Jupyter cell notebooks. Okay, so when you evaluate a single object into a Jupyter cell, this is what you get. And that's exactly what you're getting. The last thing to mention is that every single Python cell in Excel has two modes. Value mode and object mode. Default one is object mode. And so, it explains the two stacked squares we've been seeing before. That is the reference, the little icon that Microsoft has decided to give to objects into Python. And so when you see that, it means that you're having an object output. The Excel value, it means that it will print the value of the object. All right. In this case, it's a string object, so the value is actually a string. And so you see the full thing. So other things that we can see, so from this piece of code, we say from numpy import pi, and then we say first three digits of pi, and blah, blah, blah. Okay, so we print that, and if we print it with object mode, what we get is the object representation, and again, it's a string, so it's also the same, but we also get some additional information. So if you go on the cell, you can see the Python type is actually a class string, and then the type name is string, and then the representational string is the one we get. So you have genuine Python object living within workbook cells. This is an important takeaway, something I discovered myself pretty soon. Formatting of cells and Python output are two different things. They don't interact with each other, and at the moment, there's no interest in Microsoft, from Microsoft, as far as I know, to make them working together. In other words, there's no way you can control formatting of a cell from Python, okay? Which means, regardless of what is your, so if you just print pi into the cell, it could essentially generate a certain number of digits. you can control these digits by, so if you have a look at it, so in this, I don't know if you see it, so here is the Python representation of it. This is what I decided the formatting of the cell should be. So if you want, if you're looking for specific formatting in a cell, this is something you have to control externally within Excel. Python can't do anything with it. So even Even if you have the format function with specific formatings, Excel is not going to deal with it. Okay? So it's not going to happen. And that's exactly what I meant. So the reason why you see that is because I decided in the format Excel and the format of the cells to go that way. This is very interesting and very useful. I'll show you in a second in action. Python functions have an automatic spilling, an auto-spilled over multiple cells. So, if the function is generating more than one value, they are going to be automatically spilled over multiple rows, in this case, or multiple columns as well. Generally, and this is probably the interesting thing, the standard representation of data into Python and Excel are going to be Pandas DataFrame. And in fact, I just want to show you, yeah, this is also data science you can do. I'm just looking for, this is the example we're going to work on in a second. And this is what I want to talk to you about. There's a special Excel function which exists only in Python and Excel. And the Excel function is a really smart function. It's a function that you can use to select cells within the workbook. The result of this function is a Pandas data frame. So when you, and it's also very handy, because if you have selection from your workbook and you're editing a Python cell, automatically Excel will going to get your selection from the mouse and put it as parameter to the Excel function. So for example, let me just show you this, what I mean. Imagine that, yeah, just one second, I show you this. Imagine I'm writing this into a Python cell. This is black band, and so I'm in Python Excel. I start typing Excel, and then maybe I go like selecting this. As you can see, the range is going to automatically being updated. Please, fire away. If I'm adding lines or columns, will it adjust in the code as well? JOHN LINDQUIST- Good question, amazing question indeed. So this default setting is, as soon as you make any change, everything will be re-executed from scratch, which means from the first to the last Python cell using the execution model. Another question is, if everything have the same named space, does it mean if I define func a in page one and func a in page two, I will overwrite the first one, or can I use? No, you will overwrite. It's the global name space. It's my question. Yes. Yeah, please. Expand it. Like, because you can use the exclamation point and referencing a sheet, so you cannot reference, like, your old. The only way, that's okay. I see where this is going. As far as I know, the only way in which you can reference a, okay, so if you want to reference a cell, you can reference using Excel, which means you're referencing the value. If you're willing to reference a function, a.k.a. a Python object, you just use it. It's in your namespace already. You don't have to reference. It's not a good practice to use the same name of the function. I'm just wondering. Exactly. No. Okay. So if I understand what you mean is, so here in this cell, I have a Python function. Let's say I have, let's say, def my func, okay? And this function is not going to do anything but, I don't know, returning function, okay? This is the function I'm defining. I'm not defining anything. I'm not calling anything, just defining the function. If I run this cell, what you would expect would happen? What would be the output, you think? Interesting, yes. They decided to go like none, but yes. It's not actually a function object, because in this case, in a cell, you can have as many objects as you like. So it could either be the last you defined, but they decided to go for none. So, if I execute this, and to execute it is normally control return, but on Mac, this is weird, so I do like that. All right. I save it, and so it will be executed. So, as you can see, well, first off, this cell has been executed again. Have you seen the little, probably in the back, I'm sorry for that. Let me zoom in a little bit for those of you in the back. So if I save it again, nothing has changed because there was no change. And as you can see here, the highlighting is buggy, as you were saying, so you can't see the colors anymore. The way to do it, you either select it, you hide a space or something, and that works again. Otherwise, you use this one, which is way better. So let's go for selected Python cell. Right. So let's say I want to save this. Look at what happens with the cell. It says busy. And, well, this was quick, so the execution has gone, and everything has been executed. Quick tip I have for you. If you go in the option, okay, There's a way in which you can avoid executing everything from scratch because sometimes the code takes time because it's internet. I'll get to the question in a second. Thank you. Because there's internet, because of your anything, because the code requires time to run. And so sometimes you don't want to re-execute everything. So something you can do, you can go in the options, formulas, and do manual, and then recalculate the workbook before saving. So I saved the workbook before, and then he recalculated everything. Since it's manual, it means that whenever you save a single cell, it's not going to re-execute everything. If I do to automatic, if I switch to automatic, everything will be re-executed every time I saved it. It was quick, but it did happen. So let me do it again. I save a single cell, and both will be re-executed. Please, there was a question. Yes. Can you refer with XL list object? Okay. I'll get to that because I forgot to completely answer your question. I got derailed. I'll reply to that in a second. So, imagine we have my function here. So, now in this cell, I want to use this function. You just write Python here. Python cell, please. okay, pi, and then I call this function like I call it, it's in my namespace, actually my func is already in my namespace, so I can actually see it already so I call it, and then by the way, this is, these are errors that doesn't make sense for this now, but this is the diagnostic panel that shows the error that we got okay so I call this function and the function is actually returning func which was the return of the function so the function was returning func probably we can do something different we can do like hello pycon d okay now we save this everything being executed and now you see hello pycon d So I'm just calling the function. I don't have to reference the cell. The function object is already in my namespace. And lastly, you still see this annoying thing, since it's like an object output. If you just want to switch to the Excel value, you see it. And so that's the string. So Excel function can be used to reference the selected values, and the return of that function is always a Pandas dead frame, which means if you have a list of values and that's exactly what we're going to do here in this Python cell I've already written some piece of code we can expand later but can you read it? oh it's too small isn't it? I don't have much space to zoom here oh yeah I can so this is the code actually I did import pandas as pd just because I don't like shadow namespace so I prefer being explicit if I need pandas I import it but you you since it's already in the namespace you don't have to do it so we are selecting this table using this selection so this rows and this columns from that worksheet it's actually a table this is this is how you do it and then the excel function is It's actually saying, I want the headers included. And this is returning a data frame. So let's see if I can zoom out a little bit. Nice. All right, so when I execute the cell, I got this message here. And it says, look, OK, you can't see, but in the warning, it says, a cell we need to spill data into is in blank. It says there's some content there, probably. I don't know where it is. So something we can do is, like, double check where it thinks the content is. I have no idea. It could be here, perhaps. Yes, it could be here, perhaps. So there you go. So what I did was, like, I ran this code. I ran this code, the one I showed you before, and I switched the output of this cell to value like this. And that's why you're seeing the actual value. If I switch to object, you see a data frame. What is happening is I'm selecting a portion of this table here in the previous worksheet and bringing this into the Python namespace. There was a question. Thanks. Thank you very much, Jonas. So if I have defined or calculated something in a Python cell, do I have to rename it or give it a variable name, or can I just refer to it like A1 now in another Python cell? Is that possible? Okay. So, what exactly is that you want to reference? Do you want to reference the value, or do you want to reference the object? You want to reference the result of the cell? Right, okay. So, the result of a cell is generally a value, which means that it will be displayed into the cell if it's a cell value. Otherwise, it has to be an object. And that object can be referenced because it's actually a variable. You have to keep in mind that you're actually writing Python code. So the way in which you can reference it, well, there is a way in which you can do it. And I'm going to show you for very specific data types, for example, for images. I'm going to show you in a second a very good example. That was a very great question. Okay, let me go back to this one. let me switch this to value so what we've been doing so far is, okay, let me just remove this so we can get rid of some errors I don't understand and it says country, oh yes sure, okay so because I guess there is a Python cell somewhere, S3 oh it's here, right, okay yes, right I'll get back to that So, in the interest of time, let me just zoom in and show you some extra thing we can do into this one. All right. Is this big enough for you? Good. Okay, I have some snippets I can copy and paste. So, something we can do extra is like that. so instead of returning data frame in the cell something we can do it's annoying this thing isn't it I'm definitely not used to Windows so something we can do is first off we can fix the date colon to be a datetime object otherwise it's a string according to Python and this is because pandas didn't really understood the date as a date time object. Secondly, there was a strange thing that every column has a space in it, which is a very weird reference when you work with Python. So we strip all the spaces from the column names. And then we group by creating a new data frame calling country segment. We group by the result by country and segment. And then we aggregate the sales into this group by. And then we want to return the result of the group by element. So if we now save and zoom out, right, we have the new calculated field. Okay? So something we can do is, like, now we have an actual object called country segment. So this cell is actually returning an object, which is country segment. It's a variable, okay? So the return of that cell is a data frame. I switch the output mode into value, so I see the actual value, okay? So there was a bug in the beginning that didn't allow you to see all the values. Now you can actually see all the values in this data frame here. So if you go down, you see 25-ish rows into the aggregation with all the values in it. Something else we can do is, like, we can go here and write some Python code to generate a chart based on that aggregation. In fact, this code, let me zoom in again, this code is using country segment, okay? This is S3, so way, way longer and distant from that one. But it's using Seaborn, and it's creating some categorical plot using CountrySegments data frame we just created. So if I run the cell, what I get, since the default output is an object mode, what I did get was an object image in this case. Because, sorry, I didn't show you exactly, by the last row of this Python thing, this Python cell is actually returning the figure object. So I have an image object, okay, which is actually an object. Something I can do, and this is answering your question, is in another cell, I can actually reference the, it's this S3, so I can do S3 and automatically I get image, Python string, Python type, Python type name, Python size. These are the three things. If this object was an array or a list, I could have done the same thing. Instead of having image here, I would have had array or list. If I do S3, so S3 is the reference to the cell. If I do S3.image, and I run this one, Control-S, I actually get the image within the cell. So you have to, it's a bit weird, but something you have to, oh my God, no, don't do this. Something you have to do is to make it very big. We appreciate that, but this is what it is, okay? Something you can do, though, is that this can become handy, especially if you don't do this as I did, but better if, for example, you have your cell code in one place. Why is this complaining? All values in the formula are wrong data type. I have no idea what it's talking about. But maybe you have a specific part of your workbook which is automatically ready to accept the image. So, for example, I just enlarge this area of my workbook to make it working on an image. And so I can do something like that. So s3.image. And this should work. There you go. Okay? So it requires adjustment, but this is how it is. Of course, if I just switch the output to this cell from object to value, it's essentially doing this, like that. It's going to be a very tiny, teeny image there. It's the same thing. Of course, in this case, this fails because it's referencing an object that doesn't exist anymore. okay my takeaway my best practice here rule of thumb is keep on the output to object mode and then reference that the the value whenever you need it because having a cell containing this weird object here it's a sort of a placeholder to say this uh this cell is actually returning something okay this this object is in there's an image object that you could reference there All right. Going back to the slides, let's see what we have here. Okay, I told you this already. Date, this is what we've done so far. So we've been selecting the data, fixing the date colon to a date time, and then returning the date frame. And actually, we've done more than that. we can do full-fledged Pandas code within the cell. Thank you. Are we finishing 5.10? 10 passed. Okay, brilliant. Okie dokes. All right. As I was saying, you have access to data visualization libraries like Microsoft Libre and Seaborn. and these libraries are indeed part of your namespace already to be honest, so you don't have to import it but again, that's my general takeaway is to be explicit in the namespace it's, in my opinion, better code rather than having shadow import that you don't know where it comes from especially because you have to think that In this room, I would expect that 100% of the people in this room are totally familiar with programming in Python and data science Python. The majority of people using Excel, or at least for those using Excel, I don't know if it's the majority, but for people using Excel, has the reference data reporting tool, but with no programming skills, they may find this more confusing rather than useful. but it's a thing. Anyway, so far, what I hope I did with you was understanding the full potential of Python and Excel. What I want to show you now is to have a specific use case. I think we have more or less half an hour, so we can work as long as we can. In the meantime, you can ask me any question you like. I'll show you another example, like doing a machine learning experiment within Python and Excel. Familiarize Python by coding in workbooks, gotcha and tips, live coding, that's going to happen, and best practice of machine learning in Excel, and how to organize your project. This is a point I'm going to explain in a second. All right, so the machine learning demo, I will forget the slides. we can work along and move it as long as we have. Probably I'm going to show you this. What I wanted to show you is working with the IRIS data set. And in particular, are you all familiar with the IRIS data sets? So no need for me to explain. The very first thing I would like to do is to familiarize with the environment, get the data sets in Excel, and visualize the data in Excel. So the thing I want to work with right now is slightly different. So, so far, we started with the classical idea of, I have my data in Excel, and I want to use Python to work with my data in Excel. And this is totally fine. This is probably going to be the 90% of the cases in which you use Python and Excel. But just for the sake of curiosity, and because we're nerds, and we want to program a little bit. Let's try to see differently, like a different use case in which we start. We try to import data into Excel via Python, and then we work with it. So let me open the Excel Labs extension. So just in the interest of time, let me just copy and paste the snippets, and then I can describe them. There are plenty of things we can do. The very first thing I want you to try is to have an idea of versions of libraries and CPU count, which is something useful when you're running data science. OK. And this one, it's going to be this. I always do that. All right. OK, if I save this, let me show you what I'm doing. OK, I'm just importing different libraries, including scikit-learn. I want to see the Python version as long as the versions of the other library is imported and how many CPU we have in our sandbox. Be ready, because you're going to be disappointed. It's just going to be one. But we have Python 3.10 running. So pretty much, yes, I'll get back to the question. And then these versions of Scikit-learn 1.3, which is pretty new, and Pandas 2.1 and 1.26. Last time I checked, they had different versions, and version of Python, Python 3.9 was the case, which means that they're updating the environment. Please. You just copy and paste the piece of code in the cell. Yes. From your experience, is there a way to have a set of functions, lines of code in Python that you would only change in Visual Studio Code and that would be keep up to date here somehow when you modify it on the other side? No. So if I understand correctly, the question is, is there any integration between Visual Studio Code and Microsoft Excel? Is this what you mean? Similar to like Jupyter Notebook within Visual Studio Code? There's no way you can. OK, let me rephrase my answer. There's no way you can work in the Python code in Excel using a external toolbox. So Visual Studio and Python and Excel, they are separate things and they live separate lives. So, there's no way that you can, there's no way, unfortunately, to have a library of functions you can reuse. There's no way to store modules in Python and Excel. So, if you have a specific Python modules that you want to run within Python and Excel, you have to copy the code within the cell, which is insane. Probably, it's absolutely out of the scope, but you can do it. There's a follow-up, I guess. Sorry? Could you use a third-party library? I will repeat the question. No worries. Can I use a third-party library? To put the code in Excel. Okay. So you... . Yes. . I see what you mean. Okay. The question is, can you run some gimmicks or some magic that you essentially import Python code running that specific function? You can if you do something like a JavaScript evaluation thing, like you write Python code with your Python code. So that's, and I'm not sure even that works. So it's not meant to be working that way. So there's no automation. I see your point. What you're trying to understand, if there's any automation, any thing that you can use to reuse yourself, like to store and avoid boilerplates. And that's a fair point. But as far as I know, no. The answer is no. You have to work. It would be similar. I think that I had these same questions in the beginning. And the way I normally answer that is thinking of what you can do in Jupyter Notebooks. you normally don't have this sort of gimmicks in Jupyter Notebooks. Every Notebooks is independent from another one because you work with Jupyter Notebooks and the way in which you can reuse something in Notebooks is copy and pasting code within the cells. And that's exactly how it works by then in Excel. It's just a bigger Jupyter Notebooks. The execution model is the same. It's just like working with more dimensions if you want. But that's how it is right now. Yes? I'm interested in the other commands or functions in the Excel module that is imported for Excel, but it would be interesting to create an Excel chart or Excel diagram from Python called and not just a static image. That is a very interesting question. So the question was if the Excel module includes more than just the Excel function. And as far as I know, the answer is So as far as I know, the answer is no. There's just the Excel function. I don't think there's any other. We could check, if you want, and that we could do live together. Like, and we can only do by, how could we do that? Because if we import Excel, we could try to see what is the auto-completion we get. But this is how far the init in the Excel package is providing. What sits inside the Excel code, it's beyond my knowledge. And actually, I don't think the Excel package code is available anywhere, to be honest. Okay, just a quick one. Is there a quick way to remove all the Python code? So that's, like, remove all the formulas so they can pass it on? No, no. It works cell by cell. I mean, like, at the end, I say all the formulas have to go. It has to go. I want to keep the results. I give that to my boss. OK, good point. Like you would clean a Jupyter Notebook. Yeah, it's like the normal problem is make the problem go. OK, no, no. Because Python code is supposed to be shared within the workbook. So they want you to keep it. So yes, yes, because, and this, I thing makes sense. I think it makes sense because some of the results are actually Python objects. If you, they live in the workbook as Python objects. So you can actually see them as Excel tables or like a sort of a data frame turned into Excel tables. But what is it? It is really is a Python data frame. So it requires to have Python to be visualized. If the output of a cell is a data frame, and you don't have automatic execution of the formulas, you may probably get the output. You don't see what's inside the cell, so you don't see the Python code, or it just doesn't work. So you can do that. But I guess that the rational there is you share the workbook with your Python code so everything works for everyone. The interesting, so the other side of the story is that since everything works in the cloud, out, you don't have to have a specific setup. You just have to have Python feature in Excel and everything works out of the box. Yeah, that's one of my questions. And let's say I work with Python and Excel and I sent this notebook to someone else. Can this person see at the, is it still an Excel as X file? JORDAN THIBODEAU- Yes. No, it's an Excel file, full-fledged. If this person doesn't have Python and Excel, what would they see? Would they see anything, like the last results? Or would it just be nothing? JAN-FELIX SCHWARTZMANN- That is something. OK, the question was, what if I share a workbook with Python and Excel to someone not having the extension installed? And the answer is, I don't remember. I think I was actually going to say I can open the workbook on Mac OS Excel. But I just remembered that this computer is brand new, so I don't have it yet. So sorry for that. But essentially, you're going to open the workbook as it were, like a general notebook. So the Python code, I'm pretty sure I'm correct, but double check it, please, if you can. You will see the Python code as standard text in the cell, and nothing is going to happen. Some of the outputs, like the images, won't be visualizable because they're Python object things. So you have errors, strange errors into things, and not working at all. Some of the values could be retained, especially the string values could be retained. But I'm not 100% sure of that. So this is something you should tinker with. I can't exactly remember. Sorry. Regarding the question about doing like an Excel graph or something like that, I was wondering if you have a data frame output from a function, and it spills over, you could actually define a named range for the result and then use that name range to do graphs. OK, so named range, I assume, it's an Excel function? I mean, you design an area where you have data, and then you, it's a. Oh, oh, I see what you mean. Yes. Yes, you can do that. OK. Yes, you can do that. Yes. They live as values in Workbook that you can name, range, and generate charts afterwards. If I'm not mistaken, that should be the case. Yes. So you have to name your result. Because you have to set a string that will then be the reference of your data. So I'll tell you what I understood from your question. Because, OK, you're probably more experienced than I am with Excel. So if I understand correctly, what you're saying is I generate a table in Excel using Python code. And so that would be a Pandas DataFrame, which has been visualized as a value. And so it looks like a table into Excel. And then I want to reference using range values when, I don't know, I do conditional formatting or I do plots based on those values. Is this what you mean? It's more like you don't know how big it's going to get, so that what I... Oh, I see what you mean. And then as big as it's going to get, I mean, as many rows, it's like I'm going to be inside that space? Okay, no. As far as I know, you can't do any of that. The only thing I would advise you to do is to keep everything that is Python object within the Python ecosystem, so within Python code. So every selection, every chart you want to generate it's easier if you still work with Python to do everything. But I see definitely your point. Like, if I'm someone not very familiar with Python and I'm more familiar with Excel rather than Python, how do things work together? And the answer is probably there's still a bit of, you know, gray area when it comes to integrate existing Excel to new Python Excel. So, yes, that would be my answer. There was a question? Thanks. I guess a short question, because normally I can reference as well different documents in the sense of not just worksheets, but other worksheets, basically. That's possible within the... No. Okay. It was a short answer. May I have a second question? Easy. Coming from a... Shall I start with nodding already? No, no, no, no, no, no, no, no. It's not a yes or no question. This is an open question. Coming from an Excel-heavy company, I'd say, or environment, this really scares me, to be honest. Okay. Do you have any best practices in debugging? Great point. Debugging is quite... Trying to find the polite way to say it. Detrimental. Could be difficult. Jokes apart, it's... panel helps a lot, like, because it gives you the reference exactly of where the error is going to get and what is happening. This is an empty cell. You see none. But if I do any mistakes here, like I either have, I don't know, error in my code or anything, you have to go cell by cell. And most of the time, the error you get will reflect to other parts of the workbook, which means that as soon as you fix something, you re-execute everything, and then you do. Which means that the takeaway of that is try to keep the code as small as possible, give feedback to yourself, as in, if you're defining a function like I'm doing right now, right? So, if I have some execution to do, label things you're doing, and in cells like this, use a string to mark what's happening there. So, for example, if some cells are just dedicated to define some code that we'll reuse later on, just add a final string there to say function definition, like if the function is called split data, split data function defined, something like that. This will help you in understanding where to go and look at things. Otherwise, you have to look for the cell you're looking for, cell by cell. There's no other way, because you see the output, you don't see the code, because the code lives in the formulas area, so it's always tricky. But debugging is still probably a room for improvement kind of feature. Yes, please. So it's similarly terrified of what's going to happen. Yeah, this is a very similar question, dovetailing with the previous. Also terrified of what's going to happen when Cal from Accounts sends me a sheet with 17 worksheets and someone's defined XE to calculate exchange rates twice in the worksheet. can I dump all the Python code out of the sheet into a sequential list to go through it, or there's no XMRD to dump it? No, at the moment, there's no debugging feature. There's no debugging capability. I presume that the answer to this very fair question is that, and that's based on my impression, not facts, is this feature is having more Excel users rather than Python users in mind. So it's meant to be like the feature of stop using Visual Basic to do strange things. If you need to go beyond what Excel is providing and you're using Visual Basic to do it, you can now do it in Python. And actually, you can do way more than that because Python is actually a much more powerful language and the libraries you have access to are much more powerful than what you have in Visual Basics. And so this opens up unprecedented use cases, but at the same time, as you're saying, it opens up to lots of issues from a software engineering perspective. So it's more like use it with caution kind of thing or beware. But generally, to my experience, the amount of code you write into cells and Python cells is the same amount of code you would write into Jupyter Notebooks, which means if you would come to me with a Jupyter Notebook essentially containing the implementation of a Python module, I would definitely give you the Notebook back and say, please, rewrite this, use Python module, and call the module from the Jupyter Notebook. That's the way you use Jupyter Notebooks, okay? And that's exactly how it should be. So even to answer the previous question of what is the automation I can use to bring Python code and blah, blah, blah, it is fair because it could save you time, but the idea is that you're probably thinking the wrong way. You have to think of, like, writing snippets that can interact with the data you have in the workbook cells. Yes? Very quick question. The version numbers of the libraries that are already given, are they persistent over this document So the next time I open, I will get the same versions or will it change? That is a fantastic question. And that's what I said the last time I checked. They were different. So this will probably answer the question. So far, we have no control on the environment. What I presume is happening is that everyone is checking whether there's any incompatibility between the multiple distributions to guarantee legacy back-end portability. But the reality is I more tend to think that since this is better, people should be expecting things to break. And so at some point they say, this is better. So if you're really relying on some function of specific versions, it's on you kind of thing. Yes, please. Let's say I'm dealing with sensitive data, which I do not want to run into Microsoft Cloud. Is there any way to avoid that? Okay. Maybe in the future? Okay. So this is also a question that came up quite a lot. The answer to that is, well, the specific answer to your question is no. There's no way you can, I'm sorry, I'm very negative today. I promise you I will be more cheerful in 20 minutes. What? No, no, it's the right audience, actually. I'm enjoying this. Not to say no, but I really love this interactivity. Wrong audience for speech. Wrong audience for speech. I don't think so, though. I still believe there's value in it. It is just new, so you have to get used to it. It's quite a different mindset. It would be, if you're working with Excel, this could really open up new use cases. What we're talking here, we're talking soft engineering, and that's absolutely fine, but this is beyond what is the status of Python and Excel at the moment. There's no way you can control what data goes in and goes out, but the answer to your question is that Microsoft has done lots of thinking about that. The sandboxing is guaranteeing that none of the data is going outside the sandbox. No one can access the sandbox. And Microsoft can never access neither the code nor the data being executed. So this is, by design, what they say. So it's part of the feature of Python and Excel. And that is the only thing I can tell you about it. The rest of it is beyond me, so I don't know. All right. I guess we have very four minutes. Oh, ten minutes left? Okay, so we finish at 20? We finish at 20. 20, okay, brilliant. Okay, let's do that. And thank you very much for being such an amazing audience. Okay, so let's say I'm just copying and pasting again. Let me do something different, though. I just want to exit the full screen so I can do the switch easier so something I want to share with you is this something I think it's worthwhile as a best practice is since we're dealing with we have this ability of organizing our code and data into multiple worksheets I'm running a machine learning experiment so presumably a good way to organize this is like to have a single worksheet for every single step of my of my experiment and since this is actually a workflow a pipeline of everything uh the execution model is actually go is getting along because we actually execute everything from left to right so in this very first one we are actually importing the data sets uh the next thing I want to do is import low diuresis from scikit-learn. As you can see here, I will be and saving. OK. So as you can see here, let me just switch to Selected Set Python Cell. So this will be easier. What I'm doing is I'm getting the data as Pandas DataFrame, doing some things with the variable, And printing a string, okay? And then returning the string. Has the object evaluation. And the output of this cell is object, so this will actually be represented as an object string. So we have the usual information, 150 samples, four features, and so on and so forth. Two classes, class names, everything as Python object. Last thing before we move on is to, Oh, something we can do is we can add a new Python cell here, like add Python cell in F1, and this is how it happens. So we just fix a random seed, and we set it. Okay, this is an example of what I mentioned before. I'm, like, setting a seed, just setting this as a random numpy seed. If I don't have this final string in the very end, this will happen as a none in the end, so it's not useful for you. If you just add this string, it's just documentation in a way. Okay? Okay. Running this and setting random seed. This is what we got in output. Setting random seed. Finally, we want to see the data set. We have X and Y. We can add a Python cell here and just write X. This is the variable we define here, so the execution order is respected. We can save it. So, blah, blah, blah, blah. This is an output. This is a data frame. We can switch it to Excel value, save it, and we can see it as DataFrame. All right. So here it is. Probably here we could add the labels, and we could do the same thing. We can add Y and switch it to Excel value and go and save it. And we have the targets. this is an actual data frame so we have the headers has the first row and everything so this is how it looks like if you spill directly that I could save some space here maybe let's see I want to get out of this and hiding on there There you go. So first one is, first point is done. The second is setting up a machine learning experiment. We do data partitioning. So we can do train and test split. And then we visualize it into Python and Excel. If you will download and you want to play along on your own, yourself, in the workbook, You'll see that I've prepared the strings already, so you just have to fill in the code. But I will share the complete notebook in the end, for those of you. I will update the pre-talks page, so you'll find everything there. And, okay, training sample. Well, we're going to just use the classical train test split from scikit-learn. Don't just have me writing it. It's just simple stuff. just three lines of code, and blah, blah, blah. So we want the training samples in this one, so at the end of the cell, we're going to return the X train, and so we have the X train variable as a data frame. This is certainly going to be Y train. It's pretty easy. Unfortunately, the control enter here is not working, which is annoying, I just don't understand why, probably I should do this, it's just the weird parallel things on Mac, but I can also do this, I didn't show you this, oops, let me save this for now, something you can do is, you can, this is serious, that makes sense. I just want to see if I can run it. Okay, no, it's not interesting. Test sample, it's going to be, oops, it's going to be X test. Sorry, this is boring. And this is going to be ytest. Okay. So, training info is something we can try adding, but I can just copy it. Xtrain shape. Oh, that sort of info. Yeah. Xtrain.shape. And we save it. Oops. it's a cheaper one the value and the last thing is sorry I should save it and then we can visualize it here wants to spill on two cells right because it's it goes it spills vertically we I don't know what I did sorry is this one yes let me go let me go through this one which is way more interesting we can define in yeah we have five minutes I just want to show you this very quick we can define a plot function feature feature plot and this is the first thing I want to say like use when you define our documentations when you I copied the wrong code, sorry when you define functions it's very difficult to understand what's going on so regardless of what I'm doing here which is just defining a plot function using pairplot from seaborn what I do in the end is like documenting as a string like this is a feature plot x y title this is how I do it by the way it doesn't have to be this way but it's something I found useful whenever I was working with it. So I do define a string that's essentially documenting what's the API of this one, and then I can plot, I can call this function on data. So now I don't remember the code, but it's going to be feature plot, and then it's going to be x train, y train, And then the title is Training Features or Training. I now run this. This is taking a little bit of time because pair plot takes some time. And so you can appreciate that sometimes the code can take time. So imagine that that cell would just define the function, run the function, and do that for training and testing. It's too much. So try to work on small pieces of things. This is an image. We can do the same thing here. Feature plot on X test, Y test, test. This is super quick. And then I finish, I promise. I see you approaching. And this is the thing. When you re-execute everything from scratch, He has executed the previous one before, so this is how you do it, but generally when you're working on it, definitely not recommend it. Okay, we have two images, and so I have it prepared already here, so let me do it full screen, but we can close this. I just referenced it, b3.image, if you look at the formula here, this is b3.image, this is b4.image and so you visualize it into the workbook here as we've done before that's it I'm sorry and time has run up both of us have a little history of running late I have some questions, if you also want your question answered. We just typed them down that slider. We will go through them now. First question is, can I remove all Python code? Oh, this was already answered. Can I call a function dir with the brackets and get a list of what is defined where? Dir works, yes. Although the where is going to be path in the sandbox. So that's where it is. So it's a file system you can't deal with. It is where it is. You can't create, you don't have right permissions on that file system, so you can't create new folders and things like that. Are there any more questions? I don't get any. No? Yes, please. Is there an execution time limit on the cells? So if I train a model and it takes two hours, it's frequently time for me? No, the kernel is going to be shut down. I think it's a few second execution. I can't exactly tell you the limit. I think the first limit is on the size of the execution. But I think at some point it goes time out. I can't, probably it's going to be a minute, two minutes, this sort of things. It's not going to run more than two minutes, my impression. There can be nuances there, but generally it's a very short execution. Yes, that's a good point. I think there are no questions left, so thank you for your tutorial. Thank you so much. You've been fantastic.