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]:
Add a default commision rate of 2% for all sales
In [2]:
df["commission"] = .02
df.head()
Out[2]:
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]:
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]:
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]:
Calculate the compensation at the line item leve
In [7]:
df["comp"] = df["commission"] * df["ext price"] + df["bonus"]
df.head()
Out[7]:
Calculate the commissions by sales rep
In [8]:
df.groupby(["sales rep"])["comp"].sum().round(2)
Out[8]: