{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbercustomer namesales repskucategoryquantityunit priceext pricedate
0680916Mueller and SonsLoring PredovicGP-14407Belt1988.491681.312015-11-17 05:58:34
1680916Mueller and SonsLoring PredovicFI-01804Shirt378.07234.212016-02-13 04:04:11
2530925Purdy and SonsTeagan O'KeefeEO-54210Shirt1930.21573.992015-08-11 12:44:38
314406Harber, Lubowitz and FaheyEsequiel SchinnerNZ-99565Shirt1290.291083.482016-01-23 02:15:50
4398620Brekke LtdEsequiel SchinnerNZ-99565Shirt572.64363.202015-08-10 07:16:03
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbercustomer namesales repskucategoryquantityunit priceext pricedatecommission
0680916Mueller and SonsLoring PredovicGP-14407Belt1988.491681.312015-11-17 05:58:340.02
1680916Mueller and SonsLoring PredovicFI-01804Shirt378.07234.212016-02-13 04:04:110.02
2530925Purdy and SonsTeagan O'KeefeEO-54210Shirt1930.21573.992015-08-11 12:44:380.02
314406Harber, Lubowitz and FaheyEsequiel SchinnerNZ-99565Shirt1290.291083.482016-01-23 02:15:500.02
4398620Brekke LtdEsequiel SchinnerNZ-99565Shirt572.64363.202015-08-10 07:16:030.02
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbercustomer namesales repskucategoryquantityunit priceext pricedatecommission
0680916Mueller and SonsLoring PredovicGP-14407Belt1988.491681.312015-11-17 05:58:340.020
1680916Mueller and SonsLoring PredovicFI-01804Shirt378.07234.212016-02-13 04:04:110.025
2530925Purdy and SonsTeagan O'KeefeEO-54210Shirt1930.21573.992015-08-11 12:44:380.025
314406Harber, Lubowitz and FaheyEsequiel SchinnerNZ-99565Shirt1290.291083.482016-01-23 02:15:500.025
4398620Brekke LtdEsequiel SchinnerNZ-99565Shirt572.64363.202015-08-10 07:16:030.025
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbercustomer namesales repskucategoryquantityunit priceext pricedatecommission
0680916Mueller and SonsLoring PredovicGP-14407Belt1988.491681.312015-11-17 05:58:340.040
1680916Mueller and SonsLoring PredovicFI-01804Shirt378.07234.212016-02-13 04:04:110.025
2530925Purdy and SonsTeagan O'KeefeEO-54210Shirt1930.21573.992015-08-11 12:44:380.025
314406Harber, Lubowitz and FaheyEsequiel SchinnerNZ-99565Shirt1290.291083.482016-01-23 02:15:500.025
4398620Brekke LtdEsequiel SchinnerNZ-99565Shirt572.64363.202015-08-10 07:16:030.025
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbercustomer namesales repskucategoryquantityunit priceext pricedatecommissionbonus
314406Harber, Lubowitz and FaheyEsequiel SchinnerNZ-99565Shirt1290.291083.482016-01-23 02:15:500.0250
4398620Brekke LtdEsequiel SchinnerNZ-99565Shirt572.64363.202015-08-10 07:16:030.0250
5282122Connelly, Abshire and VonBeth SkilesGJ-90272Shoes2096.621932.402016-03-17 10:19:050.045250
6398620Brekke LtdEsequiel SchinnerDU-87462Shirt1067.64676.402015-11-25 22:05:360.0250
7218667Jaskolski-O'HaraTrish DeckowDU-87462Shirt1191.861010.462016-04-24 15:05:580.0250
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
account numbercustomer namesales repskucategoryquantityunit priceext pricedatecommissionbonuscomp
0680916Mueller and SonsLoring PredovicGP-14407Belt1988.491681.312015-11-17 05:58:340.040067.25240
1680916Mueller and SonsLoring PredovicFI-01804Shirt378.07234.212016-02-13 04:04:110.02505.85525
2530925Purdy and SonsTeagan O'KeefeEO-54210Shirt1930.21573.992015-08-11 12:44:380.025014.34975
314406Harber, Lubowitz and FaheyEsequiel SchinnerNZ-99565Shirt1290.291083.482016-01-23 02:15:500.025027.08700
4398620Brekke LtdEsequiel SchinnerNZ-99565Shirt572.64363.202015-08-10 07:16:030.02509.08000
\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 }