A Pandas Pivot Table Demo

pivot_table_demo.py

import pandas as pd
import numpy as np

# based on [Pandas Pivot Table Explained](http://pbpython.com/pandas-pivot-table-explained.html)

# basic pivot table function

df = pd.read_excel('./sales-funnel.xlsx')
df['Status'] = df['Status'].astype('category')
df['Status'].cat.set_categories(["won", "pending", "presented", "declined"], inplace=True)
pd.pivot_table(df, index=["Manager", "Rep"], values=["Price"], aggfunc=np.sum)
pd.pivot_table(df, index=["Manager", "Rep"], values=["Price"], aggfunc=[np.mean, len])
pd.pivot_table(df, index=['Manager', 'Rep'], values=['Price'], columns=['Product'], aggfunc=[np.sum])
pd.pivot_table(df, index=['Manager', 'Rep'], values=['Price'], columns=['Product'], aggfunc=[np.sum],
               fill_value=0)
pd.pivot_table(df, index=['Manager', 'Rep'], values=['Price', 'Quantity'], columns=['Product'],
               aggfunc=[np.sum], fill_value=0)
pd.pivot_table(df, index=['Manager', 'Rep', 'Product'], values=['Price', 'Quantity'],
               aggfunc=[np.sum], fill_value=0)
pd.pivot_table(df, index=['Manager', 'Rep', 'Product'], values=['Price', 'Quantity'],
               aggfunc=[np.sum, np.mean], fill_value=0, margins=True)
pd.pivot_table(df, index=['Manager', 'Status'], values=['Price', 'Quantity'],
               aggfunc=[np.sum], fill_value=0, margins=True)
pd.pivot_table(df, index=['Manager', 'Status'], values=['Price', 'Quantity'], columns=['Product'],
               aggfunc={'Quantity': len, 'Price': np.sum}, fill_value=0)
pd.pivot_table(df, index=['Manager', 'Status'], values=['Price', 'Quantity'], columns=['Product'],
               aggfunc={'Quantity': len, 'Price': [np.sum, np.mean]}, fill_value=0)

# advanced filtering

df.query('Manager == ["Debra Henley"]')
df.query('Status == ["pending", "won"]')