Data Science Jumpstart with 10 Projects Transcripts
Chapter: Project 2: Excel Integration with Adult Income Data
Lecture: Writing and Formatting Excel Sheets in Pandas with to_excel and XlsxWriter add_format
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Okay, let's explore how to write Excel. First of all, you're going to want to make sure you have this xlsx-writer library installed.
0:09
Again, Pandas is going to leverage that under the covers, but it won't install it by default. And then I've just got some code here
0:17
to show various features of writing this. So I'm going to say, let's make an Excel writer. And then I'm going to call toExcel.
0:27
I'm actually calling toExcel twice. The first one I'm saying onSheet1, just put all the data. Next one's saying onSheetLT30.
0:34
I'm going to query everything that has an age of less than 30. And then after that, I'm going to say, okay, let's get the workbook from this,
0:42
which represents basically the Excel file. I'm going to change the format to the workbook down below. I also want Sheet1,
0:52
and I'm going to change Sheet1 down below. You can see that I'm looping over my columns, and on my worksheet, Sheet1,
1:01
I'm changing the format from those column headers. And then also for my workbook, I'm saying on all of the cells, set TextWrap to true.
1:13
And I'm also saying for worksheet1, set AutoFit. So make those columns based on the length of that. And then we're going to close our file there.
1:24
So a little bit more code to do that. Let's open this up and see what it looks like after we do that. Okay, here's our file.
1:32
You can see in Sheet1, we have highlighted those column headers with our code to loop over those. And you can see we also made this LT30.
1:41
All these ages in here should be less than 30. And if we look on the left-hand side, that age column looks like that is the case.