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