How to build a spreadsheet with Python
CC Attribution - NonCommercial - ShareAlike 3.0 Unported:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this
Percival, Harry
Harry Percival - How to build a spreadsheet with Python Do you know how a spreadsheet works? Can you imagine building one, from scratch, in Python? This talk will be a whirlwind overview of how to do just that. Based on the source code of Dirigible, a short- lived experiment in building a cloud-based Pythonic spreadsheet (now [open-sourced], for the curious). We'll start from scratch, with a simple data representation for a two- by-two grid, and then gradually build up the functionality of our spreadsheet: - Cell objects, and the formula/value distinction - Evaluating cells, from simple arithmetic up to an Excel-like dialect - Building up the dependency graph, and the ensuing fun times with recursion (arg!) - Integrating custom functions and user-defined code. Showing and explaining code examples, and alternating with live demos (don't worry, I've done this before!) And it's all in Python! You'll be surprised at how easy it turns out to be, when you go step-by-step, each building on the last... And I promise you'll be at least a couple of moderately mind-blowing moments :)
 Hello, everyone. Thanks very much for coming. Hooray. All right. Thanks for coming to this talk. I appreciate that I kind of mistitled it, and it's a very boring title. Maybe you think I'm going to talk about how to output data to a spreadsheet with Python. That's not what I'm going to talk about. I'm going to talk about how to build a spreadsheet application with Python, how to build an alternative to Excel. That's me. My name is Harry. My Twitter handle is the HJWP. Like all Twitter addicts, I'm more followers is crack to me, so be followers. My website is abaythetestinggoat.com, where I talk about testing. But here, I'm going to talk about magical Python spreadsheet. So that's what we're going to do. Does anybody here know how a spreadsheet works? How a spreadsheet calculates the functions you put into it? Yes, sir. You do? Anybody else? Good. All right. So you at the end can tell me whether I gave a good description. And everybody else, my plan is to try and demonstrate that making a spreadsheet is a little bit easier than you think. I'm going to try and build one up step by step. So first, I'd like to take you back to, let's say, 2005. It was a simpler time. There was no, well, Facebook had just started out and just got out of universities in America. MySpace was still all the rage. I had a MySpace page. Britney Spears had just released her seminal comeback album, Toxic. And of course, it was a great, great year for the beginning of the North American ragged jungle renaissance, if you don't like Britney Spears. So in this time, a series of programmers got together and they had this idea that everyone loves using spreadsheets, but actually working with them sort of sucks because you have to use VBA. And wouldn't it be great if instead of having to use VBA when you want to script your spreadsheet, you can use Python? Wouldn't it be great if there was a Python spreadsheet? So these guys got together and they had this crazy idea and they went off and they built a Python spreadsheet. It was a GUI app. And then four or five years later, I joined the company as we were just re-implementing this app in a web-based form. Come in. You're not late. The conference is late. It's lovely to see you. Hi, Paul. Hi, David. So we're going to build then a Python spreadsheet with our colleagues here back in 2010. And it's going to be a web-based tool. And what I want to show you is take you step by step through how can we build a working spreadsheet with Python starting from scratch. And it's going to look a little bit like this. So first of all, we'll assume that the GUI is a solved problem and that we can make a two-dimensional grid like this with all clever JavaScript that's going to allow the user to interact with it. And we're just going to build the back end, the engine, for recalculating the spreadsheet when the user does stuff. So here's a spreadsheet. I want to be able to do things like type into it. Yes, it's a talk with live demos. It's going to go wrong. I'm going to type in things like this. Sorry, ignore that. So I'm going to type in two. And I'm going to type in and three. Fine. OK. So what have I got so far is I've got a sort of two-dimensional grid. And if I want to store this data, I'm going to be able to say, OK, well, a1 is 1, b3 is the string, and 3. So we'll start off nice and easily. That might look, I'm going to propose to you something like this. We're going to have a dictionary. It's going to be indexed on a tuple of row number and column number. And that's going to contain a cell object. And that cell object is going to say, oh, I'm 1, I'm 2, I'm 3. So far, so good. Hooray. A round of applause. Yeah. Yeah. Yeah. You guys, you guys, you guys, you can't just applaud when the speaker asks for it. That's like super cheap. But I'm the cheap one. I'm not you guys. All right. So that's a pretty useless spreadsheet. Let's see if we can't make it do something better than that. Like over here, what if I want to go equals 2 plus 2? A spreadsheet should allow us to do some maths. OK. So who do you think is going to happen when I press Enter here? No, it's going to work. Hooray, 2 plus 2. So it works. That's not too bad. And notice what I've introduced here is that there's a difference between the formula. The formula is equals 2 plus 2. And then the result, or the value of the cell, is 4. So now I've introduced a cell. It's not just a bit of text. It's also there's a distinction between formula and value. And that might look a little bit like this in code. Let's say I'm going to have a cell class. It's going to have a formula and a value, which we initialize to a sort of magical undefined special variable. And then when we want to calculate the worksheet, we just go and find all of the cells in the worksheet. Don't look back at your slides. That's in my speakers tips. Are we going to go through all the cells in the worksheet and we're going to see how? Does this start with a little equals? In that case, it's a formula and I have to do something special. Otherwise, the value is just what the user entered. So what special thing can I do to get 2 plus 2 to turn into 4? I can basically just do eval, cell.formula. Room answer, just call that. And so 2 plus 2 is going to turn into 4 because I'm going to call eval on it. Hooray, eval statements. The best thing about Python. They never go wrong, do they? OK. Now is anyone particularly evil that would like me to change the formula that I've put into this cell? Any suggestions? Yes? OK, for the liberal people's time. I could do, yes you can. And you can put it in a string. How about if I do this? Everyone wants to see this, right? Error. Oh, hang on a minute. I thought of that too. Somehow we have to handle this. We have to notice when the user does something stupid. We have to catch some errors, maybe give them a nice little trace back and show them a little warning. There's division by 0. So how can we do that? OK. Well, we're going to put try accept, classic. We're going to call eval cell.formula. And then when we catch an exception, we're going to go and populate that error on the cell instead of calculating its new value. Fair enough. OK, well, so hooray. Well done. I've got a spreadsheet that's basically a two-dimensional calculator. None of the cells can talk to each other. This is still not a spreadsheet. It is still no better than a calculator. So what we'd really want to do is maybe be able to refer to other cells in the spreadsheets, right? In my things, I want to be able to go something like a1 plus a2. Everyone think this is going to work? Yes! Hooray! a1 plus a2. OK, so what have we done there? We've taken something that looks like a1 plus a2. And we need to somehow turn it into something Python's going to understand. So in a way, we're taking a1 and a2. And we've got our worksheet object, which is a dictionary, and it contains all of the cell objects. And basically, we want to translate the sort of string a1 and the string a2 to become some valid Python. So if I manage to turn a1 into worksheet11.value and worksheet12.value, then I could call eval on that. And so the way we're going to do this is we need to transform things that look like Excel formula, things that include cell references, into things that look like that Python can understand. And we've already got our worksheet object to refer to cells. Does that make sense so far? OK. All right, so what are we going to do that? OK, fine. We're going to have a little formula, a little setter for our formula. We're going to say, hey, if it starts with an equal, then we're going to go and parse the user's input and other one. And even, I'm going to show you that, you might get a formula error. So if there's a syntax error in their formula, you can pop that in there. And meanwhile, you can transform a user's formula into a Python one. Does that make sense? Now, would you like to see some of the magic of parse to Python formula? Enthusiastic yes. Yeah. Yeah. So this is the first little bit of recursive fun. And I thought I'd present it using some tests. So we want to be able to say equals1 should turn into 1, equals1 plus 2 should turn into 1 plus 2. Equals a1 should become worksheet1.value. And then you're going to have crazy formulas in your things. You can have x times a1 for x in range 5. That is a valid formula that you can enter into this Pythonic spreadsheet. And that's going to turn into this. So all of these sorts of things will happen. I'm not going to go into the details of the parser. Parsers are parsers. This is a special one that knows how to understand Excel and turn it into Python. But you're going to have some recursive fun. You're going to look at a node. You're going to say, hey, if it's a cell range, we rewrite that. If it's a cell reference, we rewrite that. And then we're going to call the parser the rewrite function on each of the nodes inside it. So if you've got a cell reference or a cell node, you've got some children. So that's the first bit of recursive fun. You parse things, and you look at your a1s, and you've transformed some a1, a2s into valid Python. OK. So that makes sense. But we're not really finished with that job. OK. So we've got equals a1 and a2. What if we have something like this? I'm just going to go and browse. Sorry, I should have opened this before I started. What if we'll out? I'll just show you on this spreadsheet instead. OK. So I've got equals a1 plus a2, and that's fine. Now what if over here I have another one, which says equals b3? Well, now I can't just evaluate these cells in any old order, because before I can calculate this one, I need to know that it depends on this one. And then this one depends on these two. So what I've introduced is a kind of dependency graph. That's the fundamental structure that underlies a spreadsheet. You have a series of cell references, cells point to each other, and those are the dependencies. And in order to actually do my calculation of my spreadsheet, I'm going to need to know what that graph looks like so that I can calculate the things that have no dependencies first, and I calculate things that depend on them, and then I calculate things that depend on them, and I do it in a sane order. And so we had a little bit of recursion in our parser, and now the real recursive fun begins. Recursion, it's the only truly fun thing about programming. We're going to do something a little bit like that. We can parse the dependencies out. We've used our parser to recognize a1s anyway, so we can also say, oh, OK, that I can find for any cell formula. What other cells does it depend on? I can have a little function to calculate a cell. I'm going to go, OK, evalves cells Python formula. And now I'm going to be able to build a dependency graph. And this is going to say, what order am I going to be able to re-evaluate my cells in? If I build a dependency graph, find out everything that depends on everything else. I can find all the leaves in that graph. I start with the leaves. I pop them off this sort of queue. And each time I calculate a particular cell, I can then remove it from its parents. Does that make sense? We're going to say that when you've calculated a cell, the things that depend on it don't depend on it anymore, so you remove yourself from the parents. That means that we did have a graph that was a one-way graph. We knew what every cell depended on. But now we need to know who and so we know the cell's children. And now we also need to know the cell's parents. So we need an algorithm for parsing a one-way graph and turning it into a two-way graph. And let me see if I can explain that to you. So build a dependency graph. We generate a cell subgraph for each of the cells. And we're going to parse in these arguments. We look at the worksheet. We keep track of the current graph, which is the two-way graph instead of the one-way graph. We keep track of the current location, and we keep track of what things we've completed already. So like any recursive algorithm, the very first thing you need to think about is what's the exit condition. The exit condition is a thing that you come across and know that you've already done, in which case you can exit out. Fantastic. Next, we look at our cell. We know who its children are, so we're going to add them into the graph. And then for each of those children, we're going to recursively call the same function to go and find, you know, do the same thing. So we do the children, and then we recurse down into each of the children. And once we've done that, we've completed that particular cell. Adding the children involves creating a node for the parent, and for each of the children, we create a node for them, and we say that you are in the parent-child relationship with it. Does that make sense so far? Hooray! Who now? That's quite a lot of hard work. You have to wrap your head around it, and then pretty soon you'll be thinking, hang on a minute. What if I have A1 depends on A2, and A2 depends on A3, but A3 depends back on A1, and I've got a circular dependency, so I can't quite do it like that. I'm going to have to track the current path that I've taken through the graph as I'm recursing down into it. And if I spot that the current location I'm at is already in the path that I came to in the current stack of the recursion, I'm going to raise a cycle error. And that means that I need to catch the cycle errors when I make the recursive call down into the algorithm that's looking at each of the subcells. So that is going to make sure that I can also catch cycle errors. Still OK, everyone? Yep. OK, fantastic. Who knows a better way to do this? It's rated tool? Not sure about that. You can do a thing that's I think called NetworkX, which is a network analysis package. And you can give NetworkX a one-way graph, and it will just give you back a two-way graph in two lines of code. So a lot of wasted effort and hard work on the part of the dirigible spreadsheet developers. But it was character building. We enjoy recursion. It made us joyful, so fantastic. All right, so far so good. All right, great. I've now got a little thing that can go, OK, b3, a1, a2, a3. Now, what if I wanted to go into my spreadsheet and actually start having some proper Pythonic fun? So if I wanted to define some custom functions and now start using more Python in my actual spreadsheet. So if I wanted to go def foo of, like, say, x, and we're going to return x plus 42. All right, great. It'd be lovely to be able to use this function foo inside of our, what should we call it, spreadsheet, right? Am I going to do foo of 3? Do we think that could work? Yes! All right, how are we going to get that work to work? We've now got two sets of user inputs. We've got all the values and all the formulas they've put into the spreadsheet. And we've got also some custom user code that they've put onto the right-hand side. So what we're going to need to do then is we've got two sets of evaluations. First, we need to evaluate the user code. And second, we need to evaluate each of the cells. And the way we're going to do that is we're going to start isolating our eval calls from the global context, which is probably something we should have done ages ago. And we're going to call eval the user code. And that's going to populate the foo function into this context. So a context is just a dictionary. All of Python's namespaces are just dictionaries. Namespaces aren't they great? And then I'm going to pass that same context to the eval context of each one of my cells. Hooray, I've now got custom functions. But I am sure I hear you ask, what if we want to write a function that actually can access things that are already in the spreadsheet? So supposing rather than having a function foo, I'm going to have a function that say, have I got it over here, is going to say sum everything in row A. And I'm going to want it to say, OK, well, look at things that are already in the spreadsheet. So I can't evaluate my user code before, say, I've already done a cycle of loading some of the constants in the spreadsheet. So I can always load the constants if I know, if something is a constant, I don't need to evaluate it. So I could evaluate, before I evaluate each of the cells, I can evaluate some of these functions. And then I'm going to have something like this. All right, so I'm going to say, load all the constants, then eval my user code. So the user code has access to the constant. So I can have a function that looks at what's already constant in the spreadsheet. And then evaluate the formula. But as I'm sure you're wondering, what if I wanted to write a custom function that can access the results of evaluating the cells? Well, then I'm going to have to say, maybe I'm going to let the user input two types of user code. One to be run after we load the constants, but before we evaluate the formulae. And one to be run after we evaluate the formulae. And sure enough, that is what one can do. And you're going to do something a little bit like this. So you've now got a load constants function. You've got an eval of the user code pre-formular evaluation. You evaluate the formulae. And you eval the user code post-formular evaluation. Who is still with me? Hands up. OK, almost everyone. So that's fair enough. So here's the realness. So that would mean then I would have to put two little code panels over onto the right-hand side. And hopefully, this is the bit of the presentation where something slightly magical happens. If we have a look at this bit of code here, where we're doing, OK, let's build a context. We add the worksheet to it. We call this load constants function. We call the evaluate formulae function. And then we've got this user code that's kind of before and afterwards. What if instead you had a thing? Oh, incidentally, you can now, if you put things into the pre-formular evaluation functions, you can actually put formulae into the spreadsheet from the user code panel before the formulae evaluated. This is a certain. I mean, this amazing Python spreadsheet is a surefire set of guns pointed at both feet at the same time. It's brilliant. OK. So what about, this is what we're doing right now. And then what the user actually sees when they log in to a dirajor spreadsheet, and then I'll start a brand new one for you, is they see this. The user code panel is pre-populated with a function called load constants, which you're noticing is exactly the same name as the function that I used in the actual evaluation of the spreadsheet. And it's got a function called evaluate formulae, which you might remember was the same name as the function we're going to use in the real evaluation. And so here what I'm going to do is I'm basically going to turn the whole spreadsheet on its head and say that the spreadsheet is the user code. And I have my load constants evaluate formulae in my calculate function. I take my context. I put the worksheet into it. I put the load constants function into it. I put the evaluate formulae in context, which takes the worksheet and carries it into my normal evaluate formulae function. And then all I do is exec the user code. And it's just your user code panel that loads the constants and evaluates the formulae and all of that. And that means that you can do totally crazy things in your user code, like you can evaluate formulae multiple times. Or you can put nested recursive calls to the spreadsheet itself. You can make spreadsheets that call other spreadsheets. You can populate formulas done programmatically. All of that sort of fun. The spreadsheet is the user code. The user code is the spreadsheet. This is the most pythonic spreadsheet available. Am I telling you that you should use this spreadsheet? Absolutely not. If you want to work with spreadsheet type data in Python, just use an ipython notebook and pandas. This is just a little bit of fun that I thought you might be interested in. The dirigible source code is all available on our GitHub at github.pythonanywhere. forwardslash dirigible spreadsheet. If you fancy taking a look at it, I've now given you a tour about it. And hands up, please, if you found that easier to understand than you had thought it was going to be to understand how a spreadsheet works. Wait, no. Hands up if you now understand how a spreadsheet works. That is like a 90%, I'd say. Hooray. And who thought that was easier than they thought it was going to be before they arrived into the room that they thought that they thought that the spreadsheet would be evaluated for what it was going to be? Irish just held up a sign saying, are you kidding? So hands up if you thought that was easier and simpler than ever. One person. All right, that's good enough. No, no, no. Please, hands up if you genuinely thought it. And if you thought that was really confusing, there you go. Hands up if you think I should never do this talk again. All right. So by implication, that's two people putting their hands up incidentally, any program working groups that are considering talk submissions for future conferences, two people put their hand up, which means about 200 people in here think I should do the talk again. Thank you very much, everyone. Good night. We have four minutes for questions. Hooray. Hi. Did you think about security? Yes, we did. So obviously that was one of the major things we were giving this out to random strangers on the internet saying, hey, will you eval your Python code on our servers for fun? So yes, we have a sort of sandboxing model that allows make sure that each of the users can only access a restricted part of the file system and basically the whole containerization story that you've heard to death at this conference already, no doubt. That's the story. I have stripped all that code out of the dirigible that's published online, although you can find it in the history if you're interested, because it's easier to demo and easier to understand without the security stuff. But yes, you can. Good question. OK, maybe one more quick one? Thank you for the talk. It's more common than a question. Well, to be serious, when I look at my colleagues work, and they work a lot of time with Excel, I think it could be some kind of killer application or serious, because we can work with pundas and do it at this level. But during our work, often people really want to change single cells, among 1,000 cells, because of some special thing they just heard or whatever. And if this is easily trackable and expandable with Python functions, I think it has potential for really serious or useful tools. No, you're right. I mean, the spreadsheet is a wonderful tool. I wrote a thesis about it for my masters. And that whole instinctive two-dimensional, I can see the numbers, I can change them, I can visually represent the relationships. It's all wonderful stuff. But it turns out nobody wants to use a spreadsheet other than Excel. And so as soon as you try and build a competitor Excel, everyone's like, oh, I want every single shortcut key that Excel has to also work in yours. OK? And that's quite a lot of work, catching up with 20 years in Microsoft. So however, any iPython developers, iPython notebook developers, who want to integrate some sort of spreadsheet component, would be happy to point you around the Dirigible Cobase and see if there's anything in there that you can use. There you go. What time do we have to finish, Iris? Two minutes. Two minutes. Hooray! That's time for another question. Here you go. Again, hooray! That's someone really engaged. Everyone else is like, how long before we can get? Maybe a weird question for a Python conference, but did you think about implementing this in JavaScript? Uh... So the straight answer to that is no. But I'm thinking about it now. Yeah, so one of the interesting things about it is that you run things on the server. So remember when we executed that, we looped around all the leaves in the spreadsheet and we had a sort of queue of them. It's very easy to start parallelizing that. And then you can do things like recalculate your spreadsheet across a cluster of machines. Because once you've got a leaf node, that's totally independent from all the others. So we were thinking maybe massive parallelization would be an interesting market spot to be in. You can't really do that with JavaScript. Is that the right sort of answer, Charles? Yeah, yeah. And no JS didn't exist back then. No, but you were thinking maybe you'd have the whole thing on the browser. Well, mostly to run it all locally. Yeah, all in the browser. I'm sure you could rerun it. All Python to JavaScript is pretty easy to rewrite. Knock yourself out. Exactly zero minutes. Let's rush to the lacking talk to see more Harry's performance. Yeah, I do have another job. Thanks very much for coming, guys. There you go.
 Hello, everyone.  Thanks very much for coming.  Hooray.  All right.  Thanks for coming to this talk.  I appreciate that I kind of mistitled it,  and it's a very boring title.  Maybe you think I'm going to talk  about how to output data to a spreadsheet with Python.  That's not what I'm going to talk about.  I'm going to talk about how to build a spreadsheet application  with Python, how to build an alternative to Excel.  That's me.  My name is Harry.  My Twitter handle is the HJWP.  Like all Twitter addicts, I'm more  followers is crack to me, so be followers.  My website is abaythetestinggoat.com,  where I talk about testing.  But here, I'm going to talk about magical Python spreadsheet.  So that's what we're going to do.  Does anybody here know how a spreadsheet works?  How a spreadsheet calculates the functions you put into it?  Yes, sir.  You do?  Anybody else?  Good.  All right.  So you at the end can tell me whether I gave a good description.  And everybody else, my plan is to try and demonstrate  that making a spreadsheet is a little bit easier  than you think.  I'm going to try and build one up step by step.  So first, I'd like to take you back to, let's say, 2005.  It was a simpler time.  There was no, well, Facebook had just started out  and just got out of universities in America.  MySpace was still all the rage.  I had a MySpace page.  Britney Spears had just released her seminal comeback  album, Toxic.  And of course, it was a great, great year  for the beginning of the North American  ragged jungle renaissance, if you don't like Britney Spears.  So in this time, a series of programmers got together  and they had this idea that everyone loves using spreadsheets,  but actually working with them sort of sucks  because you have to use VBA.  And wouldn't it be great if instead of having to use VBA  when you want to script your spreadsheet, you can use Python?  Wouldn't it be great if there was a Python spreadsheet?  So these guys got together and they had this crazy idea  and they went off and they built a Python spreadsheet.  It was a GUI app.  And then four or five years later,  I joined the company as we were just  re-implementing this app in a web-based form.  Come in.  You're not late.  The conference is late.  It's lovely to see you.  Hi, Paul.  Hi, David.  So we're going to build then a Python spreadsheet  with our colleagues here back in 2010.  And it's going to be a web-based tool.  And what I want to show you is take you step by step  through how can we build a working spreadsheet with Python  starting from scratch.  And it's going to look a little bit like this.  So first of all, we'll assume that the GUI is a solved  problem and that we can make a two-dimensional grid like this  with all clever JavaScript that's  going to allow the user to interact with it.  And we're just going to build the back end, the engine,  for recalculating the spreadsheet  when the user does stuff.  So here's a spreadsheet.  I want to be able to do things like type into it.  Yes, it's a talk with live demos.  It's going to go wrong.  I'm going to type in things like this.  Sorry, ignore that.  So I'm going to type in two.  And I'm going to type in and three.  Fine.  OK.  So what have I got so far is I've  got a sort of two-dimensional grid.  And if I want to store this data, I'm  going to be able to say, OK, well, a1 is 1, b3 is the string,  and 3.  So we'll start off nice and easily.  That might look, I'm going to propose to you something like  this.  We're going to have a dictionary.  It's going to be indexed on a tuple of row number and column  number.  And that's going to contain a cell object.  And that cell object is going to say, oh, I'm 1, I'm 2, I'm 3.  So far, so good.  Hooray.  A round of applause.  Yeah.  Yeah.  Yeah.  You guys, you guys, you guys, you can't just applaud when  the speaker asks for it.  That's like super cheap.  But I'm the cheap one.  I'm not you guys.  All right.  So that's a pretty useless spreadsheet.  Let's see if we can't make it do something better than that.  Like over here, what if I want to go equals 2 plus 2?  A spreadsheet should allow us to do some maths.  OK.  So who do you think is going to happen when I press Enter here?  No, it's going to work.  Hooray, 2 plus 2.  So it works.  That's not too bad.  And notice what I've introduced here is that there's a  difference between the formula.  The formula is equals 2 plus 2.  And then the result, or the value of the cell, is 4.  So now I've introduced a cell.  It's not just a bit of text.  It's also there's a distinction between formula and value.  And that might look a little bit like this in code.  Let's say I'm going to have a cell class.  It's going to have a formula and a value, which we  initialize to a sort of magical undefined special variable.  And then when we want to calculate the worksheet, we  just go and find all of the cells in the worksheet.  Don't look back at your slides.  That's in my speakers tips.  Are we going to go through all the cells in the worksheet  and we're going to see how?  Does this start with a little equals?  In that case, it's a formula and I have to do something  special.  Otherwise, the value is just what the user entered.  So what special thing can I do to get 2 plus 2 to  turn into 4?  I can basically just do eval, cell.formula.  Room answer, just call that.  And so 2 plus 2 is going to turn into 4 because I'm going  to call eval on it.  Hooray, eval statements.  The best thing about Python.  They never go wrong, do they?  OK.  Now is anyone particularly evil that would like me to  change the formula that I've put into this cell?  Any suggestions?  Yes?  OK, for the liberal people's time.  I could do, yes you can.  And you can put it in a string.  How about if I do this?  Everyone wants to see this, right?  Error.  Oh, hang on a minute.  I thought of that too.  Somehow we have to handle this.  We have to notice when the user does something stupid.  We have to catch some errors, maybe give them a nice little  trace back and show them a little warning.  There's division by 0.  So how can we do that?  OK.  Well, we're going to put try accept, classic.  We're going to call eval cell.formula.  And then when we catch an exception, we're going to go  and populate that error on the cell instead of  calculating its new value.  Fair enough.  OK, well, so hooray.  Well done.  I've got a spreadsheet that's basically a two-dimensional  calculator.  None of the cells can talk to each other.  This is still not a spreadsheet.  It is still no better than a calculator.  So what we'd really want to do is maybe be able to refer to  other cells in the spreadsheets, right?  In my things, I want to be able to go something like a1  plus a2.  Everyone think this is going to work?  Yes!  Hooray!  a1 plus a2.  OK, so what have we done there?  We've taken something that looks like a1 plus a2.  And we need to somehow turn it into something Python's going  to understand.  So in a way, we're taking a1 and a2.  And we've got our worksheet object, which is a dictionary,  and it contains all of the cell objects.  And basically, we want to translate the sort of string  a1 and the string a2 to become some valid Python.  So if I manage to turn a1 into worksheet11.value and  worksheet12.value, then I could call eval on that.  And so the way we're going to do this is we need to  transform things that look like Excel formula, things that  include cell references, into things that look like that  Python can understand.  And we've already got our worksheet object to refer to  cells.  Does that make sense so far?  OK.  All right, so what are we going to do that?  OK, fine.  We're going to have a little formula, a little setter for  our formula.  We're going to say, hey, if it starts with an equal, then  we're going to go and parse the user's input and other one.  And even, I'm going to show you that, you might get a  formula error.  So if there's a syntax error in their formula, you can pop  that in there.  And meanwhile, you can transform a user's formula  into a Python one.  Does that make sense?  Now, would you like to see some of the magic of parse to  Python formula?  Enthusiastic yes.  Yeah.  Yeah.  So this is the first little bit of recursive fun.  And I thought I'd present it using some tests.  So we want to be able to say equals1 should turn into  1, equals1 plus 2 should turn into 1 plus 2.  Equals a1 should become worksheet1.value.  And then you're going to have crazy formulas in your  things.  You can have x times a1 for x in range 5.  That is a valid formula that you can enter into this  Pythonic spreadsheet.  And that's going to turn into this.  So all of these sorts of things will happen.  I'm not going to go into the details of the parser.  Parsers are parsers.  This is a special one that knows how to understand Excel and  turn it into Python.  But you're going to have some recursive fun.  You're going to look at a node.  You're going to say, hey, if it's a cell range, we rewrite  that.  If it's a cell reference, we rewrite that.  And then we're going to call the parser the rewrite  function on each of the nodes inside it.  So if you've got a cell reference or a cell node,  you've got some children.  So that's the first bit of recursive fun.  You parse things, and you look at your a1s, and you've  transformed some a1, a2s into valid Python.  OK.  So that makes sense.  But we're not really finished with that job.  OK.  So we've got equals a1 and a2.  What if we have something like this?  I'm just going to go and browse.  Sorry, I should have opened this before I started.  What if we'll out?  I'll just show you on this spreadsheet instead.  OK.  So I've got equals a1 plus a2, and that's fine.  Now what if over here I have another one, which says equals  b3?  Well, now I can't just evaluate these cells in any old  order, because before I can calculate this one, I need to  know that it depends on this one.  And then this one depends on these two.  So what I've introduced is a kind of dependency graph.  That's the fundamental structure that  underlies a spreadsheet.  You have a series of cell references, cells point to  each other, and those are the dependencies.  And in order to actually do my calculation of my  spreadsheet, I'm going to need to know what that graph  looks like so that I can calculate the things that  have no dependencies first, and I calculate things that  depend on them, and then I calculate things that depend  on them, and I do it in a sane order.  And so we had a little bit of recursion in our parser, and  now the real recursive fun begins.  Recursion, it's the only truly fun thing about programming.  We're going to do something a little bit like that.  We can parse the dependencies out.  We've used our parser to recognize a1s anyway, so we  can also say, oh, OK, that I can find for any cell formula.  What other cells does it depend on?  I can have a little function to calculate a cell.  I'm going to go, OK, evalves cells Python formula.  And now I'm going to be able to build a dependency graph.  And this is going to say, what order am I going to be able  to re-evaluate my cells in?  If I build a dependency graph, find out everything that  depends on everything else.  I can find all the leaves in that graph.  I start with the leaves.  I pop them off this sort of queue.  And each time I calculate a particular cell, I can then  remove it from its parents.  Does that make sense?  We're going to say that when you've calculated a cell, the  things that depend on it don't depend on it anymore, so you  remove yourself from the parents.  That means that we did have a graph that was a one-way graph.  We knew what every cell depended on.  But now we need to know who and so we know the cell's  children.  And now we also need to know the cell's parents.  So we need an algorithm for parsing a one-way graph and  turning it into a two-way graph.  And let me see if I can explain that to you.  So build a dependency graph.  We generate a cell subgraph for each of the cells.  And we're going to parse in these arguments.  We look at the worksheet.  We keep track of the current graph, which is the two-way  graph instead of the one-way graph.  We keep track of the current location, and we keep track of  what things we've completed already.  So like any recursive algorithm, the very first thing you  need to think about is what's the exit condition.  The exit condition is a thing that you come across and know  that you've already done, in which case you can exit out.  Fantastic.  Next, we look at our cell.  We know who its children are, so we're going to add them  into the graph.  And then for each of those children, we're going to  recursively call the same function to go and find, you  know, do the same thing.  So we do the children, and then we recurse down into each of  the children.  And once we've done that, we've completed that  particular cell.  Adding the children involves creating a node for the  parent, and for each of the children, we create a node for  them, and we say that you are in the parent-child  relationship with it.  Does that make sense so far?  Hooray!  Who now?  That's quite a lot of hard work.  You have to wrap your head around it, and then pretty  soon you'll be thinking, hang on a minute.  What if I have A1 depends on A2, and A2 depends on A3, but  A3 depends back on A1, and I've got a circular  dependency, so I can't quite do it like that.  I'm going to have to track the current path that I've taken  through the graph as I'm recursing down into it.  And if I spot that the current location I'm at is already in  the path that I came to in the current stack of the  recursion, I'm going to raise a cycle error.  And that means that I need to catch the cycle errors when I  make the recursive call down into the algorithm that's  looking at each of the subcells.  So that is going to make sure that I can also catch  cycle errors.  Still OK, everyone?  Yep.  OK, fantastic.  Who knows a better way to do this?  It's rated tool?  Not sure about that.  You can do a thing that's I think called NetworkX, which  is a network analysis package.  And you can give NetworkX a one-way graph, and it will  just give you back a two-way graph in two  lines of code.  So a lot of wasted effort and hard work on the part of the  dirigible spreadsheet developers.  But it was character building.  We enjoy recursion.  It made us joyful, so fantastic.  All right, so far so good.  All right, great.  I've now got a little thing that can go, OK, b3, a1, a2, a3.  Now, what if I wanted to go into my spreadsheet and  actually start having some proper Pythonic fun?  So if I wanted to define some custom functions and now start  using more Python in my actual spreadsheet.  So if I wanted to go def foo of, like, say, x, and we're  going to return x plus 42.  All right, great.  It'd be lovely to be able to use this function foo inside  of our, what should we call it, spreadsheet, right?  Am I going to do foo of 3?  Do we think that could work?  Yes!  All right, how are we going to get that work to work?  We've now got two sets of user inputs.  We've got all the values and all the formulas they've put  into the spreadsheet.  And we've got also some custom user code that they've put  onto the right-hand side.  So what we're going to need to do then is we've got two  sets of evaluations.  First, we need to evaluate the user code.  And second, we need to evaluate each of the cells.  And the way we're going to do that is we're going to start  isolating our eval calls from the global context, which is  probably something we should have done ages ago.  And we're going to call eval the user code.  And that's going to populate the foo function  into this context.  So a context is just a dictionary.  All of Python's namespaces are just dictionaries.  Namespaces aren't they great?  And then I'm going to pass that same context to the eval  context of each one of my cells.  Hooray, I've now got custom functions.  But I am sure I hear you ask, what if we want to write a  function that actually can access things that are  already in the spreadsheet?  So supposing rather than having a function foo, I'm going  to have a function that say, have I got it over here, is  going to say sum everything in row A. And I'm going to want  it to say, OK, well, look at things that are already in  the spreadsheet.  So I can't evaluate my user code before, say, I've already  done a cycle of loading some of the constants in the  spreadsheet.  So I can always load the constants if I know, if  something is a constant, I don't need to evaluate it.  So I could evaluate, before I evaluate each of the cells, I  can evaluate some of these functions.  And then I'm going to have something like this.  All right, so I'm going to say, load all the constants,  then eval my user code.  So the user code has access to the constant.  So I can have a function that looks at what's already  constant in the spreadsheet.  And then evaluate the formula.  But as I'm sure you're wondering, what if I wanted to  write a custom function that can access the results of  evaluating the cells?  Well, then I'm going to have to say, maybe I'm going to let  the user input two types of user code.  One to be run after we load the constants, but before we  evaluate the formulae.  And one to be run after we evaluate the formulae.  And sure enough, that is what one can do.  And you're going to do something a little bit like this.  So you've now got a load constants function.  You've got an eval of the user code pre-formular  evaluation.  You evaluate the formulae.  And you eval the user code post-formular evaluation.  Who is still with me?  Hands up.  OK, almost everyone.  So that's fair enough.  So here's the realness.  So that would mean then I would have to put two little code  panels over onto the right-hand side.  And hopefully, this is the bit of the presentation where  something slightly magical happens.  If we have a look at this bit of code here, where we're  doing, OK, let's build a context.  We add the worksheet to it.  We call this load constants function.  We call the evaluate formulae function.  And then we've got this user code that's kind of before and  afterwards.  What if instead you had a thing?  Oh, incidentally, you can now, if you put things into the  pre-formular evaluation functions, you can actually  put formulae into the spreadsheet from the user code  panel before the formulae evaluated.  This is a certain.  I mean, this amazing Python spreadsheet is a surefire set  of guns pointed at both feet at the same time.  It's brilliant.  OK.  So what about, this is what we're doing right now.  And then what the user actually sees when they log in to a  dirajor spreadsheet, and then I'll start a brand new one for  you, is they see this.  The user code panel is pre-populated with a  function called load constants, which you're noticing is  exactly the same name as the function that I used in the  actual evaluation of the spreadsheet.  And it's got a function called evaluate formulae, which you  might remember was the same name as the function we're  going to use in the real evaluation.  And so here what I'm going to do is I'm basically going to  turn the whole spreadsheet on its head and say that the  spreadsheet is the user code.  And I have my load constants evaluate formulae in my  calculate function.  I take my context.  I put the worksheet into it.  I put the load constants function into it.  I put the evaluate formulae in context, which takes the  worksheet and carries it into my normal evaluate formulae  function.  And then all I do is exec the user code.  And it's just your user code panel that loads the  constants and evaluates the formulae and all of that.  And that means that you can do totally crazy things in your  user code, like you can evaluate formulae multiple  times.  Or you can put nested recursive calls to the spreadsheet  itself.  You can make spreadsheets that call other spreadsheets.  You can populate formulas done programmatically.  All of that sort of fun.  The spreadsheet is the user code.  The user code is the spreadsheet.  This is the most pythonic spreadsheet available.  Am I telling you that you should use this spreadsheet?  Absolutely not.  If you want to work with spreadsheet type data in  Python, just use an ipython notebook and pandas.  This is just a little bit of fun that I thought you might  be interested in.  The dirigible source code is all available on our GitHub at  github.pythonanywhere.  forwardslash dirigible spreadsheet.  If you fancy taking a look at it, I've now given you a tour  about it.  And hands up, please, if you found that easier to  understand than you had thought it was going to be to  understand how a spreadsheet works.  Wait, no.  Hands up if you now understand how a spreadsheet works.  That is like a 90%, I'd say.  Hooray.  And who thought that was easier than they thought it was  going to be before they arrived into the room that they  thought that they thought that the spreadsheet would be  evaluated for what it was going to be?  Irish just held up a sign saying, are you kidding?  So hands up if you thought that was easier and simpler than  ever.  One person.  All right, that's good enough.  No, no, no.  Please, hands up if you genuinely thought it.  And if you thought that was really confusing, there you  go.  Hands up if you think I should never do this talk again.  All right.  So by implication, that's two people putting their hands up  incidentally, any program working groups that are considering  talk submissions for future conferences, two people put  their hand up, which means about 200 people in here think I  should do the talk again.  Thank you very much, everyone.  Good night.  We have four minutes for questions.  Hooray.  Hi.  Did you think about security?  Yes, we did.  So obviously that was one of the major things we were giving  this out to random strangers on the internet saying, hey,  will you eval your Python code on our servers for fun?  So yes, we have a sort of sandboxing model that  allows make sure that each of the users can only access a  restricted part of the file system and basically the whole  containerization story that you've heard to death at this  conference already, no doubt.  That's the story.  I have stripped all that code out of the dirigible that's  published online, although you can find it in the history if  you're interested, because it's easier to demo and easier to  understand without the security stuff.  But yes, you can.  Good question.  OK, maybe one more quick one?  Thank you for the talk.  It's more common than a question.  Well, to be serious, when I look at my colleagues work, and  they work a lot of time with Excel, I think it could be  some kind of killer application or serious, because we can work  with pundas and do it at this level.  But during our work, often people really want to change  single cells, among 1,000 cells, because of some special  thing they just heard or whatever.  And if this is easily trackable and expandable with Python  functions, I think it has potential for really serious  or useful tools.  No, you're right.  I mean, the spreadsheet is a wonderful tool.  I wrote a thesis about it for my masters.  And that whole instinctive two-dimensional, I can see the  numbers, I can change them, I can visually represent the  relationships.  It's all wonderful stuff.  But it turns out nobody wants to use a spreadsheet other than  Excel.  And so as soon as you try and build a competitor Excel,  everyone's like, oh, I want every single shortcut key that  Excel has to also work in yours.  OK?  And that's quite a lot of work, catching up with 20 years  in Microsoft.  So however, any iPython developers, iPython  notebook developers, who want to integrate some sort of  spreadsheet component, would be happy to point you around the  Dirigible Cobase and see if there's anything in there  that you can use.  There you go.  What time do we have to finish, Iris?  Two minutes.  Two minutes.  Hooray!  That's time for another question.  Here you go.  Again, hooray!  That's someone really engaged.  Everyone else is like, how long before we can get?  Maybe a weird question for a Python conference, but did you  think about implementing this in JavaScript?  Uh...  So the straight answer to that is no.  But I'm thinking about it now.  Yeah, so one of the interesting things about it is that you run things on the server.  So remember when we executed that, we looped around all the leaves in the spreadsheet  and we had a sort of queue of them.  It's very easy to start parallelizing that.  And then you can do things like recalculate your spreadsheet across a cluster of machines.  Because once you've got a leaf node, that's totally independent from all the others.  So we were thinking maybe massive parallelization would be an interesting  market spot to be in. You can't really do that with JavaScript.  Is that the right sort of answer, Charles?  Yeah, yeah.  And no JS didn't exist back then.  No, but you were thinking maybe you'd have the whole thing on the browser.  Well, mostly to run it all locally.  Yeah, all in the browser.  I'm sure you could rerun it.  All Python to JavaScript is pretty easy to rewrite.  Knock yourself out.  Exactly zero minutes.  Let's rush to the lacking talk to see more Harry's performance.  Yeah, I do have another job.  Thanks very much for coming, guys. There you go.
Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_0_215.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_216_299.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_300_1175.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_1176_3551.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_3552_5411.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_5412_6323.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_6324_7475.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_7476_9239.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_9240_9383.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_9384_12863.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_12864_13055.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_13056_19439.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_19440_20843.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_20844_24083.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_24084_24251.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_24252_25319.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_25320_29879.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_29880_30563.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_30564_31151.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_31152_32423.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_32424_33131.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_33132_33635.png Harry_Percival_-_How_to_build_a_spreadsheet_with_Python_33636_35225.png