Python for Excel

Chapter 7. Excel File Manipulation with pandas

After six chapters of intense introductions to tools, Python, and pandas, I will give you a break and start this chapter with a practical case study that allows you to put your newly acquired skills to good use: with just ten lines of pandas code, you will consolidate dozens of Excel files into an Excel report, ready to be sent to your managers. After the case study, I’ll give you a more in-depth introduction to the tools that pandas offers to work with Excel files: the read_excel function and the ExcelFile class for reading, and the to_excel method and the ExcelWriter class for writing Excel files. pandas does not rely on the Excel application to read and write Excel files, which means that all code samples in this chapter run everywhere Python runs, including Linux.

Case Study: Excel Reporting

This case study is inspired by a few real-world reporting projects I was involved in over the last few years. Even though the projects took place in completely different industries—including telecommunication, digital marketing, and finance—they were still remarkably similar: the starting point is usually a directory with Excel files that need to be processed into an Excel report—often on a monthly, weekly, or daily basis. In the companion repository, in the sales_data directory, you will find Excel files with fictitious sales transactions for a telecommunication provider selling different plans (Bronze, Silver, Gold) in a few stores throughout ...