Create and Customize Excel Pivot Tables Using Python

Source: Dev.to

Excel pivot tables are powerful tools for summarizing and analyzing large datasets, helping users quickly uncover trends and insights from raw data. However, creating and maintaining pivot tables manually can be time‑consuming and error‑prone, especially when reports need to be updated regularly.

Python offers an efficient way to automate this process. By generating pivot tables programmatically, you can ensure consistency, reduce manual effort, and improve the reliability of your reporting workflows.

In this article you’ll learn how to create and customize Excel pivot tables using Python. We’ll cover:

• Environment setup
• Data preparation
• Basic pivot‑table creation
• Common customizations (row/column fields, aggregation methods, report filters)

---

Setting Up Your Environment and Preparing Data

To begin our automation journey we need a reliable Python library for interacting with Excel files. For this tutorial we’ll use Spire.XLS for Python, a robust library designed for reading, writing, and manipulating Excel documents.

Install the library

pip install spire.xls

Create sample data and write it to an Excel sheet

from spire.xls import *

# 1. Sample data
headers = [
    "Region", "Product", "Salesperson",
    "Units Sold", "Revenue", "Date"
]

data = [
    ["East",  "A", "Alice",   10, 1000, "2023-01-15"],
    ["West",  "B", "Bob",     15, 1500, "2023-01-20"],
    ["East",  "A", "Alice",   12, 1200, "2023-02-10"],
    ["South", "C", "Charlie",  8,  800, "2023-02-25"],
    ["West",  "B", "Bob",     20, 2000, "2023-03-05"],
    ["North", "A", "Alice",   11, 1100, "2023-03-12"],
    ["East",  "C", "Charlie",  9,  900, "2023-04-01"],
    ["South", "B", "Bob",     14, 1400, "2023-04-18"],
    ["North", "C", "Alice",    7,  700, "2023-05-01"],
    ["West",  "A", "Charlie", 18, 1800, "2023-05-10"],
]

# 2. Initialize workbook and sheet
workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]
sheet.Name = "RawData"

# 3. Write headers
for col_index, header in enumerate(headers, start=1):
    sheet.Range[1, col_index].Text = header

# 4. Write data rows
for row_index, row in enumerate(data, start=2):
    for col_index, value in enumerate(row, start=1):
        sheet.Range[row_index, col_index].Value = str(value)

# 5. Auto‑fit columns
for col in range(1, len(headers) + 1):
    sheet.AutoFitColumn(col)

print("Sample data written to 'RawData' sheet.")

# Optional: Save the workbook
# workbook.SaveToFile("DataForPivot.xlsx", ExcelVersion.Version2016)

Preview of the generated sheet

---

The Core: Creating a Basic Pivot Table

# Continue from the previous script

# Determine the last row (header + data rows)
last_row = len(data) + 1

# Define the data range (A1:F{last_row})
data_range = sheet.Range[f"A1:F{last_row}"]

# Add a worksheet for the pivot table
pivot_sheet = workbook.Worksheets.Add("PivotReport")

# Create a pivot cache based on the data range
cache = workbook.PivotCaches.Add(data_range)

# Add the pivot table to the new sheet
pt = pivot_sheet.PivotTables.Add(
    "SalesPivot",          # Pivot table name
    pivot_sheet.Range["A1"],  # Start cell
    cache                 # Pivot cache
)

print("Basic pivot table structure created.")

At this point the workbook contains an empty pivot‑table shell. Opening the file in Excel will show a blank pivot area ready for field configuration.

---

Customizing Your Pivot Table for Deeper Insights

The real power of pivot tables lies in configuring their fields: rows, columns, data (aggregations), and report filters.

Adding Row Fields

# Add 'Region' and 'Product' as row fields
row_field_region = pt.PivotFields["Region"]
row_field_region.Axis = AxisTypes.Row

row_field_product = pt.PivotFields["Product"]
row_field_product.Axis = AxisTypes.Row

print("Row fields 'Region' and 'Product' added.")

Adding Column Fields

# Add 'Salesperson' as a column field
col_field_salesperson = pt.PivotFields["Salesperson"]
col_field_salesperson.Axis = AxisTypes.Column

print("Column field 'Salesperson' added.")

Adding Data Fields (Aggregations)

# Add 'Revenue' as a data field with SUM aggregation
data_field_revenue = pt.PivotFields["Revenue"]
data_field_revenue.Function = AggregateFunction.Sum
data_field_revenue.Name = "Total Revenue"

# Add 'Units Sold' as a data field with SUM aggregation
data_field_units = pt.PivotFields["Units Sold"]
data_field_units.Function = AggregateFunction.Sum
data_field_units.Name = "Total Units Sold"

print("Data fields added with SUM aggregation.")

Adding Report Filters

# Add 'Date' as a report filter
filter_field_date = pt.PivotFields["Date"]
filter_field_date.Axis = AxisTypes.Page

print("Report filter 'Date' added.")

Finalizing and Saving the Workbook

# Optional: Refresh the pivot table to apply changes
pt.RefreshData()
pt.Calculate()

# Save the workbook with the pivot table
workbook.SaveToFile("AutomatedPivotReport.xlsx", ExcelVersion.Version2016)

print("Workbook saved as 'AutomatedPivotReport.xlsx'.")

---

Adding Column Fields (Cross‑Tabulation)

# Continue from the previous script

# Add 'Salesperson' as a column field
col_field_salesperson = pt.PivotFields["Salesperson"]
col_field_salesperson.Axis = AxisTypes.Column   # Assign to column axis

print("Column field 'Salesperson' added.")

---

Defining Data Fields (Value Fields)

Data fields are where the actual calculations happen. You specify which field to aggregate and what aggregation function to use (e.g., Sum, Count, Average).

# Continue from the previous script

# Add 'Units Sold' as a data field with Sum aggregation
data_field_units = pt.PivotFields["Units Sold"]
pt.DataFields.Add(data_field_units, "Sum of Units Sold", SubtotalTypes.Sum)

# Add 'Revenue' as a data field with Sum aggregation
data_field_revenue = pt.PivotFields["Revenue"]
pt.DataFields.Add(data_field_revenue, "Sum of Revenue", SubtotalTypes.Sum)

# You can also add the same field with a different aggregation, e.g., Average Revenue
pt.DataFields.Add(data_field_revenue, "Average Revenue", SubtotalTypes.Average)

print("Data fields 'Units Sold' (Sum) and 'Revenue' (Sum, Average) added.")

---

Applying Report Filters

Report filters allow users to filter the entire pivot table based on specific criteria, providing interactive data exploration.

# Continue from the previous script

# Add 'Date' as a report filter
report_filter_date = PivotReportFilter("Date", True)
pt.ReportFilters.Add(report_filter_date)

print("Report filter 'Date' added.")

---

Optional: Layout and Styling

While extensive styling might be done interactively in Excel, spire.xls does offer options for basic layout and built‑in styles to enhance readability.

# Continue from the previous script

# Apply a built‑in pivot table style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12

# Set the report layout to tabular form for better readability
# This repeats item labels for better clarity in hierarchical row fields
pt.Options.RowLayout = PivotTableLayoutType.Tabular
pt.Options.RowHeaderCaption = "Region/Product"   # Custom caption for row header

# Ensure item labels are repeated for clarity in tabular layout
pt.PivotFields["Region"].RepeatItemLabels = True
pt.PivotFields["Product"].RepeatItemLabels = True

# Calculate the pivot table to ensure all changes are applied
pt.CalculateData()

print("Pivot table style and layout applied.")

---

Saving and Reviewing Your Automated Report

# Continue from the previous script

# Save the workbook to a file
output_file = "Automated_Sales_Report.xlsx"
workbook.SaveToFile(output_file, ExcelVersion.Version2016)
workbook.Dispose()

print(f"Pivot table report saved to '{output_file}'")
print("Automation complete!")

The generated report will look like this:

Now, when you open Automated_Sales_Report.xlsx, you will find two sheets:

• RawData – containing your original dataset.
• PivotReport – with a pre‑configured pivot table that summarizes sales data by region and product, broken down by salesperson, and allows filtering by date.

This entire process, from data preparation to a fully formatted report, was executed with a single Python script.

---

Conclusion

Automating Excel pivot‑table creation with Python offers a significant leap forward in data analysis and reporting efficiency. We’ve demonstrated how to leverage Spire.XLS for Python to:

• Set up your environment and prepare data for pivot‑table creation.
• Initiate a basic pivot‑table structure.
• Customize pivot tables by adding row fields, column fields, data fields with various aggregation functions, and report filters.
• Apply basic styling and layout options for improved readability.
• Save the final, automated Excel report.

Benefits

• Increased efficiency.
• Reduced manual errors.
• Reproducible workflows.

Integrate these automation techniques into your own projects, explore further customizations, and apply them to your unique datasets. The future of data analysis lies in smart automation, and Python is your key to unlocking it.