Python & Spreadsheets: 2017 Edition by Kojo Idrissa

Speaker 1: So I am here , I am your presenter, Kojo Adriesa. Here are my slides. As you can see, I'm very fancy and I've made very fancy elaborate slides. with white and black text. Because as a man who talks about spreadsheets, of course I'm very concerned about fancy things. So uh the talk here, Python and Spreadsheet, State of the Union, August 2017. Uh it's called that for a reason, and just for informational purposes, my name is there. My Twitter handle is also on on every slide. So if you have questions you want to tweet. to me later then that's a thing you can do. I'm usually pretty available. So this is called State of the Union August 27 because I gave this talk initially in 2013.

Speaker 1: uh or version of it in 2013 when I first started using the library I'll be talking about. So right now I'm a QA specialist for a startup in Houston called Decisio Health. I used to be an accountant and uh then I got an MBA, then I ran away to China and was a college instructor there for a few years. And I also taught here in the US. The sl all the the slides and the iPython notebook I'm using for this talk will be available at the link there at my GitHub, uh DjangoCon 2017. It's not available at the moment, but it will be soon. Note to self when I turn to look at to look at a screen, turn my body and not my head, because if I just turn my head, the volume goes down. See that's not good. This is what happens when you you know practice this talk a few times, you realize these finer details.

Speaker 1: So basic outline of What the talk's gonna be I'm gonna talk about how I got here, sort of my secret origin story. Um demonstrate some of the fundamentals of some of some of the fundamental data types that OpenPy excel gives you And that's because there are a few things there that are a little non-obvious, but then once they're explained, they seem you recognize how helpful they are. Then we'll take a do a basic demo of some basic things you can do with Python and spreadsheets. And then we'll look at some of the problems that you'll run into. Trying to use spreadsheets with code. There are certain things that are there's some situations where using a spreadsheet with code is straightforward and simple, but then there are a lot of situations where it's not, and so I just want to highlight some of those again the the pain of my life as a recovering accountant can hopefully

Speaker 1: benefit you at some point. So da da da secret origin of Kojo. H to be a professional spreadsheet fighter, which is also what's known as an accountant. Lots of spreadsheets all the time. You'd be terrified by how many large organizations are running spreadsheets. But I'd always had an interest in code and learning to program, but I never really needed to as someone who wasn't a professional programmer. So I decided in late 2012 to get more serious about teaching myself to code and becoming a professional developer. And I I went pro. I got my first development job in December of 2015. So that's sort of how I got to this point. And so my role in the Python community is such that I've always had an interest in trying to make some sort of a contribution to the community. But as someone who didn't come from a traditional

Speaker 1: CS background or a coding background, I knew I wasn't going to start just making making contributions by writing awesome code on day one or even early on. So I thought well I can you know I got decent personal skills and interpersonal skills so I can maybe help grow the community. And so I thought one of the best ways to grow the Python community was not by converting people who used other languages into Python developers, but by taking people who weren't developers and bringing them into the Python community by showing them how Python can benefit. them. And for one reason, if if you're already developing say in Java or C<unk> or what have you, you already have your built-in biases. But at the same time, there are more people who aren't programmers than there are people who are programmers who can work So it's just a bigger growth factor.

Speaker 1: Also wanting to look at some solutions that are not obvious to people who aren't developers. So there are certain things, if you're a developer, you think about things in a certain way. When I first gave this talk, one of the so one of the solutions to this problem that someone suggested was well just put it in a in a database. Well if you already know SQL and you have access to databases you you wouldn't have the problems that I'm discussing in this talk. So trying to come up with some solutions that are useful to people who aren't already developers. So who's the talk for? We've got two sort of two sets of people the talk is geared at. And again, I'm trying, well not again, but if you've read the description, you might have seen that. I'm trying to make this talk as beginner-friendly as possible And so we got two general categories of people.

Speaker 1: One, people who are using spreadsheets on a regular basis but want to sort of step their game up, want to be able to do some different things. with spreadsheets, in most cases Excel. So they want to be able to do some things outside of the norm with the spreadsheet. And then the next set of people are people who are already Python developers, but they keep being given spreadsheets. keep being confronted with spreadsheets and it's like, well what do I do with these? Like there's got to be some better way to handle these. So hopefully this will benefit both sets of people. So the code here is not going to be very advanced. Again, partially because I wanted this talk to be as beginner-friendly as possible, but also because the really interesting things code-wise are going to be based on your specific application. So pulling data out of the spreadsheet, writing data back to the spreadsheet, those things are fairly straightforward.

Speaker 1: There are some other things that the library will help you do, but the really interesting thing. are going to be based around your specific use case. So I can't, I don't know what that is, and I can't demonstrate that code. So I'm showing fairly basic code for a couple of basic applications to give you some ideas of what can be done. So now it's time for demo. This demo sponsored by Jupyter Notebooks. Jupyter Notebooks, purveyor of fine intranet notebooks since like five years ago. So not that long ago, but still very helpful. So step one, we've got the Jupyter. Is this readable to people in the back? Yes? Okay. Uh uh I got I gotta kind of. I gotta

Speaker 1: Is that is that too big? Is that yes? Alright. Um I think I think I've gotten rid of all those, yeah. So the first thing you have to do is you have to know your data. And for that, we'll take a look at the actual spreadsheet file. It's almost impossible to try to to work with code programmatically or work with a spreadsheet programmatically if you don't know what's in the spreadsheet. And so here what we've got for my simple example and we try to that it won't work You pre I don't I don't want help. I want you to do what I say. I want you to do what I mean, not what I say. These spreadsheets are trouble already. There we go. I'm gonna blame my own mistakes on the spreadsheet. That's how that goes.

Speaker 1: So what we have here is we have some data. We have what is uh some simulated timesheet data. So what we have here, we have an employee number, and the employee number identifies each employee. So that's the same employee, this is a different employee. An employee number, the cost center that that employee works for. So there's sort of their specific work group. We also have their division within the company. So your company is broken into multiple different divisions. And so each division will have multiple cost centers in it Then who is that employee's manager? And of course these are you know fake simulated names. And then the date that they worked. So again this is timesheet data. So we're seeing here on this date, this employee with this name and this employee number. Can you all see the the cell moving clear

Speaker 1: fairly well? Alright. So this employee worked one hour on a project. And so this is simplified data, just for the purposes. Um If you work in a professional services firm, so I've worked in accounting firms and in for engineering companies and things of that nature, one of the things that they look at is what's called utilization. So how much time do you spend working on an actual billable project versus what's called overhead time where you're at work and you're doing things but you're not working on a specific client project that they can bill for. For this example, everybody's working on a billable project. And so we're not doing that level of analysis. But that's what we've got here. So we've got basic timesheet data. So this employee worked one hour on a project and then they worked three hours on another project the next day, so on and so forth.

Speaker 1: So I've got about 10,000, no, not 10,000, a thousand rows of data here Uh examples that I've worked on before had 10, 15, 20,000 rows of data. So this is the basic data. And again, if you're gonna be, it's just like anything else in programming. If you're going to be writing code to manipulate data, you need to be familiar with that data. So here's the basic data we've got. And then we start with some basics, reading a file and some getting some basic data types. So the library that I'm using here is called OpenPyXL. OpenPyXL is a library, a Python library, that lets you read from and write to XLSX files And for those not familiar, the the older XLS file format is what uh what Microsoft Word used

Speaker 1: up until I believe And uh after that they switched to the XLSX format, and that that extra X means that it's it's uh based around XML and it is compatible with the open document uh organization 's formats. And so at the time in 2013 when I started looking at this OpenPyXL was one of the few libraries that would actually work with that file format. That's what I had to deal with. So again, very straightforward stuff here. Importing OpenPyXL. By show of hands, how many of you would consider yourselves sort of beginner or novice programmers sort of early in early stages? Okay? And so how many of you might know some people who would be beginner or novice.

Speaker 1: And not you yourselves of course, but you might know some other people who might be beginner or novice programmers and might maybe benefit from hearing things described in that way. Okay, so yeah, so please be sure to tell a friend and share this with them. So yeah, I'm trying to step through this in a fairly straightforward way, again, so that you could explain it to someone who's not an experienced developer and they could actually get some benefit from this. So you're importing OpenPyXL, and then from OpenPy Excel, importing workbook. And this gets you a workbook object and we'll talk about that distinction between workbooks and worksheets and that sort of thing later. And then this line of code, workbook equals openpyxl. load workbook, this load workbook function, this is the name of the file that we're using. So the spreadsheet we that we just saw, this spreadsheet, this is

Speaker 1: PyXL underscore demo underscore DjangoCon. xlsx. Up that should be. That's wrong. That should be true. I was playing I was playing with this earlier. Um the distinction here is this data only equals true is for situations where you have a formula in a spreadsheet, which lots of spreadsheets have formulas in them, if that data only is equal to true, what that's going to do is that's going to give you the result of the formula in in all cases at the point to bring you back the formula itself. So that's that and let me do this. I'm just gonna run this Just to make sure I've got everything behaving as it should All right. And so this is all sort of fairly straightforward Python stuff except for this data only equals true.

Speaker 1: This is something specific to OpenPy Excel. So now we want to talk about this distinction between workbooks versus worksheets or spreadsheets and tabs Most people tend to use the terms sort of interchangeably. They'll say, you know, send me a spreadsheet. Oh no, I want that spreadsheet in the spreadsheet. So in In accounting nerd talk, a spreadsheet is a workbook. So Excel describes them as workbooks. A workbook is the actual file itself. So what we see here is part of a workbook. The individual tabs here I have clean data which is what we're using and then K-pop draft roster which is a whole different thing. K-pop is one of my things. So each of these tabs is known as a worksheet. And the multiple worksheets make up a workbook

Speaker 1: And so that is important because when you're using , so most people will say like a spreadsheet or a tab in a spreadsheet. But open but Excel defines them as a workbook, which is a collection of individual worksheets. And that's important because when you're accessing the data, you need need to tell the you you've got a workbook file so you want to open that file that's what we're opening up here in this cell but then you need to know which worksheet you need to get out of that workbook and there are different objects that have different properties to them And so here we've got this WB is the workbook that we've opened, which is the entire thing. And For those of you who are again newer to Python or who have friends who are newer, the the DIR function, the directory function in Python is helpful because it will give you a list of the attributes.

Speaker 1: that are available for a particular object. Now for a lot of basic Python constructs, if you're already familiar with these, maybe you don't care. But if you are using a new library like this, like OpenPy Excel, it has some different data types. That you're not going to be familiar with because you haven't seen them before. So using the DIR function on them is helpful. And so we see a lot of the different attributes. And we'll notice here a couple uh sheets. And so this will Show you the sheets that are available. Let's see. Copy worksheets that you can copy a specific worksheet. Create sheet, which we'll see a little bit later. get sheet by name so you can go to a you you can grab a specific worksheet out of a workbook or if you don't know which sheets are available get sheet name

Speaker 1: And so that will tell you, okay, what sheets are actually here in this workbook. So we'll look at those. Keep skipping around. So that's that. And then so I'm here, I've got the workbook, and I print workbook. sheet names because I want to know what sheet names are there. Now you will notice Is it high enough? Yeah, I'm trying to make sure it's high enough on the screen so people can see it. You'll notice the workbook. sheet names. When we looked at the at the actual spreadsheet itself, we saw two worksheets, clean data and kpop draft roster. But when I print the workbook sheet names, I get three Japan spending, clean data, and K-pop draft roster. So the Japan spending worksheet is actually hidden

Speaker 1: When I as I was working on this talk initially, I was in Japan and I was trying to figure out where all my money had gone. And so I started, I was like, well, I shouldn't have spent that much money. So show sheet They're paying spending. So that's a hidden worksheet. I point that up because if if you're in a situation where you're trying to hide something from someone and you want to hide a sheet, well, if they just look at it, they won't see it. But if they get a list of the sheet names, it's still visible there So those are the worksheets we have available to us. The one we want is the clean data worksheet. And so here I'm going to create this variable called demo worksheet. And I want to get sheet by name and then you pass it the worksheet name. And so now this WB is a workbook object. And actually, let me do that here.

Speaker 1: The live coding portion. No says WB, it's a workbook object. Again, which is not something that natively exists in Python. So openpyxl. workwork. workbook dot workbook. So it's a workbook object and it has those different attributes that we saw before. That's why I had the DIR. So I ran the directory function on it. And again, here I've created this demo worksheet, which pulls a specific worksheet. And we see that it is a worksheet object that has a specific set of attributes that we can see with the directory function And so those are the different things you can do with it. And the two we're gonna focus on most here are, well say we've got

Speaker 1: Cell, so you can get a particular cell from worksheet, but we're gonna look at I just passed it columns and rows. So each worksheet, again, depending on how familiar is with the worksheet columns go up and down. rows go side to side. In this case each row represents a particular record like similar to in a relational database. So those are the different attributes we've got for The worksheet object and these workbook and worksheet objects and the cell objects we'll see later. There are a lot of different attributes, a lot of different options. There's clearly not time to go through all of them, but I'm just gonna try to go over some of the highlights So we've got this worksheet now, this demo worksheet. And so what we want is we want the data out of this, so we're going to grab it by rows.

Speaker 1: And so demo worksheet. So what you so what are the rows here? And you'll notice it returns this generator object. So generator object worksheet. sells by row. And so what this does is It gives us a generator object. Instead of reading every row out of the spreadsheet, it creates a generator object. And if you are not familiar with generators, a simple A simpler way to think about them is that a generator object is something that has a a collection of items, but instead of giving you all those items at once, It will give them to you one at a time, and this helps to save memory. So instead of having all 10,000 rows of this spreadsheet, you have this generator object that gives you one row at a time as needed.

Speaker 1: And that helps to save memory. But I point that out because if you say, oh, okay, well show me the rows, you're not going to just get plain rows of plain text or things of that nature. You're going to get this generator object that's going to give you a row at a time. Um and so that from that before Oh maybe I didn't, okay. So what you get is when you print out this generator object again, so this Demo. worksheet. So for row in demo. worksheet. row, so I I want to see what these rows are like. And you'll notice that each row, I've I've also got it printing the type. And so you've got a generator object, but what it returns, each object it returns is a tuple

Speaker 1: of cells. And so you've got this cell clean data A1. Clean data A2, so cell A1, cell B1, cell C1 of this clean data worksheet. And so A1, B1, C1, that's going to correspond to the first row. And then the next one goes to the second row and so on and so forth. And so you have a tuple. So this generator object is giving you tuples. And each element in that tuple is a cell, which is again, so tuple standard Python data construct. A cell is not. A cell is another open PyXL data construct. And we'll see why that's important and useful in just a moment. So we move on, we'll take a look at cells and just want to show you some of the differences

Speaker 1: about cells. So for cell in next , demo worksheet. So again, demo worksheet. This is a generator object. If you have a generator object, like I said, it gives you one new item at a time. To get one new item at a time, you can use this next function. And so that's just pull you know pulling one item out of that generator at a time. And so I'm having it print out just cell and then the cell name. So here cell. colum because it tells me what column the cell is in, cell dot row tells me what row the cell is in. using some string formatting here to make that look sort of nice. And then I'm printing the cell itself, which gives me information about the cell, and then the type of cell, just to demonstrate that it's this different data, it's this different uh data type that's not native to Python, it's provided by the library.

Speaker 1: So cell A1, cell A1 from Clean Data, it's of the type cell and The value in it, that last line, is going to show you what's actually in that cell. So like when you look at a spreadsheet, that's what you're actually after. You're after the cell. data And so we see A1, the value is employee num, B1, its cost center, C1, its division. So this row one, these are the headers. And so on and so forth until we move on. So I just popped off that top row just to demonstrate that. And so we've got these cell values and types, and now OpenPyXL is also smart enough to uh try to take the data that's in a cell, the values that are in a cell, and convert them to the appropriate Python data type.

Speaker 1: So here we're looking at demo worksheet E1 dot value and so I also do this to demonstrate that instead of grabbing things just by rows you can go to a specific cell if you want to. So in this case we're going to this demo worksheet which is the clean data worksheet and we're grabbing Cell E1 specifically, and we're getting its value. And we're doing the same thing to cell E2. So if we take a look at the spreadsheet, cell E1 is date worked, E2 is that first date. And so we print these, and these two cells, 180 and 181, are basically the same thing, just shown differently. So that's the value, and then that's the type. So the value here is this date worth and it's a string. The value here is that first date.

Speaker 1: So it's showing it, it's displaying it here, but it's also showing you that it's a date time. date time object. And so Python knows that, hey, this is a date. And so that's useful. Um, so cell attributes, again, so why is there a cell attribute? Just like we had the workbook and the worksheet objects, we have these cell objects that have all these different attributes. And the primary reason for that is because when you're looking at a spreadsheet, because it's a cell object, when you're looking at a s a spreadsheet, there's a lot more going on than just what's in the cell, the cell itself, than just the value. their other attributes. So is it is it bold what sort of styling is going on, what other you know things are happening with the cell. And so these cell objects contain all those other attributes.

Speaker 1: And the one that we're going to use the most here is the the value attribute. So we'll be doing cell dot value to get the actual value of. That's the actual data we're wanting to work with. However, there's other data in the cell, so if you need to know if a cell is colored a certain way or or uses a certain type of font or something like that, you can grab that information and do things with it as well. But for our purposes we'll be focusing mostly on that. When you look at cell styles, did not do that. There's not enough time to really for me to do a demo on the self-styles, but there are all sorts of things you can do working with styles. OpenPyXL documentation is of course on Read the Docs. Thank you, Eric Hulcher , for making uh things like this available for us. So there's a lot of style information

Speaker 1: and while that might not seem like the most important things when you're dealing with data One, you might be in a situation where the spreadsheets that you are given are being styled in a certain way. So maybe a a number that's a loss is red and a number that's you know a profit is green or something like that. You can actually make use of that information So you can actually pull that out. And if you're needing to write your results to a spreadsheet, you can write them in that fashion as well. So making beautiful spreadsheets has been left as an exercise for the viewer. So I'll let you all do that on your own So example one, aggregating timesheet info, what we're basically want to do here is take this timesheet and instead of having these individual lines for each day what we want is we want to see okay how much time did each employee work in this month

Speaker 1: so this is June of 2017 so we want to see okay how much time did each employee work in this month so we want to aggregate that information A lot of the Python here is just it's not uh particularly impressive, but I wanted to to point out the spots where we use regular Python mixed with things that are specific to OpenPy Excel. So here You can use a for loop versus a set comprehension. I used a set comprehension because I wanted to be able to I wanted a set of the employee IDs. I didn't want every occurrence of an employee ID because you could have multiple. So I use the set comprehension here. Trey Hunter, who is here at the conference, does an exceptional talk on what he calls comprehensible comprehension.

Speaker 1: And so you can take a you can if you see Trey, ask him and tell him I told you that. Is Trey here? No, Trey? He's probably in that room. Um so yeah, if you see Trey, ask him about comprehensions and tell him that Kojo told you to ask him. Um so he'll do a better job of explaining comprehensions. than I will. But what you would do with an SH4 loop in a lot of cases you can do with a comprehension. So here I'm creating this set comprehension of employee IDs just so I have a list of the unique employee IDs. And that's what this looks like here. And then I'm using that set that I called uh employee IDs1. And I'm using that to create a dictionary that takes The hours, I'm using list comprehensions here, list and set comprehensions here. I want all the hours for that employee.

Speaker 1: I want A set comprehension of the cost center, each employee should only work in one cost center. So here I've got a set comprehension of one cost center, a set comprehension of divisions, again, each employee should only work for one division Each employee should only have one manager. So this is again the part about knowing your data. So I got set copy hunters that are building those things. You'll notice here I got row six dot value for row and demo worksheet. rows. So in this case Demo worksheet. ros is again that that generator object that's giving us a row to time. So here I'm saying I want row index six for hours and if we take a look back at our spreadsheet we

Speaker 1: see one two three four five six seven because Spreadsheets, so and so and this is this is where it gets a little tricky, we'll see this later. Python indexes from zero. So if you have a list of four items, Python will count them as zero, one, two, or three, spreadsheets index from one And so we'll see in the code a little bit later where you have to make that adjustment. So here, index 0, 1, 2, 3, 4, well, I can't Use my key 0, 1, 2, 3, 4, 5, 6. So I'm getting the hours there. But I'm using that row dot value, row 6, row index 6, which is going to be a cell object. in the row, then dot value, so I'm pulling the value out of that. And that's what's getting me my hours. Doing the same thing to get the cost center, the division, and the manager.

Speaker 1: I do QA where I work now and I also used to be an auditor so I tend to try to want to test things and things of that nature. So I've got a little assertion here because I know there should only be one cost center one division and one manager for employee so I just do that there and then I build this employee aggregate object And again, so a lot of this is regular Python here. I've got some of the specifics to OpenPy Excel. And then I print this employee aggregate object, just a pretty printed it's a dictionary just so it's clear. So what you end up with is an employee ID as the key and then the cost center, the division, and then the number of hours for that employee And so this lets me take

Speaker 1: this spreadsheet, turn it into a dictionary, which could also be used as a JSON object. And uh I know from some filtering of the spreadsheet, some manual filtering of the spreadsheet I that I should have 49 employees. And so that's what's happening here Now, here we've looked at reading data from a spreadsheet and then processing it and turning it into something in Python. The next thing becomes, what if you've already got a Python program that's running? and you want those results to be written to a spreadsheet. Well you can do the same thing with OpenPyXL. So first you need to create a workbook and I've given it the very creative name of Output Book. And so I've created this new workbook object. called output book and then you need to create well let's see

Speaker 1: you can create a specific sheet here I'm creating a sheet called output sheet So outputbook. createSheet, which is a this create sheet is a method that belongs to the workbook object. And I'm giving it a name here, aggregate time, and I'm also giving it this argument is zero. This zero argument means it's going to be the zeroth item in the workbook. Otherwise by default when you create a workbook it will have like a sheet one as the first object. So here I'm saying make this the first item in the sheet that we see Uh and then when we look at output book, we see that it's this a workbook type object. Then I decide to build a header because I don't want to just write the raw data to the spreadsheet. I also want some sort of a header so the spreadsheet looks sort of organized when I give it to someone else so they can understand it.

Speaker 1: And so I'm just building a header here basically by just copying the values out of the demo worksheet. And so again I'm just accessing those cells directly And then I'm printing the header to make sure it's it's what I want, and so I've got that same header. And then for output data, I build this table, it's a it's a list of lists. and I move through this employee aggregate and I'm creating new row, I'm building these new rows. So I want the row with the employee, the cost center, the division, the manager, and their number of hours. But here it's going to be the number of hours that were aggregated. from the earlier dictionary that we saw. And then he now I'm assigning those values that are in this output data construct

Speaker 1: I'm writing them to the output sheet and I'm I've got a nested I've got nested for loops here Because I'm writing them by row and by cell. And here we see excuse me. We see this row index that I've got here. and the column number, but I've got to use plus one because the indexes that come from Python start with zero on a spreadsheet they start with one So that writes that stuff. And so here is the output data construct that I built. And so you see the first list is the header and the next list is the aggregate numbers for each employee. So this employee worked 160 hours in the month, so on and so forth. So

Speaker 1: there we go. And now I can save that. So outputbook. save and then I give it a file name. And so this is the file name. of uh the file that is being written to. And let me open that. All right. Open And so this is the result. And now this doesn't take a huge amount of time, but It 's a small amount of data. So there's this. So I've got this aggregate timesheet with those times and I can check the totals.

Speaker 1: So the total there is, well you can't see it at the bottom, it's 5,386 hours. And if I go back to the original spreadsheet I've got the same total 5,386 hours, which is here at the bottom, perhaps visible to people in the front row And again, this isn't the uh the most complicated of things, but it gives you an idea of Yeah if you have m a lot more spreadsheets to work with ten or a hundred or you have a lot more data. The last thing we do is I can take that object, that aggregate time object that I created, and I can write it out as a JSON file. And so then what I end up with

Speaker 1: is This where is my JF file? There we go. Uh some other thing And so I get that as a JSON file that I can then use to configure something else or to do other processing if I'd like. So the problems that you'll run into, so the reading of reading the data, writing the data, that sort of thing, not terribly complicated, the problems that you run into is that often a spreadsheet is gonna be to be used as a visual medium. And so s someone wants a spreadsheet to look nice. And so that spreadsheet might not make sense to code. If if the only spreadsheet you ever

Speaker 1: you ever get is one that looks like this. then you'll be fine because you've got fairly well structured data. But the reality is someone the boss wants the spreadsheet to look a certain way and so it's been laid out. They've tried to do desktop publishing with it or whatever and then you've got to sort of go through it. Um in those situations You might be able to access individual cells or you may be able to convince them to maybe change some of the formatting with the idea that hey we can speed this up by literally a hundred times So if you might have visual input or you might have a visual output requirement, that's where Stanley can help you. You can make new friends by helping teach a coworker how to automate some of their simple tasks with Python. Again, the Python here that I demonstrated wasn't terribly complicated. The comprehensions were probably the most complicated thing.

Speaker 1: And so you can teach them to read data in, do some things with it, and read write the data back out. That's what I've got. So I am transition on Twitter if you have questions or the slides and the code will be available in this GitHub repository very shortly. So hope you see a bottom.

Speaker 2: With the uh the Python CSV module, you're able to use DictReader and actually get named columns in and out. Does uh OpenPyXcel support that?

Speaker 1: I'm not sure if it supports the name columns. It will let you so I here I read in I read things in by rows, but you can also do the same thing by columns.

Speaker 2: Okay.

Speaker 1: So

Speaker 3: Uh you mentioned that uh uh visual spreadsheets would not be a good candidate for this sort of approach. Are there any types of uh actual data structures in spreadsheets that you that would not be good for uh programmatic analysis this way?

Speaker 1: Data structures in spreadsheets. Um let's see, so if you have a lot of computations being happening in macros And that's something I I've I had not worked with very much, but if you've got a lot of macro calculations going on, that might be a little tricky, but if you can You should be able to either grab either the data that's going into that macro or the results of those macro calculations. And so that's probably what you'd want. There's a whole different approach that involves being able to run Python inside of a spreadsheet. I might add that to this talk and update it later, but thus far I've focused on just the files themselves. Uh i

Speaker 4: is there any way to make pivot tables in it? I know you can do pandas, you could do a pivot table in pandas and write it statically to the Excel sheet, but is there any way?

Speaker 1: I believe there is.

Speaker 4: If you're looking to because it's in my mind all this stuff kind of gets rid of BBA

Speaker 1: So if you go to the OpenPyXL documentation, I believe there is a pivot table function that's there. Off the top of my head, I can't recall. I personally am sort of a I have sort of a love-hate relationship with pivot table.

Speaker 5: Um okay yeah uh just before I ask answer or ask my question. Uh I've worked a lot with poi and if you make a pivot table uh and then uh make it use a a range you can can actually use something like this to populate your pivot table. So you can sort of have a pivot table as a question for you is uh streaming like uh and and and uh Stability. So have you noticed any bugs or any stability problems and then uh when you're dealing with large amounts of data, is there any streaming interface that you're familiar with and any anything you comments on that?

Speaker 1: Uh not familiar with the streaming interface Um so I haven't used this recently with huge amounts of data. Um so uh I I couldn't really speak to that. I think the fact that it's not pulling in at time, I've seen large spreadsheets that have caused Excel itself to sort of slow down and run in slowly. And so I haven't run this with those same because those unfortunately were visually formatted. But I think the fact that this is creating this generator object and returning the data in small pieces at a time would help alleviate some of that issue, but I haven't actually had a chance to test it. I need to create a fake thing with a bunch of data and try that.

Speaker 6: Hi. Um so your your Excel was pretty nicely obviously formatted. Would it deal uh with cells or rows that are merged in front in in the middle of the sheets For example, a merge cell of Monday, days of the week and so on.

Speaker 1: It has some capacity to deal to deal with that, but again, that's sort of a knowing your data type of thing. I haven't specifically tried things with merge cells, but you can access individual cells. So that might be a situation where you might need to access an individual cell because I'm not sure how OpenPyXL sort of views that that. Visually, just like with the hidden worksheet, you can't see the hidden worksheet, but OpenPyXL can see it in all those two sheet names. So I'm not sure how OpenPyXL sees those because the merging is just a visual thing. It's not an actual data

Speaker 7: Have you ever used uh XLRD or XLWT and how does it compare to

Speaker 1: the So this is one of the one of the more common questions I get with this talk. Um I've used those a little bit, but when I started when I started using this library At the time, this is 2013, so those two libraries, XL uh, XLRD and XLWT, they wouldn't work with XLSX files. And so I think I believe now they do, but at the time they wouldn't. And so I played with them a little bit, but I was like, oh well I can't I I either have to take out every spreadsheet, convert it to an XLS file, and use this, or I can just use a a library that supports a native

Speaker 8: Thank you. I'm going to reveal uh reveal my ignorance real quick. But what tool were you using to run your Python in a browser and show us the output?

Speaker 1: That was Uh Jupyter Notebooks. So this this talk is sponsored by Jupyter Notebooks. Purveyor of fine. So a Jupyter Notebook.

Speaker 2: Alright, seeing none. Thank you, Codro.