Chapter 8: Advanced Automation - Creating Pivot Tables

You've learned how to create beautifully formatted reports with charts, but one of Excel's most powerful features for data analysis is the Pivot Table. Pivot tables allow you to summarize large amounts of data into a compact, insightful table, making it easy to see patterns and totals.

In this chapter, we will take our automation to the next level by learning how to create a pivot table programmatically.

The Goal

Our goal is to take the sales data from the report we generated in Chapter 7 and create a pivot table that summarizes the total revenue for each product category. This will give us a high-level view of our sales performance.

A Powerful New Tool: The pandas Library

While openpyxl is excellent for reading, writing, and styling Excel files, it doesn't have a direct way to create pivot tables. For this task, we will introduce a new, incredibly powerful library called pandas.

pandas is the most popular library in the Python world for data manipulation and analysis. It's the perfect tool for this job. Before we start, you'll need to install it. Open your terminal or command prompt and run:

pip install pandas

You'll also need to install the engine that pandas uses to interact with Excel files, which is openpyxl (already installed) or another one called XlsxWriter. It's good practice to have XlsxWriter as well.

pip install XlsxWriter

The Script: create_pivot_table.py

We will use the monthly_sales_report.xlsx file as our data source. Create a new Python script named create_pivot_table.py.

The process will be:

1. Use pandas to read the data from the "Sales Data" sheet into a special structure called a DataFrame.

2. Use the pivot_table function in pandas to summarize the data.

3. Write this new pivot table to a new sheet in our existing report file.

Here is the complete script:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font

# The Excel file we will be working with
excel_file = 'monthly_sales_report.xlsx'

# 1. Read the "Sales Data" sheet into a pandas DataFrame
# A DataFrame is like a table in memory
df = pd.read_excel(excel_file, sheet_name='Sales Data')

print("Step 1: Sales data loaded into a pandas DataFrame.")

# 2. Create the pivot table
# We want to see the sum of 'Total Revenue' for each 'Category'
pivot_table = df.pivot_table(index='Category',
                             values='Total Revenue',
                             aggfunc='sum')

# Let's format the revenue to look like currency for better presentation
pivot_table['Total Revenue'] = pivot_table['Total Revenue'].map('${:,.2f}'.format)

print("Step 2: Pivot table created successfully.")

# 3. Write the pivot table to a new sheet in the same Excel file
# We use the 'openpyxl' engine to modify an existing file
with pd.ExcelWriter(excel_file, engine='openpyxl', mode='a') as writer:
    pivot_table.to_excel(writer, sheet_name='Sales Summary')

print("Step 3: Pivot table written to a new 'Sales Summary' sheet.")

# --- Optional: Style the new sheet using openpyxl ---
wb = load_workbook(excel_file)
ws = wb['Sales Summary']

# Make the headers bold
for cell in ws[1]:
    cell.font = Font(bold=True)

# Adjust column widths
ws.column_dimensions['A'].width = 20
ws.column_dimensions['B'].width = 20

wb.save(excel_file)
print("Formatting applied to the summary sheet.")
print("Automation complete! Check 'monthly_sales_report.xlsx'.")

Run the script. When it's finished, open your monthly_sales_report.xlsx file. You will find a new sheet named "Sales Summary" containing a perfectly summarized pivot table showing the total revenue for each category.

What You've Accomplished

You have now stepped into the world of advanced data analysis with Python. You've learned how to integrate the pandas library into your workflow to perform complex data aggregations like pivot tables. This combination of pandas for data crunching and openpyxl for reporting and styling is the standard for professional-level Excel automation.