{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# PB Python Excel Filter and Edit Example "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Full article is posted here - http://pbpython.com/excel-filter-edit.html"
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" customer name | \n",
" sales rep | \n",
" sku | \n",
" category | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" GP-14407 | \n",
" Belt | \n",
" 19 | \n",
" 88.49 | \n",
" 1681.31 | \n",
" 2015-11-17 05:58:34 | \n",
"
\n",
" \n",
" 1 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" FI-01804 | \n",
" Shirt | \n",
" 3 | \n",
" 78.07 | \n",
" 234.21 | \n",
" 2016-02-13 04:04:11 | \n",
"
\n",
" \n",
" 2 | \n",
" 530925 | \n",
" Purdy and Sons | \n",
" Teagan O'Keefe | \n",
" EO-54210 | \n",
" Shirt | \n",
" 19 | \n",
" 30.21 | \n",
" 573.99 | \n",
" 2015-08-11 12:44:38 | \n",
"
\n",
" \n",
" 3 | \n",
" 14406 | \n",
" Harber, Lubowitz and Fahey | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 12 | \n",
" 90.29 | \n",
" 1083.48 | \n",
" 2016-01-23 02:15:50 | \n",
"
\n",
" \n",
" 4 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 5 | \n",
" 72.64 | \n",
" 363.20 | \n",
" 2015-08-10 07:16:03 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number customer name sales rep sku \\\n",
"0 680916 Mueller and Sons Loring Predovic GP-14407 \n",
"1 680916 Mueller and Sons Loring Predovic FI-01804 \n",
"2 530925 Purdy and Sons Teagan O'Keefe EO-54210 \n",
"3 14406 Harber, Lubowitz and Fahey Esequiel Schinner NZ-99565 \n",
"4 398620 Brekke Ltd Esequiel Schinner NZ-99565 \n",
"\n",
" category quantity unit price ext price date \n",
"0 Belt 19 88.49 1681.31 2015-11-17 05:58:34 \n",
"1 Shirt 3 78.07 234.21 2016-02-13 04:04:11 \n",
"2 Shirt 19 30.21 573.99 2015-08-11 12:44:38 \n",
"3 Shirt 12 90.29 1083.48 2016-01-23 02:15:50 \n",
"4 Shirt 5 72.64 363.20 2015-08-10 07:16:03 "
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"import pandas as pd\n",
"df = pd.read_excel(\"https://github.com/chris1610/pbpython/blob/master/data/sample-sales-reps.xlsx?raw=true\")\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Add a default commision rate of 2% for all sales"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" customer name | \n",
" sales rep | \n",
" sku | \n",
" category | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
" commission | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" GP-14407 | \n",
" Belt | \n",
" 19 | \n",
" 88.49 | \n",
" 1681.31 | \n",
" 2015-11-17 05:58:34 | \n",
" 0.02 | \n",
"
\n",
" \n",
" 1 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" FI-01804 | \n",
" Shirt | \n",
" 3 | \n",
" 78.07 | \n",
" 234.21 | \n",
" 2016-02-13 04:04:11 | \n",
" 0.02 | \n",
"
\n",
" \n",
" 2 | \n",
" 530925 | \n",
" Purdy and Sons | \n",
" Teagan O'Keefe | \n",
" EO-54210 | \n",
" Shirt | \n",
" 19 | \n",
" 30.21 | \n",
" 573.99 | \n",
" 2015-08-11 12:44:38 | \n",
" 0.02 | \n",
"
\n",
" \n",
" 3 | \n",
" 14406 | \n",
" Harber, Lubowitz and Fahey | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 12 | \n",
" 90.29 | \n",
" 1083.48 | \n",
" 2016-01-23 02:15:50 | \n",
" 0.02 | \n",
"
\n",
" \n",
" 4 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 5 | \n",
" 72.64 | \n",
" 363.20 | \n",
" 2015-08-10 07:16:03 | \n",
" 0.02 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number customer name sales rep sku \\\n",
"0 680916 Mueller and Sons Loring Predovic GP-14407 \n",
"1 680916 Mueller and Sons Loring Predovic FI-01804 \n",
"2 530925 Purdy and Sons Teagan O'Keefe EO-54210 \n",
"3 14406 Harber, Lubowitz and Fahey Esequiel Schinner NZ-99565 \n",
"4 398620 Brekke Ltd Esequiel Schinner NZ-99565 \n",
"\n",
" category quantity unit price ext price date commission \n",
"0 Belt 19 88.49 1681.31 2015-11-17 05:58:34 0.02 \n",
"1 Shirt 3 78.07 234.21 2016-02-13 04:04:11 0.02 \n",
"2 Shirt 19 30.21 573.99 2015-08-11 12:44:38 0.02 \n",
"3 Shirt 12 90.29 1083.48 2016-01-23 02:15:50 0.02 \n",
"4 Shirt 5 72.64 363.20 2015-08-10 07:16:03 0.02 "
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[\"commission\"] = .02\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Since shirts are high margin, adjust all products in the shirt categort with a commission rate of 2.5%"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" customer name | \n",
" sales rep | \n",
" sku | \n",
" category | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
" commission | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" GP-14407 | \n",
" Belt | \n",
" 19 | \n",
" 88.49 | \n",
" 1681.31 | \n",
" 2015-11-17 05:58:34 | \n",
" 0.020 | \n",
"
\n",
" \n",
" 1 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" FI-01804 | \n",
" Shirt | \n",
" 3 | \n",
" 78.07 | \n",
" 234.21 | \n",
" 2016-02-13 04:04:11 | \n",
" 0.025 | \n",
"
\n",
" \n",
" 2 | \n",
" 530925 | \n",
" Purdy and Sons | \n",
" Teagan O'Keefe | \n",
" EO-54210 | \n",
" Shirt | \n",
" 19 | \n",
" 30.21 | \n",
" 573.99 | \n",
" 2015-08-11 12:44:38 | \n",
" 0.025 | \n",
"
\n",
" \n",
" 3 | \n",
" 14406 | \n",
" Harber, Lubowitz and Fahey | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 12 | \n",
" 90.29 | \n",
" 1083.48 | \n",
" 2016-01-23 02:15:50 | \n",
" 0.025 | \n",
"
\n",
" \n",
" 4 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 5 | \n",
" 72.64 | \n",
" 363.20 | \n",
" 2015-08-10 07:16:03 | \n",
" 0.025 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number customer name sales rep sku \\\n",
"0 680916 Mueller and Sons Loring Predovic GP-14407 \n",
"1 680916 Mueller and Sons Loring Predovic FI-01804 \n",
"2 530925 Purdy and Sons Teagan O'Keefe EO-54210 \n",
"3 14406 Harber, Lubowitz and Fahey Esequiel Schinner NZ-99565 \n",
"4 398620 Brekke Ltd Esequiel Schinner NZ-99565 \n",
"\n",
" category quantity unit price ext price date commission \n",
"0 Belt 19 88.49 1681.31 2015-11-17 05:58:34 0.020 \n",
"1 Shirt 3 78.07 234.21 2016-02-13 04:04:11 0.025 \n",
"2 Shirt 19 30.21 573.99 2015-08-11 12:44:38 0.025 \n",
"3 Shirt 12 90.29 1083.48 2016-01-23 02:15:50 0.025 \n",
"4 Shirt 5 72.64 363.20 2015-08-10 07:16:03 0.025 "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.loc[df[\"category\"] == \"Shirt\", [\"commission\"]] = .025\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Since there is a special program for selling 10 or more belts in a transaction, you get 4% commission!"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" customer name | \n",
" sales rep | \n",
" sku | \n",
" category | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
" commission | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" GP-14407 | \n",
" Belt | \n",
" 19 | \n",
" 88.49 | \n",
" 1681.31 | \n",
" 2015-11-17 05:58:34 | \n",
" 0.040 | \n",
"
\n",
" \n",
" 1 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" FI-01804 | \n",
" Shirt | \n",
" 3 | \n",
" 78.07 | \n",
" 234.21 | \n",
" 2016-02-13 04:04:11 | \n",
" 0.025 | \n",
"
\n",
" \n",
" 2 | \n",
" 530925 | \n",
" Purdy and Sons | \n",
" Teagan O'Keefe | \n",
" EO-54210 | \n",
" Shirt | \n",
" 19 | \n",
" 30.21 | \n",
" 573.99 | \n",
" 2015-08-11 12:44:38 | \n",
" 0.025 | \n",
"
\n",
" \n",
" 3 | \n",
" 14406 | \n",
" Harber, Lubowitz and Fahey | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 12 | \n",
" 90.29 | \n",
" 1083.48 | \n",
" 2016-01-23 02:15:50 | \n",
" 0.025 | \n",
"
\n",
" \n",
" 4 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 5 | \n",
" 72.64 | \n",
" 363.20 | \n",
" 2015-08-10 07:16:03 | \n",
" 0.025 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number customer name sales rep sku \\\n",
"0 680916 Mueller and Sons Loring Predovic GP-14407 \n",
"1 680916 Mueller and Sons Loring Predovic FI-01804 \n",
"2 530925 Purdy and Sons Teagan O'Keefe EO-54210 \n",
"3 14406 Harber, Lubowitz and Fahey Esequiel Schinner NZ-99565 \n",
"4 398620 Brekke Ltd Esequiel Schinner NZ-99565 \n",
"\n",
" category quantity unit price ext price date commission \n",
"0 Belt 19 88.49 1681.31 2015-11-17 05:58:34 0.040 \n",
"1 Shirt 3 78.07 234.21 2016-02-13 04:04:11 0.025 \n",
"2 Shirt 19 30.21 573.99 2015-08-11 12:44:38 0.025 \n",
"3 Shirt 12 90.29 1083.48 2016-01-23 02:15:50 0.025 \n",
"4 Shirt 5 72.64 363.20 2015-08-10 07:16:03 0.025 "
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.loc[(df[\"category\"] == \"Belt\") & (df[\"quantity\"] >= 10), [\"commission\"]] = .04\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Finally, some transactions can get a bonus and a commission increase."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"collapsed": false
},
"outputs": [],
"source": [
"df[\"bonus\"] = 0\n",
"df.loc[(df[\"category\"] == \"Shoes\") & (df[\"ext price\"] >= 1000 ), [\"bonus\", \"commission\"]] = 250, 0.045"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" customer name | \n",
" sales rep | \n",
" sku | \n",
" category | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
" commission | \n",
" bonus | \n",
"
\n",
" \n",
" \n",
" \n",
" 3 | \n",
" 14406 | \n",
" Harber, Lubowitz and Fahey | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 12 | \n",
" 90.29 | \n",
" 1083.48 | \n",
" 2016-01-23 02:15:50 | \n",
" 0.025 | \n",
" 0 | \n",
"
\n",
" \n",
" 4 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 5 | \n",
" 72.64 | \n",
" 363.20 | \n",
" 2015-08-10 07:16:03 | \n",
" 0.025 | \n",
" 0 | \n",
"
\n",
" \n",
" 5 | \n",
" 282122 | \n",
" Connelly, Abshire and Von | \n",
" Beth Skiles | \n",
" GJ-90272 | \n",
" Shoes | \n",
" 20 | \n",
" 96.62 | \n",
" 1932.40 | \n",
" 2016-03-17 10:19:05 | \n",
" 0.045 | \n",
" 250 | \n",
"
\n",
" \n",
" 6 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" DU-87462 | \n",
" Shirt | \n",
" 10 | \n",
" 67.64 | \n",
" 676.40 | \n",
" 2015-11-25 22:05:36 | \n",
" 0.025 | \n",
" 0 | \n",
"
\n",
" \n",
" 7 | \n",
" 218667 | \n",
" Jaskolski-O'Hara | \n",
" Trish Deckow | \n",
" DU-87462 | \n",
" Shirt | \n",
" 11 | \n",
" 91.86 | \n",
" 1010.46 | \n",
" 2016-04-24 15:05:58 | \n",
" 0.025 | \n",
" 0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number customer name sales rep sku \\\n",
"3 14406 Harber, Lubowitz and Fahey Esequiel Schinner NZ-99565 \n",
"4 398620 Brekke Ltd Esequiel Schinner NZ-99565 \n",
"5 282122 Connelly, Abshire and Von Beth Skiles GJ-90272 \n",
"6 398620 Brekke Ltd Esequiel Schinner DU-87462 \n",
"7 218667 Jaskolski-O'Hara Trish Deckow DU-87462 \n",
"\n",
" category quantity unit price ext price date commission \\\n",
"3 Shirt 12 90.29 1083.48 2016-01-23 02:15:50 0.025 \n",
"4 Shirt 5 72.64 363.20 2015-08-10 07:16:03 0.025 \n",
"5 Shoes 20 96.62 1932.40 2016-03-17 10:19:05 0.045 \n",
"6 Shirt 10 67.64 676.40 2015-11-25 22:05:36 0.025 \n",
"7 Shirt 11 91.86 1010.46 2016-04-24 15:05:58 0.025 \n",
"\n",
" bonus \n",
"3 0 \n",
"4 0 \n",
"5 250 \n",
"6 0 \n",
"7 0 "
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.ix[3:7]"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Calculate the compensation at the line item leve"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/html": [
"\n",
"
\n",
" \n",
" \n",
" | \n",
" account number | \n",
" customer name | \n",
" sales rep | \n",
" sku | \n",
" category | \n",
" quantity | \n",
" unit price | \n",
" ext price | \n",
" date | \n",
" commission | \n",
" bonus | \n",
" comp | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" GP-14407 | \n",
" Belt | \n",
" 19 | \n",
" 88.49 | \n",
" 1681.31 | \n",
" 2015-11-17 05:58:34 | \n",
" 0.040 | \n",
" 0 | \n",
" 67.25240 | \n",
"
\n",
" \n",
" 1 | \n",
" 680916 | \n",
" Mueller and Sons | \n",
" Loring Predovic | \n",
" FI-01804 | \n",
" Shirt | \n",
" 3 | \n",
" 78.07 | \n",
" 234.21 | \n",
" 2016-02-13 04:04:11 | \n",
" 0.025 | \n",
" 0 | \n",
" 5.85525 | \n",
"
\n",
" \n",
" 2 | \n",
" 530925 | \n",
" Purdy and Sons | \n",
" Teagan O'Keefe | \n",
" EO-54210 | \n",
" Shirt | \n",
" 19 | \n",
" 30.21 | \n",
" 573.99 | \n",
" 2015-08-11 12:44:38 | \n",
" 0.025 | \n",
" 0 | \n",
" 14.34975 | \n",
"
\n",
" \n",
" 3 | \n",
" 14406 | \n",
" Harber, Lubowitz and Fahey | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 12 | \n",
" 90.29 | \n",
" 1083.48 | \n",
" 2016-01-23 02:15:50 | \n",
" 0.025 | \n",
" 0 | \n",
" 27.08700 | \n",
"
\n",
" \n",
" 4 | \n",
" 398620 | \n",
" Brekke Ltd | \n",
" Esequiel Schinner | \n",
" NZ-99565 | \n",
" Shirt | \n",
" 5 | \n",
" 72.64 | \n",
" 363.20 | \n",
" 2015-08-10 07:16:03 | \n",
" 0.025 | \n",
" 0 | \n",
" 9.08000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" account number customer name sales rep sku \\\n",
"0 680916 Mueller and Sons Loring Predovic GP-14407 \n",
"1 680916 Mueller and Sons Loring Predovic FI-01804 \n",
"2 530925 Purdy and Sons Teagan O'Keefe EO-54210 \n",
"3 14406 Harber, Lubowitz and Fahey Esequiel Schinner NZ-99565 \n",
"4 398620 Brekke Ltd Esequiel Schinner NZ-99565 \n",
"\n",
" category quantity unit price ext price date commission \\\n",
"0 Belt 19 88.49 1681.31 2015-11-17 05:58:34 0.040 \n",
"1 Shirt 3 78.07 234.21 2016-02-13 04:04:11 0.025 \n",
"2 Shirt 19 30.21 573.99 2015-08-11 12:44:38 0.025 \n",
"3 Shirt 12 90.29 1083.48 2016-01-23 02:15:50 0.025 \n",
"4 Shirt 5 72.64 363.20 2015-08-10 07:16:03 0.025 \n",
"\n",
" bonus comp \n",
"0 0 67.25240 \n",
"1 0 5.85525 \n",
"2 0 14.34975 \n",
"3 0 27.08700 \n",
"4 0 9.08000 "
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[\"comp\"] = df[\"commission\"] * df[\"ext price\"] + df[\"bonus\"]\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Calculate the commissions by sales rep"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": false
},
"outputs": [
{
"data": {
"text/plain": [
"sales rep\n",
"Ansley Cummings 2169.76\n",
"Beth Skiles 3028.60\n",
"Esequiel Schinner 10451.21\n",
"Loring Predovic 10108.60\n",
"Shannen Hudson 5275.66\n",
"Teagan O'Keefe 7989.52\n",
"Trish Deckow 5807.74\n",
"Name: comp, dtype: float64"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.groupby([\"sales rep\"])[\"comp\"].sum().round(2)"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"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.5.1"
}
},
"nbformat": 4,
"nbformat_minor": 0
}