The pivot table is a powerful tool to summarize and present data in Excel. However, in Python, Pandas has a function which allows you to quickly convert a DataFrame to a pivot table.

This function is very useful but sometimes it can be tricky to remember how to use it to get the data formatted in a way you need.

Read in the data

import pandas as pd
import numpy as np

Read in the sales funnel data into DataFrame.

df = pd.read_excel("../sales-funnel.xlsx")
df.head()

For convenience sake, I define the status column as a category and set the order we’d like to view. This isn’t strictly required but helps us keep the order we want as we work through analyzing the data.

df["Status"] = df["Status"].astype("category")
df["Status"].cat.set_categories(["won","pending","presented","declined"],inplace=True)

Pivot the data

As we build up the pivot table, I think it’s easiest to take it one step at a time. Add items one at a time and check each step to verify you are getting the results you expect.

The simplest pivot table must have a dataframe and an index.

pd.pivot_table(df,index=["Name"])

You can have multiple indexes as well. In fact, most of the pivot_table args can take multiple values via a list.

pd.pivot_table(df,index=["Name","Rep","Manager"])

This is interesting but not particularly useful. What we probably want to do is look at this by Manager and Director. It’s easy enough to do by changing the index.

pd.pivot_table(df,index=["Manager","Rep"])

Now we start to get a glimpse of what a pivot table can do for us.

For this purpose, the Account and Quantity columns aren’t really useful. Let’s remove it by explicitly defining the columns we care about using the values field.

pd.pivot_table(df,index=["Manager","Rep"],values=["Price"])

The Price column automatically averages the data but we can do a count or a sum. Adding them is simple using aggfunc.

pd.pivot_table(df,index=["Manager","Rep"],values=["Price"],aggfunc=np.sum)

aggfunc can take a list of functions. Let’s try a mean using the numpy functions and len to get a count.

pd.pivot_table(df,index=["Manager","Rep"],values=["Price"],aggfunc=[np.mean,len])

If we want to see sales broken down by the Products, the columns variable allows us to define one or more columns.

pd.pivot_table(df,index=["Manager","Rep"],values=["Price"],
               columns=["Product"],aggfunc=[np.sum])

The NaN’s are a bit distracting. If we want to remove them, we could use fill_value to set them to 0.

pd.pivot_table(df,index=["Manager","Rep"],values=["Price"],
               columns=["Product"],aggfunc=[np.sum],fill_value=0)

It would be useful to add the quantity as well. Add Quantity to the values list.

pd.pivot_table(df,index=["Manager","Rep"],values=["Price","Quantity"],
               columns=["Product"],aggfunc=[np.sum],fill_value=0)

What’s interesting is that you can move items to the index to get a different visual representation. We can add the Products to the index.

pd.pivot_table(df,index=["Manager","Rep","Product"],
               values=["Price","Quantity"],aggfunc=[np.sum],fill_value=0)

For this data set, this representation makes more sense. Now, what if I want to see some totals? margins=True does that for us.

pd.pivot_table(df,index=["Manager","Rep","Product"],
               values=["Price","Quantity"],
               aggfunc=[np.sum,np.mean],fill_value=0,margins=True)

Let’s move the analysis up a level and look at our pipeline at the manager level. Notice how the Status is ordered based on our earlier category definition.

pd.pivot_table(df,index=["Manager","Status"],values=["Price"],
               aggfunc=[np.sum],fill_value=0,margins=True)

A really handy feature is the ability to pass a dictionary to the aggfunc so you can perform different functions on each of the values you select.

pd.pivot_table(df,index=["Manager","Status"],columns=["Product"],values=["Quantity","Price"],
               aggfunc={"Quantity":len,"Price":np.sum},fill_value=0)

You can provide a list of aggfunctions to apply to each value too:

table = pd.pivot_table(df,index=["Manager","Status"],columns=["Product"],values=["Quantity","Price"],
               aggfunc={"Quantity":len,"Price":[np.sum,np.mean]},fill_value=0)
table

It can look daunting to try to pull this all together at once but as soon as you start playing with the data and slowly add the items, you can get a clear understanding for how it works.

Pivot Table Filtering

Once you have generated your data, it is in a DataFrame so you can filter on it using your normal DataFrame functions.

table.query('Manager == ["Debra Henley"]')

table.query('Status == ["pending","won"]')

This is a brief walk-through on how to use pivot tables on your data sets.