#!/usr/bin/env python
# coding: utf-8

# # 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()


# Add a default commision rate of 2% for all sales

# In[2]:


df["commission"] = .02
df.head()


# 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()


# 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()


# 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]


# Calculate the compensation at the line item leve

# In[7]:


df["comp"] = df["commission"] * df["ext price"] + df["bonus"]
df.head()


# Calculate the commissions by sales rep

# In[8]:


df.groupby(["sales rep"])["comp"].sum().round(2)