Move from Excel to Python with Pandas Transcripts
Chapter: Data I/O (input and output)
Lecture: Demo: Simple Excel saves
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Now I will walk through how to save an Excel file, have created the notebook and included in my standard imports at the top,
0:08
as well as to finding the source file, which we mark through in the previous videos and the report file that we're going to
0:14
create. So now let's read in the sales data from the source file. I've read in the data from the sales data us worksheet in the file,
0:25
and I'm only using columns a through G. This shows the sales data in our spreadsheet,
0:31
and now I'm gonna build a couple summary reports that we can include in our output Excel file. So first I'm gonna build a sale summary.
0:50
We used our group by the group by company and product into some how much each customer spent. So this is a very simple report.
0:58
This tells us by customer and product how much they purchased, which is a really nice summary view of the data. We could also look at average sales,
1:17
and this is definitely a higher level summary of the average sale and the total amount that we've sold. And I don't like having this in a in here,
1:26
so I'm going to now we have a good summary table of all of our sales And now let's build a pivot table of product sales.
1:36
So we'll call this one product summary. This is another good summary of our product information so we can see how much we
1:48
spend per product what the average transaction was per product, as well as how many transactions we had for each product.
1:55
Very useful summary in a very short amount of code. Now, if we want to save one of these data frames to Excel file,
2:02
it's fairly simple. And now that data frame has been written to the report file Now I've opened up that file.
2:13
He can see that the data looks very similar to the original data frame, which is useful, but we would want to be able to build something a little
2:21
more powerful where we include information on multiple sheets and maybe different locations on the worksheet