# 9.10:  Custom 'summary' function that refers to other columns (.groupby() only).  Building on the
# previous solution, customize the function to sum SaleAmount but reduce 'CPU' values by 20% in the
# sum (because of the overhead cost of handling hardware).

import pandas as pd

def price_sum(this_df):
    this_df.loc[ this_df.Product.isin(['CPU', 'Monitor']),
                                      'SaleAmount' ] = this_df.SaleAmount * .8
    return this_df.SaleAmount.sum()

df = pd.read_excel("../sales-funnel.xlsx")

dfgb = df.groupby('SalesRep').apply(price_sum)

dfgb

# Since .pivot_table() only passes the Series of the column being aggregated, a custom aggregation
# cannot take into account the values in other columns.  (In multi-column aggregations using
# .pivot_table(), the aggfunc= function is applied separately to each column).

