Skip to content

Instantly share code, notes, and snippets.

@kshirsagarsiddharth

kshirsagarsiddharth/pivot_table.ipynb

Created December 1, 2019 19:14
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kshirsagarsiddharth/649982c591143271334142b8ce45b5ea to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kshirsagarsiddharth/649982c591143271334142b8ce45b5ea to your computer and use it in GitHub Desktop.
Download ZIP
Created on Cognitive Class Labs
Display the source blob
Display the rendered blob
Raw
pivot_table.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np"
]
},
{
"cell_type": "code",
"execution_count": 25,
"metadata": {},
"outputs": [],
"source": [
"df = pd.read_excel(\"sales-funnel.xlsx\")"
]
},
{
"cell_type": "code",
"execution_count": 26,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Account</th>\n",
" <th>Name</th>\n",
" <th>Rep</th>\n",
" <th>Manager</th>\n",
" <th>Product</th>\n",
" <th>Quantity</th>\n",
" <th>Price</th>\n",
" <th>Status</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>714466</td>\n",
" <td>Trantow-Barrows</td>\n",
" <td>Craig Booker</td>\n",
" <td>Debra Henley</td>\n",
" <td>CPU</td>\n",
" <td>1</td>\n",
" <td>30000</td>\n",
" <td>presented</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>714466</td>\n",
" <td>Trantow-Barrows</td>\n",
" <td>Craig Booker</td>\n",
" <td>Debra Henley</td>\n",
" <td>Software</td>\n",
" <td>1</td>\n",
" <td>10000</td>\n",
" <td>presented</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>714466</td>\n",
" <td>Trantow-Barrows</td>\n",
" <td>Craig Booker</td>\n",
" <td>Debra Henley</td>\n",
" <td>Maintenance</td>\n",
" <td>2</td>\n",
" <td>5000</td>\n",
" <td>pending</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>737550</td>\n",
" <td>Fritsch, Russel and Anderson</td>\n",
" <td>Craig Booker</td>\n",
" <td>Debra Henley</td>\n",
" <td>CPU</td>\n",
" <td>1</td>\n",
" <td>35000</td>\n",
" <td>declined</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>146832</td>\n",
" <td>Kiehn-Spinka</td>\n",
" <td>Daniel Hilton</td>\n",
" <td>Debra Henley</td>\n",
" <td>CPU</td>\n",
" <td>2</td>\n",
" <td>65000</td>\n",
" <td>won</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>218895</td>\n",
" <td>Kulas Inc</td>\n",
" <td>Daniel Hilton</td>\n",
" <td>Debra Henley</td>\n",
" <td>CPU</td>\n",
" <td>2</td>\n",
" <td>40000</td>\n",
" <td>pending</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>218895</td>\n",
" <td>Kulas Inc</td>\n",
" <td>Daniel Hilton</td>\n",
" <td>Debra Henley</td>\n",
" <td>Software</td>\n",
" <td>1</td>\n",
" <td>10000</td>\n",
" <td>presented</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>412290</td>\n",
" <td>Jerde-Hilpert</td>\n",
" <td>John Smith</td>\n",
" <td>Debra Henley</td>\n",
" <td>Maintenance</td>\n",
" <td>2</td>\n",
" <td>5000</td>\n",
" <td>pending</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>740150</td>\n",
" <td>Barton LLC</td>\n",
" <td>John Smith</td>\n",
" <td>Debra Henley</td>\n",
" <td>CPU</td>\n",
" <td>1</td>\n",
" <td>35000</td>\n",
" <td>declined</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>141962</td>\n",
" <td>Herman LLC</td>\n",
" <td>Cedric Moss</td>\n",
" <td>Fred Anderson</td>\n",
" <td>CPU</td>\n",
" <td>2</td>\n",
" <td>65000</td>\n",
" <td>won</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>163416</td>\n",
" <td>Purdy-Kunde</td>\n",
" <td>Cedric Moss</td>\n",
" <td>Fred Anderson</td>\n",
" <td>CPU</td>\n",
" <td>1</td>\n",
" <td>30000</td>\n",
" <td>presented</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>239344</td>\n",
" <td>Stokes LLC</td>\n",
" <td>Cedric Moss</td>\n",
" <td>Fred Anderson</td>\n",
" <td>Maintenance</td>\n",
" <td>1</td>\n",
" <td>5000</td>\n",
" <td>pending</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>239344</td>\n",
" <td>Stokes LLC</td>\n",
" <td>Cedric Moss</td>\n",
" <td>Fred Anderson</td>\n",
" <td>Software</td>\n",
" <td>1</td>\n",
" <td>10000</td>\n",
" <td>presented</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>307599</td>\n",
" <td>Kassulke, Ondricka and Metz</td>\n",
" <td>Wendy Yule</td>\n",
" <td>Fred Anderson</td>\n",
" <td>Maintenance</td>\n",
" <td>3</td>\n",
" <td>7000</td>\n",
" <td>won</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>688981</td>\n",
" <td>Keeling LLC</td>\n",
" <td>Wendy Yule</td>\n",
" <td>Fred Anderson</td>\n",
" <td>CPU</td>\n",
" <td>5</td>\n",
" <td>100000</td>\n",
" <td>won</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>729833</td>\n",
" <td>Koepp Ltd</td>\n",
" <td>Wendy Yule</td>\n",
" <td>Fred Anderson</td>\n",
" <td>CPU</td>\n",
" <td>2</td>\n",
" <td>65000</td>\n",
" <td>declined</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>729833</td>\n",
" <td>Koepp Ltd</td>\n",
" <td>Wendy Yule</td>\n",
" <td>Fred Anderson</td>\n",
" <td>Monitor</td>\n",
" <td>2</td>\n",
" <td>5000</td>\n",
" <td>presented</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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",
"5 218895 Kulas Inc Daniel Hilton Debra Henley \n",
"6 218895 Kulas Inc Daniel Hilton Debra Henley \n",
"7 412290 Jerde-Hilpert John Smith Debra Henley \n",
"8 740150 Barton LLC John Smith Debra Henley \n",
"9 141962 Herman LLC Cedric Moss Fred Anderson \n",
"10 163416 Purdy-Kunde Cedric Moss Fred Anderson \n",
"11 239344 Stokes LLC Cedric Moss Fred Anderson \n",
"12 239344 Stokes LLC Cedric Moss Fred Anderson \n",
"13 307599 Kassulke, Ondricka and Metz Wendy Yule Fred Anderson \n",
"14 688981 Keeling LLC Wendy Yule Fred Anderson \n",
"15 729833 Koepp Ltd Wendy Yule Fred Anderson \n",
"16 729833 Koepp Ltd Wendy Yule Fred Anderson \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 \n",
"5 CPU 2 40000 pending \n",
"6 Software 1 10000 presented \n",
"7 Maintenance 2 5000 pending \n",
"8 CPU 1 35000 declined \n",
"9 CPU 2 65000 won \n",
"10 CPU 1 30000 presented \n",
"11 Maintenance 1 5000 pending \n",
"12 Software 1 10000 presented \n",
"13 Maintenance 3 7000 won \n",
"14 CPU 5 100000 won \n",
"15 CPU 2 65000 declined \n",
"16 Monitor 2 5000 presented "
]
},
"execution_count": 26,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [],
"source": [
"df[\"Status\"] = df[\"Status\"].astype(\"category\")\n",
"df[\"Status\"].cat.set_categories([\"won\",\"pending\",\"presented\",\"declined\"],inplace = True)"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 28,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Account</th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Name</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Barton LLC</th>\n",
" <td>740150</td>\n",
" <td>35000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Fritsch, Russel and Anderson</th>\n",
" <td>737550</td>\n",
" <td>35000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Herman LLC</th>\n",
" <td>141962</td>\n",
" <td>65000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Jerde-Hilpert</th>\n",
" <td>412290</td>\n",
" <td>5000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Kassulke, Ondricka and Metz</th>\n",
" <td>307599</td>\n",
" <td>7000</td>\n",
" <td>3.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Keeling LLC</th>\n",
" <td>688981</td>\n",
" <td>100000</td>\n",
" <td>5.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Kiehn-Spinka</th>\n",
" <td>146832</td>\n",
" <td>65000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Koepp Ltd</th>\n",
" <td>729833</td>\n",
" <td>35000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Kulas Inc</th>\n",
" <td>218895</td>\n",
" <td>25000</td>\n",
" <td>1.500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Purdy-Kunde</th>\n",
" <td>163416</td>\n",
" <td>30000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Stokes LLC</th>\n",
" <td>239344</td>\n",
" <td>7500</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Trantow-Barrows</th>\n",
" <td>714466</td>\n",
" <td>15000</td>\n",
" <td>1.333333</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Name\"])"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Account</th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Name</th>\n",
" <th>Rep</th>\n",
" <th>Manager</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>Barton LLC</th>\n",
" <th>John Smith</th>\n",
" <th>Debra Henley</th>\n",
" <td>740150</td>\n",
" <td>35000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Fritsch, Russel and Anderson</th>\n",
" <th>Craig Booker</th>\n",
" <th>Debra Henley</th>\n",
" <td>737550</td>\n",
" <td>35000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Herman LLC</th>\n",
" <th>Cedric Moss</th>\n",
" <th>Fred Anderson</th>\n",
" <td>141962</td>\n",
" <td>65000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Jerde-Hilpert</th>\n",
" <th>John Smith</th>\n",
" <th>Debra Henley</th>\n",
" <td>412290</td>\n",
" <td>5000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Kassulke, Ondricka and Metz</th>\n",
" <th>Wendy Yule</th>\n",
" <th>Fred Anderson</th>\n",
" <td>307599</td>\n",
" <td>7000</td>\n",
" <td>3.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Keeling LLC</th>\n",
" <th>Wendy Yule</th>\n",
" <th>Fred Anderson</th>\n",
" <td>688981</td>\n",
" <td>100000</td>\n",
" <td>5.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Kiehn-Spinka</th>\n",
" <th>Daniel Hilton</th>\n",
" <th>Debra Henley</th>\n",
" <td>146832</td>\n",
" <td>65000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Koepp Ltd</th>\n",
" <th>Wendy Yule</th>\n",
" <th>Fred Anderson</th>\n",
" <td>729833</td>\n",
" <td>35000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Kulas Inc</th>\n",
" <th>Daniel Hilton</th>\n",
" <th>Debra Henley</th>\n",
" <td>218895</td>\n",
" <td>25000</td>\n",
" <td>1.500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Purdy-Kunde</th>\n",
" <th>Cedric Moss</th>\n",
" <th>Fred Anderson</th>\n",
" <td>163416</td>\n",
" <td>30000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Stokes LLC</th>\n",
" <th>Cedric Moss</th>\n",
" <th>Fred Anderson</th>\n",
" <td>239344</td>\n",
" <td>7500</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Trantow-Barrows</th>\n",
" <th>Craig Booker</th>\n",
" <th>Debra Henley</th>\n",
" <td>714466</td>\n",
" <td>15000</td>\n",
" <td>1.333333</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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 "
]
},
"execution_count": 29,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#using multiple values as indexe\n",
"pd.pivot_table(df,index = [\"Name\",\"Rep\",\"Manager\"])"
]
},
{
"cell_type": "code",
"execution_count": 30,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>Account</th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>720237.0</td>\n",
" <td>20000.000000</td>\n",
" <td>1.250000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>194874.0</td>\n",
" <td>38333.333333</td>\n",
" <td>1.666667</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>576220.0</td>\n",
" <td>20000.000000</td>\n",
" <td>1.500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>196016.5</td>\n",
" <td>27500.000000</td>\n",
" <td>1.250000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>614061.5</td>\n",
" <td>44250.000000</td>\n",
" <td>3.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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"
]
},
"execution_count": 30,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"])\n",
"#in this case we can analyse how the manager is related to its representatives"
]
},
{
"cell_type": "code",
"execution_count": 31,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>20000.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>38333.333333</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>20000.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>27500.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>44250.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Price\n",
"Manager Rep \n",
"Debra Henley Craig Booker 20000.000000\n",
" Daniel Hilton 38333.333333\n",
" John Smith 20000.000000\n",
"Fred Anderson Cedric Moss 27500.000000\n",
" Wendy Yule 44250.000000"
]
},
"execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"],values = [\"Price\"])\n",
"#in case of values we can only mention the data that we wan in our table\n",
"#"
]
},
{
"cell_type": "code",
"execution_count": 32,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>80000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>115000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>40000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>110000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>177000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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"
]
},
"execution_count": 32,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"],values = [\"Price\"],aggfunc = np.sum)\n",
"#in this case ew are adding values for each representative wrt each manager\n"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead tr th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe thead tr:last-of-type th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th>mean</th>\n",
" <th>len</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" <th>Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>20000.000000</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>38333.333333</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>20000.000000</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>27500.000000</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>44250.000000</td>\n",
" <td>4</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" mean len\n",
" Price Price\n",
"Manager Rep \n",
"Debra Henley Craig Booker 20000.000000 4\n",
" Daniel Hilton 38333.333333 3\n",
" John Smith 20000.000000 2\n",
"Fred Anderson Cedric Moss 27500.000000 4\n",
" Wendy Yule 44250.000000 4"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"],values = [\"Price\"],aggfunc = [np.mean,len])"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead tr th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe thead tr:last-of-type th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th colspan=\"4\" halign=\"left\">sum</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th colspan=\"4\" halign=\"left\">Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Product</th>\n",
" <th>CPU</th>\n",
" <th>Maintenance</th>\n",
" <th>Monitor</th>\n",
" <th>Software</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>65000.0</td>\n",
" <td>5000.0</td>\n",
" <td>NaN</td>\n",
" <td>10000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>105000.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>10000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>35000.0</td>\n",
" <td>5000.0</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>95000.0</td>\n",
" <td>5000.0</td>\n",
" <td>NaN</td>\n",
" <td>10000.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>165000.0</td>\n",
" <td>7000.0</td>\n",
" <td>5000.0</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sum \n",
" Price \n",
"Product CPU Maintenance Monitor Software\n",
"Manager Rep \n",
"Debra Henley Craig Booker 65000.0 5000.0 NaN 10000.0\n",
" Daniel Hilton 105000.0 NaN NaN 10000.0\n",
" John Smith 35000.0 5000.0 NaN NaN\n",
"Fred Anderson Cedric Moss 95000.0 5000.0 NaN 10000.0\n",
" Wendy Yule 165000.0 7000.0 5000.0 NaN"
]
},
"execution_count": 34,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#if we want to see sales broken down by products we can use the column function\n",
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"],values = [\"Price\"],columns = [\"Product\"],aggfunc = [np.sum])"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>20000.000000</td>\n",
" <td>1.250000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>38333.333333</td>\n",
" <td>1.666667</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>20000.000000</td>\n",
" <td>1.500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>27500.000000</td>\n",
" <td>1.250000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>44250.000000</td>\n",
" <td>3.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Price Quantity\n",
"Manager Rep \n",
"Debra Henley Craig Booker 20000.000000 1.250000\n",
" Daniel Hilton 38333.333333 1.666667\n",
" John Smith 20000.000000 1.500000\n",
"Fred Anderson Cedric Moss 27500.000000 1.250000\n",
" Wendy Yule 44250.000000 3.000000"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"],values = [\"Price\",\"Quantity\"])"
]
},
{
"cell_type": "code",
"execution_count": 39,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead tr th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe thead tr:last-of-type th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th colspan=\"8\" halign=\"left\">sum</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th colspan=\"4\" halign=\"left\">Price</th>\n",
" <th colspan=\"4\" halign=\"left\">Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>Product</th>\n",
" <th>CPU</th>\n",
" <th>Maintenance</th>\n",
" <th>Monitor</th>\n",
" <th>Software</th>\n",
" <th>CPU</th>\n",
" <th>Maintenance</th>\n",
" <th>Monitor</th>\n",
" <th>Software</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Debra Henley</th>\n",
" <th>Craig Booker</th>\n",
" <td>65000</td>\n",
" <td>5000</td>\n",
" <td>0</td>\n",
" <td>10000</td>\n",
" <td>2</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Daniel Hilton</th>\n",
" <td>105000</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>10000</td>\n",
" <td>4</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>John Smith</th>\n",
" <td>35000</td>\n",
" <td>5000</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Fred Anderson</th>\n",
" <th>Cedric Moss</th>\n",
" <td>95000</td>\n",
" <td>5000</td>\n",
" <td>0</td>\n",
" <td>10000</td>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Wendy Yule</th>\n",
" <td>165000</td>\n",
" <td>7000</td>\n",
" <td>5000</td>\n",
" <td>0</td>\n",
" <td>7</td>\n",
" <td>3</td>\n",
" <td>2</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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 "
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\"],values = [\"Price\",\"Quantity\"],aggfunc = [np.sum],columns = [\"Product\"],fill_value = 0)"
]
},
{
"cell_type": "code",
"execution_count": 40,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead tr th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe thead tr:last-of-type th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th colspan=\"2\" halign=\"left\">sum</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th>Product</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
" <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
" <th>CPU</th>\n",
" <td>65000</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>5000</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Software</th>\n",
" <td>10000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
" <th>CPU</th>\n",
" <td>105000</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Software</th>\n",
" <td>10000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
" <th>CPU</th>\n",
" <td>35000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>5000</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
" <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
" <th>CPU</th>\n",
" <td>95000</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>5000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Software</th>\n",
" <td>10000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
" <th>CPU</th>\n",
" <td>165000</td>\n",
" <td>7</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>7000</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Monitor</th>\n",
" <td>5000</td>\n",
" <td>2</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" 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"
]
},
"execution_count": 40,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Rep\",\"Product\"],values = [\"Price\",\"Quantity\"],aggfunc = [np.sum],fill_value = 0)"
]
},
{
"cell_type": "code",
"execution_count": 42,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead tr th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe thead tr:last-of-type th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th colspan=\"2\" halign=\"left\">sum</th>\n",
" <th colspan=\"2\" halign=\"left\">mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Rep</th>\n",
" <th>Product</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"7\" valign=\"top\">Debra Henley</th>\n",
" <th rowspan=\"3\" valign=\"top\">Craig Booker</th>\n",
" <th>CPU</th>\n",
" <td>65000</td>\n",
" <td>2</td>\n",
" <td>32500</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>5000</td>\n",
" <td>2</td>\n",
" <td>5000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Software</th>\n",
" <td>10000</td>\n",
" <td>1</td>\n",
" <td>10000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">Daniel Hilton</th>\n",
" <th>CPU</th>\n",
" <td>105000</td>\n",
" <td>4</td>\n",
" <td>52500</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Software</th>\n",
" <td>10000</td>\n",
" <td>1</td>\n",
" <td>10000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">John Smith</th>\n",
" <th>CPU</th>\n",
" <td>35000</td>\n",
" <td>1</td>\n",
" <td>35000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>5000</td>\n",
" <td>2</td>\n",
" <td>5000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"6\" valign=\"top\">Fred Anderson</th>\n",
" <th rowspan=\"3\" valign=\"top\">Cedric Moss</th>\n",
" <th>CPU</th>\n",
" <td>95000</td>\n",
" <td>3</td>\n",
" <td>47500</td>\n",
" <td>1.500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>5000</td>\n",
" <td>1</td>\n",
" <td>5000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Software</th>\n",
" <td>10000</td>\n",
" <td>1</td>\n",
" <td>10000</td>\n",
" <td>1.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">Wendy Yule</th>\n",
" <th>CPU</th>\n",
" <td>165000</td>\n",
" <td>7</td>\n",
" <td>82500</td>\n",
" <td>3.500000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Maintenance</th>\n",
" <td>7000</td>\n",
" <td>3</td>\n",
" <td>7000</td>\n",
" <td>3.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Monitor</th>\n",
" <td>5000</td>\n",
" <td>2</td>\n",
" <td>5000</td>\n",
" <td>2.000000</td>\n",
" </tr>\n",
" <tr>\n",
" <th>All</th>\n",
" <th></th>\n",
" <th></th>\n",
" <td>522000</td>\n",
" <td>30</td>\n",
" <td>30705</td>\n",
" <td>1.764706</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sum mean \n",
" Price Quantity Price Quantity\n",
"Manager Rep Product \n",
"Debra Henley Craig Booker CPU 65000 2 32500 1.000000\n",
" Maintenance 5000 2 5000 2.000000\n",
" Software 10000 1 10000 1.000000\n",
" Daniel Hilton CPU 105000 4 52500 2.000000\n",
" Software 10000 1 10000 1.000000\n",
" John Smith CPU 35000 1 35000 1.000000\n",
" Maintenance 5000 2 5000 2.000000\n",
"Fred Anderson Cedric Moss CPU 95000 3 47500 1.500000\n",
" Maintenance 5000 1 5000 1.000000\n",
" Software 10000 1 10000 1.000000\n",
" Wendy Yule CPU 165000 7 82500 3.500000\n",
" Maintenance 7000 3 7000 3.000000\n",
" Monitor 5000 2 5000 2.000000\n",
"All 522000 30 30705 1.764706"
]
},
"execution_count": 42,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"#to get totals in the data set we use function margins = True\n",
"pd.pivot_table(df,index = [\"Manager\",\"Rep\",\"Product\"],values = [\"Price\",\"Quantity\"],aggfunc = [np.sum,np.mean],fill_value = 0,margins = True)"
]
},
{
"cell_type": "code",
"execution_count": 45,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Manager</th>\n",
" <th>Status</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"4\" valign=\"top\">Debra Henley</th>\n",
" <th>won</th>\n",
" <td>65000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>pending</th>\n",
" <td>50000</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>presented</th>\n",
" <td>50000</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>declined</th>\n",
" <td>70000</td>\n",
" <td>2</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"4\" valign=\"top\">Fred Anderson</th>\n",
" <th>won</th>\n",
" <td>172000</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>pending</th>\n",
" <td>5000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>presented</th>\n",
" <td>45000</td>\n",
" <td>3</td>\n",
" </tr>\n",
" <tr>\n",
" <th>declined</th>\n",
" <td>65000</td>\n",
" <td>1</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Price Quantity\n",
"Manager Status \n",
"Debra Henley won 65000 1\n",
" pending 50000 3\n",
" presented 50000 3\n",
" declined 70000 2\n",
"Fred Anderson won 172000 3\n",
" pending 5000 1\n",
" presented 45000 3\n",
" declined 65000 1"
]
},
"execution_count": 45,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"pd.pivot_table(df,index = [\"Manager\",\"Status\"],columns = [\"Product\"],values = )"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python",
"language": "python",
"name": "conda-env-python-py"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.6.7"
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment