Move from Excel to Python with Pandas Transcripts
Chapter: Data I/O (input and output)
Lecture: Demo: Multi-sheet Excel workbooks
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
We've used the simple to excel function in pandas to save a data frame as an excel file. If we want to doom or complicated Excel file creation,
0:10
we're going to need to use Excel SX writer, so you'll need to make sure you conduct install that before you continue with the rest
0:19
of this exercise. So when we save using XLs writer, we need to create a writer object first.
0:28
In this example, we tell it to use Excel s ex writer as our output
0:32
engine. And we choose that because it gives us the flexibility to do additional formatting
0:38
of the Excel file. So now that we've defined this writer object because we have to find the writer object now when we call Excel,
0:50
we pass it the writer object, and that allows us to do things like to find a sheet name, which will be useful. So in this case,
0:57
we're gonna have a sheet name called Sales Summary. Also, by using writer, we can specify start rows and columns,
1:09
so we're gonna have the average displayed on the same sheet name as the sale summary
1:14
But we'll start over in column four so that the data doesn't overlap. And here's an example of using a different sheet name,
1:23
so we would expect that our output is gonna have a single file with a sale summary and a product summary tab.
1:30
And when we're done, if we want to say that you need to call writers safe, I've opened up the file. As you can see, it's called TPS Report,
1:39
and we have one day to frame. Here we have our summary information starting in column E on the product summary tab.
1:47
We have the summary information for each of the products that was in the sales data
1:52
One of the things you will notice is that the formatting is not really nice on this. The columns, for instance, you want probably want to expand.
2:01
And then there's no number formatting, so we can do that as well.