Automating Pivot Tables with Python

In the last post I explained the basic concept behind Pivot Tables and provided some examples. Pivot tables are an easy-to-use tool to derive some basic business intelligence from your data. As discussed last time, there are occasions when you’ll need to do interactive data mining by changing column and row fields. But in my experience, it’s handy to have my favorite reports built automatically, with the reports ready to go as soon as I open the spreadsheet. In this post I’ll develop and explain the code to create a set of pivot tables automatically in worksheet.

The goal of this exercise is to automate the generation of pivot tables from the last post, and save them to a new Excel file.

Pivot Tables

I started with the file newABCDCatering.xls from the previous post and record the macro to create this simple pivot table showing Net Bookings by Sales Rep and Food Name for the last four quarters.

Net Bookings

Captured in Excel 2007, the recorded macro looks like this:

The post Mapping Excel VB Macros to Python covered a technique for recording a Visual Basic macro and porting it to Python. Using that approach, you could simply turn on the macro recorder and generate all the required tables, producing a long script with lots of redundancy. A better approach is to build a general purpose function that can be used over and over to generate the pivot tables.

Looking at the macro, you see lines specifying the Orientation of the field name, such as .Orientation = xlRowField and .Orientation = xlColumnField. A pivot table has four basic areas for fields:

  • Report Filter (.Orientation = xlPageField)
  • Column area (.Orientation = xlColumnField)
  • Row area (.Orientation = xlRowField)
  • Values area (PivotTables().AddDataField())

Each of these supports multiple fields (column fields for Sales Rep Name and Food Name were added in the example). The ordering of the fields changes the appearance of the table.

A general pattern should be apparent in this macro. First, the pivot table is created with the ActiveWorkbook.PivotCaches.Create() statement. Next, the columns and rows are configured with a series of ActiveSheet.PivotTables("PivotTable1").PivotFields() statements. Finally, the field used in the Values section of the table is configured using the ActiveSheet.PivotTables("PivotTable1").AddDataField statement. The general purpose function will need to contain all of these constructs. Note the parts that can’t be hard-coded: the source of the data, "Sheet2!R1C1:R791C13", and destination for the table, "Sheet3!R3C1" need to be determined based on the characteristics of the source data and can’t be hard coded in the general solution.

In Python, this pattern can be reduced to the following loop that covers fields for the Report Filter, Columns and Rows:

def addpivot(wb,sourcedata,title,filters=(),columns=(),
             rows=(),sumvalue=(),sortfield=""):
    """Build a pivot table using the provided source location data
    and specified fields
    """
    ...
    for fieldlist,fieldc in ((filters,win32c.xlPageField),
                            (columns,win32c.xlColumnField),
                            (rows,win32c.xlRowField)):
        for i,val in enumerate(fieldlist):
            wb.ActiveSheet.PivotTables(tname).PivotFields(val).Orientation = fieldc
        wb.ActiveSheet.PivotTables(tname).PivotFields(val).Position = i+1
    ...

Processing the Values field is more or less copied from the Visual Basic. To keep things simple in this example, this code is limited to adding “Sum of” values only, and doesn’t handle other Summarize Value functions such as Count, Min, Max, etc.

wb.ActiveSheet.PivotTables(tname).AddDataField(
    wb.ActiveSheet.PivotTables(tname).PivotFields(sumvalue[7:]),
    sumvalue,
    win32c.xlSum)

The actual values for filters, columns and rows in the function are defined in the call to the function. The complete function creates a new sheet within the workbook, then adds an empty pivot table to the sheet and builds the table using the field information provided. For example, to answer the question: What were the total sales in each of the last four quarters?, the pivot table is built with the following call to the addpivot function:

# What were the total sales in each of the last four quarters?
addpivot(wb,src,
         title="Sales by Quarter",
         filters=(),
         columns=(),
         rows=("Fiscal Quarter",),
         sumvalue="Sum of Net Booking",
         sortfield=())

which defines a pivot table using the row header “Fiscal Quarter” and data value “Sum of Net Booking”. The title “Sales by Quarter” is used to name the sheet itself.

To make the output spreadsheet more understandable, the title parameter passed into the function and used as a title in each worksheet and as the tab name.

Title Tabs

The complete script is shown below. Caveats:

  • This script has been modified to run on both Excel 2007 and Excel 2003 and has been tested on those versions.
  • Adding pivot tables increases the size of the output Excel file, which can be mitigated by disabling caching of pivot table data. Line 48 of the script contains the command newsheet.PivotTables(tname).SaveData = False, which has been commented out. Uncommenting this command will reduce the size of the output Excel file, but will require that the pivot table be refreshed before use by clicking on Refresh Data on the PivotTable toolbar.

Prerequisites

Python (refer to http://www.python.org)

Microsoft Excel (refer to http://office.microsoft.com/excel)

Source Files and Scripts

Source for the program erpdatapivot.py and input spreadsheet file ABCDCatering.xls are available at http://github.com/pythonexcels/examples

Thanks — Dan

Introducing Pivot Tables

A working knowledge of Microsoft Excel is now a prerequisite for just about every working professional using a computer on a regular basis. Excel training classes can be found in junior colleges, adult education centers, computer training centers, libraries, job search centers and of course online. Microsoft Excel basics is even taught in high schools.

But in my professional life, I’ve found few people who have a solid knowledge of pivot tables and are really comfortable using them in Excel. If you aren’t aware of pivot tables or haven’t had the time to try out this function in Excel, pivot tables provide a way to cross tabulate, sort, segregate and aggregate tabular data, enabling you to quickly summarize data and extract totals, averages, and other information from the source data. I first found out about pivot tables when I was working with our business unit financial analyst more than 10 years ago. She didn’t like our corporate ERP system (SAP) any more than I did, and found it much faster to dump the raw data into Excel and get the answers to her questions by using a pivot table. I’ve been a pivot table convert ever since.

Using the spreadsheet newABCDCatering.xls developed in the last post, let’s add a pivot table and answer the questions raised previously:

  • What were the total sales in each of the last four quarters?
  • What are the sales for each food item in each quarter?
  • Who were the top 10 customers for ABCD Catering in 2009?
  • Who was the highest producing sales rep for the year?
  • What food item had the highest unit sales in Q4?

To build the pivot table, begin by selecting the entire data table in the Sheet2 worksheet by clicking cell A1, and typing the Control-* key combination (hold down Ctrl and press *). This selects the data in the table without also grabbing blank cells surrounding the table. This is effectively the same as selecting cell A1 and scrolling to the last column and row of data while holding down the left mouse key.

Next, if you’re using Excel 2007 or later, select the Insert tab then select PivotTable from the Pivot Table icon

Insert Pivot Table

Because you’ve selected the spreadsheet data, the dialog should already be populated with the range Sheet2!$A1:$M791 as shown below

Create Pivot Table

Click OK to create the empty pivot table.

Empty Pivot Table

In Excel 2003 and earlier versions, select the table data as described above, then select Data->Pivot Table and Pivot Chart Report to create the pivot table.

Pivot Table Menu

You’re presented with a three step wizard. For now, just hit Next for the first two dialogs, thenFinish at the final dialog.

Dialog 1 Dialog 2 Dialog 3

Once you’ve completed the above steps, you’ll see the following displayed in older versions of Excel.

Empty Pivot Table

Now you’re ready to do some data analysis.

What were the total sales in each of the last four quarters?

To understand the sales for the last four quarters, create a pivot table with “Fiscal Quarter” as a Row Label, and “Net Booking” as a Values field. To do this, drag the field “Fiscal Quarter” to the Row Labels section, and “Net Booking” to the Values section. (In older Excel versions, drag the “Fiscal Quarter” field directly onto the spreadsheet to the “Drop Row Fields Here” area, then drag the “Net Bookings” field onto the “Drop Data Items Here” area)

Pivot Table Field List

Your spreadsheet should now look something like this:

Pivot Table

The header for the table data should say “Sum of Net Bookings”. If it doesn’t, double click on the header text and select “Sum” in the list box “Summarize value field by”, or right mouse click over the text and select Summarize Data By->Sum.

Value Field Settings Summarize By

Based on the spreadsheet data, the total net bookings in each of the last four quarters were $83465, $77180, $79605 and $77440 respectively.

What are the sales for each food item in each quarter?

To answer this question, we need the same fields as setup previously (Fiscal Quarter as a Row Label, Sum of Net Booking as a Value field), plus a column header for “Food Name”. Remember that “Food” represents the numerical identifier for each food item, and “Food Name” contains the text description. Drag “Food Name” to the Column Labels section (in older versions of Excel, drag it to the “Drop Column Fields Here” area). The spreadsheet should now look like this:

Sales by Food Item Setup

Note that each food item is listed as a column header, each of the four quarters are listed as row headers. Using this table you can quickly scan the data and understand the sales for each food item. For example, Caesar Salad sales were $7890, $7140, $7960 and $6990 in each of the respective quarters.

Who were the top 10 customers for ABCD Catering in 2009?

Again, Sum of Net Bookings is the data value, but we no longer need the Food Name or Fiscal Quarter data fields. Remove them by selecting them in the Row Labels or Column Labels boxes and dragging them back to the top, or by clicking the small triangle and selecting “Remove Field”.

Remove Field

In Excel 2003 and earlier versions, select the column or row header and drag it back into the Field Chooser widget.

Now, add the Company Name field to the table by dragging it to the Row Labels box (or “Drop Row Fields Here” area in older Excel). The pivot table now contains the list of companies and their purchases, listed in alphabetical order. To find the top 10 customers, select the booking number for Adobe Systems, right click and select Sort->Sort Largest to Smallest

Sort Largest to Smallest

In older versions of Excel, select a booking number and click the “Sort Descending” icon in the tool bar, or select “Data->Sort” from the menu and select the descending sort.

Sort Descending

The list is now sorted, the top 10 customers for ABCD Catering are Hewlett-Packard, Intel, Oracle, Cisco Systems, Sanmina SCI, Sun Microsystems, Apple, Con-Way, eBay and Yahoo.

Top 10 Customers

Who was the highest producing sales rep for the year?

At ABCD Catering, sales reps cover multiple accounts. To find the highest producing rep, remove the Company Name field, replace it with the Sales Rep Name field and sort by Net Bookings. The top 10 sales reps are Dave Davidson, Lin Linares, Carl Carlson, Kay Kaywood and Nicole Nichols.

Top 10 Reps

What accounts are these top reps responsible for? To find out, drag the Company Name field into the Row Labels area.

Top 10 Rep Accounts

In older versions of Excel, drag Company Name directly onto the table

Top 10 Rep Accounts

Since Hewlett-Packard, Intel and Cisco Systems were 3 of the top 4 producing accounts, it’s no surprise that their sales rep Dave Davidson was the top performer.

Which food item had the highest unit sales in Q4?

To find the food item with the highest unit sales, change the data value field to Sum of Quantity by removing Sum of Net Bookings, adding Quantity and making sure the Value Field Setting is “Sum” and not some other setting. Next, remove the Sales Rep Name and Company Name row header fields and replace them with Food Name. To limit the data to the Q4 quarter, drag the Fiscal Quarter field to the Report Filter area, and select “2009-Q4”.

Fiscal Quarters

Finally, do a descending sort on the Sum of Quantity field to find the item with the highest unit sales.

Top Food Items

The number one item by unit volume was Potato Chips, followed by Soda and Churro.

Hopefully this gives you a feel for the power and flexibility of pivot tables. In the next post, we’ll automate everything with Python and generate a simple framework for quickly building pivot tables.

Prerequisites

Microsoft Excel (refer to http://office.microsoft.com/excel)

Source Files and Scripts

The spreadsheet newABCDCatering.xls is available at http://github.com/pythonexcels/excelexamples/tree/master

Thanks — Dan

Cleaning Up Corporate ERP Data

The previous posts have used Excel and Python to create and manipulate small spreadsheets. In reality, Python and Excel are especially well suited to tackling large data sets. This post will illustrate some techniques for cleaning up data downloaded from corporate ERP systems such as SAP and Oracle, and getting it ready for some serious data mining with Excel.

In this example, a fictional company called ABCD Catering has recorded sales and order history for 2009 in their corporate ERP system. ABCD Catering provides catering services to leading Silicon Valley companies, providing the best in hamburgers, hot dogs, churros, sodas and other comfort food. Your boss has asked you to examine this data and answer some questions and produce charts representing some of the data:

  • What were the total sales in each of the last four quarters?
  • What are the sales for each food item in each quarter?
  • Who were the top 10 customers for ABCD catering in Q1?
  • Who was the highest producing sales rep for the year?
  • What food item had the highest unit sales in Q4?

Generating this information typically involves running five separate reports in the system. Since your boss is looking for this same information at the end of each quarter, you want to simplify your life and your bosses by automating the report. Using Python and Excel, you can download a spreadsheet copy of the raw data, process it, generate the key figures and charts and save them to a spreadsheet.

Take a look at the data in ABCDCatering.xls:

ABCDCatering.xls

The spreadsheet contains some header information, then a large table of records for each order. Each record contains the fiscal year and quarter, food item, company name, order data, sales representative, booking and order quantity for each order. The data needs some work before you can use it in a pivot table. First, the data in rows 1 through 11 must be ignored, it’s meaningless for the pivot table. Also, some columns do not have a proper header and must be corrected before the data can be used. The good news is that after some minor massaging, this data will be ideally suited for processing with a pivot table in Excel. Close the spreadsheet and get ready to build the reports.

The program begins with the standard boilerplate: import the win32 module and start Excel. If you have questions on this, please refer to Basic Excel Driving with Python and Python Excel Mini Cookbook.

#
# erpdata.py: Load raw EPR data and clean up header info
#
import win32com.client as win32
import sys
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True

Next, open the spreadsheet ABCDCatering.xls with some exception handling. The try/exceptclause attempts to open the file with the Workbooks.Open() method, and exits gracefully if the file is missing or some other problem occurred. Lastly, the variable ws is set to the spreadsheet containing the data.

try:
    wb = excel.Workbooks.Open('ABCDCatering.xls')
except:
    print "Failed to open spreadsheet ABCDCatering.xls"
    sys.exit(1)
ws = wb.Sheets('Sheet1')

An easy way to load the entire spreadsheet into Python is the UsedRange method. The following command:

xldata = ws.UsedRange.Value

grabs all the data in the Sheet1 worksheet and copies it into a tuple named xldata. Once inside Python, the data can be manipulated and placed back into the spreadsheet with minimal calls to the COM interface, resulting in faster, more efficient processing.

To delete rows, add columns and do other operations on the data, it must be converted to or copied to a list. The approach used here is to examine the data row by row, discarding the non essential header rows and copying everything else to a new list. The first step is to remove the rows that are not part of the column header row or record data. If you are using Python to generate the program interactively, you can investigate the data in the xldata tuple and display the data for the first record (xldata[0]) and header record (xldata[11]):

Data Records

The length of both rows is 13, though xldata[0] contains many elements with a value of None. The following code checks the length of the data and skips any rows shorter then 13 fields or rows that contain None in the last field. Note that this code assumes that the actual data in the table always contains complete records, true in this dataset but you should always understand the characteristics of the data you’re working on.

newdata = []
for row in xldata:
    if row[-1] is not None and len(row) == 13:
        newdata.append(row)

The newdata list now contains the header and data rows from the spreadsheet, but the header row is still not complete. All column headers must contain text in order to use this data in a pivot table. Unfortunately, the spreadsheet downloads produced by the ERP system have the column label over the numberical identifier for the item, while the text column header is blank. You can see that for the “Food” and “Company” data below.

Food and Company Columns

One approach that works for this data is to scan the header and insert a column header based on the contents of the previous column. For example, the label for column F could be “Company Name”, created by simply appending the text ” Name” to the column header “Company” from the prior column. Using this simple algorithm, the column header row can be filled out and the spreadsheet made ready for pivot table conversion. A more complex lookup could be used as well, but the simple algorithm described here will scale if new fields are added to the report.

for i,field in enumerate(newdata[0]):
  if field is None:
    newdata[0][i] = lasthdr + " Name"
  else:
    lasthdr = newdata[0][i]

Now the data is ready for insertion back into the spreadsheet. To enable comparison between the new data set and the original, create a new sheet in the workbook, write the data to the new sheet and autofit the columns.

wsnew = wb.Sheets.Add()
wsnew.Range(wsnew.Cells(1,1),wsnew.Cells(len(newdata),len(newdata[0]))).Value = newdata
wsnew.Columns.AutoFit()

The last step is to save the worksheet to a new file and quit Excel. The Excel version is checked in order to save the data in the correct spreadsheet format. Version 12 corresponds to Excel 2007, which uses the .xlsx file extension. You also have to specify the constant xlOpenXMLWorkbook to define the type of output Excel file. Earlier version of Excel use the .xlsextension, and because the input file was .xls format, no output format specifier is needed for users of older versions of Excel.

if int(float(excel.Version)) >= 12:
    wb.SaveAs('newABCDCatering.xlsx',win32.constants.xlOpenXMLWorkbook)
else:
    wb.SaveAs('newABCDCatering.xls')
excel.Application.Quit()

If the file newABCDCatering.xlsx or newABCDCatering.xls already exists in My Documents, you will see the following popup when you run the script.

Error Message

Click “Yes” to overwrite the spreadsheet file. To run the script cleanly, erase the file newABCDCatering.xlsx or newABCDCatering.xls and try the script again.

After running the script, open the file newABCDCatering.xlsx or newABCDCatering.xls and view the contents. Note that the extraneous header information has been removed and blank column header information has been inserted programmatically as described earlier.

New Spreadsheet

The new spreadsheet is ready for use in a pivot table, which will be covered in the next post. Here is the complete script, also available at github.

Prerequisites

Python (refer to http://www.python.org)

Win32 Python module (refer to http://sourceforge.net/projects/pywin32)

Microsoft Excel (refer to http://office.microsoft.com/excel)

Source Files and Scripts

Source for the program erpdata.py and spreadsheet file ABCDCatering.xls are available athttp://github.com/pythonexcels/examples

Thanks — Dan