import pandas
df_hist = pandas.read_excel("soda_sales_historical_data.xlsx")
df_hist[:5]

df_hist[df_hist["Product"]=="11 Down"]

from pandas import DataFrame, get_dummies
categorical_columns = ['Product','Easter Included','Super Bowl Included', 
                       'Christmas Included', 'Other Holiday']
df_hist = get_dummies(df_hist, prefix={k:"dmy_%s"%k for k in categorical_columns},
                      columns = list(categorical_columns))
df_hist[:5]

from sklearn.tree import DecisionTreeRegressor
from sklearn.ensemble import RandomForestRegressor
from sklearn.linear_model import LinearRegression
from sklearn.ensemble import BaggingRegressor
from sklearn import model_selection
experiments = {"Algorithm":["Ordinary Least Squares", "Regression Tree", 
                            "Big Random Forest", "Random Forest", 
                            "Bagging"], 
               "Objects" : [lambda : LinearRegression(), 
                            lambda : DecisionTreeRegressor(), 
                            lambda : RandomForestRegressor(n_estimators=100), 
                            lambda : RandomForestRegressor(), 
                            lambda : BaggingRegressor()], 
               "Predictions":[[] for _ in range(5)]}
actuals = []

from sklearn.model_selection import train_test_split
for _ in range (4):
    train_X, test_X, train_y, test_y = (
        train_test_split(df_hist.drop("Sales", axis=1), 
                         df_hist["Sales"], test_size=0.25))
    for i, obj_factory in enumerate(experiments["Objects"]):
        obj = obj_factory()
        obj.fit(y=train_y,X=train_X)
        experiments["Predictions"][i] += list(obj.predict(test_X))
    actuals += list(test_y)
actuals = pandas.Series(actuals)
experiments["Predictions"] = list(map(pandas.Series, experiments["Predictions"]))

experiments["Results"] = []
for o in experiments["Objects"]:
    experiments["Results"].append(
        model_selection.cross_val_score(o(), y=df_hist['Sales'], 
                                        X=df_hist.drop("Sales", axis=1),
                                        cv=5).mean())
DataFrame(experiments).drop(["Objects", "Predictions"], 
                            axis=1).set_index("Algorithm")

fitted = (experiments["Objects"]
          [experiments["Algorithm"].index("Big Random Forest")]().
          fit(y=df_hist["Sales"], X=df_hist.drop("Sales", axis=1)))

df_superbowl_original = pandas.read_excel("super_bowl_promotion_data.xlsx")
df_superbowl = get_dummies(df_superbowl_original, 
                           prefix={k:"dmy_%s"%k for k in categorical_columns},
                           columns = list(categorical_columns))
assert "Sales" not in df_superbowl.columns 
assert {"Sales"}.union(df_superbowl.columns).issubset(set(df_hist.columns))
len(df_superbowl)

for fld in set(df_hist.columns).difference(df_superbowl.columns, {"Sales"}):
    assert fld.startswith("dmy_")
    df_superbowl[fld] = 0
    
df_superbowl = df_superbowl[list(df_hist.drop("Sales", axis=1).columns)]

predicted = fitted.predict(df_superbowl)

from __future__ import print_function
from ortools.constraint_solver import pywrapcp as cp

soda_family = {'11 Down': 'Clear', 'AB Root Beer': 'Dark', 
               'Alpine Stream': 'Clear', 'Bright': 'Clear', 
               'Crisp Clear': 'Clear', 'DC Kola': 'Dark',
               'Koala Kola': 'Dark', 'Mr. Popper': 'Dark', 
               'Popsi Kola': 'Dark'}
family  = set(soda_family[j] for j in soda_family)
soda    = set(j for j in soda_family)
max_prom = {f:2 for f in family}
product_prices = set(forecast_sales.index.values)
normal_price = {b:0 for b in soda}
for b,p in product_prices:
    normal_price[b] = max(normal_price[b],p)

forecast_sales = df_superbowl_original[["Product", "Cost Per Unit"]].copy()
forecast_sales["Sales"] = predicted

forecast_sales = forecast_sales.groupby(['Product','Cost Per Unit']).mean()

normal_price_2 = dict(forecast_sales.reset_index()[["Product","Cost Per Unit"]].groupby("Product").max()["Cost Per Unit"])



maxer = forecast_sales.reset_index().groupby("Product").max()


## This is to get the sales value of the max cost

maxer["Sales"] = forecast_sales[forecast_sales.index.isin(maxer.reset_index().set_index(["Product","Cost Per Unit"]).index)]["Sales"].values

maxer

import pandas as pd
forecast_sales_r =forecast_sales.reset_index()
bat = pd.DataFrame()
for product in maxer.index:
    rat = forecast_sales_r[forecast_sales_r["Product"]==product]["Cost Per Unit"]-maxer.loc[product,"Cost Per Unit"]
    rat = rat.to_frame()
    bat = pd.concat((bat,rat))

forecast_sales_r["marginal_investment"] =  bat.values

import pandas as pd

bat = pd.DataFrame()
for product in maxer.index:
    rat = forecast_sales_r[forecast_sales_r["Product"]==product]["Sales"]-maxer.loc[product,"Sales"]
    rat = rat.to_frame()
    bat = pd.concat((bat,rat))
forecast_sales_r["extra_sales"] =  bat.values

forecast_sales_r

for j in range(num_tasks):
    ## This in effect sets constraints on the domain
    t.append(solver.IntVar(0, 1, "x[%i,%i]" % (i, j)))

forecast_sales = forecast_sales.reset_index()

forecast_sales

from ortools.constraint_solver import pywrapcp as cp


solver = cp.Solver("nothing_fancy")

x_array = []

"""x_array["Product", "Price"]"""

x_product = []
for i, product in enumerate(forecast_sales_r.drop_duplicates("Product")["Product"].values):
    t = []
    for j, price in enumerate(forecast_sales_r[forecast_sales_r["Product"]==product]["Product"].values):
        ## This in effect sets constraints on the domain
        t.append(solver.IntVar(0, 1, "x[%i,%i]" % (i, j)))
    x_array.extend(t)
    x_product.append(t)

## More Specific Keys
x_array = []

"""x_array["Product", "Price"]"""

x_product = []
for i, product in enumerate(forecast_sales_r.drop_duplicates("Product")["Product"].values):
    t = []
    for j, price in enumerate(forecast_sales_r[forecast_sales_r["Product"]==product]["Cost Per Unit"].values):
        ## This in effect sets constraints on the domain
        t.append(solver.IntVar(0, 1, "x[%s,%s]" % (product, price)))
    x_array.extend(t) ## produces a flat array
    x_product.append(t)

x_product

forecast_sales_r["Extra Sales Dollars"] = forecast_sales_r["Cost Per Unit"]*forecast_sales_r["extra_sales"]

forecast_sales_r["Investment Dollars"] = forecast_sales_r["marginal_investment"]*forecast_sales_r["Sales"]

## I would change it to say largest sales dollars increase
## and then compare that against the investment 
## UNFORTUNATTELY - I Turned this into a knapsack problem 

forecast_sales_r["Investment Dollars"] = forecast_sales_r["Investment Dollars"].abs()

forecast_sales_r.head()

weights = []
weight_1 = list(forecast_sales_r["Investment Dollars"].values)

weights.append(weight_1)

capacities = [350]
values = list(forecast_sales_r["Extra Sales Dollars"].values)

from ortools.algorithms import pywrapknapsack_solver

# Create the solver.
solver = pywrapknapsack_solver.KnapsackSolver(
  pywrapknapsack_solver.KnapsackSolver.
  KNAPSACK_DYNAMIC_PROGRAMMING_SOLVER,
  'test')

solver.Init(values, weights, capacities)
computed_value = solver.Solve()

packed_items = [x for x in range(0, len(weights[0]))
                if solver.BestSolutionContains(x)]
packed_weights = [weights[0][i] for i in packed_items]

print("Packed items: ", packed_items)
print("Packed weights: ", packed_weights)
print("Total weight (same as total value): ", computed_value)

packed_items = [x for x in range(0, len(weights[0]))
                  if solver.BestSolutionContains(x)]

## This dataframe is the best selection, using the knapsack problem.
## Different costs differnt values 
## The knapsack problem does appear i a lot of places 

forecast_sales_r[forecast_sales_r.index.isin(packed_items)]

total_cost = solver.IntVar(0, 300, "total_cost")

solver.Add(total_cost <= 300)

decision_builder = solver.Phase([x, y],
                                  solver.CHOOSE_FIRST_UNBOUND,
                                  solver.ASSIGN_MIN_VALUE)

# Total cost
solver.Add(
  total_cost < solver.Sum(solver.ScalProd(x_array, forecast_sales_r["marginal_investment"].values)))
objective = solver.Minimize(total_cost, 1)
db = solver.Phase(x_array,
                solver.CHOOSE_FIRST_UNBOUND, # The VAR strategy
                solver.ASSIGN_MIN_VALUE)     # The Value strategy

# Create a solution collector.
collector = solver.LastSolutionCollector()
# Add decision variables
#collector.Add(x_array)

for i in range(num_workers):
    collector.Add(x_worker[i])

# Total cost
solver.Add(
  total_cost == solver.Sum( [solver.ScalProd(x_row, cost_row) for (x_row, cost_row) in zip(x_array, forecast_sales_r["marginal_investment"].values)]))
objective = solver.Minimize(total_cost, 1)
db = solver.Phase(x_array,
                solver.CHOOSE_FIRST_UNBOUND, # The VAR strategy
                solver.ASSIGN_MIN_VALUE)     # The Value strategy

# Create a solution collector.
collector = solver.LastSolutionCollector()
# Add decision variables
#collector.Add(x_array)

for i in range(num_workers):
    collector.Add(x_worker[i])

## I only want to do two promotions for easy soda category
## for now I will just ignore this constraint, 
## one soda onl once - true

max_prom

for code, price, ranger in zip([ra[0] for ra in list(product_prices)],[ra[1] for ra in list(product_prices)],range(len([ra[1] for ra in list(product_prices)]))):
    print(code, price)
    select_price[code, ranger] = solver.IntVar(ub=price, lb=price, name='X')