PB Python Excel Filter and Edit Example

Full article is posted here - http://pbpython.com/excel-filter-edit.html

In [1]:
import pandas as pd
df = pd.read_excel("https://github.com/chris1610/pbpython/blob/master/data/sample-sales-reps.xlsx?raw=true")
df.head()
Out[1]:
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

In [2]:
df["commission"] = .02
df.head()
Out[2]:
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%

In [3]:
df.loc[df["category"] == "Shirt", ["commission"]] = .025
df.head()
Out[3]:
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!

In [4]:
df.loc[(df["category"] == "Belt") & (df["quantity"] >= 10), ["commission"]] = .04
df.head()
Out[4]:
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.

In [5]:
df["bonus"] = 0
df.loc[(df["category"] == "Shoes") & (df["ext price"] >= 1000 ), ["bonus", "commission"]] = 250, 0.045
In [6]:
df.ix[3:7]
Out[6]:
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

In [7]:
df["comp"] = df["commission"] * df["ext price"] + df["bonus"]
df.head()
Out[7]:
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

In [8]:
df.groupby(["sales rep"])["comp"].sum().round(2)
Out[8]:
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