{ "metadata": { "name": "", "signature": "sha256:ecb36114fc91dd75cc92acfc07c73c1d36c2bb44bf7891a2b023968fa8f1b635" }, "nbformat": 3, "nbformat_minor": 0, "worksheets": [ { "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "The pivot table is a powerful tool to summarize and present data. Pandas has a function which allows you to quickly convert a DataFrame to a pivot table - \n", "http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.pivot_table.html\n", "\n", "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.\n", "\n", "This notebook will walk through how to use the pivot_table.\n", "\n", "The full blog post for this article is here - http://pbpython.com/pandas-pivot-table-explained.html" ] }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Read in the data" ] }, { "cell_type": "code", "collapsed": false, "input": [ "import pandas as pd\n", "import numpy as np" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 1 }, { "cell_type": "markdown", "metadata": {}, "source": [ "Read in our sales funnel data into our DataFrame" ] }, { "cell_type": "code", "collapsed": false, "input": [ "df = pd.read_excel(\"../in/sales-funnel.xlsx\")\n", "df.head()" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AccountNameRepManagerProductQuantityPriceStatus
0 714466 Trantow-Barrows Craig Booker Debra Henley CPU 1 30000 presented
1 714466 Trantow-Barrows Craig Booker Debra Henley Software 1 10000 presented
2 714466 Trantow-Barrows Craig Booker Debra Henley Maintenance 2 5000 pending
3 737550 Fritsch, Russel and Anderson Craig Booker Debra Henley CPU 1 35000 declined
4 146832 Kiehn-Spinka Daniel Hilton Debra Henley CPU 2 65000 won
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 2, "text": [ " Account Name Rep Manager \\\n", "0 714466 Trantow-Barrows Craig Booker Debra Henley \n", "1 714466 Trantow-Barrows Craig Booker Debra Henley \n", "2 714466 Trantow-Barrows Craig Booker Debra Henley \n", "3 737550 Fritsch, Russel and Anderson Craig Booker Debra Henley \n", "4 146832 Kiehn-Spinka Daniel Hilton Debra Henley \n", "\n", " Product Quantity Price Status \n", "0 CPU 1 30000 presented \n", "1 Software 1 10000 presented \n", "2 Maintenance 2 5000 pending \n", "3 CPU 1 35000 declined \n", "4 CPU 2 65000 won " ] } ], "prompt_number": 2 }, { "cell_type": "markdown", "metadata": {}, "source": [ "For convenience sake, let's define the status column as a category and set the order we want to view.\n", "\n", "This isn't strictly required but helps us keep the order we want as we work through analyzing the data." ] }, { "cell_type": "code", "collapsed": false, "input": [ "df[\"Status\"] = df[\"Status\"].astype(\"category\")\n", "df[\"Status\"].cat.set_categories([\"won\",\"pending\",\"presented\",\"declined\"],inplace=True)" ], "language": "python", "metadata": {}, "outputs": [], "prompt_number": 3 }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Pivot the data" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "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.\n", "\n", "The simplest pivot table must have a dataframe and an index. In this case, let's use the Name as our index." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Name\"])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AccountPriceQuantity
Name
Barton LLC 740150 35000 1.000000
Fritsch, Russel and Anderson 737550 35000 1.000000
Herman LLC 141962 65000 2.000000
Jerde-Hilpert 412290 5000 2.000000
Kassulke, Ondricka and Metz 307599 7000 3.000000
Keeling LLC 688981 100000 5.000000
Kiehn-Spinka 146832 65000 2.000000
Koepp Ltd 729833 35000 2.000000
Kulas Inc 218895 25000 1.500000
Purdy-Kunde 163416 30000 1.000000
Stokes LLC 239344 7500 1.000000
Trantow-Barrows 714466 15000 1.333333
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 4, "text": [ " Account Price Quantity\n", "Name \n", "Barton LLC 740150 35000 1.000000\n", "Fritsch, Russel and Anderson 737550 35000 1.000000\n", "Herman LLC 141962 65000 2.000000\n", "Jerde-Hilpert 412290 5000 2.000000\n", "Kassulke, Ondricka and Metz 307599 7000 3.000000\n", "Keeling LLC 688981 100000 5.000000\n", "Kiehn-Spinka 146832 65000 2.000000\n", "Koepp Ltd 729833 35000 2.000000\n", "Kulas Inc 218895 25000 1.500000\n", "Purdy-Kunde 163416 30000 1.000000\n", "Stokes LLC 239344 7500 1.000000\n", "Trantow-Barrows 714466 15000 1.333333" ] } ], "prompt_number": 4 }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can have multiple indexes as well. In fact, most of the pivot_table args can take multiple values via a list." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Name\",\"Rep\",\"Manager\"])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AccountPriceQuantity
NameRepManager
Barton LLCJohn SmithDebra Henley 740150 35000 1.000000
Fritsch, Russel and AndersonCraig BookerDebra Henley 737550 35000 1.000000
Herman LLCCedric MossFred Anderson 141962 65000 2.000000
Jerde-HilpertJohn SmithDebra Henley 412290 5000 2.000000
Kassulke, Ondricka and MetzWendy YuleFred Anderson 307599 7000 3.000000
Keeling LLCWendy YuleFred Anderson 688981 100000 5.000000
Kiehn-SpinkaDaniel HiltonDebra Henley 146832 65000 2.000000
Koepp LtdWendy YuleFred Anderson 729833 35000 2.000000
Kulas IncDaniel HiltonDebra Henley 218895 25000 1.500000
Purdy-KundeCedric MossFred Anderson 163416 30000 1.000000
Stokes LLCCedric MossFred Anderson 239344 7500 1.000000
Trantow-BarrowsCraig BookerDebra Henley 714466 15000 1.333333
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 5, "text": [ " Account Price \\\n", "Name Rep Manager \n", "Barton LLC John Smith Debra Henley 740150 35000 \n", "Fritsch, Russel and Anderson Craig Booker Debra Henley 737550 35000 \n", "Herman LLC Cedric Moss Fred Anderson 141962 65000 \n", "Jerde-Hilpert John Smith Debra Henley 412290 5000 \n", "Kassulke, Ondricka and Metz Wendy Yule Fred Anderson 307599 7000 \n", "Keeling LLC Wendy Yule Fred Anderson 688981 100000 \n", "Kiehn-Spinka Daniel Hilton Debra Henley 146832 65000 \n", "Koepp Ltd Wendy Yule Fred Anderson 729833 35000 \n", "Kulas Inc Daniel Hilton Debra Henley 218895 25000 \n", "Purdy-Kunde Cedric Moss Fred Anderson 163416 30000 \n", "Stokes LLC Cedric Moss Fred Anderson 239344 7500 \n", "Trantow-Barrows Craig Booker Debra Henley 714466 15000 \n", "\n", " Quantity \n", "Name Rep Manager \n", "Barton LLC John Smith Debra Henley 1.000000 \n", "Fritsch, Russel and Anderson Craig Booker Debra Henley 1.000000 \n", "Herman LLC Cedric Moss Fred Anderson 2.000000 \n", "Jerde-Hilpert John Smith Debra Henley 2.000000 \n", "Kassulke, Ondricka and Metz Wendy Yule Fred Anderson 3.000000 \n", "Keeling LLC Wendy Yule Fred Anderson 5.000000 \n", "Kiehn-Spinka Daniel Hilton Debra Henley 2.000000 \n", "Koepp Ltd Wendy Yule Fred Anderson 2.000000 \n", "Kulas Inc Daniel Hilton Debra Henley 1.500000 \n", "Purdy-Kunde Cedric Moss Fred Anderson 1.000000 \n", "Stokes LLC Cedric Moss Fred Anderson 1.000000 \n", "Trantow-Barrows Craig Booker Debra Henley 1.333333 " ] } ], "prompt_number": 5 }, { "cell_type": "markdown", "metadata": {}, "source": [ "This is interesting but not particularly useful. What we probably want to do is look at this by Manager and Director.\n", "It's easy enough to do by changing the index." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
AccountPriceQuantity
ManagerRep
Debra HenleyCraig Booker 720237.0 20000.000000 1.250000
Daniel Hilton 194874.0 38333.333333 1.666667
John Smith 576220.0 20000.000000 1.500000
Fred AndersonCedric Moss 196016.5 27500.000000 1.250000
Wendy Yule 614061.5 44250.000000 3.000000
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 6, "text": [ " Account Price Quantity\n", "Manager Rep \n", "Debra Henley Craig Booker 720237.0 20000.000000 1.250000\n", " Daniel Hilton 194874.0 38333.333333 1.666667\n", " John Smith 576220.0 20000.000000 1.500000\n", "Fred Anderson Cedric Moss 196016.5 27500.000000 1.250000\n", " Wendy Yule 614061.5 44250.000000 3.000000" ] } ], "prompt_number": 6 }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can see that the pivot table is smart enough to start aggregating the data and summarizing it by grouping the reps with their managers. Now we start to get a glimpse of what a pivot table can do for us.\n", "\n", "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Price
ManagerRep
Debra HenleyCraig Booker 20000
Daniel Hilton 38333
John Smith 20000
Fred AndersonCedric Moss 27500
Wendy Yule 44250
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 7, "text": [ " Price\n", "Manager Rep \n", "Debra Henley Craig Booker 20000\n", " Daniel Hilton 38333\n", " John Smith 20000\n", "Fred Anderson Cedric Moss 27500\n", " Wendy Yule 44250" ] } ], "prompt_number": 7 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The price column automatically averages the data but we can do a count or a sum. Adding them is simple using aggfunc." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],aggfunc=np.sum)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Price
ManagerRep
Debra HenleyCraig Booker 80000
Daniel Hilton 115000
John Smith 40000
Fred AndersonCedric Moss 110000
Wendy Yule 177000
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 8, "text": [ " Price\n", "Manager Rep \n", "Debra Henley Craig Booker 80000\n", " Daniel Hilton 115000\n", " John Smith 40000\n", "Fred Anderson Cedric Moss 110000\n", " Wendy Yule 177000" ] } ], "prompt_number": 8 }, { "cell_type": "markdown", "metadata": {}, "source": [ "aggfunc can take a list of functions. Let's try a mean using the numpy functions and len to get a count." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],aggfunc=[np.mean,len])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
meanlen
PricePrice
ManagerRep
Debra HenleyCraig Booker 20000 4
Daniel Hilton 38333 3
John Smith 20000 2
Fred AndersonCedric Moss 27500 4
Wendy Yule 44250 4
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 9, "text": [ " mean len\n", " Price Price\n", "Manager Rep \n", "Debra Henley Craig Booker 20000 4\n", " Daniel Hilton 38333 3\n", " John Smith 20000 2\n", "Fred Anderson Cedric Moss 27500 4\n", " Wendy Yule 44250 4" ] } ], "prompt_number": 9 }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we want to see sales broken down by the products, the columns variable allows us to define one or more columns." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],\n", " columns=[\"Product\"],aggfunc=[np.sum])" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum
Price
ProductCPUMaintenanceMonitorSoftware
ManagerRep
Debra HenleyCraig Booker 65000 5000 NaN 10000
Daniel Hilton 105000 NaN NaN 10000
John Smith 35000 5000 NaN NaN
Fred AndersonCedric Moss 95000 5000 NaN 10000
Wendy Yule 165000 7000 5000 NaN
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 10, "text": [ " sum \n", " Price \n", "Product CPU Maintenance Monitor Software\n", "Manager Rep \n", "Debra Henley Craig Booker 65000 5000 NaN 10000\n", " Daniel Hilton 105000 NaN NaN 10000\n", " John Smith 35000 5000 NaN NaN\n", "Fred Anderson Cedric Moss 95000 5000 NaN 10000\n", " Wendy Yule 165000 7000 5000 NaN" ] } ], "prompt_number": 10 }, { "cell_type": "markdown", "metadata": {}, "source": [ "The NaN's are a bit distracting. If we want to remove them, we could use fill_value to set them to 0." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\"],\n", " columns=[\"Product\"],aggfunc=[np.sum],fill_value=0)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum
Price
ProductCPUMaintenanceMonitorSoftware
ManagerRep
Debra HenleyCraig Booker 65000 5000 0 10000
Daniel Hilton 105000 0 0 10000
John Smith 35000 5000 0 0
Fred AndersonCedric Moss 95000 5000 0 10000
Wendy Yule 165000 7000 5000 0
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 11, "text": [ " sum \n", " Price \n", "Product CPU Maintenance Monitor Software\n", "Manager Rep \n", "Debra Henley Craig Booker 65000 5000 0 10000\n", " Daniel Hilton 105000 0 0 10000\n", " John Smith 35000 5000 0 0\n", "Fred Anderson Cedric Moss 95000 5000 0 10000\n", " Wendy Yule 165000 7000 5000 0" ] } ], "prompt_number": 11 }, { "cell_type": "markdown", "metadata": {}, "source": [ "I think it would be useful to add the quantity as well. Add Quantity to the values list." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\"],values=[\"Price\",\"Quantity\"],\n", " columns=[\"Product\"],aggfunc=[np.sum],fill_value=0)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum
PriceQuantity
ProductCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftware
ManagerRep
Debra HenleyCraig Booker 65000 5000 0 10000 2 2 0 1
Daniel Hilton 105000 0 0 10000 4 0 0 1
John Smith 35000 5000 0 0 1 2 0 0
Fred AndersonCedric Moss 95000 5000 0 10000 3 1 0 1
Wendy Yule 165000 7000 5000 0 7 3 2 0
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 12, "text": [ " sum \\\n", " Price Quantity \n", "Product CPU Maintenance Monitor Software CPU \n", "Manager Rep \n", "Debra Henley Craig Booker 65000 5000 0 10000 2 \n", " Daniel Hilton 105000 0 0 10000 4 \n", " John Smith 35000 5000 0 0 1 \n", "Fred Anderson Cedric Moss 95000 5000 0 10000 3 \n", " Wendy Yule 165000 7000 5000 0 7 \n", "\n", " \n", " \n", "Product Maintenance Monitor Software \n", "Manager Rep \n", "Debra Henley Craig Booker 2 0 1 \n", " Daniel Hilton 0 0 1 \n", " John Smith 2 0 0 \n", "Fred Anderson Cedric Moss 1 0 1 \n", " Wendy Yule 3 2 0 " ] } ], "prompt_number": 12 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\",\"Product\"],\n", " values=[\"Price\",\"Quantity\"],aggfunc=[np.sum],fill_value=0)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum
PriceQuantity
ManagerRepProduct
Debra HenleyCraig BookerCPU 65000 2
Maintenance 5000 2
Software 10000 1
Daniel HiltonCPU 105000 4
Software 10000 1
John SmithCPU 35000 1
Maintenance 5000 2
Fred AndersonCedric MossCPU 95000 3
Maintenance 5000 1
Software 10000 1
Wendy YuleCPU 165000 7
Maintenance 7000 3
Monitor 5000 2
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 13, "text": [ " sum \n", " Price Quantity\n", "Manager Rep Product \n", "Debra Henley Craig Booker CPU 65000 2\n", " Maintenance 5000 2\n", " Software 10000 1\n", " Daniel Hilton CPU 105000 4\n", " Software 10000 1\n", " John Smith CPU 35000 1\n", " Maintenance 5000 2\n", "Fred Anderson Cedric Moss CPU 95000 3\n", " Maintenance 5000 1\n", " Software 10000 1\n", " Wendy Yule CPU 165000 7\n", " Maintenance 7000 3\n", " Monitor 5000 2" ] } ], "prompt_number": 13 }, { "cell_type": "markdown", "metadata": {}, "source": [ "For this data set, this representation makes more sense. Now, what if I want to see some totals? margins=True does that for us." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Rep\",\"Product\"],\n", " values=[\"Price\",\"Quantity\"],\n", " aggfunc=[np.sum,np.mean],fill_value=0,margins=True)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
summean
PriceQuantityPriceQuantity
ManagerRepProduct
Debra HenleyCraig BookerCPU 65000 2 32500.000000 1.000000
Maintenance 5000 2 5000.000000 2.000000
Software 10000 1 10000.000000 1.000000
Daniel HiltonCPU 105000 4 52500.000000 2.000000
Software 10000 1 10000.000000 1.000000
John SmithCPU 35000 1 35000.000000 1.000000
Maintenance 5000 2 5000.000000 2.000000
Fred AndersonCedric MossCPU 95000 3 47500.000000 1.500000
Maintenance 5000 1 5000.000000 1.000000
Software 10000 1 10000.000000 1.000000
Wendy YuleCPU 165000 7 82500.000000 3.500000
Maintenance 7000 3 7000.000000 3.000000
Monitor 5000 2 5000.000000 2.000000
All 522000 30 30705.882353 1.764706
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 14, "text": [ " sum mean \\\n", " Price Quantity Price \n", "Manager Rep Product \n", "Debra Henley Craig Booker CPU 65000 2 32500.000000 \n", " Maintenance 5000 2 5000.000000 \n", " Software 10000 1 10000.000000 \n", " Daniel Hilton CPU 105000 4 52500.000000 \n", " Software 10000 1 10000.000000 \n", " John Smith CPU 35000 1 35000.000000 \n", " Maintenance 5000 2 5000.000000 \n", "Fred Anderson Cedric Moss CPU 95000 3 47500.000000 \n", " Maintenance 5000 1 5000.000000 \n", " Software 10000 1 10000.000000 \n", " Wendy Yule CPU 165000 7 82500.000000 \n", " Maintenance 7000 3 7000.000000 \n", " Monitor 5000 2 5000.000000 \n", "All 522000 30 30705.882353 \n", "\n", " \n", " Quantity \n", "Manager Rep Product \n", "Debra Henley Craig Booker CPU 1.000000 \n", " Maintenance 2.000000 \n", " Software 1.000000 \n", " Daniel Hilton CPU 2.000000 \n", " Software 1.000000 \n", " John Smith CPU 1.000000 \n", " Maintenance 2.000000 \n", "Fred Anderson Cedric Moss CPU 1.500000 \n", " Maintenance 1.000000 \n", " Software 1.000000 \n", " Wendy Yule CPU 3.500000 \n", " Maintenance 3.000000 \n", " Monitor 2.000000 \n", "All 1.764706 " ] } ], "prompt_number": 14 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Status\"],values=[\"Price\"],\n", " aggfunc=[np.sum],fill_value=0,margins=True)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sum
Price
ManagerStatus
Debra Henleydeclined 70000
pending 50000
presented 50000
won 65000
Fred Andersondeclined 65000
pending 5000
presented 45000
won 172000
All 522000
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 15, "text": [ " sum\n", " Price\n", "Manager Status \n", "Debra Henley declined 70000\n", " pending 50000\n", " presented 50000\n", " won 65000\n", "Fred Anderson declined 65000\n", " pending 5000\n", " presented 45000\n", " won 172000\n", "All 522000" ] } ], "prompt_number": 15 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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. This has a side-effect of making the labels a little cleaner." ] }, { "cell_type": "code", "collapsed": false, "input": [ "pd.pivot_table(df,index=[\"Manager\",\"Status\"],columns=[\"Product\"],values=[\"Quantity\",\"Price\"],\n", " aggfunc={\"Quantity\":len,\"Price\":np.sum},fill_value=0)" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PriceQuantity
ProductCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftware
ManagerStatus
Debra Henleydeclined 70000 0 0 0 2 0 0 0
pending 40000 10000 0 0 1 2 0 0
presented 30000 0 0 20000 1 0 0 2
won 65000 0 0 0 1 0 0 0
Fred Andersondeclined 65000 0 0 0 1 0 0 0
pending 0 5000 0 0 0 1 0 0
presented 30000 0 5000 10000 1 0 1 1
won 165000 7000 0 0 2 1 0 0
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 16, "text": [ " Price Quantity \\\n", "Product CPU Maintenance Monitor Software CPU \n", "Manager Status \n", "Debra Henley declined 70000 0 0 0 2 \n", " pending 40000 10000 0 0 1 \n", " presented 30000 0 0 20000 1 \n", " won 65000 0 0 0 1 \n", "Fred Anderson declined 65000 0 0 0 1 \n", " pending 0 5000 0 0 0 \n", " presented 30000 0 5000 10000 1 \n", " won 165000 7000 0 0 2 \n", "\n", " \n", "Product Maintenance Monitor Software \n", "Manager Status \n", "Debra Henley declined 0 0 0 \n", " pending 2 0 0 \n", " presented 0 0 2 \n", " won 0 0 0 \n", "Fred Anderson declined 0 0 0 \n", " pending 1 0 0 \n", " presented 0 1 1 \n", " won 1 0 0 " ] } ], "prompt_number": 16 }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can provide a list of aggfunctions to apply to each value too:" ] }, { "cell_type": "code", "collapsed": false, "input": [ "table = pd.pivot_table(df,index=[\"Manager\",\"Status\"],columns=[\"Product\"],values=[\"Quantity\",\"Price\"],\n", " aggfunc={\"Quantity\":len,\"Price\":[np.sum,np.mean]},fill_value=0)\n", "table" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PriceQuantity
meansumlen
ProductCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftware
ManagerStatus
Debra Henleydeclined 35000 0 0 0 70000 0 0 0 2 0 0 0
pending 40000 5000 0 0 40000 10000 0 0 1 2 0 0
presented 30000 0 0 10000 30000 0 0 20000 1 0 0 2
won 65000 0 0 0 65000 0 0 0 1 0 0 0
Fred Andersondeclined 65000 0 0 0 65000 0 0 0 1 0 0 0
pending 0 5000 0 0 0 5000 0 0 0 1 0 0
presented 30000 0 5000 10000 30000 0 5000 10000 1 0 1 1
won 82500 7000 0 0 165000 7000 0 0 2 1 0 0
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 17, "text": [ " Price \\\n", " mean sum \n", "Product CPU Maintenance Monitor Software CPU \n", "Manager Status \n", "Debra Henley declined 35000 0 0 0 70000 \n", " pending 40000 5000 0 0 40000 \n", " presented 30000 0 0 10000 30000 \n", " won 65000 0 0 0 65000 \n", "Fred Anderson declined 65000 0 0 0 65000 \n", " pending 0 5000 0 0 0 \n", " presented 30000 0 5000 10000 30000 \n", " won 82500 7000 0 0 165000 \n", "\n", " Quantity \\\n", " len \n", "Product Maintenance Monitor Software CPU Maintenance \n", "Manager Status \n", "Debra Henley declined 0 0 0 2 0 \n", " pending 10000 0 0 1 2 \n", " presented 0 0 20000 1 0 \n", " won 0 0 0 1 0 \n", "Fred Anderson declined 0 0 0 1 0 \n", " pending 5000 0 0 0 1 \n", " presented 0 5000 10000 1 0 \n", " won 7000 0 0 2 1 \n", "\n", " \n", " \n", "Product Monitor Software \n", "Manager Status \n", "Debra Henley declined 0 0 \n", " pending 0 0 \n", " presented 0 2 \n", " won 0 0 \n", "Fred Anderson declined 0 0 \n", " pending 0 0 \n", " presented 1 1 \n", " won 0 0 " ] } ], "prompt_number": 17 }, { "cell_type": "markdown", "metadata": {}, "source": [ "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 feel for how it works." ] }, { "cell_type": "heading", "level": 1, "metadata": {}, "source": [ "Advanced Pivot Table Filtering" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Once you have generated your data, it is in a DataFrame so you can filter on it using your normal DataFrame functions." ] }, { "cell_type": "code", "collapsed": false, "input": [ "table.query('Manager == [\"Debra Henley\"]')" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PriceQuantity
meansumlen
ProductCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftware
ManagerStatus
Debra Henleydeclined 35000 0 0 0 70000 0 0 0 2 0 0 0
pending 40000 5000 0 0 40000 10000 0 0 1 2 0 0
presented 30000 0 0 10000 30000 0 0 20000 1 0 0 2
won 65000 0 0 0 65000 0 0 0 1 0 0 0
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 18, "text": [ " Price \\\n", " mean sum \n", "Product CPU Maintenance Monitor Software CPU Maintenance \n", "Manager Status \n", "Debra Henley declined 35000 0 0 0 70000 0 \n", " pending 40000 5000 0 0 40000 10000 \n", " presented 30000 0 0 10000 30000 0 \n", " won 65000 0 0 0 65000 0 \n", "\n", " Quantity \n", " len \n", "Product Monitor Software CPU Maintenance Monitor Software \n", "Manager Status \n", "Debra Henley declined 0 0 2 0 0 0 \n", " pending 0 0 1 2 0 0 \n", " presented 0 20000 1 0 0 2 \n", " won 0 0 1 0 0 0 " ] } ], "prompt_number": 18 }, { "cell_type": "code", "collapsed": false, "input": [ "table.query('Status == [\"pending\",\"won\"]')" ], "language": "python", "metadata": {}, "outputs": [ { "html": [ "
\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PriceQuantity
meansumlen
ProductCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftwareCPUMaintenanceMonitorSoftware
ManagerStatus
Debra Henleypending 40000 5000 0 0 40000 10000 0 0 1 2 0 0
won 65000 0 0 0 65000 0 0 0 1 0 0 0
Fred Andersonpending 0 5000 0 0 0 5000 0 0 0 1 0 0
won 82500 7000 0 0 165000 7000 0 0 2 1 0 0
\n", "
" ], "metadata": {}, "output_type": "pyout", "prompt_number": 19, "text": [ " Price \\\n", " mean sum \n", "Product CPU Maintenance Monitor Software CPU Maintenance \n", "Manager Status \n", "Debra Henley pending 40000 5000 0 0 40000 10000 \n", " won 65000 0 0 0 65000 0 \n", "Fred Anderson pending 0 5000 0 0 0 5000 \n", " won 82500 7000 0 0 165000 7000 \n", "\n", " Quantity \n", " len \n", "Product Monitor Software CPU Maintenance Monitor Software \n", "Manager Status \n", "Debra Henley pending 0 0 1 2 0 0 \n", " won 0 0 1 0 0 0 \n", "Fred Anderson pending 0 0 0 1 0 0 \n", " won 0 0 2 1 0 0 " ] } ], "prompt_number": 19 }, { "cell_type": "markdown", "metadata": {}, "source": [ "I hope this tutorial has shown you how to use pivot tables on your data sets." ] } ], "metadata": {} } ] }